-
如何将dataSet中的数据导入到Excel文件(*.xls)整理
1
using System;
2
using System.Collections;
3
using System.ComponentModel;
4
using System.Data;
5
using System.Drawing;
6
using System.Web;
7
using System.Web.SessionState;
8
using System.Web.UI;
9
using System.Web.UI.WebControls;
10
using System.Web.UI.HtmlControls;
11
//*************************************
12
public class DataSetToExcel
13![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
14![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public DataSetToExcel()
{}
15
public void Convert(DataSet oDS,HttpResponse Response)
16![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/None.gif)
34
35![](/Images/OutliningIndicators/None.gif)
36
(2)
37![](/Images/OutliningIndicators/None.gif)
38
using System;
39
using System.Data;
40
using System.Data.OleDb;
41
namespace GRIS.ExcelReprot
42![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
43![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
44
/// ImportExportToExcel 的摘要说明。
45
/// </summary>
46
public class ImportExportToExcel
47![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
55
//
56
// TODO: 在此处添加构造函数逻辑
57
//
58
this.openFileDlg.DefaultExt = "xls";
59
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
60![](/Images/OutliningIndicators/InBlock.gif)
61
this.saveFileDlg.DefaultExt="xls";
62
this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
63![](/Images/OutliningIndicators/InBlock.gif)
64
}
65![](/Images/OutliningIndicators/InBlock.gif)
66![](/Images/OutliningIndicators/ContractedSubBlock.gif)
从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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
77
/// 从选择的Excel文件导入
78
/// </summary>
79
/// <returns>DataSet</returns>
80
public DataSet ImportFromExcel()
81![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
82
DataSet ds=new DataSet();
83
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
84
ds=doImport(openFileDlg.FileName);
85
return ds;
86
}
87![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**///// <summary>
88
/// 从指定的Excel文件导入
89
/// </summary>
90
/// <param name="strFileName">Excel文件名</param>
91
/// <returns></returns>
92
public DataSet ImportFromExcel(string strFileName)
93![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
94
DataSet ds=new DataSet();
95
ds=doImport(strFileName);
96
return ds;
97
}
98![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
99
/// 执行导入
100
/// </summary>
101
/// <param name="strFileName">文件名</param>
102
/// <returns>DataSet</returns>
103
private DataSet doImport(string strFileName)
104![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/InBlock.gif)
112
DataSet ExcelDs = new DataSet();
113![](/Images/OutliningIndicators/InBlock.gif)
114
try
115![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116
ExcelDA.Fill(ExcelDs, "ExcelInfo");
117
}
118
catch(Exception err)
119![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
120
System.Console.WriteLine( err.ToString() );
121
}
122
return ExcelDs;
123
}
124
#endregion
125![](/Images/OutliningIndicators/InBlock.gif)
126![](/Images/OutliningIndicators/ContractedSubBlock.gif)
从DataSet到出到Excel#region 从DataSet到出到Excel
127![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
134
if (ds.Tables.Count==0 || strExcelFileName=="") return;
135
doExport(ds,strExcelFileName);
136
}
137![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
138
/// 导出用户选择的Excel文件
139
/// </summary>
140
/// <param name="ds">DataSet</param>
141
public void ExportToExcel(DataSet ds)
142![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
143
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
144
doExport(ds,saveFileDlg.FileName);
145
146
}
147![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
148
/// 执行导出
149
/// </summary>
150
/// <param name="ds">要导出的DataSet</param>
151
/// <param name="strExcelFileName">要导出的文件名</param>
152
private void doExport(DataSet ds,string strExcelFileName)
153![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/InBlock.gif)
160
int rowIndex=1;
161
int colIndex=0;
162![](/Images/OutliningIndicators/InBlock.gif)
163
excel.Application.Workbooks.Add(true);
164
165
166
System.Data.DataTable table=ds.Tables[0] ;
167
foreach(DataColumn col in table.Columns)
168![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
169
colIndex++;
170
excel.Cells[1,colIndex]=col.ColumnName;
171
}
172![](/Images/OutliningIndicators/InBlock.gif)
173
foreach(DataRow row in table.Rows)
174![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
175
rowIndex++;
176
colIndex=0;
177
foreach(DataColumn col in table.Columns)
178![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/InBlock.gif)
190![](/Images/OutliningIndicators/InBlock.gif)
191
//excel.Save(strExcelFileName);
192
excel.Quit();
193
excel=null;
194
195
GC.Collect();//垃圾回收
196
}
197
#endregion
198![](/Images/OutliningIndicators/InBlock.gif)
199![](/Images/OutliningIndicators/ContractedSubBlock.gif)
从XML导入到Dataset#region 从XML导入到Dataset
200![](/Images/OutliningIndicators/InBlock.gif)
201![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
202
/// 从选择的XML文件导入
203
/// </summary>
204
/// <returns>DataSet</returns>
205
public DataSet ImportFromXML()
206![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
try
{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
213![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch
{}
214
return ds;
215
}
216![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
217
/// 从指定的XML文件导入
218
/// </summary>
219
/// <param name="strFileName">XML文件名</param>
220
/// <returns></returns>
221
public DataSet ImportFromXML(string strFileName)
222![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
223
if (strFileName=="")
224
return null;
225
DataSet ds=new DataSet();
226![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
try
{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
227![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch
{}
228
return ds;
229
}
230
231
#endregion
232![](/Images/OutliningIndicators/InBlock.gif)
233![](/Images/OutliningIndicators/ContractedSubBlock.gif)
从DataSet导出到XML#region 从DataSet导出到XML
234![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
241
if (ds.Tables.Count==0 || strXMLFileName=="") return;
242
doExportXML(ds,strXMLFileName);
243
}
244![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
245
/// 导出用户选择的XML文件
246
/// </summary>
247
/// <param name="ds">DataSet</param>
248
public void ExportToXML(DataSet ds)
249![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/InBlock.gif)
257![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
264
try
265![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
266
catch(Exception ex)
267![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}
268
}
269![](/Images/OutliningIndicators/InBlock.gif)
270
#endregion
271
}
272
}
273![](/Images/OutliningIndicators/None.gif)
274
public void ExportResult(DataSet ds)
275![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
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![](/Images/OutliningIndicators/InBlock.gif)
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![](/Images/OutliningIndicators/InBlock.gif)
288
HttpContext.Current.Response.Write(stringWrite.ToString());
289
HttpContext.Current.Response.End();
290
}
291![](/Images/OutliningIndicators/None.gif)
292![](/Images/OutliningIndicators/None.gif)
-
相关阅读:
详细分析Orchard的Content、Drivers, Shapes and Placement 类型
什么是现代的应用程序?
MySQL锁详解!(转载)
EF DataFirst修改数据类型
EF+LINQ事物处理
.net防止SQL注入的一种方式
.net解决Xss攻击
IDEA创建Struts2报错——web.xml
WebServer搭建过程
设计模式——观察者模式
-
原文地址:https://www.cnblogs.com/jincwfly/p/379680.html
Copyright © 2020-2023
润新知