Mvc 拼接Html 导出 Excel（服务器不用安装呦！支持2007以上版本）

　　新公司，新接触，老方法，更实用。

　　之前接触过Webform,winfrom 的导出Excel方法，优点：省事。缺点：服务器必须安装Office

　　这几天做项目和大牛学习了一下新的方法，自己加以总结。希望更多的博友获益。不多说。先上图，后上源码。

　　很简单的MVC4 的页面献丑了

效果图

　　你猜了对了我用的是 ClosedXml、NPOI 不是很新的东西！

　　看看代码怎么实现吧！

　　1、工厂封装直接调用：

 1   public class ExportFactory
 2     {
 3         public static byte[] exportToExcel(string type) 
 4         {
 5             byte[] bytes = null;
 6             switch (type.ToLower())
 7             {
 8                 case "npoi":
 9                     bytes = NpoiExcelHelp.GenerateXlsxBytes(GetHtml());
10                     break;
11                 case "closexml":
12                     bytes = ClosedXmlExcelHelp.GenerateXlsxBytes(GetHtml());
13                     break;
14                 default:
15                     break;
16             }
17             return bytes;
18         }
19 
20         static string GetHtml()
21         {
22             StringBuilder strHtml = new StringBuilder();
23             strHtml.Append("<table>");
24             strHtml.Append("<tr>");
25             strHtml.Append("<td rowspan='2'>First Row/First Colunm</td>");
26             strHtml.Append("<td>Second Row/First Colunm</td>");
27             strHtml.Append("</tr>");
28             strHtml.Append("<tr>");
29             strHtml.Append("<td>First Row/Second Colunm</td>");
30             strHtml.Append("</tr>");
31             strHtml.Append("<tr>");
32             strHtml.Append("<td>Second Row/Second Colunm</td>");
33             strHtml.Append("<td>Third Row/Second Colunm</td>");
34             strHtml.Append("</tr>");
35             strHtml.Append("</table>");
36             return strHtml.ToString();
37         }
38     }

View Code

　　2、ClosedXmlExportHelp

  1   public class ClosedXmlExcelHelp
  2     {
  3         public static byte[] GenerateXlsxBytes(string tableHtml)
  4         {
  5             string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + tableHtml;
  6 
  7             XmlDocument doc = new XmlDocument();
  8             doc.LoadXml(xml);
  9 
 10             XmlNode table = doc.SelectSingleNode("/table");
 11 
 12             int colspan = 1;
 13             int rowspan = 1;
 14 
 15             var workBook = new XLWorkbook();
 16             var ws = workBook.Worksheets.Add("Export");
 17 
 18             int rowNum;
 19             int columnNum;
 20 
 21             rowNum = 1;
 22             columnNum = 1;
 23 
 24             string mapKey = string.Empty;
 25             string mergKey = string.Empty;
 26 
 27             int rowCount = table.ChildNodes.Count;
 28             int colCount = 0;
 29 
 30             foreach (XmlNode row in table.ChildNodes)
 31             {
 32                 if (colCount < row.ChildNodes.Count)
 33                 {
 34                     colCount = row.ChildNodes.Count;
 35                 }
 36             }
 37 
 38             bool[,] map = new bool[rowCount + 1, colCount + 1];
 39 
 40             foreach (XmlNode row in table.ChildNodes)
 41             {
 42                 columnNum = 1;
 43                 foreach (XmlNode column in row.ChildNodes)
 44                 {
 45                     if (column.Attributes["rowspan"] != null)
 46                     {
 47                         rowspan = Convert.ToInt32(column.Attributes["rowspan"].Value);
 48                     }
 49                     else
 50                     {
 51                         rowspan = 1;
 52                     }
 53 
 54                     if (column.Attributes["colspan"] != null)
 55                     {
 56                         colspan = Convert.ToInt32(column.Attributes["colspan"].Value);
 57                     }
 58                     else
 59                     {
 60                         colspan = 1;
 61                     }
 62 
 63                     while (map[rowNum, columnNum])
 64                     {
 65                         columnNum++;
 66                     }
 67 
 68                     if (rowspan == 1 && colspan == 1)
 69                     {
 70                         ws.Cell(string.Format("{0}{1}", Char.Chr(columnNum), rowNum)).Value = column.InnerText;
 71                         map[rowNum, columnNum] = true;
 72                     }
 73                     else
 74                     {
 75                         ws.Cell(string.Format("{0}{1}", Char.Chr(columnNum), rowNum)).Value = column.InnerText;
 76                         mergKey =
 77                             string.Format("{0}{1}:{2}{3}",
 78                                 Char.Chr(columnNum), rowNum, Char.Chr(columnNum + colspan - 1), rowNum + rowspan - 1);
 79                         ws.Range(mergKey).Merge();
 80 
 81                         for (int m = 0; m < rowspan; m++)
 82                         {
 83                             for (int n = 0; n < colspan; n++)
 84                             {
 85                                 map[rowNum + m, columnNum + n] = true;
 86                             }
 87                         }
 88                     }
 89                     columnNum++;
 90                 }
 91                 rowNum++;
 92             }
 93 
 94             MemoryStream stream = new MemoryStream();
 95             workBook.SaveAs(stream);
 96 
 97             return stream.ToArray();
 98 
 99         }
100     }

View Code

　　3、NPOIExportHelp

  1   public class NpoiExcelHelp
  2     {
  3         public static byte[] GenerateXlsxBytes(string tableHtml)
  4         {
  5             string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + tableHtml;
  6 
  7             XmlDocument doc = new XmlDocument();
  8             doc.LoadXml(xml);
  9 
 10             XmlNode table = doc.SelectSingleNode("/table");
 11 
 12             int colspan = 1;
 13             int rowspan = 1;
 14 
 15             int rowNum;
 16             int columnNum;
 17             rowNum = 1;
 18             columnNum = 1;
 19 
 20             var workBook = new HSSFWorkbook();
 21             var ws = workBook.CreateSheet("Export");
 22 
 23             string mapKey = string.Empty;
 24             string mergKey = string.Empty;
 25 
 26             int rowCount = table.ChildNodes.Count;
 27             int colCount = FetchColCount(table.ChildNodes);
 28 
 29             InitSheet(ws, rowCount, colCount);
 30 
 31             bool[,] map = new bool[rowCount + 1, colCount + 1];
 32 
 33             foreach (XmlNode row in table.ChildNodes)
 34             {
 35                 columnNum = 1;
 36                 foreach (XmlNode column in row.ChildNodes)
 37                 {
 38                     if (column.Attributes["rowspan"] != null)
 39                     {
 40                         rowspan = Convert.ToInt32(column.Attributes["rowspan"].Value);
 41                     }
 42                     else
 43                     {
 44                         rowspan = 1;
 45                     }
 46 
 47                     if (column.Attributes["colspan"] != null)
 48                     {
 49                         colspan = Convert.ToInt32(column.Attributes["colspan"].Value);
 50                     }
 51                     else
 52                     {
 53                         colspan = 1;
 54                     }
 55 
 56                     while (map[rowNum, columnNum])
 57                     {
 58                         columnNum++;
 59                     }
 60 
 61                     if (rowspan == 1 && colspan == 1)
 62                     {
 63                         SetCellValue(ws, string.Format("{0}{1}", Char.Chr(columnNum), rowNum), column.InnerText);
 64                         map[rowNum, columnNum] = true;
 65                     }
 66                     else
 67                     {
 68                         SetCellValue(ws, string.Format("{0}{1}", Char.Chr(columnNum), rowNum), column.InnerText);
 69                         mergKey =
 70                             string.Format("{0}{1}:{2}{3}",
 71                                 Char.Chr(columnNum), rowNum, Char.Chr(columnNum + colspan - 1), rowNum + rowspan - 1);
 72                         MergCells(ws, mergKey);
 73 
 74                         for (int m = 0; m < rowspan; m++)
 75                         {
 76                             for (int n = 0; n < colspan; n++)
 77                             {
 78                                 map[rowNum + m, columnNum + n] = true;
 79                             }
 80                         }
 81                     }
 82                     columnNum++;
 83                 }
 84                 rowNum++;
 85             }
 86 
 87             MemoryStream stream = new MemoryStream();
 88             workBook.Write(stream);
 89 
 90             return stream.ToArray();
 91 
 92         }
 93 
 94         static int FetchColCount(XmlNodeList nodes)
 95         {
 96             int colCount = 0;
 97 
 98             foreach (XmlNode row in nodes)
 99             {
100                 if (colCount < row.ChildNodes.Count)
101                 {
102                     colCount = row.ChildNodes.Count;
103                 }
104             }
105 
106             return colCount;
107         }
108 
109         static void InitSheet(ISheet sheet, int rowCount, int colCount)
110         {
111             for (int i = 0; i < rowCount; i++)
112             {
113                 IRow row = sheet.CreateRow(i);
114                 for (int j = 0; j < colCount; j++)
115                 {
116                     row.CreateCell(j);
117                 }
118             }
119         }
120 
121         static void SetCellValue(ISheet sheet, string cellReferenceText, string value)
122         {
123             CellReference cr = new CellReference(cellReferenceText);
124             IRow row = sheet.GetRow(cr.Row);
125             ICell cell = row.GetCell(cr.Col);
126             cell.SetCellValue(value);
127         }
128 
129         static void MergCells(ISheet sheet, string mergeKey)
130         {
131             string[] cellReferences = mergeKey.Split(':');
132 
133             CellReference first = new CellReference(cellReferences[0]);
134             CellReference last = new CellReference(cellReferences[1]);
135 
136             CellRangeAddress region = new CellRangeAddress(first.Row, last.Row, first.Col, last.Col);
137             sheet.AddMergedRegion(region);
138         }
139     }

View Code

　　4、Ascii 转化

1  public class Char
2     {
3         public static string Chr(int i)
4         {
5             char c = (char)(64 + i);
6             return c.ToString();
7         }
8     }

View Code

　 5、EpplusExportHelp (office 2007 以上版本)

   public class EpplusExcelHelp
    {

        public static byte[] GenerateXlsxBytes2007(string tableHtml)
        {
            string xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + tableHtml;

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

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

            int colspan = 1;
            int rowspan = 1;

            int rowNum;
            int columnNum;
            rowNum = 1;
            columnNum = 1;

            ExcelPackage excel = new ExcelPackage();
            ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Export");

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

            bool[,] map = new bool[rowCount + 1, colCount + 1];
            MemoryStream stream = new MemoryStream();
            try
            {
                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.Cells.Style.ShrinkToFit = true;
            
                excel.SaveAs(stream);

                return stream.ToArray();
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            finally
            {
                stream.Close();
            }
        }

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

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

            return colCount;
        }
    }

View Code

　　以上代码就是实现Export Excel的全部代码

　思路:拼接字符串构造一个纯Html的结构。用rowspan colspan来跨行跨列，把Html当做参数直接传过去调用写好的导出方法

　返回数组。保存完成！很简单！

　希望能帮助大家！我的可能不是最好的方法！但是我在尽力去想！希望广大的博友一起想！想出更好的方法解决中国的所有技术人员的困惑！如有想法请留下您的宝贵评论！

Check Me Give You Source!

Mvc 拼接Html 导出 Excel（服务器不用安装呦！支持2007以上版本）

推荐阅读