ITPub博客

首页 > Linux操作系统 > Linux操作系统 > C#操作Excel

C#操作Excel

原创 Linux操作系统 作者:fangqm 时间:2011-02-24 09:41:28 0 删除 编辑

OFFICE开发帮助

http://msdn.microsoft.com/zh-cn/library/bb726434(en-us).aspx

添加引用: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"];
 worksheet1 = (Worksheet)workbook1.Worksheets.Add(Type.Missing, workbook1.Worksheets[1], 1, Type.Missing);
            worksheet1.Activate();
            worksheet1.Cells[2, 2] = 3455555;
            excel1.Visible = true;
            excel1.DisplayAlerts = false;//不显示提示框
            workbook1.Close(true, "d:\\1.xls", null);
            //关闭

           worksheet1 = null;
            workbook1 = null;
            excel1.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
            excel1   =   null; 
            System.GC.Collect(); 

关闭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;

worksheet1 = null;
workbook1 = null;
excel1.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1   =   null; 
System.GC.Collect();
重命名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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23109131/viewspace-687827/,如需转载,请注明出处,否则将追究法律责任。

上一篇: winform 会话框
下一篇: webbrowser开发
请登录后发表评论 登录
全部评论

注册时间:2010-03-24

  • 博文量
    297
  • 访问量
    735316