WinForm控件设计：DataGridView导出数据
做WinForm开发离不开一些基本的控件，作为数据列表显示控件中，其中最为重要的要数 DataGridView，以前用的是一些第三方控件，提供了比较灵活和方便的功能，比如：根据所见即所得导出数据到Excel 或者 文本，没办法自力更生才是生存之道。
DataGridView的数据导出功能在网络上搜索后，有一些同仁实现过，但有些是需要依赖Excel ,这种方式不但耦合性强，性能也差，为了一劳永逸的解决这个问题，特改写了代码，在此提供给各位同仁，欢迎斧正。
1、首先，看使用方法：
dgvProjectList.ExportToExcel();
或者
dgvProjectList.ExportToExcel("项目列表");
如果不使用数据导出功能，不会对现有 DateGridView控件产生任何负作用，也不占用内存；
2、扩展方法定义：
代码
/// <summary>
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv)
{
ExportToExcel(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "表格数据文件(*.csv)|*.csv";
sfd.FileName = string.Format("{0}.csv", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.CSV, fileName, ExportHelper.ApplicationType.WindowsForm);
}
}
/// <summary>
/// 将表格数据导出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv)
{
ExportToText(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "文本文件(*.txt)|*.txt";
sfd.FileName = string.Format("{0}.txt", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.TXT, fileName, ExportHelper.ApplicationType.WindowsForm);
}
}
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv)
{
ExportToExcel(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "表格数据文件(*.csv)|*.csv";
sfd.FileName = string.Format("{0}.csv", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.CSV, fileName, ExportHelper.ApplicationType.WindowsForm);
}
}
/// <summary>
/// 将表格数据导出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv)
{
ExportToText(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "文本文件(*.txt)|*.txt";
sfd.FileName = string.Format("{0}.txt", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.TXT, fileName, ExportHelper.ApplicationType.WindowsForm);
}
}
3、导出类定义：
1 //导出辅助类
2 public class ExportHelper
3 {
4 /// <summary>
5 /// Export format enumeration
6 /// </summary>
7 public enum ExportFormat : int
8 {
9 /// <summary>
10 /// CSV
11 /// </summary>
12 CSV,
13 /// <summary>
14 /// DOC
15 /// </summary>
16 DOC,
17 /// <summary>
18 /// TXT
19 /// </summary>
20 TXT
21 };
22
23 /// <summary>
24 /// 应用程序类型
25 /// </summary>
26 public enum ApplicationType : int
27 {
28 WindowsForm,
29 Web
30 }
31
32 /// <summary>
33 /// 导出SmartGridView的数据源的数据为Excel
34 /// </summary>
35 // <param name="dt">数据源</param>
36 /// <param name="fileName">文件名</param>
37 /// <param name="ApplicationType">应用宿主类型</param>
38 public static void ExportDetails(DataTable dt, string fileName, ApplicationType ApplicationType)
39 {
40 ExportDetails(dt, ExportFormat.CSV, fileName, ApplicationType);
41 }
42
43 #region ExportDetails OverLoad : Type#1
44
45 // Function : ExportDetails
46 // Arguments : DetailsTable, FormatType, FileName
47 // Purpose : To get all the column headers in the datatable and
48 // exorts in CSV / Excel format with all columns
49
50 public static void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
51 {
52 try
53 {
54 if (DetailsTable.Rows.Count == 0)
55 throw new Exception("There are no details to export.");
56
57 // Create Dataset
58 DataSet dsExport = new DataSet("Export");
59 DataTable dtExport = DetailsTable.Copy();
60 dtExport.TableName = "Values";
61 dsExport.Tables.Add(dtExport);
62
63 // Getting Field Names
64 string[] sHeaders = new string[dtExport.Columns.Count];
65 string[] sFileds = new string[dtExport.Columns.Count];
66
67 for (int i = 0; i < dtExport.Columns.Count; i++)
68 {
69 sHeaders[i] = dtExport.Columns[i].ColumnName;
70 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
71 }
72
73 if (ApplicationType == ApplicationType.Web)
74 {
75 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
76 }
77 else if (ApplicationType == ApplicationType.WindowsForm)
78 {
79 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
80 }
81 }
82 catch (Exception Ex)
83 {
84 throw Ex;
85 }
86 }
87
88 #endregion // ExportDetails OverLoad : Type#1
89
90 #region ExportDetails OverLoad : Type#2
91
92 // Function : ExportDetails
93 // Arguments : DetailsTable, ColumnList, FormatType, FileName
94 // Purpose : To get the specified column headers in the datatable and
95 // exorts in CSV / Excel format with specified columns
96
97 public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName,
98 ApplicationType ApplicationType)
99 {
100 try
101 {
102 if (DetailsTable.Rows.Count == 0)
103 throw new Exception("There are no details to export");
104
105 // Create Dataset
106 DataSet dsExport = new DataSet("Export");
107 DataTable dtExport = DetailsTable.Copy();
108 dtExport.TableName = "Values";
109 dsExport.Tables.Add(dtExport);
110
111 if (ColumnList.Length > dtExport.Columns.Count)
112 throw new Exception("ExportColumn List should not exceed Total Columns");
113
114 // Getting Field Names
115 string[] sHeaders = new string[ColumnList.Length];
116 string[] sFileds = new string[ColumnList.Length];
117
118 for (int i = 0; i < ColumnList.Length; i++)
119 {
120 if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
121 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
122
123 sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
124 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
125 }
126
127 if (ApplicationType == ApplicationType.Web)
128 {
129 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
130 }
131 else if (ApplicationType == ApplicationType.WindowsForm)
132 {
133 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
134 }
135 }
136 catch (Exception Ex)
137 {
138 throw Ex;
139 }
140 }
141
142 #endregion // ExportDetails OverLoad : Type#2
143
144 #region ExportDetails OverLoad : Type#3
145
146 // Function : ExportDetails
147 // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
148 // Purpose : To get the specified column headers in the datatable and
149 // exorts in CSV / Excel format with specified columns and
150 // with specified headers
151
152 public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] sHeaders, ExportFormat FormatType,
153 string FileName, ApplicationType ApplicationType)
154 {
155 try
156 {
157 if (DetailsTable.Rows.Count == 0)
158 throw new Exception("There are no details to export");
159
160 // Create Dataset
161 DataSet dsExport = new DataSet("Export");
162 DataTable dtExport = DetailsTable.Copy();
163 dtExport.TableName = "Values";
164 dsExport.Tables.Add(dtExport);
165
166 if (ColumnList.Length != sHeaders.Length)
167 throw new Exception("ExportColumn List and Headers List should be of same length");
168 else if (ColumnList.Length > dtExport.Columns.Count || sHeaders.Length > dtExport.Columns.Count)
169 throw new Exception("ExportColumn List should not exceed Total Columns");
170
171 // Getting Field Names
172 string[] sFileds = new string[ColumnList.Length];
173
174 for (int i = 0; i < ColumnList.Length; i++)
175 {
176 if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
177 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
178
179 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
180 }
181
182 if (ApplicationType == ApplicationType.Web)
183 {
184 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
185 }
186 else if (ApplicationType == ApplicationType.WindowsForm)
187 {
188 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
189 }
190 }
191 catch (Exception Ex)
192 {
193 throw Ex;
194 }
195 }
196
197 /// <summary>
198 /// 导出SmartGridView的数据源的数据
199 /// </summary>
200 /// <param name="DetailsTable">数据源</param>
201 /// <param name="columnNameList">导出的列的列名数组</param>
202 /// <param name="sHeaders">导出的列标题数组</param>
203 /// <param name="FormatType">导出文件的格式</param>
204 /// <param name="FileName">输出文件名</param>
205 /// <param name="ApplicationType">应用宿主类型</param>
206 public static void ExportDetails(DataTable DetailsTable, string[] columnNameList, string[] sHeaders,
207 ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
208 {
209 List<int> columnIndexList = new List<int>();
210 DataColumnCollection dcc = DetailsTable.Columns;
211
212 foreach (string s in columnNameList)
213 {
214 columnIndexList.Add(GetColumnIndexByColumnName(dcc, s));
215 }
216
217 ExportDetails(DetailsTable, columnIndexList.ToArray(), sHeaders, FormatType, FileName, ApplicationType);
218 }
219
220 #endregion // ExportDetails OverLoad : Type#3
221
222 #region ExportDetails OverLoad : Type#3
223
224 // Function : ExportDetails
225 // Arguments : DetailsTable, FormatType, FileName
226 // Purpose : To get all the column headers in the datatable and
227 // exorts in CSV / Excel format with all columns
228 public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
229 {
230 try
231 {
232 string NewFileName;
233
234 foreach (DataTable DetailsTable in DetailsTables)
235 {
236 if (DetailsTable.Rows.Count == 0)
237 throw new Exception("There are no details to export.");
238
239 NewFileName = FileName.Substring(0, FileName.LastIndexOf("."));
240 NewFileName += " - " + DetailsTable.TableName;
241 NewFileName += FileName.Substring(FileName.LastIndexOf("."));
242
243 // Create Dataset
244 DataSet dsExport = new DataSet("Export");
245 DataTable dtExport = DetailsTable.Copy();
246 dtExport.TableName = "Values";
247 dsExport.Tables.Add(dtExport);
248
249 // Getting Field Names
250 string[] sHeaders = new string[dtExport.Columns.Count];
251 string[] sFileds = new string[dtExport.Columns.Count];
252
253 for (int i = 0; i < dtExport.Columns.Count; i++)
254 {
255 sHeaders[i] = dtExport.Columns[i].ColumnName;
256 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
257 }
258
259 if (ApplicationType == ApplicationType.Web)
260 {
261 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
262 }
263 else if (ApplicationType == ApplicationType.WindowsForm)
264 {
265 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
266 }
267 }
268 }
269 catch (Exception Ex)
270 {
271 throw Ex;
272 }
273 }
274
275 #endregion //ExportDetails OverLoad : Type#4
276
277 #region Export_with_XSLT_Web
278
279 // Function : Export_with_XSLT_Web
280 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
281 // Purpose : Exports dataset into CSV / Excel format
282 private static void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
283 {
284 try
285 {
286 // Appending Headers
287 HttpContext.Current.Response.Clear();
288 HttpContext.Current.Response.Buffer = true;
289 HttpContext.Current.Response.ContentType = String.Format("text/{0}", FormatType.ToString().ToLower());
290 HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.{1}", FileName, FormatType.ToString().ToLower()));
291 //HttpContext.Current.Response.ContentEncoding = encoding;
292
293
294 // XSLT to use for transforming this dataset.
295 MemoryStream stream = new MemoryStream();
296 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);
297
298 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
299 writer.Flush();
300 stream.Seek(0, SeekOrigin.Begin);
301
302 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
303 XslCompiledTransform xslTran = new XslCompiledTransform();
304 xslTran.Load(new XmlTextReader(stream));
305
306 System.IO.StringWriter sw = new System.IO.StringWriter();
307 xslTran.Transform(xmlDoc, null, sw);
308
309 //Writeout the Content
310 HttpContext.Current.Response.Write(sw.ToString());
311 sw.Close();
312 writer.Close();
313 stream.Close();
314 HttpContext.Current.Response.End();
315 }
316 catch (ThreadAbortException Ex)
317 {
318 string ErrMsg = Ex.Message;
319 }
320 catch (Exception Ex)
321 {
322 throw Ex;
323 }
324 }
325
326 #endregion // Export_with_XSLT
327
328 #region Export_with_XSLT_Windows
329
330 // Function : Export_with_XSLT_Windows
331 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
332 // Purpose : Exports dataset into CSV / Excel format
333 private static void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds,
334 ExportFormat FormatType, string FileName)
335 {
336
337 try
338 {
339 // XSLT to use for transforming this dataset.
340 MemoryStream stream = new MemoryStream();
341 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
342
343 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
344 writer.Flush();
345 stream.Seek(0, SeekOrigin.Begin);
346
347 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
348 XslCompiledTransform xslTran = new XslCompiledTransform();
349 xslTran.Load(new XmlTextReader(stream));
350
351 System.IO.StringWriter sw = new System.IO.StringWriter();
352 xslTran.Transform(xmlDoc, null, sw);
353
354 //Writeout the Content
355 StreamWriter strwriter = new StreamWriter(FileName, false, Encoding.Default);
356 strwriter.WriteLine(sw.ToString());
357 strwriter.Close();
358
359 sw.Close();
360 writer.Close();
361 stream.Close();
362 }
363 catch (Exception Ex)
364 {
365 throw Ex;
366 }
367 }
368
369 #endregion // Export_with_XSLT
370
371 #region CreateStylesheet
372
373 // Function : WriteStylesheet
374 // Arguments : writer, sHeaders, sFileds, FormatType
375 // Purpose : Creates XSLT file to apply on dataset's XML file
376 private static void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
377 {
378 try
379 {
380 // xsl:stylesheet
381 string ns = "http://www.w3.org/1999/XSL/Transform";
382 writer.Formatting = Formatting.Indented;
383 writer.WriteStartDocument();
384 writer.WriteStartElement("xsl", "stylesheet", ns);
385 writer.WriteAttributeString("version", "1.0");
386 writer.WriteStartElement("xsl:output");
387 writer.WriteAttributeString("method", "text");
388 writer.WriteAttributeString("version", "4.0");
389 writer.WriteEndElement();
390
391 // xsl-template
392 writer.WriteStartElement("xsl:template");
393 writer.WriteAttributeString("match", "/");
394
395 // xsl:value-of for headers
396 for (int i = 0; i < sHeaders.Length; i++)
397 {
398 writer.WriteString("\"");
399 writer.WriteStartElement("xsl:value-of");
400 writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
401 writer.WriteEndElement(); // xsl:value-of
402 writer.WriteString("\"");
403 if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
404 }
405
406 // xsl:for-each
407 writer.WriteStartElement("xsl:for-each");
408 writer.WriteAttributeString("select", "Export/Values");
409 writer.WriteString("\r\n");
410
411 // xsl:value-of for data fields
412 for (int i = 0; i < sFileds.Length; i++)
413 {
414 writer.WriteString("\"");
415 writer.WriteStartElement("xsl:value-of");
416 writer.WriteAttributeString("select", sFileds[i]);
417 writer.WriteEndElement(); // xsl:value-of
418 writer.WriteString("\"");
419 if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
420 }
421
422 writer.WriteEndElement(); // xsl:for-each
423 writer.WriteEndElement(); // xsl-template
424 writer.WriteEndElement(); // xsl:stylesheet
425 writer.WriteEndDocument();
426 }
427 catch (Exception Ex)
428 {
429 throw Ex;
430 }
431 }
432
433 public static string ReplaceSpecialChars(string input)
434 {
435 // space -> _x0020_
436 // % -> _x0025_
437 // # -> _x0023_
438 // & -> _x0026_
439 // / -> _x002F_
440
441 input = input.Replace(" ", "_x0020_")
442 .Replace("%", "_x0025_")
443 .Replace("#", "_x0023_")
444 .Replace("&", "_x0026_")
445 .Replace("/", "_x002F_");
446
447 return input;
448 }
449 /// <summary>
450 /// 根据数据列的列名取数据列的列索引
451 /// </summary>
452 /// <param name="dcc">数据列集合</param>
453 /// <param name="columnName">数据列的列名</param>
454 /// <returns></returns>
455 public static int GetColumnIndexByColumnName(DataColumnCollection dcc, string columnName)
456 {
457 int result = -1;
458
459 for (int i = 0; i < dcc.Count; i++)
460 {
461 if (dcc[i].ColumnName.ToLower() == columnName.ToLower())
462 {
463 result = i;
464 break;
465 }
466 }
467
468 return result;
469 }
470 #endregion // WriteStylesheet
471
472 }
3 {
4 /// <summary>
5 /// Export format enumeration
6 /// </summary>
7 public enum ExportFormat : int
8 {
9 /// <summary>
10 /// CSV
11 /// </summary>
12 CSV,
13 /// <summary>
14 /// DOC
15 /// </summary>
16 DOC,
17 /// <summary>
18 /// TXT
19 /// </summary>
20 TXT
21 };
22
23 /// <summary>
24 /// 应用程序类型
25 /// </summary>
26 public enum ApplicationType : int
27 {
28 WindowsForm,
29 Web
30 }
31
32 /// <summary>
33 /// 导出SmartGridView的数据源的数据为Excel
34 /// </summary>
35 // <param name="dt">数据源</param>
36 /// <param name="fileName">文件名</param>
37 /// <param name="ApplicationType">应用宿主类型</param>
38 public static void ExportDetails(DataTable dt, string fileName, ApplicationType ApplicationType)
39 {
40 ExportDetails(dt, ExportFormat.CSV, fileName, ApplicationType);
41 }
42
43 #region ExportDetails OverLoad : Type#1
44
45 // Function : ExportDetails
46 // Arguments : DetailsTable, FormatType, FileName
47 // Purpose : To get all the column headers in the datatable and
48 // exorts in CSV / Excel format with all columns
49
50 public static void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
51 {
52 try
53 {
54 if (DetailsTable.Rows.Count == 0)
55 throw new Exception("There are no details to export.");
56
57 // Create Dataset
58 DataSet dsExport = new DataSet("Export");
59 DataTable dtExport = DetailsTable.Copy();
60 dtExport.TableName = "Values";
61 dsExport.Tables.Add(dtExport);
62
63 // Getting Field Names
64 string[] sHeaders = new string[dtExport.Columns.Count];
65 string[] sFileds = new string[dtExport.Columns.Count];
66
67 for (int i = 0; i < dtExport.Columns.Count; i++)
68 {
69 sHeaders[i] = dtExport.Columns[i].ColumnName;
70 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
71 }
72
73 if (ApplicationType == ApplicationType.Web)
74 {
75 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
76 }
77 else if (ApplicationType == ApplicationType.WindowsForm)
78 {
79 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
80 }
81 }
82 catch (Exception Ex)
83 {
84 throw Ex;
85 }
86 }
87
88 #endregion // ExportDetails OverLoad : Type#1
89
90 #region ExportDetails OverLoad : Type#2
91
92 // Function : ExportDetails
93 // Arguments : DetailsTable, ColumnList, FormatType, FileName
94 // Purpose : To get the specified column headers in the datatable and
95 // exorts in CSV / Excel format with specified columns
96
97 public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName,
98 ApplicationType ApplicationType)
99 {
100 try
101 {
102 if (DetailsTable.Rows.Count == 0)
103 throw new Exception("There are no details to export");
104
105 // Create Dataset
106 DataSet dsExport = new DataSet("Export");
107 DataTable dtExport = DetailsTable.Copy();
108 dtExport.TableName = "Values";
109 dsExport.Tables.Add(dtExport);
110
111 if (ColumnList.Length > dtExport.Columns.Count)
112 throw new Exception("ExportColumn List should not exceed Total Columns");
113
114 // Getting Field Names
115 string[] sHeaders = new string[ColumnList.Length];
116 string[] sFileds = new string[ColumnList.Length];
117
118 for (int i = 0; i < ColumnList.Length; i++)
119 {
120 if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
121 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
122
123 sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
124 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
125 }
126
127 if (ApplicationType == ApplicationType.Web)
128 {
129 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
130 }
131 else if (ApplicationType == ApplicationType.WindowsForm)
132 {
133 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
134 }
135 }
136 catch (Exception Ex)
137 {
138 throw Ex;
139 }
140 }
141
142 #endregion // ExportDetails OverLoad : Type#2
143
144 #region ExportDetails OverLoad : Type#3
145
146 // Function : ExportDetails
147 // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
148 // Purpose : To get the specified column headers in the datatable and
149 // exorts in CSV / Excel format with specified columns and
150 // with specified headers
151
152 public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] sHeaders, ExportFormat FormatType,
153 string FileName, ApplicationType ApplicationType)
154 {
155 try
156 {
157 if (DetailsTable.Rows.Count == 0)
158 throw new Exception("There are no details to export");
159
160 // Create Dataset
161 DataSet dsExport = new DataSet("Export");
162 DataTable dtExport = DetailsTable.Copy();
163 dtExport.TableName = "Values";
164 dsExport.Tables.Add(dtExport);
165
166 if (ColumnList.Length != sHeaders.Length)
167 throw new Exception("ExportColumn List and Headers List should be of same length");
168 else if (ColumnList.Length > dtExport.Columns.Count || sHeaders.Length > dtExport.Columns.Count)
169 throw new Exception("ExportColumn List should not exceed Total Columns");
170
171 // Getting Field Names
172 string[] sFileds = new string[ColumnList.Length];
173
174 for (int i = 0; i < ColumnList.Length; i++)
175 {
176 if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
177 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
178
179 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
180 }
181
182 if (ApplicationType == ApplicationType.Web)
183 {
184 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
185 }
186 else if (ApplicationType == ApplicationType.WindowsForm)
187 {
188 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
189 }
190 }
191 catch (Exception Ex)
192 {
193 throw Ex;
194 }
195 }
196
197 /// <summary>
198 /// 导出SmartGridView的数据源的数据
199 /// </summary>
200 /// <param name="DetailsTable">数据源</param>
201 /// <param name="columnNameList">导出的列的列名数组</param>
202 /// <param name="sHeaders">导出的列标题数组</param>
203 /// <param name="FormatType">导出文件的格式</param>
204 /// <param name="FileName">输出文件名</param>
205 /// <param name="ApplicationType">应用宿主类型</param>
206 public static void ExportDetails(DataTable DetailsTable, string[] columnNameList, string[] sHeaders,
207 ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
208 {
209 List<int> columnIndexList = new List<int>();
210 DataColumnCollection dcc = DetailsTable.Columns;
211
212 foreach (string s in columnNameList)
213 {
214 columnIndexList.Add(GetColumnIndexByColumnName(dcc, s));
215 }
216
217 ExportDetails(DetailsTable, columnIndexList.ToArray(), sHeaders, FormatType, FileName, ApplicationType);
218 }
219
220 #endregion // ExportDetails OverLoad : Type#3
221
222 #region ExportDetails OverLoad : Type#3
223
224 // Function : ExportDetails
225 // Arguments : DetailsTable, FormatType, FileName
226 // Purpose : To get all the column headers in the datatable and
227 // exorts in CSV / Excel format with all columns
228 public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
229 {
230 try
231 {
232 string NewFileName;
233
234 foreach (DataTable DetailsTable in DetailsTables)
235 {
236 if (DetailsTable.Rows.Count == 0)
237 throw new Exception("There are no details to export.");
238
239 NewFileName = FileName.Substring(0, FileName.LastIndexOf("."));
240 NewFileName += " - " + DetailsTable.TableName;
241 NewFileName += FileName.Substring(FileName.LastIndexOf("."));
242
243 // Create Dataset
244 DataSet dsExport = new DataSet("Export");
245 DataTable dtExport = DetailsTable.Copy();
246 dtExport.TableName = "Values";
247 dsExport.Tables.Add(dtExport);
248
249 // Getting Field Names
250 string[] sHeaders = new string[dtExport.Columns.Count];
251 string[] sFileds = new string[dtExport.Columns.Count];
252
253 for (int i = 0; i < dtExport.Columns.Count; i++)
254 {
255 sHeaders[i] = dtExport.Columns[i].ColumnName;
256 sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
257 }
258
259 if (ApplicationType == ApplicationType.Web)
260 {
261 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
262 }
263 else if (ApplicationType == ApplicationType.WindowsForm)
264 {
265 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
266 }
267 }
268 }
269 catch (Exception Ex)
270 {
271 throw Ex;
272 }
273 }
274
275 #endregion //ExportDetails OverLoad : Type#4
276
277 #region Export_with_XSLT_Web
278
279 // Function : Export_with_XSLT_Web
280 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
281 // Purpose : Exports dataset into CSV / Excel format
282 private static void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
283 {
284 try
285 {
286 // Appending Headers
287 HttpContext.Current.Response.Clear();
288 HttpContext.Current.Response.Buffer = true;
289 HttpContext.Current.Response.ContentType = String.Format("text/{0}", FormatType.ToString().ToLower());
290 HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.{1}", FileName, FormatType.ToString().ToLower()));
291 //HttpContext.Current.Response.ContentEncoding = encoding;
292
293
294 // XSLT to use for transforming this dataset.
295 MemoryStream stream = new MemoryStream();
296 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);
297
298 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
299 writer.Flush();
300 stream.Seek(0, SeekOrigin.Begin);
301
302 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
303 XslCompiledTransform xslTran = new XslCompiledTransform();
304 xslTran.Load(new XmlTextReader(stream));
305
306 System.IO.StringWriter sw = new System.IO.StringWriter();
307 xslTran.Transform(xmlDoc, null, sw);
308
309 //Writeout the Content
310 HttpContext.Current.Response.Write(sw.ToString());
311 sw.Close();
312 writer.Close();
313 stream.Close();
314 HttpContext.Current.Response.End();
315 }
316 catch (ThreadAbortException Ex)
317 {
318 string ErrMsg = Ex.Message;
319 }
320 catch (Exception Ex)
321 {
322 throw Ex;
323 }
324 }
325
326 #endregion // Export_with_XSLT
327
328 #region Export_with_XSLT_Windows
329
330 // Function : Export_with_XSLT_Windows
331 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
332 // Purpose : Exports dataset into CSV / Excel format
333 private static void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds,
334 ExportFormat FormatType, string FileName)
335 {
336
337 try
338 {
339 // XSLT to use for transforming this dataset.
340 MemoryStream stream = new MemoryStream();
341 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
342
343 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
344 writer.Flush();
345 stream.Seek(0, SeekOrigin.Begin);
346
347 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
348 XslCompiledTransform xslTran = new XslCompiledTransform();
349 xslTran.Load(new XmlTextReader(stream));
350
351 System.IO.StringWriter sw = new System.IO.StringWriter();
352 xslTran.Transform(xmlDoc, null, sw);
353
354 //Writeout the Content
355 StreamWriter strwriter = new StreamWriter(FileName, false, Encoding.Default);
356 strwriter.WriteLine(sw.ToString());
357 strwriter.Close();
358
359 sw.Close();
360 writer.Close();
361 stream.Close();
362 }
363 catch (Exception Ex)
364 {
365 throw Ex;
366 }
367 }
368
369 #endregion // Export_with_XSLT
370
371 #region CreateStylesheet
372
373 // Function : WriteStylesheet
374 // Arguments : writer, sHeaders, sFileds, FormatType
375 // Purpose : Creates XSLT file to apply on dataset's XML file
376 private static void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
377 {
378 try
379 {
380 // xsl:stylesheet
381 string ns = "http://www.w3.org/1999/XSL/Transform";
382 writer.Formatting = Formatting.Indented;
383 writer.WriteStartDocument();
384 writer.WriteStartElement("xsl", "stylesheet", ns);
385 writer.WriteAttributeString("version", "1.0");
386 writer.WriteStartElement("xsl:output");
387 writer.WriteAttributeString("method", "text");
388 writer.WriteAttributeString("version", "4.0");
389 writer.WriteEndElement();
390
391 // xsl-template
392 writer.WriteStartElement("xsl:template");
393 writer.WriteAttributeString("match", "/");
394
395 // xsl:value-of for headers
396 for (int i = 0; i < sHeaders.Length; i++)
397 {
398 writer.WriteString("\"");
399 writer.WriteStartElement("xsl:value-of");
400 writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
401 writer.WriteEndElement(); // xsl:value-of
402 writer.WriteString("\"");
403 if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
404 }
405
406 // xsl:for-each
407 writer.WriteStartElement("xsl:for-each");
408 writer.WriteAttributeString("select", "Export/Values");
409 writer.WriteString("\r\n");
410
411 // xsl:value-of for data fields
412 for (int i = 0; i < sFileds.Length; i++)
413 {
414 writer.WriteString("\"");
415 writer.WriteStartElement("xsl:value-of");
416 writer.WriteAttributeString("select", sFileds[i]);
417 writer.WriteEndElement(); // xsl:value-of
418 writer.WriteString("\"");
419 if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
420 }
421
422 writer.WriteEndElement(); // xsl:for-each
423 writer.WriteEndElement(); // xsl-template
424 writer.WriteEndElement(); // xsl:stylesheet
425 writer.WriteEndDocument();
426 }
427 catch (Exception Ex)
428 {
429 throw Ex;
430 }
431 }
432
433 public static string ReplaceSpecialChars(string input)
434 {
435 // space -> _x0020_
436 // % -> _x0025_
437 // # -> _x0023_
438 // & -> _x0026_
439 // / -> _x002F_
440
441 input = input.Replace(" ", "_x0020_")
442 .Replace("%", "_x0025_")
443 .Replace("#", "_x0023_")
444 .Replace("&", "_x0026_")
445 .Replace("/", "_x002F_");
446
447 return input;
448 }
449 /// <summary>
450 /// 根据数据列的列名取数据列的列索引
451 /// </summary>
452 /// <param name="dcc">数据列集合</param>
453 /// <param name="columnName">数据列的列名</param>
454 /// <returns></returns>
455 public static int GetColumnIndexByColumnName(DataColumnCollection dcc, string columnName)
456 {
457 int result = -1;
458
459 for (int i = 0; i < dcc.Count; i++)
460 {
461 if (dcc[i].ColumnName.ToLower() == columnName.ToLower())
462 {
463 result = i;
464 break;
465 }
466 }
467
468 return result;
469 }
470 #endregion // WriteStylesheet
471
472 }
