Aspose.Cells导出Excel（1）

2018-06-22 07:11:07来源：未知阅读 ()

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