Excel 操作，excel操作
Aspose.Cells.dll ：無需安裝office相關組件就可以操作excel。
![]()
1 using System;
2 using System.Web;
3 using Aspose.Cells;
4 using System.Data;
5 using System.Collections.Specialized;
6 using System.Collections.Generic;
7 using System.Reflection;
8 using System.IO;
9
10 namespace APP.ExcelOperation
11 {
12 public class AsposeCellsHelper
13 {
14 #region 導出excel數據
15 /// <summary>
16 /// 導出excel數據
17 /// </summary>
18 /// <param name="dt">數據table</param>
19 /// <param name="coll">標題對映數據列名</param>
20 public static void ExportExcel(DataTable dt, NameValueCollection coll, string fileName)
21 {
22 Workbook workbook = new Workbook();
23 Worksheet sheet = workbook.Worksheets[0];
24
25 Aspose.Cells.Style s = new Aspose.Cells.Style();
26 s.Font.IsBold = true;
27 s.Font.Size = 12;
28 s.Number = 49;
29
30 //設置標題及格式
31 for (int i = 0; i < coll.Count; i++)
32 {
33 sheet.Cells[0, i].SetStyle(s);
34 sheet.Cells[0, i].Value = coll[i];
35 }
36 //填充內容
37 for (int i = 0; i < dt.Rows.Count; i++)
38 {
39 for (int c = 0; c < coll.Count; c++)
40 {
41 sheet.Cells[i + 1, c].Value = dt.Rows[i][coll.Keys[c]];
42 }
43 }
44 ResponseFile(workbook, fileName);
45 }
46
47 public static void ExportExcel<T>(IEnumerable<T> data,string fileName)
48 {
49 Workbook workbook = new Workbook();
50 Worksheet sheet = (Worksheet)workbook.Worksheets[0];
51
52 PropertyInfo[] ps = typeof(T).GetProperties();
53 var colIndex = "A";
54
55 foreach (var p in ps)
56 {
57
58 sheet.Cells[colIndex + 1].PutValue(p.Name);
59 int i = 2;
60 foreach (var d in data)
61 {
62 sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
63 i++;
64 }
65
66 colIndex = ((char)(colIndex[0] + 1)).ToString();
67 }
68 ResponseFile(workbook, fileName);
69 }
70 private static void ResponseFile(Workbook workbook, string fileName)
71 {
72 HttpResponse response = HttpContext.Current.Response;
73 response.Clear();
74 response.Buffer = true;
75 response.Charset = "utf-8";
76 response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
77 response.ContentEncoding = System.Text.Encoding.UTF8;
78 response.ContentType = "application/ms-excel";
79 response.BinaryWrite(workbook.SaveToStream().ToArray());
80 response.End();
81 }
82
83 #endregion 導出excel數據end
84
85 #region 讀取excel中的數據
86
87 /// <summary>
88 /// 讀取excel文件流到datatable
89 /// </summary>
90 /// <param name="fileStream"></param>
91 /// <returns></returns>
92 public static DataTable ReadExcel(Stream fileStream)
93 {
94 Workbook book = new Workbook(fileStream);
95 Worksheet sheet = book.Worksheets[0];
96 Cells cells = sheet.Cells;
97 return cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
98 }
99
100 /// <summary>
101 /// 讀取excel文件流到datatable，格式是string
102 /// 推薦使用
103 /// </summary>
104 /// <param name="fileStream"></param>
105 /// <returns></returns>
106 public static DataTable ReadExcelAsString(Stream fileStream)
107 {
108 Workbook book = new Workbook(fileStream);
109 Worksheet sheet = book.Worksheets[0];
110 Cells cells = sheet.Cells;
111 return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
112 }
113
114 /// <summary>
115 /// 讀取excel文件流到DataSet，格式是string
116 /// 推薦使用
117 /// </summary>
118 /// <param name="fileStream"></param>
119 /// <returns></returns>
120 public static DataSet ReadExcelAsStringToDataSet(Stream fileStream)
121 {
122 Workbook book = new Workbook(fileStream);
123 DataSet ds = new DataSet();
124 for (int i = 0; i < book.Worksheets.Count; i++)
125 {
126 Worksheet sheet = book.Worksheets[i];
127 Cells cells = sheet.Cells;
128 if (cells.Rows.Count > 0)
129 {
130 var dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
131 dt.TableName = sheet.Name;
132 ds.Tables.Add(dt);
133 }
134 }
135 return ds;
136 }
137
138 /// <summary>
139 /// 讀取excel文件到datatable
140 /// </summary>
141 /// <param name="fileStream"></param>
142 /// <returns></returns>
143 public static DataTable ReadExcel(String strFileName)
144 {
145 Workbook book = new Workbook(strFileName);
146 Worksheet sheet = book.Worksheets[0];
147 Cells cells = sheet.Cells;
148 return cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
149 }
150
151 /// <summary>
152 /// 讀取excel文件到datatable，格式是string
153 /// 推薦使用
154 /// </summary>
155 /// <param name="fileStream"></param>
156 /// <returns></returns>
157 public static DataTable ReadExcelAsString(String strFileName)
158 {
159 Workbook book = new Workbook(strFileName);
160 Worksheet sheet = book.Worksheets[0];
161 Cells cells = sheet.Cells;
162 return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
163 }
164 #endregion
165
166 }
167 }
View Code
