C#生成Excel代码_Aspose.Cells导出ExcelC Demo
2016-10-24 12:50:14  By: dwtedx

相信大家在平时的开发过程中、难免会要乱Excel这种需求、今天不给大家介绍一种简单的方法来生成Excel、就是利用Aspose.Cells导出excel、实现代码很简单、但是需要注意以下3个问题

1、DataTable的处理

2、进行编码,便于中文名文件下载

3、别忘了Aspose.Cells.dll(可以自己在网上搜索)


DataTableToExcel2

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

}


btnExport_Click

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


若资源对你有帮助、浏览后有很大收获、不妨小额打赏我一下、你的鼓励是维持我不断写博客最大动力

想获取DD博客最新代码、你可以扫描下方的二维码、关注DD博客微信公众号(ddblogs)

或者你也可以关注我的新浪微博、了解DD博客的最新动态:DD博客官方微博(dwtedx的微博)

如对资源有任何疑问或觉得仍然有很大的改善空间、可以对该博文进行评论、希望不吝赐教

为保证及时回复、可以使用博客留言板给我留言: DD博客留言板(dwtedx的留言板)

感谢你的访问、祝你生活愉快、工作顺心、欢迎常来逛逛


快速评论


技术评论

  • 该技术还没有评论、赶快抢沙发吧...
DD记账
top
+