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