. NET-based Excel Development: operations on cell areas (read, assign values, borders, and formats)

• Reference

using Excel = Microsoft.Office.Interop.Excel;

 

• Definition

1 Excel.ApplicationClass app;2 Excel.Workbooks books;3 Excel.Workbook book;4 Excel.Sheets sheets;5 Excel.Worksheet sheet;6 Excel.Range m_objRange;7 object missing = System.Reflection.Missing.Value;

 

• Read valid cell area

 1 foreach (Excel.Worksheet sheet in book.Sheets) 2 { 3     for (int i = 1; i <= sheet.UsedRange.Cells.Rows.Count; i++) 4     { 5         for (int j = 1; j <= sheet.UsedRange.Cells.Columns.Count; j++) 6         { 7             Excel.Range m_objRange = (Excel.Range)sheet.Cells[i, j]; 8         } 9     }10 }

 

• Cell region assignment

1 m_objRange = sheet. get_Range ("A1", missing); 2 m_objRange.set_Value (missing, "cell value ");

 

• Cell area settings Excel Formula

1 m_objRange = sheet .get_Range("A1", missing ); 2 m_objRange.set_Value(missing , "=SUM(A2:A5)");

 

• Bold font

1 m_objRange.Font.Bold = true;

 

• Set numeric format

1 // retain 2 decimal places 2 m_objRange.NumberFormat = "0.00 ";

 

• Set borders

1 m_objRange = sheet. get_Range ("A1", "B2"); 2 // set the Border width 3 m_objRange.Borders.Weight = 2; 4 // set the border style 5 m_objRange.Borders.get_Item (Excel. xlBordersIndex. xlEdgeTop ). lineStyle = Excel. xlLineStyle. xlDouble;

 

• XlLineStyle Enumeration

XlContinuous: solid line. XlDash: dotted line. XlDashDot: Point and line. XlDashDotDot: it is followed by two dots. XlDot: dot line. XlDouble: double line. XlLineStyleNone: Wireless entry. XlSlantDashDot: Skewed dashes.

• XlBordersIndex Enumeration

XlDiagonalDown: diagonal line, from the upper left to the lower right corner. XlDiagonalUp: diagonal line, from the upper left to the lower right corner. XlEdgeBottom: Bottom Border. XlEdgeLeft: Left Border. XlEdgeRight: Right Border. XlEdgeTop: Top border. XlInsideHorizontal: Internal horizontal border. XlInsideVertical Vertical: Internal Vertical border.