Mvc 导出 Excel
之前接触过Webform,winfrom 的导出Excel方法 ，优点：省事。缺点：服务器必须安装Office
这几天做项目 和 大牛学习了一下 新的方法，自己加以总结。希望更多的博友获益。不多说 。先上图，后上源码。
很简单的MVC4 的页面 献丑了
效果图
你猜了对了 我用的是 ClosedXml、NPOI 不是很新的东西！
看看代码怎么实现吧 ！
1、工厂封装直接调用：
public class ExportFactory
{
public static byte[] exportToExcel(string type)
{
byte[] bytes = null;
switch (type.ToLower())
{
case "npoi":
bytes = NpoiExcelHelp.GenerateXlsxBytes(GetHtml());
break;
case "closexml":
bytes = ClosedXmlExcelHelp.GenerateXlsxBytes(GetHtml());
break;
default:
break;
}
return bytes;
}
static string GetHtml()
{
StringBuilder strHtml = new StringBuilder();
strHtml.Append("<table>");
strHtml.Append("<tr>");
strHtml.Append("<td rowspan='2'>First Row/First Colunm</td>");
strHtml.Append("<td>Second Row/First Colunm</td>");
strHtml.Append("</tr>");
strHtml.Append("<tr>");
strHtml.Append("<td>First Row/Second Colunm</td>");
strHtml.Append("</tr>");
strHtml.Append("<tr>");
strHtml.Append("<td>Second Row/Second Colunm</td>");
strHtml.Append("<td>Third Row/Second Colunm</td>");
strHtml.Append("</tr>");
strHtml.Append("</table>");
return strHtml.ToString();
}
}
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
public class ClosedXmlExcelHelp
{
public static byte[] GenerateXlsxBytes(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;
var workBook = new XLWorkbook();
var ws = workBook.Worksheets.Add("Export");
int rowNum;
int columnNum;
rowNum = 1;
columnNum = 1;
string mapKey = string.Empty;
string mergKey = string.Empty;
int rowCount = table.ChildNodes.Count;
int colCount = 0;
foreach (XmlNode row in table.ChildNodes)
{
if (colCount < row.ChildNodes.Count)
{
colCount = row.ChildNodes.Count;
}
}
bool[,] map = new bool[rowCount + 1, colCount + 1];
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)
{
ws.Cell(string.Format("{0}{1}", Char.Chr(columnNum), rowNum)).Value = column.InnerText;
map[rowNum, columnNum] = true;
}
else
{
ws.Cell(string.Format("{0}{1}", Char.Chr(columnNum), rowNum)).Value = column.InnerText;
mergKey =
string.Format("{0}{1}:{2}{3}",
Char.Chr(columnNum), rowNum, Char.Chr(columnNum + colspan - 1), rowNum + rowspan - 1);
ws.Range(mergKey).Merge();
for (int m = 0; m < rowspan; m++)
{
for (int n = 0; n < colspan; n++)
{
map[rowNum + m, columnNum + n] = true;
}
}
}
columnNum++;
}
rowNum++;
}
MemoryStream stream = new MemoryStream();
workBook.SaveAs(stream);
return stream.ToArray();
}
}
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
public class NpoiExcelHelp
{
public static byte[] GenerateXlsxBytes(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;
var workBook = new HSSFWorkbook();
var ws = workBook.CreateSheet("Export");
string mapKey = string.Empty;
string mergKey = string.Empty;
int rowCount = table.ChildNodes.Count;
int colCount = FetchColCount(table.ChildNodes);
InitSheet(ws, rowCount, colCount);
bool[,] map = new bool[rowCount + 1, colCount + 1];
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)
{
SetCellValue(ws, string.Format("{0}{1}", Char.Chr(columnNum), rowNum), column.InnerText);
map[rowNum, columnNum] = true;
}
else
{
SetCellValue(ws, string.Format("{0}{1}", Char.Chr(columnNum), rowNum), column.InnerText);
mergKey =
string.Format("{0}{1}:{2}{3}",
Char.Chr(columnNum), rowNum, Char.Chr(columnNum + colspan - 1), rowNum + rowspan - 1);
MergCells(ws, mergKey);
for (int m = 0; m < rowspan; m++)
{
for (int n = 0; n < colspan; n++)
{
map[rowNum + m, columnNum + n] = true;
}
}
}
columnNum++;
}
rowNum++;
}
MemoryStream stream = new MemoryStream();
workBook.Write(stream);
return stream.ToArray();
}
static int FetchColCount(XmlNodeList nodes)
{
int colCount = 0;
foreach (XmlNode row in nodes)
{
if (colCount < row.ChildNodes.Count)
{
colCount = row.ChildNodes.Count;
}
}
return colCount;
}
static void InitSheet(ISheet sheet, int rowCount, int colCount)
{
for (int i = 0; i < rowCount; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < colCount; j++)
{
row.CreateCell(j);
}
}
}
static void SetCellValue(ISheet sheet, string cellReferenceText, string value)
{
CellReference cr = new CellReference(cellReferenceText);
IRow row = sheet.GetRow(cr.Row);
ICell cell = row.GetCell(cr.Col);
cell.SetCellValue(value);
}
static void MergCells(ISheet sheet, string mergeKey)
{
string[] cellReferences = mergeKey.Split(':');
CellReference first = new CellReference(cellReferences[0]);
CellReference last = new CellReference(cellReferences[1]);
CellRangeAddress region = new CellRangeAddress(first.Row, last.Row, first.Col, last.Col);
sheet.AddMergedRegion(region);
}
}
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 转化
public class Char
{
public static string Chr(int i)
{
char c = (char)(64 + i);
return c.ToString();
}
}
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 }
以上代码就是实现Export Excel的全部代码
思路:拼接字符串构造一个纯Html的结构。用rowspan colspan来跨行跨列，把Html当做参数直接传过去调用写好的导出方法
返回数组。保存 完成！很简单！
希望能帮助大家！我的可能不是最好的方法！但是我在尽力去想！希望广大的博友一起想！想出更好的方法解决中国的所有技术人员的困惑！如有想法请留下您的宝贵评论！