1 使用公式
yourWorkSheet.Cells[rowCount, column].Formula = string.Format("=SUM(G1:G{0})", rowCount);
2设定边界
private void BorderAround(Excel.Range range, int colour)
{
Excel.Borders borders = range.Borders;
borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Color = colour;
borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders = null;
}
3 合并单元格
eWSheet.Range[eWSheet.Cells[1, 1], eWSheet.Cells[4, 1]].Merge();
4 改变字体样式
range.Font.Name = "Arial"
range.Font.Size = 10
range.Font.Bold = true
5使用数组进行单元格的批量赋值
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1];
Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count];
Excel.Range range = wsh.get_Range(c1, c2);
range.Value = arr;
6对Range的循环操作
foreach (excel.Range cell in count.Cells)
{
if (cell.Value2 != null && cell.Value2 is double?)
{
sum += cell.Value2;
numRows += 1;
}
}
7 小计
private void CreateSubtotal()
{
this.Range["A1", missing].Value2 = "Row 1";
this.Range["B1", missing].Value2 = "Row 2";
this.Range["C1", missing].Value2 = "Row 3";
this.Range["A2", "A5"].Value2 = 10;
this.Range["B2", "B5"].Value2 = 20;
this.Range["C2", "C5"].Value2 = 30;
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
this.Controls.AddNamedRange(this.Range["A1", "C5"],
"namedRange1");
int[] fields = new int[] { 1, 2, 3 };
namedRange1.Subtotal(1, Excel.XlConsolidationFunction.xlSum,
fields, missing, missing, Excel.XlSummaryRow.xlSummaryBelow);
}