本文发表在 rolia.net 枫下论坛使用Microsoft Vistual Stdio最让人赞不绝口的是它的智能完成，而我们在写SQL语句的时候却还是最原始的方法一个一个字母的输入，我们必须记住每一张表名、字段名、表之间的关联字段信息等等，如何有效的提高开发速度，答案就是实现类似MS的智能完成功能。
SpDevelop已经实现了数据库设计和基本的查询分析器，在这个基础上开发继续开发，现在已经实现了一下几个功能：
一、智能完成功能
* 表/视图名称完成
* 列名称完成
* JOIN/JOIN ON 完成
* 自动把关键字转换成大写
* 关键字自动弹出
二、支持多种数据库
查询分析器可以支持对MsSQL,Oracle,Sybase,MySQL,ProstgreSQL等多种数据库的操作。
三、多种导入导出功能
可以将查询出来的结果导出成XML,Text,Excel等格式的数据文件
开发心得:
1、我们采用SharpDevelop中的开源编辑器ICSharpCode.TextEditor为编辑器，并指定为TSQL格式，这个开源的编辑器虽然比不上一些商用的，但基本够用而且速度比较快（打开一兆的SQL语句速度基本不影响), 应用代码如下：
1 TextEditorControl txtEditor = new TextEditorControl();
2 txtEditor.SetHighlighting("TSQL"); //实现SQL着色
3 txtEditor.ShowEOLMarkers = false;
4 txtEditor.ShowSpaces = false;
5 txtEditor.ShowTabs = false;
6 txtEditor.ShowInvalidLines = false;
7 txtEditor.Dock = DockStyle.Fill;
8 txtEditor.VRulerRow = 200;2、SQL语句的分析，由于SQL语法比较有限，自己编写一个语法分析器。这个语法分析器的难点在于性能的优化上。
3、SpDevelop框架和其中数据库建模插件中已经提供了一些编程接口，获取数据库建模中数据库信息或者连接上各种数据库获取数据库信息。为了实现能够从各种数据库中灵活的获取信息，注意使用继承机制和单一模式来编程
4、导出数据,我们这里就没有再做重复的工作，采用了Rama Krishna一个比较好的导出类
1# region Includes
2
3using System;
4using System.Data;
5using System.Web;
6using System.Web.SessionState;
7using System.IO;
8using System.Text;
9using System.Xml;
10using System.Xml.Xsl;
11using System.Threading;
12
13# endregion // Includes
14
15namespace MyLib.ExportData
16{
17 # region Summary
18
19 /**//// <summary>
20 /// Exports datatable to CSV or Excel format.
21 /// This uses DataSet's XML features and XSLT for exporting.
22 ///
23 /// C#.Net Example to be used in WebForms
24 /// -------------------------------------
25 /// using MyLib.ExportData;
26 ///
27 /// private void btnExport_Click(object sender, System.EventArgs e)
28 /// {
29 /// try
30 /// {
31 /// // Declarations
32 /// DataSet dsUsers = ((DataSet) Session["dsUsers"]).Copy( );
33 /// MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web");
34 /// string FileName = "UserList.csv";
35 /// int[] ColList = {2, 3, 4, 5, 6};
36 /// oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.ExportFormat.CSV, FileName);
37 /// }
38 /// catch(Exception Ex)
39 /// {
40 /// lblError.Text = Ex.Message;
41 /// }
42 /// }
43 /// </summary>
44
45 # endregion // Summary
46
47 public class ExportData
48 {
49 public enum ExportFormat : int {CSV = 1, Excel = 2}; // Export format enumeration
50 System.Web.HttpResponse response;
51 private string appType;
52
53 public ExportData()
54 {
55 appType = "Web";
56 response = System.Web.HttpContext.Current.Response;
57 }
58
59 public ExportData(string ApplicationType)
60 {
61 appType = ApplicationType;
62 if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
63 if (appType == "Web") response = System.Web.HttpContext.Current.Response;
64 }
65
66 ExportDetails OverLoad : Type#1#region ExportDetails OverLoad : Type#1
67
68 // Function : ExportDetails
69 // Arguments : DetailsTable, FormatType, FileName
70 // Purpose : To get all the column headers in the datatable and
71 // exorts in CSV / Excel format with all columns
72
73 public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)
74 {
75 try
76 {
77 if(DetailsTable.Rows.Count == 0)
78 throw new Exception("There are no details to export.");
79
80 // Create Dataset
81 DataSet dsExport = new DataSet("Export");
82 DataTable dtExport = DetailsTable.Copy();
83 dtExport.TableName = "Values";
84 dsExport.Tables.Add(dtExport);
85
86 // Getting Field Names
87 string[] sHeaders = new string[dtExport.Columns.Count];
88 string[] sFileds = new string[dtExport.Columns.Count];
89
90 for (int i=0; i < dtExport.Columns.Count; i++)
91 {
92 sHeaders[i] = dtExport.Columns[i].ColumnName;
93 sFileds[i] = dtExport.Columns[i].ColumnName;
94 }
95
96 if(appType == "Web")
97 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
98 else if(appType == "Win")
99 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
100 }
101 catch(Exception Ex)
102 {
103 throw Ex;
104 }
105 }
106
107 #endregion // ExportDetails OverLoad : Type#1
108
109 ExportDetails OverLoad : Type#2#region ExportDetails OverLoad : Type#2
110
111 // Function : ExportDetails
112 // Arguments : DetailsTable, ColumnList, FormatType, FileName
113 // Purpose : To get the specified column headers in the datatable and
114 // exorts in CSV / Excel format with specified columns
115
116 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
117 {
118 try
119 {
120 if(DetailsTable.Rows.Count == 0)
121 throw new Exception("There are no details to export");
122
123 // Create Dataset
124 DataSet dsExport = new DataSet("Export");
125 DataTable dtExport = DetailsTable.Copy();
126 dtExport.TableName = "Values";
127 dsExport.Tables.Add(dtExport);
128
129 if(ColumnList.Length > dtExport.Columns.Count)
130 throw new Exception("ExportColumn List should not exceed Total Columns");
131
132 // Getting Field Names
133 string[] sHeaders = new string[ColumnList.Length];
134 string[] sFileds = new string[ColumnList.Length];
135
136 for (int i=0; i < ColumnList.Length; i++)
137 {
138 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
139 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
140
141 sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
142 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
143 }
144
145 if(appType == "Web")
146 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
147 else if(appType == "Win")
148 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
149 }
150 catch(Exception Ex)
151 {
152 throw Ex;
153 }
154 }
155
156 #endregion // ExportDetails OverLoad : Type#2
157
158 ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
159
160 // Function : ExportDetails
161 // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
162 // Purpose : To get the specified column headers in the datatable and
163 // exorts in CSV / Excel format with specified columns and
164 // with specified headers
165
166 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType,
167 string FileName)
168 {
169 try
170 {
171 if(DetailsTable.Rows.Count == 0)
172 throw new Exception("There are no details to export");
173
174 // Create Dataset
175 DataSet dsExport = new DataSet("Export");
176 DataTable dtExport = DetailsTable.Copy();
177 dtExport.TableName = "Values";
178 dsExport.Tables.Add(dtExport);
179
180 if(ColumnList.Length != Headers.Length)
181 throw new Exception("ExportColumn List and Headers List should be of same length");
182 else if(ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
183 throw new Exception("ExportColumn List should not exceed Total Columns");
184
185 // Getting Field Names
186 string[] sFileds = new string[ColumnList.Length];
187
188 for (int i=0; i < ColumnList.Length; i++)
189 {
190 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
191 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
192
193 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
194 }
195
196 if(appType == "Web")
197 Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
198 else if(appType == "Win")
199 Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
200 }
201 catch(Exception Ex)
202 {
203 throw Ex;
204 }
205 }
206
207 #endregion // ExportDetails OverLoad : Type#3
208
209 ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
210
211 // Function : ExportDetails
212 // Arguments : DetailsTable, FormatType, FileName
213 // Purpose : To get all the column headers in the datatable and
214 // exorts in CSV / Excel format with all columns
215
216 public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName)
217 {
218 try
219 {
220 string NewFileName;
221
222 foreach(DataTable DetailsTable in DetailsTables)
223 {
224 if(DetailsTable.Rows.Count == 0)
225 throw new Exception("There are no details to export.");
226
227 NewFileName = FileName.Substring(0,FileName.LastIndexOf("."));
228 NewFileName+= " - " + DetailsTable.TableName;
229 NewFileName+= FileName.Substring(FileName.LastIndexOf("."));
230
231 // Create Dataset
232 DataSet dsExport = new DataSet("Export");
233 DataTable dtExport = DetailsTable.Copy();
234 dtExport.TableName = "Values";
235 dsExport.Tables.Add(dtExport);
236
237 // Getting Field Names
238 string[] sHeaders = new string[dtExport.Columns.Count];
239 string[] sFileds = new string[dtExport.Columns.Count];
240
241 for (int i=0; i < dtExport.Columns.Count; i++)
242 {
243 sHeaders[i] = dtExport.Columns[i].ColumnName;
244 sFileds[i] = dtExport.Columns[i].ColumnName;
245 }
246
247 if(appType == "Web")
248 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, NewFileName);
249 else if(appType == "Win")
250 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, NewFileName);
251 }
252 }
253 catch(Exception Ex)
254 {
255 throw Ex;
256 }
257 }
258
259 #endregion //ExportDetails OverLoad : Type#4
260
261 Export_with_XSLT_Web#region Export_with_XSLT_Web
262
263 // Function : Export_with_XSLT_Web
264 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
265 // Purpose : Exports dataset into CSV / Excel format
266
267 private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
268 {
269 try
270 {
271 // Appending Headers
272 response.Clear();
273 response.Buffer= true;
274
275 if(FormatType == ExportFormat.CSV)
276 {
277 response.ContentType = "text/csv";
278 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
279 }
280 else
281 {
282 response.ContentType = "application/vnd.ms-excel";
283 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
284 }
285
286 // XSLT to use for transforming this dataset.
287 MemoryStream stream = new MemoryStream( );
288 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);
289
290 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
291 writer.Flush( );
292 stream.Seek( 0, SeekOrigin.Begin);
293
294 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
295 XslTransform xslTran = new XslTransform();
296 xslTran.Load(new XmlTextReader(stream), null, null);
297
298 System.IO.StringWriter sw = new System.IO.StringWriter();
299 xslTran.Transform(xmlDoc, null, sw, null);
300
301 //Writeout the Content
302 response.Write(sw.ToString());
303 sw.Close();
304 writer.Close();
305 stream.Close();
306 response.End();
307 }
308 catch(ThreadAbortException Ex)
309 {
310 string ErrMsg = Ex.Message;
311 }
312 catch(Exception Ex)
313 {
314 throw Ex;
315 }
316 }
317
318 #endregion // Export_with_XSLT
319
320 Export_with_XSLT_Windows#region Export_with_XSLT_Windows
321
322 // Function : Export_with_XSLT_Windows
323 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
324 // Purpose : Exports dataset into CSV / Excel format
325
326 private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
327 {
328
329 try
330 {
331 // XSLT to use for transforming this dataset.
332 MemoryStream stream = new MemoryStream( );
333 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
334
335 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
336 writer.Flush( );
337 stream.Seek( 0, SeekOrigin.Begin);
338
339 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
340 XslTransform xslTran = new XslTransform();
341 xslTran.Load(new XmlTextReader(stream), null, null);
342
343 System.IO.StringWriter sw = new System.IO.StringWriter();
344 xslTran.Transform(xmlDoc, null, sw, null);
345
346 //Writeout the Content
347 StreamWriter strwriter = new StreamWriter(FileName,false,Encoding.Default);
348 strwriter.WriteLine(sw.ToString());
349 strwriter.Close();
350
351 sw.Close();
352 writer.Close();
353 stream.Close();
354 }
355 catch(Exception Ex)
356 {
357 throw Ex;
358 }
359 }
360
361 #endregion // Export_with_XSLT
362
363 CreateStylesheet#region CreateStylesheet
364
365 // Function : WriteStylesheet
366 // Arguments : writer, sHeaders, sFileds, FormatType
367 // Purpose : Creates XSLT file to apply on dataset's XML file
368
369 private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
370 {
371 try
372 {
373 // xsl:stylesheet
374 string ns = "http://www.w3.org/1999/XSL/Transform";
375 writer.Formatting = Formatting.Indented;
376 writer.WriteStartDocument( );
377 writer.WriteStartElement("xsl","stylesheet",ns);
378 writer.WriteAttributeString("version","1.0");
379 writer.WriteStartElement("xsl:output");
380 writer.WriteAttributeString("method","text");
381 writer.WriteAttributeString("version","4.0");
382 writer.WriteEndElement( );
383
384 // xsl-template
385 writer.WriteStartElement("xsl:template");
386 writer.WriteAttributeString("match","/");
387
388 // xsl:value-of for headers
389 for(int i=0; i< sHeaders.Length; i++)
390 {
391 writer.WriteString("\"");
392 writer.WriteStartElement("xsl:value-of");
393 writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
394 writer.WriteEndElement( ); // xsl:value-of
395 writer.WriteString("\"");
396 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
397 }
398
399 // xsl:for-each
400 writer.WriteStartElement("xsl:for-each");
401 writer.WriteAttributeString("select","Export/Values");
402 writer.WriteString("\r\n");
403
404 // xsl:value-of for data fields
405 for(int i=0; i< sFileds.Length; i++)
406 {
407 writer.WriteString("\"");
408 writer.WriteStartElement("xsl:value-of");
409 writer.WriteAttributeString("select", sFileds[i]);
410 writer.WriteEndElement( ); // xsl:value-of
411 writer.WriteString("\"");
412 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
413 }
414
415 writer.WriteEndElement( ); // xsl:for-each
416 writer.WriteEndElement( ); // xsl-template
417 writer.WriteEndElement( ); // xsl:stylesheet
418 writer.WriteEndDocument( );
419 }
420 catch(Exception Ex)
421 {
422 throw Ex;
423 }
424 }
425
426 #endregion // WriteStylesheet
427
428 }
429}
现在本插件基本已经可以满足一些日常的操作，可到 www.spdevelop.com下载。更多精彩文章及讨论，请光临枫下论坛 rolia.net
SpDevelop已经实现了数据库设计和基本的查询分析器，在这个基础上开发继续开发，现在已经实现了一下几个功能：
一、智能完成功能
* 表/视图名称完成
* 列名称完成
* JOIN/JOIN ON 完成
* 自动把关键字转换成大写
* 关键字自动弹出
二、支持多种数据库
查询分析器可以支持对MsSQL,Oracle,Sybase,MySQL,ProstgreSQL等多种数据库的操作。
三、多种导入导出功能
可以将查询出来的结果导出成XML,Text,Excel等格式的数据文件
开发心得:
1、我们采用SharpDevelop中的开源编辑器ICSharpCode.TextEditor为编辑器，并指定为TSQL格式，这个开源的编辑器虽然比不上一些商用的，但基本够用而且速度比较快（打开一兆的SQL语句速度基本不影响), 应用代码如下：
1 TextEditorControl txtEditor = new TextEditorControl();
2 txtEditor.SetHighlighting("TSQL"); //实现SQL着色
3 txtEditor.ShowEOLMarkers = false;
4 txtEditor.ShowSpaces = false;
5 txtEditor.ShowTabs = false;
6 txtEditor.ShowInvalidLines = false;
7 txtEditor.Dock = DockStyle.Fill;
8 txtEditor.VRulerRow = 200;2、SQL语句的分析，由于SQL语法比较有限，自己编写一个语法分析器。这个语法分析器的难点在于性能的优化上。
3、SpDevelop框架和其中数据库建模插件中已经提供了一些编程接口，获取数据库建模中数据库信息或者连接上各种数据库获取数据库信息。为了实现能够从各种数据库中灵活的获取信息，注意使用继承机制和单一模式来编程
4、导出数据,我们这里就没有再做重复的工作，采用了Rama Krishna一个比较好的导出类
1# region Includes
2
3using System;
4using System.Data;
5using System.Web;
6using System.Web.SessionState;
7using System.IO;
8using System.Text;
9using System.Xml;
10using System.Xml.Xsl;
11using System.Threading;
12
13# endregion // Includes
14
15namespace MyLib.ExportData
16{
17 # region Summary
18
19 /**//// <summary>
20 /// Exports datatable to CSV or Excel format.
21 /// This uses DataSet's XML features and XSLT for exporting.
22 ///
23 /// C#.Net Example to be used in WebForms
24 /// -------------------------------------
25 /// using MyLib.ExportData;
26 ///
27 /// private void btnExport_Click(object sender, System.EventArgs e)
28 /// {
29 /// try
30 /// {
31 /// // Declarations
32 /// DataSet dsUsers = ((DataSet) Session["dsUsers"]).Copy( );
33 /// MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web");
34 /// string FileName = "UserList.csv";
35 /// int[] ColList = {2, 3, 4, 5, 6};
36 /// oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.ExportFormat.CSV, FileName);
37 /// }
38 /// catch(Exception Ex)
39 /// {
40 /// lblError.Text = Ex.Message;
41 /// }
42 /// }
43 /// </summary>
44
45 # endregion // Summary
46
47 public class ExportData
48 {
49 public enum ExportFormat : int {CSV = 1, Excel = 2}; // Export format enumeration
50 System.Web.HttpResponse response;
51 private string appType;
52
53 public ExportData()
54 {
55 appType = "Web";
56 response = System.Web.HttpContext.Current.Response;
57 }
58
59 public ExportData(string ApplicationType)
60 {
61 appType = ApplicationType;
62 if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
63 if (appType == "Web") response = System.Web.HttpContext.Current.Response;
64 }
65
66 ExportDetails OverLoad : Type#1#region ExportDetails OverLoad : Type#1
67
68 // Function : ExportDetails
69 // Arguments : DetailsTable, FormatType, FileName
70 // Purpose : To get all the column headers in the datatable and
71 // exorts in CSV / Excel format with all columns
72
73 public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)
74 {
75 try
76 {
77 if(DetailsTable.Rows.Count == 0)
78 throw new Exception("There are no details to export.");
79
80 // Create Dataset
81 DataSet dsExport = new DataSet("Export");
82 DataTable dtExport = DetailsTable.Copy();
83 dtExport.TableName = "Values";
84 dsExport.Tables.Add(dtExport);
85
86 // Getting Field Names
87 string[] sHeaders = new string[dtExport.Columns.Count];
88 string[] sFileds = new string[dtExport.Columns.Count];
89
90 for (int i=0; i < dtExport.Columns.Count; i++)
91 {
92 sHeaders[i] = dtExport.Columns[i].ColumnName;
93 sFileds[i] = dtExport.Columns[i].ColumnName;
94 }
95
96 if(appType == "Web")
97 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
98 else if(appType == "Win")
99 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
100 }
101 catch(Exception Ex)
102 {
103 throw Ex;
104 }
105 }
106
107 #endregion // ExportDetails OverLoad : Type#1
108
109 ExportDetails OverLoad : Type#2#region ExportDetails OverLoad : Type#2
110
111 // Function : ExportDetails
112 // Arguments : DetailsTable, ColumnList, FormatType, FileName
113 // Purpose : To get the specified column headers in the datatable and
114 // exorts in CSV / Excel format with specified columns
115
116 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
117 {
118 try
119 {
120 if(DetailsTable.Rows.Count == 0)
121 throw new Exception("There are no details to export");
122
123 // Create Dataset
124 DataSet dsExport = new DataSet("Export");
125 DataTable dtExport = DetailsTable.Copy();
126 dtExport.TableName = "Values";
127 dsExport.Tables.Add(dtExport);
128
129 if(ColumnList.Length > dtExport.Columns.Count)
130 throw new Exception("ExportColumn List should not exceed Total Columns");
131
132 // Getting Field Names
133 string[] sHeaders = new string[ColumnList.Length];
134 string[] sFileds = new string[ColumnList.Length];
135
136 for (int i=0; i < ColumnList.Length; i++)
137 {
138 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
139 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
140
141 sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
142 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
143 }
144
145 if(appType == "Web")
146 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
147 else if(appType == "Win")
148 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
149 }
150 catch(Exception Ex)
151 {
152 throw Ex;
153 }
154 }
155
156 #endregion // ExportDetails OverLoad : Type#2
157
158 ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
159
160 // Function : ExportDetails
161 // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
162 // Purpose : To get the specified column headers in the datatable and
163 // exorts in CSV / Excel format with specified columns and
164 // with specified headers
165
166 public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType,
167 string FileName)
168 {
169 try
170 {
171 if(DetailsTable.Rows.Count == 0)
172 throw new Exception("There are no details to export");
173
174 // Create Dataset
175 DataSet dsExport = new DataSet("Export");
176 DataTable dtExport = DetailsTable.Copy();
177 dtExport.TableName = "Values";
178 dsExport.Tables.Add(dtExport);
179
180 if(ColumnList.Length != Headers.Length)
181 throw new Exception("ExportColumn List and Headers List should be of same length");
182 else if(ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
183 throw new Exception("ExportColumn List should not exceed Total Columns");
184
185 // Getting Field Names
186 string[] sFileds = new string[ColumnList.Length];
187
188 for (int i=0; i < ColumnList.Length; i++)
189 {
190 if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
191 throw new Exception("ExportColumn Number should not exceed Total Columns Range");
192
193 sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
194 }
195
196 if(appType == "Web")
197 Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
198 else if(appType == "Win")
199 Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
200 }
201 catch(Exception Ex)
202 {
203 throw Ex;
204 }
205 }
206
207 #endregion // ExportDetails OverLoad : Type#3
208
209 ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
210
211 // Function : ExportDetails
212 // Arguments : DetailsTable, FormatType, FileName
213 // Purpose : To get all the column headers in the datatable and
214 // exorts in CSV / Excel format with all columns
215
216 public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName)
217 {
218 try
219 {
220 string NewFileName;
221
222 foreach(DataTable DetailsTable in DetailsTables)
223 {
224 if(DetailsTable.Rows.Count == 0)
225 throw new Exception("There are no details to export.");
226
227 NewFileName = FileName.Substring(0,FileName.LastIndexOf("."));
228 NewFileName+= " - " + DetailsTable.TableName;
229 NewFileName+= FileName.Substring(FileName.LastIndexOf("."));
230
231 // Create Dataset
232 DataSet dsExport = new DataSet("Export");
233 DataTable dtExport = DetailsTable.Copy();
234 dtExport.TableName = "Values";
235 dsExport.Tables.Add(dtExport);
236
237 // Getting Field Names
238 string[] sHeaders = new string[dtExport.Columns.Count];
239 string[] sFileds = new string[dtExport.Columns.Count];
240
241 for (int i=0; i < dtExport.Columns.Count; i++)
242 {
243 sHeaders[i] = dtExport.Columns[i].ColumnName;
244 sFileds[i] = dtExport.Columns[i].ColumnName;
245 }
246
247 if(appType == "Web")
248 Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, NewFileName);
249 else if(appType == "Win")
250 Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, NewFileName);
251 }
252 }
253 catch(Exception Ex)
254 {
255 throw Ex;
256 }
257 }
258
259 #endregion //ExportDetails OverLoad : Type#4
260
261 Export_with_XSLT_Web#region Export_with_XSLT_Web
262
263 // Function : Export_with_XSLT_Web
264 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
265 // Purpose : Exports dataset into CSV / Excel format
266
267 private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
268 {
269 try
270 {
271 // Appending Headers
272 response.Clear();
273 response.Buffer= true;
274
275 if(FormatType == ExportFormat.CSV)
276 {
277 response.ContentType = "text/csv";
278 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
279 }
280 else
281 {
282 response.ContentType = "application/vnd.ms-excel";
283 response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
284 }
285
286 // XSLT to use for transforming this dataset.
287 MemoryStream stream = new MemoryStream( );
288 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);
289
290 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
291 writer.Flush( );
292 stream.Seek( 0, SeekOrigin.Begin);
293
294 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
295 XslTransform xslTran = new XslTransform();
296 xslTran.Load(new XmlTextReader(stream), null, null);
297
298 System.IO.StringWriter sw = new System.IO.StringWriter();
299 xslTran.Transform(xmlDoc, null, sw, null);
300
301 //Writeout the Content
302 response.Write(sw.ToString());
303 sw.Close();
304 writer.Close();
305 stream.Close();
306 response.End();
307 }
308 catch(ThreadAbortException Ex)
309 {
310 string ErrMsg = Ex.Message;
311 }
312 catch(Exception Ex)
313 {
314 throw Ex;
315 }
316 }
317
318 #endregion // Export_with_XSLT
319
320 Export_with_XSLT_Windows#region Export_with_XSLT_Windows
321
322 // Function : Export_with_XSLT_Windows
323 // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
324 // Purpose : Exports dataset into CSV / Excel format
325
326 private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
327 {
328
329 try
330 {
331 // XSLT to use for transforming this dataset.
332 MemoryStream stream = new MemoryStream( );
333 XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
334
335 CreateStylesheet(writer, sHeaders, sFileds, FormatType);
336 writer.Flush( );
337 stream.Seek( 0, SeekOrigin.Begin);
338
339 XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
340 XslTransform xslTran = new XslTransform();
341 xslTran.Load(new XmlTextReader(stream), null, null);
342
343 System.IO.StringWriter sw = new System.IO.StringWriter();
344 xslTran.Transform(xmlDoc, null, sw, null);
345
346 //Writeout the Content
347 StreamWriter strwriter = new StreamWriter(FileName,false,Encoding.Default);
348 strwriter.WriteLine(sw.ToString());
349 strwriter.Close();
350
351 sw.Close();
352 writer.Close();
353 stream.Close();
354 }
355 catch(Exception Ex)
356 {
357 throw Ex;
358 }
359 }
360
361 #endregion // Export_with_XSLT
362
363 CreateStylesheet#region CreateStylesheet
364
365 // Function : WriteStylesheet
366 // Arguments : writer, sHeaders, sFileds, FormatType
367 // Purpose : Creates XSLT file to apply on dataset's XML file
368
369 private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
370 {
371 try
372 {
373 // xsl:stylesheet
374 string ns = "http://www.w3.org/1999/XSL/Transform";
375 writer.Formatting = Formatting.Indented;
376 writer.WriteStartDocument( );
377 writer.WriteStartElement("xsl","stylesheet",ns);
378 writer.WriteAttributeString("version","1.0");
379 writer.WriteStartElement("xsl:output");
380 writer.WriteAttributeString("method","text");
381 writer.WriteAttributeString("version","4.0");
382 writer.WriteEndElement( );
383
384 // xsl-template
385 writer.WriteStartElement("xsl:template");
386 writer.WriteAttributeString("match","/");
387
388 // xsl:value-of for headers
389 for(int i=0; i< sHeaders.Length; i++)
390 {
391 writer.WriteString("\"");
392 writer.WriteStartElement("xsl:value-of");
393 writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
394 writer.WriteEndElement( ); // xsl:value-of
395 writer.WriteString("\"");
396 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
397 }
398
399 // xsl:for-each
400 writer.WriteStartElement("xsl:for-each");
401 writer.WriteAttributeString("select","Export/Values");
402 writer.WriteString("\r\n");
403
404 // xsl:value-of for data fields
405 for(int i=0; i< sFileds.Length; i++)
406 {
407 writer.WriteString("\"");
408 writer.WriteStartElement("xsl:value-of");
409 writer.WriteAttributeString("select", sFileds[i]);
410 writer.WriteEndElement( ); // xsl:value-of
411 writer.WriteString("\"");
412 if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) ? "," : " " );
413 }
414
415 writer.WriteEndElement( ); // xsl:for-each
416 writer.WriteEndElement( ); // xsl-template
417 writer.WriteEndElement( ); // xsl:stylesheet
418 writer.WriteEndDocument( );
419 }
420 catch(Exception Ex)
421 {
422 throw Ex;
423 }
424 }
425
426 #endregion // WriteStylesheet
427
428 }
429}
现在本插件基本已经可以满足一些日常的操作，可到 www.spdevelop.com下载。更多精彩文章及讨论，请光临枫下论坛 rolia.net