Aspose.cell操作Excel使用合集

Cells cells = sheet.Cells;

Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.IsBold = true;
style.Font.Name = "宋体";
style.Font.Size = 12;
//固定模板头
//居中、画边框、粗体、背景色为浅蓝
style = workbook.Styles[workbook.Styles.Add()];
style.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.ForegroundColor = System.Drawing.Color.FromArgb(191, 191, 191);
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;

//采样日期的格式
Style dateStyle = workbook.Styles[workbook.Styles.Add()];
dateStyle.Custom = "yyyy-MM-dd";

//采样时分的格式
Aspose.Cells.Style timeStyle = workbook.Styles[workbook.Styles.Add()];
timeStyle.Custom = "hh:mm";

//设置行高
cells.SetRowHeight(rowNum, 30);


sheet.FreezePanes(ROW_DATASTART, 0, ROW_DATASTART, 0); //冻结列头内容 
//列头设置统一样式      
style.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0);
Range dateDeatailRange = sheet.Cells.CreateRange(0, 0, ROW_DATASTART, colNum);
StyleFlag dateDeatailFlg = new StyleFlag();
dateDeatailFlg.All = true;
dateDeatailRange.ApplyStyle(style, dateDeatailFlg);/*设置单元格样式-方法2 */
dateDeatailRange.RowHeight = 20.0;//设置行高
var contentStyle = cells[i + 1, j].GetStyle();//得到原本样式
contentStyle.HorizontalAlignment = TextAlignmentType.Center;//在原本样式的基础上设置新的样式
cells[i + 1, j].SetStyle(contentStyle);  //重新设置

sheet.AutoFitColumns(); //自适应列宽
sheet.AutoFitRows(); //自适应行高

//隐藏不需要的行
cells.HideRow(ROW_LHCODEID);
 
//单元格设置值
sheet.Cells[rowNum, j].PutValue("内容");

//移除某列
sheet.Cells.DeleteColumn(9);

 

//自定义格式，按显示值来设置显示小数位
string strCustom = "##0";
if (disDegit > 0)
{
    strCustom += "." + "0".PadLeft(disDegit, '0');
}
Style cleStyle = sheet.Cells[rowNum, colNum].GetStyle();
cleStyle.Custom = strCustom;
sheet.Cells[rowNum, colNum].Value = AvgValueView;
//OR
sheet.Cells[rowNum, colNum].PutValue(AvgValueView, true);

 

Aspose.cell 给excel表格设置样式

 

//设置单元格样式 注意：样式可以被替换，多次设置（Range）会已最后一次为准
Aspose.Cells.Style cellStyle = cells[rowNum, colNum].GetStyle();
cellStyle.Font.Name = "宋体";
cellStyle.Font.Size = 11;
//设置边框
cellStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
cellStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cellStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
cellStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//设置背景色
cellStyle.Pattern = BackgroundType.Solid;
cellStyle.ForegroundColor = System.Drawing.Color.Red;
//背景色渐变
cellStyle.Pattern = BackgroundType.Solid;
var cl1 = System.Drawing.Color.FromArgb(0, 255, 255, 204);//RGB反序
var cl2 = System.Drawing.Color.FromArgb(0, 0, 0, 255);//RGB反序
cellStyle.SetTwoColorGradient(cl1, cl2, Aspose.Cells.Drawing.GradientStyleType.Vertical, 1);

 

  

 

 //批量插入DataTable数据，不插入表头，从A3行开始插入
int iCount = sheet.ImportDataTable(dtData, false, "A3");

//复制多行表头（CopyRow）
sheet.Cells.CopyRows(sheet.Cells, 0, 0, 2);

//复杂报表
WorkbookDesigner designer = new WorkbookDesigner();
DataTable dtExport = new DataTable();
dtExport.TableName = "A";
dtExport.Columns.Add("AreaName");
//加载模版
designer.Open(strSaveFilePath);
//导入Table数据
designer.SetDataSource(dtExport);
designer.Process();
//转换为workbook
Workbook workbook = designer.Workbook;
workbook.Worksheets[0].Name = "sheet1";
workbook.Worksheets[1].Name = "sheet2";

designer.ClearDataSource();

复杂报表参考：
https://www.cnblogs.com/wuhuacong/archive/2011/02/23/1962147.html
https://blog.csdn.net/kongwei521/article/details/41647747

 

//保存多种文件格式，包括Xlsx
workbook.Save("C:\\Test.xlsx", SaveFormat.Xlsx);
//转换为系统内存提供流式--输出流
workbook.SaveToStream();

//其他初始化格式
Workbook workbook = new Workbook(FileFormatType.Xlsx);
//Some code.
workbook.FileFormat = FileFormatType.Xlsx;