注意的问题
1、DataTable的处理
2、进行编码，便于中文名文件下载
3、别忘了Aspose.Cells.dll（可以自己在网上搜索）
public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
} //为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(, , );
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true; int rowIndex = ;
for (int i = ; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++; foreach (DataRow row in datatable.Rows)
{
for (int i = ; i < datatable.Columns.Count; i++)
{
wb.Worksheets[].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
} for (int k = ; k < datatable.Columns.Count; k++)
{
wb.Worksheets[].AutoFitColumn(k, , );
}
wb.Worksheets[].FreezePanes(, , , datatable.Columns.Count);
wb.Save(filepath);
return true;
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
} } protected void btnExport_Click(object sender, EventArgs e)
{//导出
int ClassID = ;
int.TryParse(hidClassID.Value, out ClassID);
string error = "";
string filepath = "";
BLL.TUser bll_TUser = new BLL.TUser();
BLL.TClass bll_Class = new BLL.TClass();
Model.TClass model = (new BLL.TClass()).GetModel(ClassID); //处理DataTable
DataTable dt = bll_TUser.GetListByClass(ClassID);
DataTable dtNew = new DataTable();
dtNew.Columns.Add("姓名", typeof(string));
dtNew.Columns.Add("学号", typeof(string));
dtNew.Columns.Add("性别", typeof(string));
dtNew.Columns.Add("电话", typeof(string));
if (dt != null && dt.Rows.Count > )
{
DataRow drNew = dtNew.NewRow(); foreach (DataRow dr in dt.Rows)
{
//drNew = dtNew.NewRow();
drNew["姓名"] = dr["UserName"];
drNew["学号"] = dr["IDNO"];
drNew["性别"] = dr["Sex"].ToString() == "" ? "男" : (dr["Sex"].ToString() == "" ? "女" : "");
drNew["电话"] = dr["Phone"];
dtNew.Rows.Add(drNew.ItemArray);
}
} if (model != null)
{
filepath = "/UploadFiles/ExportClass/";// + model.ClassName + ".xlsx";
string filaname = model.ClassName + ".xlsx";
string finalPath = MapPath("~" + filepath + filaname);
//检查有该路径是否就创建
if (!Directory.Exists(MapPath("~/UploadFiles/ExportClass/")))
{
Directory.CreateDirectory(MapPath("~/UploadFiles/ExportClass/"));
}
if (DataTableToExcel2(dtNew, finalPath, out error))
{
string SiteRoot = "http://" + Request.Url.Authority.ToString() + filepath + Uri.EscapeDataString(filaname); //进行编码，便于中文名文件下载
//下载excel
ClientScript.RegisterStartupScript(this.GetType(), "", ",<script type='text/javascript'>window.open('" + SiteRoot + "');</script>");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('提示', '" + error + "！');</script>");
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('提示', '班级不存在！');</script>");
}
}
