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在下篇写