C#操作Excel
來源：互聯網
上載者：User
public class ImportExportToExcel { private string strConn ; private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog(); private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); public ImportExportToExcel() { // // TODO: 在此處添加建構函式邏輯 // this.openFileDlg.DefaultExt = "xls"; this.openFileDlg.Filter = "Excel檔案 (*.xls)|*.xls"; this.saveFileDlg.DefaultExt="xls"; this.saveFileDlg.Filter= "Excel檔案 (*.xls)|*.xls"; } 從Excel檔案匯入到DataSet#region 從Excel檔案匯入到DataSet // /// <summary> // /// 從Excel匯入檔案 // /// </summary> // /// <param name="strExcelFileName">Excel檔案名稱</param> // /// <returns>返回DataSet</returns> // public DataSet ImportFromExcel(string strExcelFileName) // { // return doImport(strExcelFileName); // } /**//// <summary> /// 從選擇的Excel檔案匯入 /// </summary> /// <returns>DataSet</returns> public DataSet ImportFromExcel() { DataSet ds=new DataSet(); if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) ds=doImport(openFileDlg.FileName); return ds; } /**//// <summary> /// 從指定的Excel檔案匯入 /// </summary> /// <param name="strFileName">Excel檔案名稱</param> /// <returns></returns> public DataSet ImportFromExcel(string strFileName) { DataSet ds=new DataSet(); ds=doImport(strFileName); return ds; } /**//// <summary> /// 執行匯入 /// </summary> /// <param name="strFileName">檔案名稱</param> /// <returns>DataSet</returns> private DataSet doImport(string strFileName) { if (strFileName=="") return null; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;"; OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); DataSet ExcelDs = new DataSet(); try { ExcelDA.Fill(ExcelDs, "ExcelInfo"); } catch(Exception err) { System.Console.WriteLine( err.ToString() ); } return ExcelDs; } #endregion 從DataSet到出到Excel#region 從DataSet到出到Excel /**//// <summary> /// 匯出指定的Excel檔案 /// </summary> /// <param name="ds">要匯出的DataSet</param> /// <param name="strExcelFileName">要匯出的Excel檔案名稱</param> public void ExportToExcel(DataSet ds,string strExcelFileName) { if (ds.Tables.Count==0 || strExcelFileName=="") return; doExport(ds,strExcelFileName); } /**//// <summary> /// 匯出使用者選擇的Excel檔案 /// </summary> /// <param name="ds">DataSet</param> public void ExportToExcel(DataSet ds) { if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) doExport(ds,saveFileDlg.FileName); } /**//// <summary> /// 執行匯出 /// </summary> /// <param name="ds">要匯出的DataSet</param> /// <param name="strExcelFileName">要匯出的檔案名稱</param> private void doExport(DataSet ds,string strExcelFileName) { Excel.Application excel= new Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass(); // obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex=1; int colIndex=0; excel.Application.Workbooks.Add(true); System.Data.DataTable table=ds.Tables[0] ; foreach(DataColumn col in table.Columns) { colIndex++; excel.Cells[1,colIndex]=col.ColumnName; } foreach(DataRow row in table.Rows) { rowIndex++; colIndex=0; foreach(DataColumn col in table.Columns) { colIndex++; excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString(); } } excel.Visible=false; excel.Sheets[0] = "sss"; excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); //wkbNew.SaveAs strBookName //excel.Save(strExcelFileName); excel.Quit(); excel=null; GC.Collect();//記憶體回收 } #endregion 從XML匯入到Dataset#region 從XML匯入到Dataset /**//// <summary> /// 從選擇的XML檔案匯入 /// </summary> /// <returns>DataSet</returns> public DataSet ImportFromXML() { DataSet ds=new DataSet(); System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog(); openFileDlg.DefaultExt="xml"; openFileDlg.Filter= "xml檔案 (*.xml)|*.xml"; if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);} catch{} return ds; } /**//// <summary> /// 從指定的XML檔案匯入 /// </summary> /// <param name="strFileName">XML檔案名稱</param> /// <returns></returns> public DataSet ImportFromXML(string strFileName) { if (strFileName=="") return null; DataSet ds=new DataSet(); try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);} catch{} return ds; } #endregion 從DataSet匯出到XML#region 從DataSet匯出到XML /**//// <summary> /// 匯出指定的XML檔案 /// </summary> /// <param name="ds">要匯出的DataSet</param> /// <param name="strXMLFileName">要匯出的XML檔案名稱</param> public void ExportToXML(DataSet ds,string strXMLFileName) { if (ds.Tables.Count==0 || strXMLFileName=="") return; doExportXML(ds,strXMLFileName); } /**//// <summary> /// 匯出使用者選擇的XML檔案 /// </summary> /// <param name="ds">DataSet</param> public void ExportToXML(DataSet ds) { System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); saveFileDlg.DefaultExt="xml"; saveFileDlg.Filter= "xml檔案 (*.xml)|*.xml"; if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) doExportXML(ds,saveFileDlg.FileName); } /**//// <summary> /// 執行匯出 /// </summary> /// <param name="ds">要匯出的DataSet</param> /// <param name="strExcelFileName">要匯出的XML檔案名稱</param> private void doExportXML(DataSet ds,string strXMLFileName) { try {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );} catch(Exception ex) {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;} } #endregion }
