阿宗

• 存取Cells

//Using the cell name.
Cell cell = worksheet.Cells["A1"];

//Using a cell's row and column index.
Cell cell = worksheet.Cells[0,0];

//Using a cell index in the Cells collection
Cell cell = worksheet.Cells.GetCell(0, 0);

• 從Cells 存取 Data
  • 讀取部分，直接看官方範例:Examples-CSharp-Data-Handling-RetrievingDataFromCells-1.cs

//塞值
// Adding a string value to the cell
worksheet.Cells["A1"].PutValue("Hello World");
// Adding a date/time value to the cell
worksheet.Cells["A5"].PutValue(DateTime.Now);

//Improving Efficiency
//應該要先完成資料行(縱)，再塞下一列。
//也就是 A整行塞完，再去塞B

• Cells 樣式：邊框、字體顏色、背景顏色。

Cell cell = sheet.Cells["A1"];
Style style = cell.GetStyle();

//邊框
style.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);

//字體顏色
style.Font.Color = Color.Red;

//背景顏色
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Yellow;

cell.SetStyle(style);

• 10/30 增加 格式化資料(Data Formatting)
  這個 [$-404] 裡面的參數，參考這裡:[MS-LCID]，點開 PDF 或 DOCX 搜尋 zh-TW，可以找到    0x0404    zh-TW

sheet.Cells.SetColumnWidthPixel(0, 200); // 行寬 by Pixel

Cell Cell_A1 = sheet.Cells["A1"];
Style style_A1 = sheet.Cells["A1"].GetStyle();
// number 15 to show date as "d-mmm-yy"
style_A1.Number = 15;
Cell_A1.SetStyle(style_A1);
Cell_A1.PutValue(DateTime.Now);

Cell Cell_A2 = sheet.Cells["A2"];
Style style_A2 = sheet.Cells["A2"].GetStyle();
style_A2.Custom = "d-mmm-yy"; // 自訂格式
Cell_A2.SetStyle(style_A2);
Cell_A2.PutValue(DateTime.Now);

Cell Cell_A3 = sheet.Cells["A3"];
Style style_A3 = sheet.Cells["A3"].GetStyle();
//style_A3.Custom = "[DBNum2][$-804]General"; // [DBNum2][$-zh-CN]G/通用格式
style_A3.Custom = "[DBNum2][$-404]General"; // [DBNum2][$-zh-TW]G/通用格式
Cell_A3.SetStyle(style_A3);
Cell_A3.PutValue(123);

• 10/31 增加樣式：字形、粗體、文字大小、文字顏色、下劃線、刪除線、上標、下標

// Setting the font name to "Times New Roman"  字形
style.Font.Name = "Times New Roman";

// Setting the font weight to bold 粗體
style.Font.IsBold = true;

// font size 文字大小
style.Font.Size = 14;

// font color 文字顏色
style.Font.Color = Color.Blue;

//Setting Font Underline Type 下劃線
/*
Accounting          A single accounting underline
Double              Double underline
DoubleAccounting    Double accounting underline
None                No underline
Single              A single underline
*/
style.Font.Underline = FontUnderlineType.Single;

//Strikeout Effect 刪除線
style.Font.IsStrikeout = true;

// Setting subscript effect 上標
style.Font.IsSubscript = true;
// Setting superscript effect 下標
style.Font.IsSuperscript = true;