澳门至尊网站-首页

您的位置:澳门至尊网站 > 软件综合 > 格式设置

格式设置

2019-10-23 12:28

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:EXCELIQC查验报表.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.To奥莱(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.xl孔蒂nuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);

    range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xl孔蒂nuous;//块内竖线

    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;

本文由澳门至尊网站发布于软件综合,转载请注明出处:格式设置

关键词: