C#操作Excel电子表格.docx

C#操作Excel电子表格2011-01-21 12:51:47|分类： 默认分类 |标签： |字号大中小订阅 添加引用：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.Worksheetssheet1;Worksheet worksheet1 =(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheet1, 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.Worksheetssheet1;worksheet1 = (Worksheet)workbook1.Worksheets.Add(Type.Missing, workbook1.Worksheets1, 1, Type.Missing);worksheet1.Activate();worksheet1.Cells2, 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(); 关闭ExcelMicrosoft.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.Worksheetssheet1或1;worksheet1.Name = 工作计划表;excel1.Visible = true;设置或修改Excel表单元格内容Microsoft.Office.Interop.Excel.Application excel1 = newMicrosoft.Office.Interop.Excel.Application();Workbook workbook1 = excel1.Workbooks.Add(true);Worksheet worksheet1 = (Worksheet)workbook1.Worksheetssheet1;worksheet1.Cells1, 1 = 姓名;worksheet1.Cells1, 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.Worksheetssheet1;worksheet1.Columns.ColumnWidth = 20;/全局行宽worksheet1.Columns.RowHeight = 20;/全局列高Range range1 = (Range) worksheet1.Cells2, 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.Worksheetssheet1;Range range1 = (Range)worksheet1.Cells2, 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.Worksheetssheet1;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 = 123rn456;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.Worksheetssheet1;Range range1 = worksheet1.get_Range(A2, E8);/选择操作块range1.Value2 = 123rn456;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.Worksheetssheet1;worksheet1.Cells1, 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.Worksheetssheet1; worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;/纸张大小worksheet1.PageSetup.PrintTitleRows =