[轉]asp.net 導出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對象＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    Workbook workbook1 = excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);

    worksheet1.Activate();//激活sheet1表

三、建立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);

四、重命名Excel表名＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"或1];

    worksheet1.Name = "工作計劃表";

五、設定或修改Excel表單元格内容＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    worksheet1.Cells[1, 1] = "姓名";

    worksheet1.Cells[1, 2] = "性别";

六、設定Excel表行寬和列高＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    worksheet1.Columns.ColumnWidth = 20;//全局行寬

    worksheet1.Columns.RowHeight = 20;//全局列高

    Range range1 = (Range) worksheet1.Cells[2, 1];

    range1.Columns.ColumnWidth = 40;//單元格行寬

    range1.Columns.RowHeight = 40;//單元格列高

七、設定Excel表單元格邊框＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    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);

八、Excel表塊操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    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.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//塊内豎線

    range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//塊内橫線

九、Excel表單元格合并＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    excel1.Application.DisplayAlerts = false;//使合并操作不提示警告資訊

    range1.Merge(false);//參數為True則為每一行合并為一個單元格

    excel1.Application.DisplayAlerts = true;

十、複制Excel表＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    worksheet1.Cells[1, 1] = "123";

    worksheet1.Copy(Type.Missing, worksheet1);

    Worksheet worksheet2 =(Worksheet)worksheet1.Next;

    //worksheet2.Name = "Sheet2";

十一、頁面設定＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

　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頁";//中間頁腳内容