关于c#导出多表头，超过256列，N多行的大数据Excel的方法

首先出于系统性能和用户体验的问题，我并不是很赞成这样疯狂的举动。但没办法，用户非要这么做，号称这样才直观，直观，直观！！！！

基本需求是这样的，如图：

最开始我在网上找到了三师兄的帖子https://www.cnblogs.com/sanshi/p/4104411.html

具体方法，三师兄已经写的非常详细，并且确实挺好用的。（如果列不多，并且导出的数据量不大，用这种方法还是很省心的）。但我问题是，客户需要全年的客户和商品数据统计，并且还需要按特定条件小计合计。如果按照上面帖子的方法根本解决不了我的问题。首先我的Excel列肯定会超过IV列，不可能用这种方法导出。

没办法只能另寻它法，但遗憾的是，网上并没有太完整的方法。

下面是我结合大大们的思路和代码，并且加入了一些自己的烂代码实现该功能的代码，由于系统是B/S结构，我不可能去依赖与浏览器给我做数据处理工作，因为那样只会把浏览器搞死。

首先构建表头，定义model

#region 表头数据model
        public class firstModel
        {
            public string text { get; set; }
        }

        public class LastModel 
        {
            public string text { get; set; }

            public List<LastItemModel> columns { get; set; }
        }

        public class LastItemModel 
        {
            public string text { get; set; }
        }
#endregion

 然后后台拼成我们要用的Jarray集合

#region 后台生成表头

            List<LastModel> Lastlist = new List<LastModel>();

            LastModel last;

            LastItemModel lastitem;

            last = new LastModel();

            last.text = "省区";

            Lastlist.Add(last);

            last = new LastModel();

            last.text = "客户名称";

            Lastlist.Add(last);

            if (data.Count() > 0)
            {
                foreach (var item in goodList)//goodList 自己用到的集合
                {
                    last = new LastModel();

                    last.text = item.GoodName;

                    var dataitemList = data.Where(o => o.GoodUUID == item.GoodUUID).GroupBy(o => new { o.GoodType }).Select(o => new { o.Key.GoodType }).OrderBy(o => o.GoodType);

                    List<LastItemModel> LastItemlist = new List<LastItemModel>();

                    foreach (var dataitem in dataitemList)
                    {
                        lastitem = new LastItemModel();

                        lastitem.text = dataitem.GoodType;

                        LastItemlist.Add(lastitem);
                    }

                    last.columns = LastItemlist;

                    Lastlist.Add(last);

                }
            }

            last = new LastModel();

            last.text = "总数";

            Lastlist.Add(last);

            var jsonSetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };

            var json = JsonConvert.SerializeObject(Lastlist,Formatting.Indented,jsonSetting);

            var testvalues = JArray.Parse(json);

            Session["headersummary"] = JArray.Parse(json);

            #endregion

为什么要用Jarray集合呢？往下看

那么我们不仅要有表头，还要有数据，这里我直接 sqlserver 存储过程反馈结果，因为代码循环加查寻结果。最后拼成想要的结果集的执行效率太低。

存储过程部分思路如下

SELECT
Province = MAX(e.Name),
CustomerName = MAX(d.Name),
GoodType = MAX(a.GoodID) + (CASE WHEN a.GoodType = '' THEN '现金' ELSE a.GoodType END),
OutAmount = SUM(a.OutAmount),
SumData = 0
INTO #saleOuthistory
FROM Data_SalesOutHistory a 
INNER JOIN #good c ON a.GoodUUID=c.ID
INNER JOIN #customer d ON a.CustomerUUID=d.ID
INNER JOIN #customerCategory e ON d.OldParentID = e.ID
WHERE a.AccountDate>=@beginDate AND a.AccountDate<=@endDate
GROUP BY e.ID, d.ID, a.GoodUUID, a.GoodType

INSERT INTO #saleOuthistory
SELECT 
Province = MAX(e.Name),
CustomerName = MAX(d.Name),
GoodType = MAX(a.GoodID),
OutAmount = NULL,
SumData = 0
FROM Data_SalesOutHistory a 
INNER JOIN #good c ON a.GoodUUID=c.ID
INNER JOIN #customer d ON a.CustomerUUID=d.ID
INNER JOIN #customerCategory e ON d.OldParentID = e.ID
WHERE a.AccountDate>=@beginDate AND a.AccountDate<=@endDate
GROUP BY a.GoodUUID

INSERT INTO #saleOuthistory 
SELECT Province,'总小计',GoodType,SUM(OutAmount),0 
FROM #saleOuthistory
GROUP BY Province,GoodType

INSERT INTO #saleOuthistory 
SELECT '总合计','',GoodType,SUM(OutAmount),0 
FROM #saleOuthistory
GROUP BY GoodType

SELECT SUM(OutAmount) as SumData,Province,CustomerName
INTO #sumdata
From #saleOuthistory GROUP BY Province,CustomerName

DELETE from #sumdata WHERE SumData IS NULL

update a set a.SumData = b.SumData
from #saleOuthistory a INNER JOIN #sumdata b on a.Province = b.Province and a.CustomerName = b.CustomerName

DELETE FROM #saleOuthistory where sumdata = 0

--生成表结果

--获取表格表头数据
create table #header  
(
ColumnID varchar(100)
)  

INSERT INTO #header 
SELECT DISTINCT GoodType FROM #saleOuthistory

DECLARE @GoodTypestr VARCHAR(MAX);
DECLARE @sql nvarchar(MAX)

SELECT @GoodTypestr = ISNULL(@GoodTypestr+',"','"')+ ColumnID +'"'
FROM #header ORDER BY ColumnID ASC

SET @sql='
    SELECT *
    FROM #saleOuthistory
    PIVOT(SUM(OutAmount) FOR [GoodType] IN('+@GoodTypestr+')) AS T ORDER BY Province,CustomerName'

Exec(@sql) 

DROP TABLE #header

DROP TABLE #saleOuthistory

DROP TABLE #sumdata

END

获取到数据集 

var result = _salesOutHistoryService.GetSalesOutHistoryGoodTypeBySql();

Session["dataTable"] = result.Tables[0];

然后就可以用三石大大的处理思路。

public ActionResult DownloadExcel()
        {

            DataTable source = (DataTable)Session["dataTable"];

            string TD_HTML = "<td>{0}</td>";

            StringBuilder sb = new StringBuilder();
            //sb.Append("<meta http-equiv=\"Content-Type\" content=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\"/>");
            sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");


            JArray header = JArray.Parse(JsonConvert.SerializeObject(Session["headersummary"]));

            MultiHeaderTable mht = new MultiHeaderTable();
            mht.ResolveMultiHeaderTable(header);

            foreach (List<object[]> rows in mht.MultiTable)
            {
                sb.Append("<tr>");
                foreach (object[] cell in rows)
                {
                    int rowspan = Convert.ToInt32(cell[0]);
                    int colspan = Convert.ToInt32(cell[1]);
                    JObject column = cell[2] as JObject;

                    sb.AppendFormat("<th{0}{1}{2}>{3}</th>",
                        rowspan != 1 ? " rowspan=\"" + rowspan + "\"" : "",
                        colspan != 1 ? " colspan=\"" + colspan + "\"" : "",
                        colspan != 1 ? " style=\"text-align:center;\"" : "",
                        column.Value<string>("text"));
                }
                sb.Append("</tr>");
            }

            var headerBody = (List<Columns>)Session["HeaderBody"];

            foreach (DataRow row in source.Rows)
            {
                sb.Append("<tr>");
                sb.AppendFormat(TD_HTML, row["Province"]);
                sb.AppendFormat(TD_HTML, row["CustomerName"]);

                foreach (var item in headerBody)
                {
                    sb.AppendFormat(TD_HTML, row[item.HeaderText]);
                }

                sb.AppendFormat(TD_HTML, row["sumData"]);
                sb.Append("</tr>");
            }
            sb.Append("</table>");

            var excel = ExcelUtil.SuperExportToExcel("", false, sb);

            return File(excel, "application/excel", "excel的名称" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx");
        }

当然这里还引用有方法 

呐~就是这段 。 具体请去参考链接中的代码思路。我就不再赘述。

下面是导出excel表的格式处理方法SuperExportToExcel("", false, sb)

#region 多表头导出2007excel 突破IV列
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="heading">工作簿Worksheet</param>
        /// <param name="showSrNo">是否显示行编号</param>
        /// <param name="columnsToTale">要导出的列</param>
        /// <returns></returns>
        public static byte[] SuperExportToExcel(string heading = "", bool showSrNo = false, StringBuilder columns = null, List<string> decimalColumns = null)
        {
            byte[] reslut = null;

            string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + columns;

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(xml);

            XmlNode table = doc.SelectSingleNode("/table");

            int colspan = 1;
            int rowspan = 1;

            int rowNum = 1;
            int columnNum = 1;

            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));

            int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 2;

            int rowCount = table.ChildNodes.Count;
            int colCount = FetchColCount(table.ChildNodes);

            bool[,] map = new bool[rowCount + 1, colCount + 1];
            MemoryStream stream = new MemoryStream();

            foreach (XmlNode row in table.ChildNodes)
            {
                columnNum = 1;
                foreach (XmlNode column in row.ChildNodes)
                {
                    if (column.Attributes["rowspan"] != null)
                    {
                        rowspan = Convert.ToInt32(column.Attributes["rowspan"].Value);
                    }
                    else
                    {
                        rowspan = 1;
                    }

                    if (column.Attributes["colspan"] != null)
                    {
                        colspan = Convert.ToInt32(column.Attributes["colspan"].Value);
                    }
                    else
                    {
                        colspan = 1;
                    }

                    while (map[rowNum, columnNum])
                    {
                        columnNum++;
                    }

                    if (rowspan == 1 && colspan == 1)
                    {
                        worksheet.Cells[rowNum, columnNum].Value = column.InnerText;
                    }
                    else
                    {
                        worksheet.Cells[rowNum, columnNum, rowNum + rowspan - 1, columnNum + colspan - 1].Merge = true;
                        worksheet.Cells[rowNum, columnNum].Value = column.InnerText;
                        for (int m = 0; m < rowspan; m++)
                        {
                            for (int n = 0; n < colspan; n++)
                            {
                                map[rowNum + m, columnNum + n] = true;
                            }
                        }
                    }
                    columnNum++;
                }
                rowNum++;
            }

            worksheet.DefaultRowHeight = 30;

            worksheet.Column(1).Width = 20;

            worksheet.Column(2).Width = 30;

            worksheet.Column(3).Width = 20;

            worksheet.Column(4).Width = 30;

            worksheet.DefaultColWidth = 16;

            using (ExcelRange r = worksheet.Cells[startRowFrom, 1, table.ChildNodes.Count, FetchColCount(table.ChildNodes)])
            {
                r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                r.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                r.Style.Border.Top.Style = ExcelBorderStyle.Thin;

                r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                r.Style.Border.Left.Style = ExcelBorderStyle.Thin;

                r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                r.Style.Font.Size = 12;

                r.Style.WrapText = true;

                r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);

                r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);

                r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

                r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
            }

            using (ExcelRange r = worksheet.Cells[startRowFrom, 1, startRowFrom + 1, FetchColCount(table.ChildNodes)])
            {
                r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                r.Style.Font.Color.SetColor(System.Drawing.Color.White);

                r.Style.Font.Bold = true;

                r.Style.Fill.PatternType = ExcelFillStyle.Solid;

                r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));

            }

            reslut = package.GetAsByteArray();

            return reslut;
        }
        

        static int FetchColCount(XmlNodeList nodes)
        {
            int colCount = 0;

            foreach (XmlNode row in nodes)
            {
                if (colCount < row.ChildNodes.Count)
                {
                    colCount = row.ChildNodes.Count;
                }
            }

            return colCount;
        }
        #endregion

由于本人水平有限，加上工期问题，上面这段代码兼容性不是很好，如果是三行表头，四行表头，还要根据实际情况进行调整。综上就可以导出最上面所展示的效果图。最高好像支持1w多列，多少行忘记了。

纯手工编辑，第一次写，可能表达的比较混乱，如有不妥请多多谅解。