1 1using System;
2 2using System.Collections;
3 3using System.ComponentModel;
4 4using System.Data;
5 5using System.Drawing;
6 6using System.Web;
7 7using System.Web.SessionState;
8 8using System.Web.UI;
9 9using System.Web.UI.WebControls;
10 10using System.Web.UI.HtmlControls;
11 11using System.Data.OleDb;
12 12using Excel;
13 13
14 14namespace StoreManager
15 15 {
16 16
17 17
18 18 public class ImportExportToExcel
19 19 {
20 20 private string strConn ;
21 21
22 22 public ImportExportToExcel()
23 23 {
24 24 }
25 25
26 26 //从Excel文件导入到DataSet
27 27 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
28 28 /**//// 从指定的Excel文件导入
29 29 public DataSet ImportFromExcel(string strFileName)
30 30 {
31 31 DataSet ds=new DataSet();
32 32 ds=doImport(strFileName);
33 33 return ds;
34 34 }
35 35
36 36 /**//// 执行导入
37 37 private DataSet doImport(string strFileName)
38 38 {
39 39 if (strFileName=="") return null;
40 40 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
41 41 "Data Source=" + strFileName + ";" +
42 42 "Extended Properties=Excel 8.0;";
43 43 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
44 44 DataSet ExcelDs = new DataSet();
45 45 try
46 46 {
47 47 ExcelDA.Fill(ExcelDs, "ExcelInfo");
48 48
49 49 }
50 50 catch(Exception err)
51 51 {
52 52 System.Console.WriteLine( err.ToString() );
53 53 }
54 54 return ExcelDs;
55 55 }
56 56 #endregion
57 57
58 58 //从DataSet到出到Excel
59 59 从DataSet到出到Excel#region 从DataSet到出到Excel
60 60 /**//// 导出指定的Excel文件
61 61 public void ExportToExcel(DataSet ds,string strExcelFileName)
62 62 {
63 63 if (ds.Tables.Count==0 || strExcelFileName=="") return;
64 64 doExport(ds,strExcelFileName);
65 65
66 66
67 67 }
68 68 /**//// 执行导出
69 69 private void doExport(DataSet ds,string strExcelFileName)
70 70 {
71 71
72 72 Excel.Application excel= new Excel.Application();
73 73 int rowIndex=1;
74 74 int colIndex=0;
75 75 excel.Application.Workbooks.Add(true);
76 76 System.Data.DataTable table=ds.Tables[0] ;
77 77 foreach(DataColumn col in table.Columns)
78 78 {
79 79 colIndex++;
80 80 excel.Cells[1,colIndex]=col.ColumnName;
81 81 }
82 82
83 83 foreach(DataRow row in table.Rows)
84 84 {
85 85 rowIndex++;
86 86 colIndex=0;
87 87 foreach(DataColumn col in table.Columns)
88 88 {
89 89 colIndex++;
90 90 excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
91 91 }
92 92 }
93 93 excel.Visible=false;
94 94 // excel.Sheets[0] = "sss";
95 95 excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null,null);
96 96 excel.Quit();
97 97 excel=null;
98 98 GC.Collect();//垃圾回收
99 99 }
100 100 #endregion
101 101
102 102 //从XML导入到Dataset
103 103 从XML导入到Dataset#region 从XML导入到Dataset
104 104 /**//// 从指定的XML文件导入
105 105 public DataSet ImportFromXML(string strFileName)
106 106 {
107 107 if (strFileName=="")
108 108 return null;
109 109 DataSet ds=new DataSet();
110 110 try{ds.ReadXml(strFileName,System.Data.XmlReadMode.Auto);}
111 111 catch{}
112 112 return ds;
113 113 }
114 114 #endregion 从DataSet导出到XML
115 115 //从DataSet导出到XML
116 116 从DataSet导出到XML#region 从DataSet导出到XML
117 117 /**//// 导出指定的XML文件
118 118 public void ExportToXML(DataSet ds,string strXMLFileName)
119 119 {
120 120 if (ds.Tables.Count==0 || strXMLFileName=="") return;
121 121 doExportXML(ds,strXMLFileName);
122 122 }
123 123 /**//// 执行导出
124 124 private void doExportXML(DataSet ds,string strXMLFileName)
125 125 {
126 126 try
127 127 {ds.WriteXml(strXMLFileName);}
128 128 catch(Exception ex)
129 129 {throw ex;}
130 130 }
131 131 #endregion
132 132 }
133 133
134 134}
