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 =