Excel文件導入導出,需引用Microsoft Excel 11.0 Object Library
/////////////////////////////////////////////////////////////////////////// //Purpose：Excel文件導入導出,需引用Microsoft Excel 11.0 Object Library //Author: Dangmy //Date: 2007-03-09 //Version: 1.0 /////////////////////////////////////////////////////////////////////////// public class ExcelIO { private int _ReturnStatus; private string _ReturnMessage; /// <summary> /// 執行返回狀態 /// </summary> public int ReturnStatus { get{return _ReturnStatus;} } /// <summary> /// 執行返回信息 /// </summary> public string ReturnMessage { get{return _ReturnMessage;} } public ExcelIO() { } /// <summary> /// 導入EXCEL到DataSet /// </summary> /// <param name="fileName">Excel全路徑文件名</param> /// <returns>導入成功的DataSet</returns> public DataSet ImportExcel(string fileName) { //判斷是否安裝EXCEL Excel.Application xlApp=new Excel.ApplicationClass(); if(xlApp==null) { _ReturnStatus = -1; _ReturnMessage = "無法創建Excel對象，可能您的計算機未安裝Excel"; return null; } //判斷文件是否被其他進程使用 Excel.Workbook workbook; try { workbook = xlApp.Workbooks.Open(fileName,0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); } catch { _ReturnStatus = -1; _ReturnMessage = "Excel文件處於打開狀態，請保存關閉"; return null; } //獲得所有Sheet名稱 int n = workbook.Worksheets.Count; string[] SheetSet = new string[n]; System.Collections.ArrayList al = new System.Collections.ArrayList(); for(int i=1; i<=n; i++) { SheetSet[i-1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name; } //釋放Excel相關對象 workbook.Close(null,null,null); xlApp.Quit(); if(workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if(xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); //把EXCEL導入到DataSet DataSet ds = new DataSet(); string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+ fileName +";Extended Properties=Excel 8.0" ; using(OleDbConnection conn = new OleDbConnection (connStr)) { conn.Open(); OleDbDataAdapter da; for(int i=1; i<=n; i++) { string sql = "select * from ["+ SheetSet[i-1] +"$] "; da = new OleDbDataAdapter(sql,conn); da.Fill(ds,SheetSet[i-1]); da.Dispose(); } conn.Close(); conn.Dispose(); } return ds; } /// <summary> /// 把DataTable導出到EXCEL /// </summary> /// <param name="reportName">報表名稱</param> /// <param name="dt">數據源表</param> /// <param name="saveFileName">Excel全路徑文件名</param> /// <returns>導出是否成功</returns> public bool ExportExcel(string reportName,DataTable dt,string saveFileName) { if(dt==null) { _ReturnStatus = -1; _ReturnMessage = "數據集為空！"; return false; } bool fileSaved=false; Excel.Application xlApp=new Excel.ApplicationClass(); if(xlApp==null) { _ReturnStatus = -1; _ReturnMessage = "無法創建Excel對象，可能您的計算機未安裝Excel"; return false; } Excel.Workbooks workbooks=xlApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.Cells.Font.Size = 10; Excel.Range range; long totalCount=dt.Rows.Count; long rowRead=0; float percent=0; worksheet.Cells[1,1]=reportName; ((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12; ((Excel.Range)worksheet.Cells[1,1]).Font.Bold = true; //寫入字段 for(int i=0;i<dt.Columns.Count;i++) { worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[2,i+1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //寫入數值 for(int r=0;r<dt.Rows.Count;r++) { for(int i=0;i<dt.Columns.Count;i++) { worksheet.Cells[r+3,i+1]=dt.Rows[r][i].ToString(); } rowRead++; percent=((float)(100*rowRead))/totalCount; } range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null); if( dt.Rows.Count > 0) { range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; } if(dt.Columns.Count>1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } //保存文件 if(saveFileName!="") { try { workbook.Saved =true; workbook.SaveCopyAs(saveFileName); fileSaved=true; } catch(Exception ex) { fileSaved=false; _ReturnStatus = -1; _ReturnMessage = "導出文件時出錯,文件可能正被打開！\n"+ex.Message; } } else { fileSaved=false; } //釋放Excel對應的對象 if(range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if(worksheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; } if(workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if(workbooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } xlApp.Application.Workbooks.Close(); xlApp.Quit(); if(xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); return fileSaved; } } //該代碼片段來自於: http://www.sharejs.com/codes/csharp/7261
