WinForm - 將DataTable資料匯出為Excel檔案 Part II

延伸

http://subocheng.blogspot.com/2009/12/winform-datatableexcel.html

設定格線

selectRange = oSheet.get_Range("A1","H10");

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

selectRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

設定格式

oSheet.get_Range("A1" , "H10").NumberFormatLocal = "@"; //文字格式

oSheet.get_Range("A1" , "H10").NumberFormatLocal = "#,##0"; //金錢格式

對齊方式

oSheet.get_Range("A1" , "E10").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

oSheet.get_Range("F1" , "H10").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

oSheet.get_Range("C1" , "C10").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

//自動調整寬度和高度

oSheet.Cells.EntireRow.AutoFit();

oSheet.Cells.EntireColumn.AutoFit();