C# 填充Excel

1、添加引用

添加引用:Microsoft   Excel   11.0   Object   Library ;

Microsoft.Office.Interop.Excel;

添加:using Microsoft.Office.Interop.Excel;

2、使用命名空间

一、打开Excel文件============================

 using Microsoft.Office.Interop.Excel;

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

3、填充EXCEL单元格方法

    Workbook workbook1 = excel1.Workbooks.Open(@”E:/aaa.xls”,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 private void OpenExcel()
        {
            Microsoft.Office.Interop.Excel.Application excel = new
ApplicationClass();
            excel.Visible = true;
            excel.DisplayAlerts = false;
            excel.Workbooks.Open(@”D:\EXCEL\IQC检验报表.xlsx”);
            ((_Worksheet) excel.Worksheets[1]).Activate();         
            excel.get_Range(“F7”,Type.Missing).Value =
“测试Excel写入数据”;
            excel.get_Range(“F7”, Type.Missing).Font.Color =
Color.Blue;
            excel.ActiveWorkbook.Save();
            excel.Application.Workbooks.Close();
            excel.Application.Quit();
            excel.Quit();
            GC.Collect();
        }

    excel1.Visible = true;

 

二、新建Excel对象============================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 =
excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);

    worksheet1.Activate();//激活sheet1表

    excel1.Visible = true;

三、新建Excel表============================

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    Worksheet worksheet1
=(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheets[1],
1, Type.Missing);

    excel1.Visible = true;

四、重命名Excel表名============================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 =
(Worksheet)workbook1.Worksheets[“sheet1″或1];

    worksheet1.Name = “工作计划表”;

    excel1.Visible = true;

五、设置或修改Excel表单元格内容========================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    worksheet1.Cells[1, 1] = “姓名”;

    worksheet1.Cells[1, 2] = “性别”;

    excel1.Visible = true;

六、设置Excel表行宽和列高===========================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    worksheet1.Columns.ColumnWidth = 20;//全局行宽

    worksheet1.Columns.RowHeight = 20;//全局列高

    Range range1 = (Range) worksheet1.Cells[2, 1];

    range1.Columns.ColumnWidth = 40;//单元格行宽

    range1.Columns.RowHeight = 40;//单元格列高

    excel1.Visible = true;

七、设置Excel表单元格边框===========================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    Range range1 = (Range)worksheet1.Cells[2, 2];

    range1.Borders.Color =
System.Drawing.ColorTranslator.ToOle(Color.Red);

    range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

   
//也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle =
XlLineStyle.xlContinuous;//斜杠

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle =
XlLineStyle.xlContinuous;//反斜杠

    range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color =
System.Drawing.ColorTranslator.ToOle(Color.Gold);

    excel1.Visible = true;

八、Excel表块操作============================

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    Range range1 = worksheet1.get_Range(“A2”, “E8”);//选择操作块

    range1.Font.Bold = true;//设置黑体

    range1.Font.Size = 18;//设置字体大小

  range1.Font.Name = “仿宋”;//设置字体

    range1.Font.Color =
System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色

    range1.HorizontalAlignment =
XlHAlign.xlHAlignCenter;//设置水平对齐方式

    range1.VerticalAlignment =
XlVAlign.xlVAlignCenter;//设置垂直对齐方式   

    range1.Value2 = “123/r/n456”;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous;

    range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous;

   
//也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous,
XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

   
range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle =
XlLineStyle.xlContinuous;//块内竖线

    range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle
= XlLineStyle.xlContinuous;//块内横线

    excel1.Visible = true;

九、Excel表单元格合并============================

  Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    Range range1 = worksheet1.get_Range(“A2”, “E8”);//选择操作块

    range1.Value2 = “123/r/n456”;

    excel1.Application.DisplayAlerts = false;//使合并操作不提示警告信息

    range1.Merge(false);//参数为True则为每一行合并为一个单元格

    excel1.Application.DisplayAlerts = true;

    excel1.Visible = true;

十、复制Excel表============================

    Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

    Workbook workbook1 = excel1.Workbooks.Add(true);

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

    worksheet1.Cells[1, 1] = “123”;

    worksheet1.Copy(Type.Missing, worksheet1);

    Worksheet worksheet2 =(Worksheet)worksheet1.Next;

    //worksheet2.Name = “Sheet2”;

    excel1.Visible = true;

十一、页面设置============================

 Microsoft.Office.Interop.Excel.Application excel1 = new
Microsoft.Office.Interop.Excel.Application();

     Workbook workbook1 = excel1.Workbooks.Add(true);

     excel1.Caption = “我的报表”;

     Worksheet worksheet1 = (Worksheet)workbook1.Worksheets[“sheet1”];

  worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小

    worksheet1.PageSetup.PrintTitleRows = “$1:$3”;//顶端标题行

      worksheet1.PageSetup.Orientation =
XlPageOrientation.xlLandscape;//页面方向为横向

      worksheet1.PageSetup.TopMargin =
excel1.CentimetersToPoints(2);//上边距为2厘米(厘米转像素)

      worksheet1.PageSetup.BottomMargin =
excel1.CentimetersToPoints(2);//下边距为2厘米(厘米转像素)

    worksheet1.PageSetup.LeftMargin =
excel1.CentimetersToPoints(1.5);//左边距为1.5厘米(厘米转像素)

   worksheet1.PageSetup.RightMargin =
excel1.CentimetersToPoints(1.5);//右边距为1.5厘米(厘米转像素)

   worksheet1.PageSetup.HeaderMargin =
excel1.CentimetersToPoints(1.2);//页眉边距为1.2厘米(厘米转像素)

    worksheet1.PageSetup.FooterMargin =
excel1.CentimetersToPoints(1);//页脚边距为1厘米(厘米转像素)

    worksheet1.PageSetup.CenterHorizontally = true;//页面水平居中

    worksheet1.PageSetup.CenterVertically = false;//页面不垂直居中

    worksheet1.PageSetup.CenterFooter = “第&P页,共&N页”;//中间页脚内容

    excel1.Visible = true;


由于实际需要需要ASP.net中导出excel并控制EXCEL格式,以下是实现代码

private void DataGrid1_ItemDataBound(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType ==
ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add(“style”,”vnd.ms-excel.numberformat:@”);
e.Item.Cells[3].Attributes.Add(“style”,”vnd.ms-excel.numberformat:¥#,###.00″);
}
}

Excel.Application excel;// = new Application(); 
            int rowIndex = 2;
            int colIndex = 0;

            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            excel = new Excel.ApplicationClass();

            xBk = excel.Workbooks.Add(true);

            xSt = (Excel._Worksheet)xBk.ActiveSheet;

 

             //合并单元格
            xSt.get_Range(excel.Cells[3, 3], excel.Cells[4,
4]).Merge(true);//

            xSt.get_Range(excel.Cells[7, 7], excel.Cells[8,
8]).Interior.ColorIndex = 20;//设置背景色为浅黄色,共计有56种

  //标题栏固定即为冻结窗口,一下代码实现
xSt.get_Range(“B2”, “B2”).Select();
            excel.ActiveWindow.FreezePanes = true;