做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 }