Aspose.Cell和NPOI生成Excel文件

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