DataSet和Excel文件的相互导入导出

1 public class ImportExportToExcel
  2 {
  3 private string strConn ;
  4  
  5 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
  6 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();  
  7  
  8 public ImportExportToExcel()
  9 {
 10 //
 11 // TODO: 在此处添加构造函数逻辑
 12 //
 13 this.openFileDlg.DefaultExt = "xls";
 14 this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
 15 
 16 this.saveFileDlg.DefaultExt="xls";
 17 this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
 18 
 19 }
 20 
 21 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
 22 // /// <summary>
 23 // /// 从Excel导入文件
 24 // /// </summary>
 25 // /// <param name="strExcelFileName">Excel文件名</param>
 26 // /// <returns>返回DataSet</returns>
 27 // public DataSet ImportFromExcel(string strExcelFileName)
 28 // {
 29 // return doImport(strExcelFileName);
 30 // }
 31 /** <summary>
 32 /// 从选择的Excel文件导入
 33 /// </summary>
 34 /// <returns>DataSet</returns>
 35 public DataSet ImportFromExcel()
 36 {
 37 DataSet ds=new DataSet();
 38 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
 39 ds=doImport(openFileDlg.FileName);
 40 return ds;
 41 }
 42 /** <summary>
 43 /// 从指定的Excel文件导入
 44 /// </summary>
 45 /// <param name="strFileName">Excel文件名</param>
 46 /// <returns></returns>
 47 public DataSet ImportFromExcel(string strFileName)
 48 {
 49 DataSet ds=new DataSet();
 50 ds=doImport(strFileName);
 51 return ds;
 52 }
 53 /** <summary>
 54 /// 执行导入
 55 /// </summary>
 56 /// <param name="strFileName">文件名</param>
 57 /// <returns>DataSet</returns>
 58 private DataSet doImport(string strFileName)
 59 {
 60 if (strFileName=="") return null;
 61  
 62 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 63 "Data Source=" + strFileName + ";" +
 64 "Extended Properties=Excel 8.0;";
 65 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
 66 
 67 DataSet ExcelDs = new DataSet();
 68 try
 69 {
 70 ExcelDA.Fill(ExcelDs, "ExcelInfo");
 71  
 72 }
 73 catch(Exception err)
 74 {
 75 System.Console.WriteLine( err.ToString() );
 76 }
 77 return ExcelDs;
 78  
 79  
 80  
 81 }
 82 #endregion
 83 
 84 从DataSet到出到Excel#region 从DataSet到出到Excel
 85 /** <summary>
 86 /// 导出指定的Excel文件
 87 /// </summary>
 88 /// <param name="ds">要导出的DataSet</param>
 89 /// <param name="strExcelFileName">要导出的Excel文件名</param>
 90 public void ExportToExcel(DataSet ds,string strExcelFileName)
 91 {
 92 if (ds.Tables.Count==0 || strExcelFileName=="") return;
 93 doExport(ds,strExcelFileName);
 94  
 95 
 96 }
 97 /** <summary>
 98 /// 导出用户选择的Excel文件
 99 /// </summary>
100 /// <param name="ds">DataSet</param>
101 public void ExportToExcel(DataSet ds)
102 {
103 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
104 doExport(ds,saveFileDlg.FileName);
105  
106 }
107 /** <summary>
108 /// 执行导出
109 /// </summary>
110 /// <param name="ds">要导出的DataSet</param>
111 /// <param name="strExcelFileName">要导出的文件名</param>
112 private void doExport(DataSet ds,string strExcelFileName)
113 {
114  
115 Excel.Application excel= new Excel.Application();
116  
117 // Excel.Workbook obj=new Excel.WorkbookClass();
118 // obj.SaveAs("c:/zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
119 
120 int rowIndex=1;
121 int colIndex=0;
122 
123 excel.Application.Workbooks.Add(true);
124  
125  
126 System.Data.DataTable table=ds.Tables[0] ;
127 foreach(DataColumn col in table.Columns)
128 {
129 colIndex++;  
130 excel.Cells[1,colIndex]=col.ColumnName;  
131 }
132 
133 foreach(DataRow row in table.Rows)
134 {
135 rowIndex++;
136 colIndex=0;
137 foreach(DataColumn col in table.Columns)
138 {
139 colIndex++;
140 excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
141 }
142 }
143 excel.Visible=false;  
144 excel.Sheets[0] = "sss";
145 excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
146  
147  
148 //wkbNew.SaveAs strBookName
149 
150 
151 //excel.Save(strExcelFileName);
152 excel.Quit();
153 excel=null;
154  
155 GC.Collect();//垃圾回收
156 }
157 #endregion
158 
159 从XML导入到Dataset#region 从XML导入到Dataset
160 
161 /** <summary>
162 /// 从选择的XML文件导入
163 /// </summary>
164 /// <returns>DataSet</returns>
165 public DataSet ImportFromXML()
166 {
167 DataSet ds=new DataSet();
168 System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
169 openFileDlg.DefaultExt="xml";
170 openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
171 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
172 try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
173 catch{}
174 return ds;
175 }
176 /** <summary>
177 /// 从指定的XML文件导入
178 /// </summary>
179 /// <param name="strFileName">XML文件名</param>
180 /// <returns></returns>
181 public DataSet ImportFromXML(string strFileName)
182 {
183 if (strFileName=="")
184 return null;
185 DataSet ds=new DataSet();
186 try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
187 catch{}
188 return ds;
189 }
190  
191 #endregion
192 
193 从DataSet导出到XML#region 从DataSet导出到XML
194 /** <summary>
195 /// 导出指定的XML文件
196 /// </summary>
197 /// <param name="ds">要导出的DataSet</param>
198 /// <param name="strXMLFileName">要导出的XML文件名</param>
199 public void ExportToXML(DataSet ds,string strXMLFileName)
200 {
201 if (ds.Tables.Count==0 || strXMLFileName=="") return;
202 doExportXML(ds,strXMLFileName);
203 }
204 /** <summary>
205 /// 导出用户选择的XML文件
206 /// </summary>
207 /// <param name="ds">DataSet</param>
208 public void ExportToXML(DataSet ds)
209 {
210 System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
211 saveFileDlg.DefaultExt="xml";
212 saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
213 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
214 doExportXML(ds,saveFileDlg.FileName);
215 }
216 
217 /** <summary>
218 /// 执行导出
219 /// </summary>
220 /// <param name="ds">要导出的DataSet</param>
221 /// <param name="strExcelFileName">要导出的XML文件名</param>
222 private void doExportXML(DataSet ds,string strXMLFileName)
223 {
224 try
225 {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
226 catch(Exception ex)
227 {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}  
228 }
229 
230 #endregion
231 
232  
233  
234 }
235 
可能用进程的强制删除方法:kill() 
 1 #region "强制结束Word进程(垃圾回收)" 
 2 /// <summary> 
 3 /// 强制结束Word进程(垃圾回收) 
 4 /// </summary> 
 5 public void KillWordProcess() 
 6 { 
 7 int ProceedingCount = 0; 
 8 try 
 9 { 
10 System.Diagnostics.Process [] ProceddingCon = System.Diagnostics.Process.GetProcesses(); 
11 foreach(System.Diagnostics.Process IsProcedding in ProceddingCon) 
12 { 
13 if(IsProcedding.ProcessName.ToUpper() == "WINWORD") 
14 { 
15 ProceedingCount += 1; 
16 IsProcedding.Kill(); 
17 } 
18 } 
19 } 
20 catch(System.Exception err) 
21 { 
22 MessageBox.Show(err.Message + "/r" +"(" + err.Source + ")" + "/r" + err.StackTrace); 
23 } 
24 } 
25 #endregion