1.System.Data.DataTable連接數據庫
ExcelFile?是excel的完整路徑
//OleDbConnection conExcel = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 8.0");
//if (conExcel.State == ConnectionState.Closed)
//{
// ? ?conExcel.Open();
//}
2.獲取第一個sheet表名稱
不建議寫死，程序自動獲取下也很快的
System.Data.DataTable dtTmp = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tableName = dtTmp.Rows[0][2].ToString().Trim();
3.獲取datatable
"select * from [" + tableName + "]"
執行這個sql語句吧，剩下的就是datatable的操作了
根據DataTable可以獲取各行各列的值，但似乎不穩定，有時只能獲取空值而實際明明有值
4.com方式打開excel
需引用Microsoft.Office.Interop.Excel
object missing = System.Type.Missing;
Application app = new ApplicationClass();
打開excel，注意空參數不可以用null
Workbook wBook = app.Workbooks.Open(ExcelFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
app.Visible = false;
//設置禁止彈出保存和覆蓋的詢問提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
打開第一個Worksheet
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
寫值
wSheet.Cells[行, 列] = "你想寫的值";
獲取cell的值，注意不可用wSheet.Cells[行, 列]獲取，這樣一般得到"System.__ComObject"
Range ra = wSheet.get_Range(wSheet.Cells[i, j], wSheet.Cells[i, j]); 此處是單元格范圍，自己決定多大
string sVal = ra.Text.ToString();
判斷單元格是否有公式
ra.Formula.ToString().IndexOf("=") >= 0
設置excel單元格樣式，很多的，自己百度下吧
這個是設置單元格的邊框寬度
Range range = wSheet.get_Range(wSheetDes.Cells[i, j], wSheetDes.Cells[i, j]);
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = 2;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = 2;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = 2;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = 2;
Range range = wSheetDes.get_Range(wSheet.Cells[i, j], wSheet.Cells[i, j]);
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//保存工作簿，方法很多，自己決定怎么用
wSheet.SaveAs(ExcelFile, missing, missing, missing, missing, missing, missing, missing, missing, missing);
wBook.Save();
app.Save(ExcelFile);
app.SaveWorkspace(ExcelFile);
退出，比較麻煩
wBook.Close(true, missing, missing);
wSheet = null;
wBook = null;
app.Quit();
app = null;
System.GC.Collect();
System.GC.WaitForFullGCComplete();
全部關掉然后置為null，再強制垃圾回收即可
常用的大概這么多，應該夠用了
