Aspose.Cell和NPOI生成Excel文件

2022-12-26,,,,

1、使用Aspose.Cell生成Excel文件,Aspose.Cell是.NET组件控件,不依赖COM组件

  1首先一点需要使用新建好的空Excel文件做模板,否则容易产生一个多出的警告Sheet

1 Workbook workBookTemp = ExcelHelper.LoadTemplateFile(HttpContext.Current.Server.MapPath("~/../xxx.xlsx"));
2 Workbook workBook = new Workbook();
3 workBook.Copy(workBookTemp);
ExcelHelper.LoadTemplateFile实现的就是
Workbook workBookTemp = new Workbook(HttpContext.Current.Server.MapPath("~/..."))

  

2、给Excel每一行添加样式,两种标题样式,小标题样式类似。

  

Aspose.Cells.Style styleTitle = StyleTitle(workBook,TextAlignmentType.Center);
Aspose.Cells.Style styleSmallTitle = StyleSmallTitle(workBook,TextAlignmentType.Center);
//这里把两种样式封装了方法
private Aspose.Cells.Style StyleTitle(Workbook book, TextAlignmentType tape)
{
Aspose.Cells.Style styleTitle = book.Styles[book.Styles.Add()];//新增样式
styleTitle.HorizontalAlignment = tape;//文字居中
styleTitle.VerticalAlignment = tape;
styleTitle.ForegroundColor = System.Drawing.Color.Silver;
styleTitle.IsTextWarpped = true;
styleTitle.Pattern = BackgroundType.Solid;
styleTitle.IsLocked = true;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;//左边界线
styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
styleTitle.Font.Name = "微软雅黑";
styleTitle.Font.Size = 10;
styleTitle.Font.IsBold = true;
return styleTitle;
}

  3 Aspose.Cells会自动有一个Sheet表单,因此可以直接使用,list是需要导入到表的数据。

workBook.Worksheets[0].Name = "预警报表";
Worksheet sheet = workBook.Worksheets[0];
sheet.AutoFitRows();
EarlyExcel(sheet,list,styleTitle,styleSmallTitle);

  4如果需要多个Sheet表单,就需要循环创建sheet;

int index = 0;
foreach( var item in DataList)
{
index++;
Worksheet sheet = workBook.Worksheets[index];
....
}

  5创建表头,并添加单元格样式

private void EarlyExcel(Worksheet sheet, List<Model> list, Aspose.Cells.Style styleTitle, Aspose.Cells.Style styleSmallTitle )
{
Cells cells = sheet.Cells;
cells[0,0].PutValue("name1");
cells[0,1].PutValue("name2");
.....
//设置列宽,可以提出来一个方法
int columnCount = cells.MaxColumn;
int rowCount = cells.MaxRow;
for(int col = 0; col < columnCount; col++)
{
sheet.AutoFitColumn(col,0,rowCount);
}
for(int col = 0; col < columnCount; col++)
{
if(col == 2)
cells.SetColumnWidth(2,50);
else if(col>=0 && col <= 5 && col !=2)
cells.SetColumnWidthPixel(col,cells.GetColumnWidthPixel(col) + 60);
else
cells.SetColumnWidthPixel(col,cells.GetColumnWidthPixel(col) + 30);
}
//插入值
for(int i = 0; i < list.Count; i ++)
{
cells[i+1,0].PutValue(list[i].Name1);
cells[i+1,2].PutValue(list[i].Name2);
......
       //写入公式的方法
       cells[i+1,3].Formula = "SUM(A1:B1)";
       cells[i+1,4].Formula = "=AVERAGE(B1:E1)";
}
//使用写好的样式
for(int j = 0;j < sheet.Cells.MaxDataColumn + 1;j++)
{
cells[i+1,j].SetStyle(styleSmallTitle);
cells.SetRowHeight(i + 1,17);
}
}

  6到这里Excel表单创建完毕,因为我导出的Excel中需要插入图片。下面列出插入图片的方法。

//创建的图片
int pictureIndex = sheet.Pictures.Add(rownum,columnnum,Server.MapPath("~/Images/xxx.png"));
setPictureSize(sheet,pictureIndex); private void setPictureSize(Worksheet sheet,int index)
{
//使用图片
Aspose.Cells.Drawing.Picture picture = sheet.Pictures[index];
//调图片位置和宽高
picture.UpperDeltaX = 400;
picture.UpperDeltaY = 0;
picture.Hieght = 17;
picture.Width = 17;
}

  7写入到流

System.IO.MemoryStream fileStream = SaveToStream(workBook);

public static System.IO.MemoryStream SaveToStream(Workbook workbook)
{
  
//刷新全部公式单元格--当生成的单元格,在修改单元格数据时,需要动态计算取得值的时候使用
workbook.CalculateFormula(true); //输出到数据流
System.IO.MemoryStream stream = new System.IO.MemoryStream();
workbook.Save(stream,SaveFormat.Excel97To2003); return stream;
}

  8输出Excel

SaveToExcel("预警报表",fileStream,DateTime.Now.ToString(),HttpContext.Current);

public void SaveToExcel(string fileName , System.IO.MemoryStream fileStream,string time, HttpContext context)
{
context.Response.Clear();
context.Response.AppendHeader("Content-Disposition",("attachment;filename="+HttpUtility.UrlEncode(fileName.ToString() + "-" + DateTime.Parse(time).ToString("yyyyMMdd") + ".xls")+""));
context.Response.ContentType = "application/octet-stream";
context.Response.ContentEncoding = System.Text.Encoding.UTF7;
context.Response.Charset = "gb2312";
context.Response.BinaryWrite(fileStream.ToArray());
context.Response.End();
}

NPOI在下篇写

  

Aspose.Cell和NPOI生成Excel文件的相关教程结束。

《Aspose.Cell和NPOI生成Excel文件.doc》

下载本文的Word格式文档,以方便收藏与打印。