DataTable的行列转换及多表头HTML表格转Excel

需要解决的问题：

1、根据数据库中多个不同字段名称的以行形式表现的数据转为以列形式展现的数据；

2、HTML多表头数据展示，做到数据“一个萝卜一个坑”，并求出多条数据的合计数据。

3、将HTML表格转为EXCEL。

先看效果图：

DataTable的行列转换及多表头HTML表格转Excel

图1 HTML页面表格

DataTable的行列转换及多表头HTML表格转Excel

图2 导出为Excel文档

DataTable的行列转换及多表头HTML表格转Excel

图3 数据行不同，表头列数也不相同

DataTable的行列转换及多表头HTML表格转Excel

图4 行数据记录不同时，表格的表头也变得不同（对比图1），各相关行数据需要一一对应。

HTML表格的结构：

</table>

//ExcelCellInfo.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace LEBLL.ExcelOutput
{
    /// <summary>
    /// Excel单元格信息
    /// </summary>
    public class ExcelCellInfo
    {
        /// <summary>
        /// 起始位置：X方向
        /// </summary>
        public int StartX = 1;

        /// <summary>
        /// 起始位置：Y方向
        /// </summary>
        public int StartY = 1;

        /// <summary>
        /// 单元格横向格数（占几列）
        /// </summary>
        public int ColSpan = 1;

        /// <summary>
        /// 单元格竖向格数（占几行）
        /// </summary>
        public int RowSpan = 1;

        /// <summary>
        /// 单元格内容
        /// </summary>
        public string Content = string.Empty;
    }
}

//ExcelGenerator.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using HtmlAgilityPack;

namespace LEBLL.ExcelOutput
{
    public class ExcelGenerator
    {
        private string excelFileName = "Excel";
        private int sheetCount = 1;
        ExcelTableInfo excelTableInfo;
        HtmlNode htmlNodeTable;
        public string TableID = "TableStatics";

        /// <summary>
        ///
        /// </summary>
        /// <param name="tableHTML">表格的HTML代码</param>
        public ExcelGenerator(string tableHTML)
        {
            //Excel文件名
            this.excelFileName = "电池批次统计";
            //Excel文件的sheet数量
            this.sheetCount = 1;

            excelTableInfo = new ExcelTableInfo();
            excelTableInfo.TableName = "电池批次统计信息";
            excelTableInfo.SheetIndex = 1;
            excelTableInfo.StartX = 1;
            excelTableInfo.StartY = 1;

            HtmlDocument htmlDoc = new HtmlDocument();
            htmlDoc.LoadHtml(tableHTML);
            htmlNodeTable = htmlDoc.DocumentNode.SelectSingleNode("//table[@id='"+ TableID + "']");
            excelTableInfo.HtmlNodeOfTable = htmlNodeTable;
        }

        public string Save(string fileDirectory)
        {
            ExcelWriter excelWriter = new ExcelWriter(this.sheetCount);
            List<ExcelCellInfo> cells = this.ConvertToCells(excelTableInfo.StartX, excelTableInfo.StartY + 1, htmlNodeTable);
            if (null != cells && cells.Count > 0)
            {
                excelWriter.Write(excelTableInfo.TableName, excelTableInfo.SheetIndex, cells);
            }

            string filePhysicalPath = System.Web.HttpContext.Current.Server.MapPath(fileDirectory);
            string excelName = Utility.GetUniqueFileName(filePhysicalPath, this.excelFileName + ".xls");

            excelWriter.Save(filePhysicalPath, excelName);

            return excelName;
        }

        private List<ExcelCellInfo> ConvertToCells(int pnStartX, int pnStartY, HtmlNode htmlNodeOfTable)
        {
            HtmlNodeCollection trNodes = htmlNodeOfTable.SelectNodes("tr");
            List<ExcelCellInfo> listCells = new List<ExcelCellInfo>();
            for (int y = 0; y < trNodes.Count; y++)
            {
                HtmlNodeCollection tdNodes = trNodes[y].SelectNodes("td");
                for (int x = 0; x < tdNodes.Count; x++)
                {
                    ExcelCellInfo newCell = new ExcelCellInfo();
                    newCell.StartY = pnStartY + y;
                    newCell.StartX = pnStartX + this.HorizontalDeduction(tdNodes[x]);
                    newCell.ColSpan = tdNodes[x].GetAttributeValue("colspan", 1);
                    newCell.RowSpan = tdNodes[x].GetAttributeValue("rowspan", 1);
                    string content = tdNodes[x].InnerText.Trim();
                    newCell.Content = ((x == 0) ? "'" + content : content);

                    listCells.Add(newCell);
                }
            }

            this.VerticalDeduction(listCells);

            return listCells;
        }

        /// <summary>
        /// 第一次colspan推演，即横向
        /// </summary>
        /// <param name="htmlNodeOfTd"></param>
        /// <returns></returns>
        private int HorizontalDeduction(HtmlNode htmlNodeOfTd)
        {
            HtmlNode htmlNodPreSibling = htmlNodeOfTd.PreviousSibling;
            while (htmlNodPreSibling != null && htmlNodPreSibling.Name != htmlNodeOfTd.Name)
            {
                htmlNodPreSibling = htmlNodPreSibling.PreviousSibling;
            }

            if (htmlNodPreSibling != null)
            {
                int colSpan = htmlNodPreSibling.GetAttributeValue("colspan", 1);
                return HorizontalDeduction(htmlNodPreSibling) + colSpan;
            }

            return 0;
        }

        /// <summary>
        /// 第二次推演，即纵向
        /// </summary>
        /// <param name="listCells"></param>
        private void VerticalDeduction(List<ExcelCellInfo> listCells)
        {
            for (int i = 0; i < listCells.Count; i++)
            {
                ExcelCellInfo currentCell = listCells[i];

                bool bActedPush = false;

                do
                {
                    int comparedIndex = -1;
                    for (int j = i - 1; j >= 0; j--)
                    {
                        if (listCells[j].StartX == currentCell.StartX)
                        {
                            comparedIndex = j;
                            break;
                        }
                    }

                    if (comparedIndex >= 0)
                    {
                        if (listCells[comparedIndex].RowSpan > (currentCell.StartY - listCells[comparedIndex].StartY))
                        {
                            currentCell.StartX += listCells[comparedIndex].ColSpan;

                            bActedPush = true;

                            for (int k = i + 1; k < listCells.Count; k++)
                            {
                                if (listCells[k].StartY == currentCell.StartY)
                                {
                                    listCells[k].StartX += listCells[comparedIndex].ColSpan;
                                }
                            }
                        }
                        else
                        {
                            bActedPush = false;
                        }
                    }
                    else
                    {
                        bActedPush = false;
                    }
                }
                while (bActedPush);
            }
        }
    }
}

//Utility.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;
using System.Web;

namespace LEBLL.ExcelOutput
{
    public class Utility
    {
        /// <summary>
        /// 在指定目录以指定文件名为基础，取得系列文件名。
        /// 如果已存在指定文件，则生成: path\fileName_(当前数字+1).后缀名。
        /// </summary>
        /// <param name="path">指定目录</param>
        /// <param name="fileName">指定文件名</param>
        /// <returns>生成: path\fileName_(当前数字+1).后缀名</returns>
       public static string GetUniqueFileName(string path, string fileName)
       {
           string file = fileName;
           string ext = null;
           int n = 1;
           int lastDotIndex = 0;
           lastDotIndex = fileName.LastIndexOf(".");
           if (lastDotIndex == -1) {
               ext = "";
           } else {
               ext = file.Substring(lastDotIndex);
               file = file.Substring(0, lastDotIndex);
           }
           string fileOrg = file;
           while (System.IO.File.Exists(path + "\\" + file + ext)) {
                file = fileOrg + "_" + n;
               n = n + 1;
           }
           return file + ext;
       }

        /// <summary>
        /// 下载文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="fileName"></param>
        /// <param name="fileContentType"></param>
        public static void DownLoadFile(string path, string fileName, string fileContentType)
        {
            string absolutePath = System.Web.HttpContext.Current.Server.MapPath(path);
            FileInfo fi = new FileInfo(absolutePath + "\\" + fileName);
            HttpResponse contextResponse = HttpContext.Current.Response;
            contextResponse.Clear();
            contextResponse.Buffer = true;
            contextResponse.Charset = "UTF-8";
            contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));       //定义输出文件和文件名
            contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
            contextResponse.ContentEncoding = Encoding.UTF8;
            contextResponse.ContentType = fileContentType;

            contextResponse.WriteFile(fi.FullName);
            contextResponse.Flush();
            contextResponse.End();
        }
    }
}

//ExcelTableInfo.cs
using System;
using System.Collections.Generic;
using System.Text;
using HtmlAgilityPack;

namespace LEBLL.ExcelOutput
{
    public class ExcelTableInfo
    {
        public string TableName = string.Empty;
        public int SheetIndex = 1;
        public int StartX = 1;
        public int StartY = 1;
        public HtmlNode HtmlNodeOfTable = null;
    }
}

// ExcelWriter.cs
using System;
using System.Collections.Generic;
using System.Text;
using HtmlAgilityPack;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Drawing;

namespace LEBLL.ExcelOutput
{
    public class ExcelWriter
    {
        private Application _excelApp = null;
        private Workbook _excelWorkbook = null;
        private object _missingV = Missing.Value;

        public ExcelWriter(int sheetCount)
        {
            this._excelApp = new Application();
            this._excelApp.SheetsInNewWorkbook = sheetCount;
            this._excelWorkbook = this._excelApp.Workbooks.Add(this._missingV);
        }

        public void Write(string tableName,int pnAtWhickSheet, List<ExcelCellInfo> listCells)
        {
            Worksheet currentSheet = this._excelWorkbook.Sheets[pnAtWhickSheet] as Worksheet;

            foreach (ExcelCellInfo ec in listCells)
            {
                currentSheet.Cells[ec.StartY, ec.StartX] = ec.Content;
                currentSheet.Range[currentSheet.Cells[ec.StartY, ec.StartX], currentSheet.Cells[ec.StartY + ec.RowSpan - 1, ec.StartX + ec.ColSpan - 1]].Merge(this._missingV);
            }

            //表区域边框颜色
            Range rangeTable = currentSheet.Range[currentSheet.Cells[listCells[0].StartY, listCells[0].StartX], currentSheet.Cells[listCells[listCells.Count - 1].StartY + listCells[listCells.Count - 1].RowSpan - 1, listCells[listCells.Count - 1].StartX + listCells[listCells.Count - 1].ColSpan - 1]];
            rangeTable.Borders.Color = ColorTranslator.ToOle(Color.Black);

            //表名称
            currentSheet.Cells[listCells[0].StartY - 1, listCells[0].StartX] = tableName;
            Range rangeTableTitle = currentSheet.Range[currentSheet.Cells[listCells[0].StartY - 1, listCells[0].StartX], currentSheet.Cells[listCells[0].StartY - 1, listCells[listCells.Count - 1].StartX + listCells[listCells.Count - 1].ColSpan - 1]];
            rangeTableTitle.Merge(this._missingV);
            rangeTableTitle.Font.Bold = true;
            rangeTableTitle.HorizontalAlignment = XlVAlign.xlVAlignCenter;
        }

        /// <summary>
        /// 保存Excel到指定目录下的指定文件中
        /// </summary>
        /// <param name="filePhysicalPath">指定目录</param>
        /// <param name="excelFileName">Excel文件名</param>
        public void Save(string filePhysicalPath, string excelFileName)
        {
            try
            {
                this._excelWorkbook.RefreshAll();
                this._excelWorkbook.SaveAs(filePhysicalPath + "\\" + excelFileName, this._missingV, this._missingV, this._missingV, this._missingV, this._missingV, XlSaveAsAccessMode.xlNoChange, this._missingV, this._missingV, this._missingV, this._missingV, this._missingV);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                this._excelWorkbook.Close(false, this._missingV, this._missingV);
                this._excelApp.Quit();
                this._excelApp = null;
                GC.Collect();
            }
        }
    }
}

//调用时：
        private void BuildTable()
        {
            SetDefaultStartEndTime();
            TableExporter tableExporter = new TableExporter();
            tableExporter.TableID = tableID;
            string htmlTable = tableExporter.BuildTable(this.OrderNo, this.StartTime, this.EndTime);
            PanelTable.Html = htmlTable;
            TextField_TableHtml.Text = Server.HtmlEncode(htmlTable);

            string resultTimeCost = string.Format("{0}，总耗时：{1}。", "产生数据", tableExporter.TimeCost);
            Label_TimeCost.Text = resultTimeCost;
        }

        protected void ExportExcel(object sender, EventArgs e)
        {
            string directory = "~/ExcelFile";
            // 表格的HTML代码
            string tableHTML = Server.HtmlDecode(TextField_TableHtml.Text);
            ExcelGenerator excelGenerator = new ExcelGenerator(tableHTML);
            excelGenerator.TableID = tableID;
            string excelName = excelGenerator.Save(directory);

            if (excelName != null)
            {
                Utility.DownLoadFile(directory, excelName, "application/ms-excel");
            }
        }

（写作中，未完待续）

秒客网

DataTable的行列转换及多表头HTML表格转Excel

相关文章