首页 > 代码库 > Mvc 导出 Excel（服务器不用安装呦！）
Mvc 导出 Excel（服务器不用安装呦！）
新公司，新接触，老方法，更实用。
之前接触过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 }
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 =http://www.mamicode.com/ column.InnerText; 71 map[rowNum, columnNum] = true; 72 } 73 else 74 { 75 ws.Cell(string.Format("{0}{1}", Char.Chr(columnNum), rowNum)).Value =http://www.mamicode.com/ 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
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 转化
1 public class Char2 {3 public static string Chr(int i)4 {5 char c = (char)(64 + i);6 return c.ToString();7 }8 }
以上代码就是实现Export Excel的全部代码
思路:拼接字符串构造一个纯Html的结构。用rowspan colspan来跨行跨列，把Html当做参数直接传过去调用写好的导出方法
返回数组。保存 完成！很简单！
希望能帮助大家！我的可能不是最好的方法！但是我在尽力去想！希望广大的博友一起想！想出更好的方法解决中国的所有技术人员的困惑！如有想法请留下您的宝贵评论！
Check Me Give You Source!
Mvc 导出 Excel（服务器不用安装呦！）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。