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