Mvc 拼接Html 导出 Excel(服务器不用安装呦!支持2007以上版本)
新公司,新接触,老方法,更实用。
之前接触过Webform,winfrom 的导出Excel方法 ,优点:省事。缺点:服务器必须安装Office
这几天做项目 和 大牛学习了一下 新的方法,自己加以总结。希望更多的博友获益。不多说 。先上图,后上源码。
很简单的MVC4 的页面 献丑了
效果图
你猜了对了 我用的是 ClosedXml、NPOI 不是很新的东西!
看看代码怎么实现吧 !
1、工厂封装直接调用:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
2、ClosedXmlExportHelp
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
3、NPOIExportHelp
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
4、Ascii 转化
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
5、EpplusExportHelp (office 2007 以上版本)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; } }
以上代码就是实现Export Excel的全部代码
思路:拼接字符串构造一个纯Html的结构。用rowspan colspan来跨行跨列,把Html当做参数直接传过去调用写好的导出方法
返回数组。保存 完成!很简单!
希望能帮助大家!我的可能不是最好的方法!但是我在尽力去想!希望广大的博友一起想!想出更好的方法解决中国的所有技术人员的困惑!如有想法请留下您的宝贵评论!
作者:Elite.net
出处:http://www.cnblogs.com/yhyjy/
新浪微博:http://weibo.com/u/2709913775
世上本无垃圾,所谓垃圾是你放错了地方!
每天积累一点点,那么理想便会距离我们越来越近!
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
出处:http://www.cnblogs.com/yhyjy/
新浪微博:http://weibo.com/u/2709913775
世上本无垃圾,所谓垃圾是你放错了地方!
每天积累一点点,那么理想便会距离我们越来越近!
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。