c# Aspose.Cells 导出Excel表格
使用aspose.ce
/// <summary>
/// 导出Excel表格
/// </summary>
/// <param name="list">数据集合</param>
/// <param name="header">数据表头</param>
/// <param name="sumColumn">求和列</param>
/// <param name=" title"> 标题</param>
/// <returns></returns>
public static void ExportExcel(DataTable dt, string[] header, string sumColumn=null, string title = null)
{
//Workbook wb = new Workbook(FileFormatType.Xlsx);
Workbook wb = new Workbook();
try
{
Worksheet sheet = wb.Worksheets[0];
sheet.Name = "MO上行查询结果";
if (dt.Rows.Count <= 0)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('没有检测到需要导出数据!');</script>");
return;
}
// 为单元格添加样式
Aspose.Cells.Style style = wb.CreateStyle();
//style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //设置居中
style.Font.Size = 20;//文字大小
style.Font.IsBold = true;//粗体
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
// 为合计单元格样式
Aspose.Cells.Style sumstyle = wb.CreateStyle();
sumstyle.Font.Size = 12;//文字大小
sumstyle.Font.IsBold = true;//粗体
sumstyle.Font.Color = Color.Red; //颜色
//style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
//添加标题
int rowIndex = 0;
sheet.Cells.SetRowHeight(rowIndex, 40); //设置行高
if (title!=null)
{
sheet.Cells[rowIndex, 0].PutValue(title);
sheet.Cells[rowIndex, 0].SetStyle(style);
sheet.Cells.Merge(0, 0, 1, header.Length); //合并行 Merge(1, 1,1, 5); 第一行 第一列 到 第一行 第 5列
//Merge切记后两位参数不能是 0
rowIndex++;
}
//添加表头
style.Font.Size = 12;//文字大小
sheet.Cells.SetRowHeight(rowIndex, 20); //设置行高
for (int c = 0; c < header.Length; c++)
{
sheet.Cells[rowIndex, c].PutValue(header[c]);
sheet.Cells[rowIndex, c].SetStyle(style);
sheet.Cells.SetColumnWidth(c, 20);//设置宽度
//设置求和列公试
if (sumColumn != null && sumColumn.IndexOf(header[c]) > -1)
{
string sn = sheet.Cells[rowIndex + 1, c].Name;
string en = sheet.Cells[rowIndex + dt.Rows.Count, c].Name;
sheet.Cells[rowIndex + dt.Rows.Count + 1, c].SetStyle(sumstyle);
sheet.Cells[rowIndex + dt.Rows.Count + 1, c].Formula = "=SUM(" + sn + ":" + en + ")"; //设置单元格公式
}
}
rowIndex++;
//添加内容
for (int r = 0; r < dt.Rows.Count; r++)//遍历DataTable行
{
for (int c = 0; c < header.Length; c++) //列只遍历到和表头数量相等的列,其余的列不导出
{
//自动把数字变量转出后 单元格变为数字类型。
try
{
Convert.ToDecimal(dt.Rows[r][c]);
sheet.Cells[r + rowIndex, c].PutValue(Convert.ToDecimal(dt.Rows[r][c])); //要导出数字列是数字类型,必须把添加变量转数字类型。
}
catch { sheet.Cells[r + rowIndex, c].PutValue(dt.Rows[r][c].ToString()); }
}
}
//输出到Excel 直接输出到浏览器
wb.Save(HttpContext.Current.Response, DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
//保存成97或2003写法
//workbook.Save(HttpContext.Current.Response, “output.xls”, ContentDisposition.Attachment, New XlsSaveOptions(SaveFormat.Excel97To2003))
HttpContext.Current.Response.End(); //一定要结束HttpContext.Current.Response
// 释放Excel对象
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(wb);
}
catch (Exception e)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('导出异常:" + e.Message + "!');</script>");
}
}