数据集DataSet 转入Excel

最新推荐文章于 2017-11-26 08:44:00 发布

wangxiaowen1988

最新推荐文章于 2017-11-26 08:44:00 发布

阅读量336

点赞数

分类专栏： C#.net控件的使用文章标签： dataset excel datagrid null string xml

本文链接：https://blog.csdn.net/wangxiaowen1988/article/details/5999024

版权

C#.net控件的使用专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文介绍了一种将数据集(DataSet)与Excel文件互相转换的方法。包括从Excel文件导入数据到DataSet，以及将DataSet中的数据导出到Excel文件的过程。此外，还涉及了XML文件与DataSet之间的相互转换。

摘要由CSDN通过智能技术生成

以下是我转载得来，有待于继续实践，因为全是代码，而没有语言叙述的流程，所以比较乱

1using System;
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11//*************************************
12public class DataSetToExcel
13 {
14 public DataSetToExcel(){}
15 public void Convert(DataSet oDS,HttpResponse Response)
16 {
17   Response.Clear();
18   Response.Charset = "";
19   Response.ContentType = "application/vnd.ms-excel";
20   System.IO.StringWriter oSW = new System.IO.StringWriter();
21   HtmlTextWriter oHW = new HtmlTextWriter(oSW);
22   DataGrid oDG = new DataGrid();
23   oDG.DataSource = oDS.Tables[0];
24   oDG.DataBind();
25   oDG.RenderControl(oHW);
26   Response.Write(oSW.ToString());
27   Response.Flush();
28   Response.Close();
29 }
30 }
31//*********************************************************
32调用这个类就OK了
33
34
35
36（2）
37
38using System;
39using System.Data;
40using System.Data.OleDb;
41namespace GRIS.ExcelReprot
42{
43 /** <summary>
44 /// ImportExportToExcel 的摘要说明。
45 /// </summary>
46 public class ImportExportToExcel
47 {
48 private string strConn ;
49
50 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
51 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
52
53 public ImportExportToExcel()
54 {
55   //
56   // TODO: 在此处添加构造函数逻辑
57   //
58   this.openFileDlg.DefaultExt = "xls";
59   this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
60
61   this.saveFileDlg.DefaultExt="xls";
62   this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
63
64 }
65
66从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
67   //        /// <summary>
68   //        /// 从Excel导入文件
69   //        /// </summary>
70   //        /// <param name="strExcelFileName">Excel文件名</param>
71   //        /// <returns>返回DataSet</returns>
72   //        public DataSet ImportFromExcel(string strExcelFileName)
73   //        {
74   //            return doImport(strExcelFileName);
75   //        }
76   /**//**//** <summary>
77   /// 从选择的Excel文件导入
78   /// </summary>
79   /// <returns>DataSet</returns>
80   public DataSet ImportFromExcel()
81   {
82    DataSet ds=new DataSet();
83    if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
84     ds=doImport(openFileDlg.FileName);
85    return ds;
86   }
87 /**/ <summary>
88 /// 从指定的Excel文件导入
89 /// </summary>
90 /// <param name="strFileName">Excel文件名</param>
91 /// <returns></returns>
92 public DataSet ImportFromExcel(string strFileName)
93 {
94   DataSet ds=new DataSet();
95   ds=doImport(strFileName);
96   return ds;
97 }
98 /**//**//** <summary>
99 /// 执行导入
100 /// </summary>
101 /// <param name="strFileName">文件名</param>
102 /// <returns>DataSet</returns>
103 private DataSet doImport(string strFileName)
104 {
105   if (strFileName=="") return null;
106
107   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
108    "Data Source=" + strFileName + ";" +
109    "Extended Properties=Excel 8.0;";
110   OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
111
112   DataSet ExcelDs = new DataSet();
113
114   try
115   {
116    ExcelDA.Fill(ExcelDs, "ExcelInfo");
117   }
118   catch(Exception err)
119   {
120    System.Console.WriteLine( err.ToString() );
121   }
122   return ExcelDs;
123 }
124 #endregion
125
126从DataSet到出到Excel#region 从DataSet到出到Excel
127   /**//**//** <summary>
128   /// 导出指定的Excel文件
129   /// </summary>
130   /// <param name="ds">要导出的DataSet</param>
131   /// <param name="strExcelFileName">要导出的Excel文件名</param>
132   public void ExportToExcel(DataSet ds,string strExcelFileName)
133   {
134    if (ds.Tables.Count==0 || strExcelFileName=="") return;
135    doExport(ds,strExcelFileName);
136   }
137 /**//**//** <summary>
138 /// 导出用户选择的Excel文件
139 /// </summary>
140 /// <param name="ds">DataSet</param>
141 public void ExportToExcel(DataSet ds)
142 {
143   if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
144    doExport(ds,saveFileDlg.FileName);
145
146 }
147 /**//**//** <summary>
148 /// 执行导出
149 /// </summary>
150 /// <param name="ds">要导出的DataSet</param>
151 /// <param name="strExcelFileName">要导出的文件名</param>
152 private void doExport(DataSet ds,string strExcelFileName)
153 {
154
155   Excel.Application excel= new Excel.Application();
156
157   //            Excel.Workbook obj=new Excel.WorkbookClass();
158   //            obj.SaveAs("c:/zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
159
160   int rowIndex=1;
161   int colIndex=0;
162
163   excel.Application.Workbooks.Add(true);
164
165
166   System.Data.DataTable table=ds.Tables[0] ;
167   foreach(DataColumn col in table.Columns)
168   {
169    colIndex++;
170    excel.Cells[1,colIndex]=col.ColumnName;
171   }
172
173   foreach(DataRow row in table.Rows)
174   {
175    rowIndex++;
176    colIndex=0;
177    foreach(DataColumn col in table.Columns)
178    {
179     colIndex++;
180     excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
181    }
182   }
183   excel.Visible=false;
184//   excel.Sheets[0] = "sss"; ///?????????????????????//
185   excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null,null);
186
187
188   //wkbNew.SaveAs strBookName
189
190
191   //excel.Save(strExcelFileName);
192   excel.Quit();
193   excel=null;
194
195   GC.Collect();//垃圾回收
196 }
197 #endregion
198
199从XML导入到Dataset#region 从XML导入到Dataset
200
201   /**//**//** <summary>
202   /// 从选择的XML文件导入
203   /// </summary>
204   /// <returns>DataSet</returns>
205   public DataSet ImportFromXML()
206   {
207    DataSet ds=new DataSet();
208    System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
209    openFileDlg.DefaultExt="xml";
210    openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
211    if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
212     try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
213     catch{}
214    return ds;
215   }
216 /**//**//** <summary>
217 /// 从指定的XML文件导入
218 /// </summary>
219 /// <param name="strFileName">XML文件名</param>
220 /// <returns></returns>
221 public DataSet ImportFromXML(string strFileName)
222 {
223   if (strFileName=="")
224    return null;
225   DataSet ds=new DataSet();
226   try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
227   catch{}
228   return ds;
229 }
230
231 #endregion
232
233从DataSet导出到XML#region 从DataSet导出到XML
234   /**//**//** <summary>
235   /// 导出指定的XML文件
236   /// </summary>
237   /// <param name="ds">要导出的DataSet</param>
238   /// <param name="strXMLFileName">要导出的XML文件名</param>
239   public void ExportToXML(DataSet ds,string strXMLFileName)
240   {
241    if (ds.Tables.Count==0 || strXMLFileName=="") return;
242    doExportXML(ds,strXMLFileName);
243   }
244 /**//**//** <summary>
245 /// 导出用户选择的XML文件
246 /// </summary>
247 /// <param name="ds">DataSet</param>
248 public void ExportToXML(DataSet ds)
249 {
250   System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
251   saveFileDlg.DefaultExt="xml";
252   saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
253   if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
254    doExportXML(ds,saveFileDlg.FileName);
255 }
256
257 /**//**//** <summary>
258 /// 执行导出
259 /// </summary>
260 /// <param name="ds">要导出的DataSet</param>
261 /// <param name="strExcelFileName">要导出的XML文件名</param>
262 private void doExportXML(DataSet ds,string strXMLFileName)
263 {
264   try
265   {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
266   catch(Exception ex)
267   {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}
268 }
269
270 #endregion
271 }
272}
273
274public void ExportResult(DataSet ds)
275        {
276            HttpContext.Current.Response.Clear();
277            HttpContext.Current.Response.Charset = "";
278            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
279            StringWriter stringWrite = new StringWriter();
280            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
281
282            DataGrid dg = new DataGrid();
283            dg.DataSource = ds.Tables[0];
284            dg.DataBind();
285            dg.RenderControl(htmlWrite);
286            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=result.xls");
287
288            HttpContext.Current.Response.Write(stringWrite.ToString());
289            HttpContext.Current.Response.End();
290        }
291
292