[Excel] Export Excel Attributes

2012-10-24

11663
0
.NET-C#(WinForm)
2013-07-31

Excel常用操作屬性

轉貼: http://giga0066.pixnet.net/blog/post/29822726-c%23-%E6%93%8D%E4%BD%9C-excel--%E5%B8%B8%E7%94%A8%E6%96%B9%E6%B3%95%E5%B1%AC%E6%80%A7

生成excel的時候有時候需要設置單元格的一些屬性,可以參考一下:


range.NumberFormatLocal = "@";    

//獲取Excel多個單元格區域：本例做為Excel表頭
range = (Range)worksheet.get_Range("A1", "E1");     

//單元格合併動作
range.Merge(0);    

//Excel單元格賦值
worksheet.Cells[1, 1] = "Excel單元格賦值";     

//設置字體大小
range.Font.Size = 15;     //設置字體大小

//設置字體是否有下劃線
range.Font.Underline=true;    

//設置字體的種類
range.Font.Name="黑體";     

//設置字體在單元格內的對其方式
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;    

//設置單元格的寬度
range.ColumnWidth=15;   

 //設置單元格的背景色
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();    

//設置單元格邊框的粗細
range.Borders.LineStyle=1;     

//給單元格加邊框
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     

//自動調整列寬
range.EntireColumn.AutoFit();    

// 文本水平居中方式
Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

//文本垂直居中方式
Range.VerticalAlignment= Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

//文本自動換行
Range.WrapText=true;     

//填充顏色為淡紫色
Range.Interior.ColorIndex=39;     

//字體顏色
Range.Font.Color=clBlue;     

//保存Excel的時候，不彈出是否保存的窗口直接進行保存
xlsApp.DisplayAlerts=false;     

//填入完信息之後另存到路徑及文件名字
workbook.SaveCopyAs(temp);

其他屬性:



//指定已存在的excel file
Microsoft.Office.Interop.Excel._Workbook excelBook = excelApp.Workbooks.Add(@"D:\test.xls");

//指定foucs在第幾個sheet
Microsoft.Office.Interop.Excel.Worksheet excelSheet =
    (Microsoft.Office.Interop.Excel.Worksheet)(excelBook.Worksheets[1]);

excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[10, 10]).Value2 = data; //data 需為 object[,] 型態

//取得 Range
Microsoft.Office.Interop.Excel.Range range = excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[1, 10]);

//set font
range.Font.Name = "Arial";

//set font color
range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

//set background color
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(
    System.Drawing.Color.FromArgb(0, 254, 255, 154)); //後三個參數為RGB

//set 指定範圍框線
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous,
    Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick,
    Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic,
    System.Drawing.Color.Black.ToArgb());

//set 設定所有cell框線
range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;

//merge cells
range.Merge(0);

//auto columns size
excelSheet.Cells.EntireColumn.AutoFit();

//set row width
excelSheet1.Rows.EntireRow.AutoFit();

回首頁