C#操作Excel試算表

添加引用：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.Worksheet[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);//上邊距為２厘米(厘米轉像素)
     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;