你的位置:首页 > ASP.net教程

[ASP.net教程]Aspose.Cells导出excel


利用Aspose.Cells导出excel

注意的问题

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(153, 204, 0);    style.Pattern = BackgroundType.Solid;    style.Font.IsBold = true;    int rowIndex = 0;    for (int i = 0; i < datatable.Columns.Count; i++)    {      DataColumn col = datatable.Columns[i];      string columnName = col.Caption ?? col.ColumnName;      wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);      wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);    }    rowIndex++;    foreach (DataRow row in datatable.Rows)    {      for (int i = 0; i < datatable.Columns.Count; i++)      {        wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());      }      rowIndex++;    }    for (int k = 0; k < datatable.Columns.Count; k++)    {      wb.Worksheets[0].AutoFitColumn(k, 0, 150);    }    wb.Worksheets[0].FreezePanes(1, 0, 1, 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 = 0;  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 > 0)  {    DataRow drNew = dtNew.NewRow();    foreach (DataRow dr in dt.Rows)    {      //drNew = dtNew.NewRow();      drNew["姓名"] = dr["UserName"];      drNew["学号"] = dr["IDNO"];      drNew["性别"] = dr["Sex"].ToString() == "1" ? "男" : (dr["Sex"].ToString() == "2" ? "女" : "");      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>");  }}