.Net Introp Excel相关

来源：互联网发布：夏至未至抄袭nana知乎编辑：程序博客网时间：2024/06/07 06:26

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 = 10range.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);}