excel 在 .net 中的使用及设置

 

添加引用:Microsoft   Excel   11.0   Object   Library ;

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

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

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

    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);

    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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值