.net mvc 使用 aspose.cells导出数据

public class AsposeCellsHelper
{
public Workbook workBook;
public Worksheet worksheet;
Style style; /// <summary>
/// 构造函数，不设置工作表名称
/// </summary>
public AsposeCellsHelper()
{
workBook = new Workbook();
worksheet = workBook.Worksheets[0];
} /// <summary>
/// 构造函数，设置工作表名称
/// </summary>
public AsposeCellsHelper(string name)
{
workBook = new Workbook(); if (string.IsNullOrEmpty(name))
{
name = DateTime.Now.ToString("yyyyMMddHHmmssffff");
} if (workBook.Worksheets.Count > 0)
{
worksheet = workBook.Worksheets[0];
worksheet.Name = name;
}
else
{
worksheet = workBook.Worksheets.Add(name);
}
} /// <summary>
/// 创建工作表，并将对象切换为当前创建的工作表
/// </summary>
/// <param name="name"></param>
public void CreateSheet(string name)
{
if (string.IsNullOrEmpty(name))
{
name = DateTime.Now.ToString("yyyyMMddHHmmssffff");
} worksheet = workBook.Worksheets.Add(name);
} //设置单元格样式
public void SetCellStyle(int fontSize, bool IsBold)
{
if (style == null)
{
style = workBook.DefaultStyle;
} //style.BackgroundColor = backgroundColor;
style.Font.Name = "宋体";//文字字体
style.Font.Size = fontSize;//文字大小
//前景色
style.ForegroundColor = Color.White;
style.Font.IsBold = IsBold;//粗体
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
//线条样式
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//线条颜色
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
} //设置值
public void SetValue(int x, int y, string value,int rowHeight = 20)
{
//设置值
worksheet.Cells[x, y].PutValue(value, false);
//设置样式
worksheet.Cells[x,y].SetStyle(style);
//设置行高
worksheet.Cells.SetRowHeight(x, rowHeight);
} /// <summary>
/// 填充数据到excel，数据填充可自行添加或继承后扩展方法
/// </summary>
/// <param name="dt"></param>
public void SetDataByTable(DataTable dt,string title)
{
DataColumnCollection columns = dt.Columns; int addIndex = 0; //表格标题
if (!string.IsNullOrEmpty(title))
{
addIndex = 1;
SetCellStyle(16, true);
worksheet.Cells.Merge(0, 0, 1, columns.Count);
SetValue(0, 0, title, 30);
} //表头
if (columns.Count > 0)
{
int columnIndex = 0; foreach (DataColumn dc in columns)
{
SetCellStyle(14, true);
SetValue(0 + addIndex, columnIndex, dc.ColumnName, 25);
columnIndex += 1;
}
} //数据
if (dt.Rows.Count > 0)
{
int rowIndex = 1 + addIndex; foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < columns.Count; i++)
{
SetCellStyle(12, false);
SetValue(rowIndex, i, dr[i].ToString());
} rowIndex += 1;
}
} worksheet.AutoFitColumns();
} /// <summary>
/// 导出excel
/// </summary>
/// <param name="name"></param>
public void ExportExcel(string name)
{
try
{
HttpResponse Response = System.Web.HttpContext.Current.Response; if (Response != null)
{
if (string.IsNullOrEmpty(name))
{
name = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
} // Save in Xlsx format
workBook.Save(Response, name, ContentDisposition.Attachment, new OoxmlSaveOptions());
Response.End();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//可以不写dispose
worksheet.Dispose();
workBook.Dispose();
}
}
}

public void Export()
{
AsposeCellsHelper aspose = new AsposeCellsHelper("测试导出"); DataTable dt = new DataTable("cart");
DataColumn dc3 = new DataColumn("number", Type.GetType("System.Int16"));
DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("point", Type.GetType("System.Int16"));
DataColumn dc4 = new DataColumn("totalpoint", Type.GetType("System.Int64"));
DataColumn dc5 = new DataColumn("prizeid", Type.GetType("System.String"));
dt.Columns.Add(dc3);
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
//以上代码完成了DataTable的构架，但是里面是没有任何数据的
for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["prizename"] = "娃娃";
dr["point"] = 10;
dr["number"] = i;
dr["totalpoint"] = 10;
dr["prizeid"] = "001";
dt.Rows.Add(dr);
} //aspose.SetDataByTable(dt,string.Empty);
aspose.SetDataByTable(dt, "测试标题");//标题可以传空，可以修改为重载，或者修改SetDataByTable方法的最后一个参数，将其加上默认值，重载的一个变种
aspose.ExportExcel("测试.xlsx");//名称可以传空
}