From codeproject . very good.
1// ---------------------------------------------------------
2// Rama Krishna's Export class
3// Copyright (C) 2004 Rama Krishna. All rights reserved.
4// ---------------------------------------------------------
5
6# region Includes
7
8using System;
9using System.Data;
10using System.Web;
11using System.Web.SessionState;
12using System.IO;
13using System.Text;
14using System.Xml;
15using System.Xml.Xsl;
16using System.Threading;
17
18# endregion // Includes
19
20namespace RKLib.ExportData
21{
22 # region Summary
23
24 /**//// <summary>
25 /// Exports datatable to CSV or Excel format.
26 /// This uses DataSet's XML features and XSLT for exporting.
27 ///
28 /// C#.Net Example to be used in WebForms
29 /// -------------------------------------
30 /// using MyLib.ExportData;
31 ///
32 /// private void btnExport_Click(object sender, System.EventArgs e)
33 /// {
34 /// try
35 /// {
36 /// // Declarations
37 /// DataSet dsUsers = ((DataSet) Session["dsUsers"]).Copy( );
38 /// MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web");
39 /// string FileName = "UserList.csv";
40 /// int[] ColList = {2, 3, 4, 5, 6};
41 /// oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.ExportFormat.CSV, FileName);
42 /// }
43 /// catch(Exception Ex)
44 /// {
45 /// lblError.Text = Ex.Message;
46 /// }
47 /// }
48 ///
49 /// VB.Net Example to be used in WindowsForms
50 /// -----------------------------------------
51 /// Imports MyLib.ExportData
52 ///
53 /// Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
54 ///
55 /// Try
56 ///
57 /// 'Declarations
58 /// Dim dsUsers As DataSet = (CType(Session("dsUsers"), DataSet)).Copy()
59 /// Dim oExport As New MyLib.ExportData.Export("Win")
60 /// Dim FileName As String = "C:\\UserList.xls"
61 /// Dim ColList() As Integer = New Integer() {2, 3, 4, 5, 6}
62 /// oExport.ExportDetails(dsUsers.Tables(0), ColList, Export.ExportFormat.CSV, FileName)
63 ///
64 /// Catch Ex As Exception
65 /// lblError.Text = Ex.Message
66 /// End Try
67 ///
68 /// End Sub
69 ///
70 /// </summary>
71
72 # endregion // Summary
73
74 public class Export
75 {
76 public enum ExportFormat : int {CSV = 1, Excel = 2}; // Export format enumeration
77 System.Web.HttpResponse response;
78 private string appType;
79
80 public Export()
81 {
82 appType = "Web";
83 response = System.Web.HttpContext.Current.Response;
84 }
85
86 public Export(string ApplicationType)
87 {
88 appType = ApplicationType;
89 if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
90 if (appType == "Web") response = System.Web.HttpContext.Current.Response;
91 }
92
93 ExportDetails OverLoad : Type#1#region ExportDetails OverLoad : Type#1
94
95 // Function : ExportDetails
96 // Arguments : DetailsTable, FormatType, FileName
97 // Purpose : To get all the column headers in the datatable and
98 // exorts in CSV / Excel format with all columns
99
100 public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)
101 {
102 try
103 {
104 if(DetailsTable.Rows.Count == 0)
105 throw new Exception("There are no details to export.");
106
107 // Create Dataset
108 DataSet dsExport = new DataSet("Export");
109 DataTable dtExport = DetailsTable.Copy();
110 dtExport.TableName = "Values";
111 dsExport.Tables.Add(dtExport);
112
113 // Getting Field Names
114 string[] sHeaders = new string[dtExport.Columns.Count];
115 string[] sFileds = new string[dtExport.Columns.Count];
116
117 for (int i=0; i < dtExport.Columns.Count; i++)
118 {
119 sHeaders[i] = dtExport.Columns[i].ColumnName;
120 sFileds[i] = dtExport.Columns[i].ColumnName;
121 }
122
123 if(appType == "Web")
124 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
125 else if(appType == "Win")
126 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
127 }
128 catch(Exception Ex)
129 {
130 throw Ex;
131 }
132 }
133
134 #endregion // ExportDetails OverLoad : Type#1
135
136 ExportDetails OverLoad : Type#2#region ExportDetails OverLoad : Type#2
137
138 // Function : ExportDetails
139 // Arguments : DetailsTable, ColumnList, FormatType, FileName
140 // Purpose : To get the specified column headers in the datatable and
141 // exorts in CSV / Excel format with specified columns
142
143 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
144 {
145 try
146 {
147 if(DetailsTable.Rows.Count == 0)
148 throw new Exception("There are no details to export");
149
150 // Create Dataset
151 DataSet dsExport = new DataSet("Export");
152 DataTable dtExport = DetailsTable.Copy();
153 dtExport.TableName = "Values";
154 dsExport.Tables.Add(dtExport);
155
156 if(ColumnList.Length > dtExport.Columns.Count)
157 throw new Exception("ExportColumn List should not exceed Total Columns");
158
159 // Getting Field Names
160 string[] sHeaders = new string[ColumnList.Length];
161 string[] sFileds = new string[ColumnList.Length];
162
163 for (int i=0; i < ColumnList.Length; i++)
164 {
165 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
166 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
167
168 sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
169 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
170 }
171
172 if(appType == "Web")
173 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
174 else if(appType == "Win")
175 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
176 }
177 catch(Exception Ex)
178 {
179 throw Ex;
180 }
181 }
182
183 #endregion // ExportDetails OverLoad : Type#2
184
185 ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
186
187 // Function : ExportDetails
188 // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
189 // Purpose : To get the specified column headers in the datatable and
190 // exorts in CSV / Excel format with specified columns and
191 // with specified headers
192
193 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType,
194 string FileName)
195 {
196 try
197 {
198 if(DetailsTable.Rows.Count == 0)
199 throw new Exception("There are no details to export");
200
201 // Create Dataset
202 DataSet dsExport = new DataSet("Export");
203 DataTable dtExport = DetailsTable.Copy();
204 dtExport.TableName = "Values";
205 dsExport.Tables.Add(dtExport);
206
207 if(ColumnList.Length != Headers.Length)
208 throw new Exception("ExportColumn List and Headers List should be of same length");
209 else if(ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
210 throw new Exception("ExportColumn List should not exceed Total Columns");
211
212 // Getting Field Names
213 string[] sFileds = new string[ColumnList.Length];
214
215 for (int i=0; i < ColumnList.Length; i++)
216 {
217 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
218 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
219
220 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
221 }
222
223 if(appType == "Web")
224 Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
225 else if(appType == "Win")
226 Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
227 }
228 catch(Exception Ex)
229 {
230 throw Ex;
231 }
232 }
233
234 #endregion // ExportDetails OverLoad : Type#3
235
236 Export_with_XSLT_Web#region Export_with_XSLT_Web
237
238 // Function : Export_with_XSLT_Web
239 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
240 // Purpose : Exports dataset into CSV / Excel format
241
242 private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
243 {
244 try
245 {
246 // Appending Headers
247 response.Clear();
248 response.Buffer= true;
249
250 if(FormatType == ExportFormat.CSV)
251 {
252 response.ContentType = "text/csv";
253 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
254 }
255 else
256 {
257 response.ContentType = "application/vnd.ms-excel";
258 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
259 }
260
261 // XSLT to use for transforming this dataset.
262 MemoryStream stream = new MemoryStream( );
263 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
264
265 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
266 writer.Flush( );
267 stream.Seek( 0, SeekOrigin.Begin);
268
269 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
270 XslTransform xslTran = new XslTransform();
271 xslTran.Load(new XmlTextReader(stream), null, null);
272
273 System.IO.StringWriter sw = new System.IO.StringWriter();
274 xslTran.Transform(xmlDoc, null, sw, null);
275
276 //Writeout the Content
277 response.Write(sw.ToString());
278 sw.Close();
279 writer.Close();
280 stream.Close();
281 response.End();
282 }
283 catch(ThreadAbortException Ex)
284 {
285 string ErrMsg = Ex.Message;
286 }
287 catch(Exception Ex)
288 {
289 throw Ex;
290 }
291 }
292
293 #endregion // Export_with_XSLT
294
295 Export_with_XSLT_Windows#region Export_with_XSLT_Windows
296
297 // Function : Export_with_XSLT_Windows
298 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
299 // Purpose : Exports dataset into CSV / Excel format
300
301 private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
302 {
303 try
304 {
305 // XSLT to use for transforming this dataset.
306 MemoryStream stream = new MemoryStream( );
307 XmlTextWriter writer = new XmlTextWriter(stream , Encoding.GetEncoding("gb2312"));
308
309 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
310 writer.Flush( );
311 stream.Seek( 0, SeekOrigin.Begin);
312
313 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
314 XslTransform xslTran = new XslTransform();
315 xslTran.Load(new XmlTextReader(stream), null, null);
316
317
318 System.IO.StringWriter sw = new System.IO.StringWriter();
319
320 xslTran.Transform(xmlDoc, null, sw, null);
321
322
323 //Writeout the Content
324 //Stream stream = new Stream(
325
326 StreamWriter strwriter = new StreamWriter( FileName , false ,Encoding.GetEncoding("gb2312")) ;// , Encoding.GetEncoding("gb2312") );
327
328
329 strwriter.WriteLine(sw.ToString().Replace( "no_of_replies","No Of Replies" ) );
330 strwriter.Close();
331
332 sw.Close();
333 writer.Close();
334 stream.Close();
335 }
336 catch(Exception Ex)
337 {
338 throw Ex;
339 }
340 }
341
342 #endregion // Export_with_XSLT
343
344 CreateStylesheet#region CreateStylesheet
345
346 // Function : WriteStylesheet
347 // Arguments : writer, sHeaders, sFileds, FormatType
348 // Purpose : Creates XSLT file to apply on dataset's XML file
349
350 private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
351 {
352 try
353 {
354 // xsl:stylesheet
355 string ns = "http://www.w3.org/1999/XSL/Transform";
356 writer.Formatting = Formatting.Indented;
357 writer.WriteStartDocument( );
358 writer.WriteStartElement("xsl","stylesheet",ns);
359 writer.WriteAttributeString("version","1.0");
360 writer.WriteStartElement("xsl:output");
361 writer.WriteAttributeString("method","text");
362 writer.WriteAttributeString("version","4.0");
363 writer.WriteEndElement( );
364
365 // xsl-template
366 writer.WriteStartElement("xsl:template");
367 writer.WriteAttributeString("match","/");
368
369 // xsl:value-of for headers
370 for(int i=0; i< sHeaders.Length; i++)
371 {
372 writer.WriteString("\"");
373 writer.WriteStartElement("xsl:value-of");
374 writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
375 writer.WriteEndElement( ); // xsl:value-of
376 writer.WriteString("\"");
377 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
378 }
379
380 // xsl:for-each
381 writer.WriteStartElement("xsl:for-each");
382 writer.WriteAttributeString("select","Export/Values");
383 writer.WriteString("\r\n");
384
385 // xsl:value-of for data fields
386 for(int i=0; i< sFileds.Length; i++)
387 {
388 writer.WriteString("\"");
389 writer.WriteStartElement("xsl:value-of");
390 writer.WriteAttributeString("select", sFileds[i]);
391 writer.WriteEndElement( ); // xsl:value-of
392 writer.WriteString("\"");
393 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
394 }
395
396 writer.WriteEndElement( ); // xsl:for-each
397 writer.WriteEndElement( ); // xsl-template
398 writer.WriteEndElement( ); // xsl:stylesheet
399 writer.WriteEndDocument( );
400 }
401 catch(Exception Ex)
402 {
403 throw Ex;
404 }
405 }
406
407 #endregion // WriteStylesheet
408
409 }
410}
411