欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

c# Datatable导出Excel

程序员文章站 2022-04-25 18:25:50
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.L... ......
using npoi.ss.usermodel;
using npoi.xssf.usermodel;
using system;
using system.collections.generic;
using system.data;
using system.io;
using system.linq;
using newtonsoft.json;
namespace easyframe.common
{
    /// <summary>
    ///title调用信息
    /// </summary>
    public class charttitle
    {
        /// <summary>
        /// 调用名
        /// </summary>
        public string callname { get; set; }
        /// <summary>
        /// 标头信息
        /// </summary>
        public list<chartname> setnamelist { get; set; }
    }
    public class chartname
    {
        /// <summary>
        /// 一级标头
        /// </summary>
        public string name { get; set; }
        /// <summary>
        /// 二级标头
        /// </summary>
        public list<string> chartchildnamelist { get; set; }

    }

    public class excelhelper
    {

        //
        /// <summary>
        /// datatable导出excel
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="list">表头</param>
        /// <param name="counts">总条数</param>
        /// <param name="filepath">保存地址</param>
        /// <returns></returns>
        public static memorystream writeexcel(datatable dt, list<charttitle> objlist, string filepath)
        {
            var list = objlist.firstordefault(a => a.callname == "ccc").setnamelist;
            if (!string.isnullorempty(filepath) && null != dt && dt.rows.count > 0)
            {
                xssfworkbook book = new xssfworkbook();
                npoi.ss.usermodel.isheet sheet = book.createsheet(dt.tablename);
                icell cell = null;
                icellstyle style = book.createcellstyle();
                ifont font = book.createfont();//创建字体样式  
                ifont fonts = book.createfont();//创建字体样式  
                ifont fontss = book.createfont();//创建字体样式  
                font.color = npoi.hssf.util.hssfcolor.red.index;//设置字体颜色
                icellstyle styles = book.createcellstyle(); //红色
                fonts.color = npoi.hssf.util.hssfcolor.green.index;
                icellstyle styless = book.createcellstyle(); //绿色
                fontss.color = npoi.hssf.util.hssfcolor.blue.index;
                icellstyle stylesss = book.createcellstyle(); //蓝色
                styless.setfont(fonts);
                styles.setfont(font);
                stylesss.setfont(fontss);
                style.alignment = horizontalalignment.center;
                npoi.ss.usermodel.irow row1 = sheet.createrow(0);
                npoi.ss.usermodel.irow row2 = sheet.createrow(1);
                var row = row1;
                int num = 0;//记录列
                var d = true;//是否第一次加载到二级表单
                #region  创建表头
                //两行单列
                for (int i = 0; i < list.count; i++)
                {
                    if (!d)
                    {
                        row = row1;
                        d = true;
                    }
                    if (list[i].chartchildnamelist == null)
                    {
                        cell = row.createcell(num);
                        sheet.setcolumnwidth(num, 15 * 256);
                        cell.setcellvalue(list[i].name);
                        cell.cellstyle = style;
                        sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 1, num, num));
                        num++;
                        continue;
                    }
                    //两行多列
                    var count = list[i].chartchildnamelist.count; //列数
                    cell = row.createcell(num);
                    cell.setcellvalue(list[i].name);
                    cell.cellstyle = style;
                    sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, num, count + num - 1));
                    if (d)
                    {
                        d = false;
                        row = row2;
                    }
                    var t = 0;
                    var rang = sheet.getrow(1);
                    for (int j = 0; j < count; j++)
                    {
                        sheet.setcolumnwidth(num + t, 5 * 256);
                        cell = row.createcell(num + t);
                        cell.setcellvalue(list[i].chartchildnamelist[j]);
                        cell.cellstyle = style;
                        t++;
                    }
                    num = num + count;
                }
                #endregion 创建表头
                #region 写入数据
                int customs = 0;//记录列数
                int clouns = 0;//记录行数



                filepath = string.format(filepath, "xxx");
                #region 导出操作
                var data1 = jsonconvert.deserializeobject<list<类名>>(dt.rows[0][0].tostring());
                foreach (var item in data1)
                {
                    npoi.ss.usermodel.irow rows = sheet.createrow(clouns + 2);
                    customs = 0;
                    rows.createcell(customs).setcellvalue(convert.tostring(item.no)); customs++;
                    rows.createcell(customs).setcellvalue(convert.tostring(item.lotteryopenno)); customs++;
                    for (int i = 0; i < item.wan.length; i++)
                    {
                        if (item.lotteryopenno.split(',')[0] == item.wan[i].tostring())
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.wan[i]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.wan[i]); customs++;
                        }
                    }
                    for (int j = 0; j < item.qian.length; j++)
                    {
                        if (item.lotteryopenno.split(',')[1] == item.qian[j].tostring())
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.qian[j]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.qian[j]); customs++;
                        }
                    }
                    for (int k = 0; k < item.bai.length; k++)
                    {
                        if (item.lotteryopenno.split(',')[2] == item.bai[k].tostring())
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.bai[k]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.bai[k]); customs++;
                        }
                    }
                    for (int l = 0; l < item.shi.length; l++)
                    {
                        if (item.lotteryopenno.split(',')[3] == (item.shi[l].tostring()))
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.shi[l]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.shi[l]); customs++;
                        }
                    }
                    for (int m = 0; m < item.ge.length; m++)
                    {
                        if (item.lotteryopenno.split(',')[4] == (item.ge[m].tostring()))
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.ge[m]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.ge[m]); customs++;
                        }
                    }
                    for (int n = 0; n < item.fen.length; n++)
                    {
                        if (item.lotteryopenno.contains(item.fen[n].tostring()))
                        {
                            cell = rows.createcell(customs);
                            cell.setcellvalue(item.fen[n]);
                            cell.cellstyle = styles;
                            customs++;
                        }
                        else
                        {
                            rows.createcell(customs).setcellvalue(item.fen[n]); customs++;
                        }

                    }
                    clouns++;
                }

                #endregion


                #endregion 写入数据
                // 写入到客户端  
                using (system.io.memorystream ms = new system.io.memorystream())
                {
                    book.write(ms);
                    using (filestream fs = new filestream(filepath, filemode.create, fileaccess.write))
                    {
                        byte[] data = ms.toarray();
                        fs.write(data, 0, data.length);
                        fs.flush();
                    }
                    book = null;
                    return ms;
                }
            }
            return null;
        }



    }
}