首页 > 代码库 > Aspose.Cells导出excel

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>");    }}

技术分享 

Aspose.Cells导出excel