Excel常用操作屬性
生成excel的時候有時候需要設置單元格的一些屬性,可以參考一下:
range.NumberFormatLocal = "@"; //設置單元格格式為文本
range = (Range)worksheet.get_Range("A1", "E1"); //獲取Excel多個單元格區域：本例做為Excel表頭
range.Merge(0); //單元格合併動作
worksheet.Cells[1, 1] = "Excel單元格賦值"; //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= xlCenter; // 文本水平居中方式
Range.VerticalAlignment= xlCenter //文本垂直居中方式
Range.WrapText=true; //文本自動換行
Range.Interior.ColorIndex=39; //填充顏色為淡紫色
Range.Font.Color=clBlue; //字體顏色
xlsApp.DisplayAlerts=false; //保存Excel的時候，不彈出是否保存的窗口直接進行保存
workbook.SaveCopyAs(temp);/**////填入完信息之後另存到路徑及文件名字
其他屬性:
Application excelApp = new Application();
//指定已存在的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();
export speed: Method1 > Method2
Method 1
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;
using System.ComponentModel;
namespace ExportExcel1
{
class Program
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
private static void FormatColumn(Range excelRange, int col, string format)
{ ((Range)excelRange.Cells[1, col]).EntireColumn.NumberFormat = format; }
private static void ExportExcel(System.Data.DataTable dataTable, Worksheet sheet)
{
object[,] columnNames;
columnNames = new object[1, dataTable.Columns.Count];
//write column name
for (int i = 0; i < dataTable.Columns.Count; i++)
columnNames[0, i] = dataTable.Columns[i].Caption;
Range columnsNamesRange = sheet.get_Range(sheet.Cells[1,1], sheet.Cells[1, dataTable.Columns.Count]);
columnsNamesRange.Value2 = columnNames;
//set title Font as Bold
columnsNamesRange.EntireRow.Font.Bold = true;
//Release resource
System.Runtime.InteropServices.Marshal.ReleaseComObject(columnsNamesRange);
columnsNamesRange = null;
object[,] rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];
//insert data into rowData
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
rowData[i, j] = dataTable.Rows[i][j];
}
//set 寫入範圍從[2,1]~dataTable.Rows.Count + 1, dataTable.Columns.Count]
Range dataCells = sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);
//set columns type
short colIndex = 1;
string colType = string.Empty;
foreach (DataColumn dcol in dataTable.Columns)
{
colType = string.Empty;
if (dcol.DataType.Equals(typeof(string)))
colType = "G/通用格式"; //純文字 = "@"
else if (dcol.DataType.Equals(typeof(DateTime)))
colType = "yyyy/mm/dd hh:mm:ss";
if (!string.IsNullOrEmpty(colType))
FormatColumn(dataCells, colIndex, colType);
++colIndex;
}
//assign data to worksheet
dataCells.Value2 = rowData;
//Release resource
System.Runtime.InteropServices.Marshal.ReleaseComObject(dataCells);
dataCells = null;
}
private void ExportSetting(object[][] data, string FilePath)
{
Application excelApp1 = null;
_Workbook excelBook1 = null;
Worksheet excelSheet1 = null;
excelApp1 = new Application();
excelBook1 = excelApp1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
excelSheet1 = (Worksheet)excelBook1.Worksheets[1];
//set save as .xlsx(columns > 10000)
excelApp1.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
//To suppress the save as alert
excelApp1.DisplayAlerts = false;
excelApp1.AlertBeforeOverwriting = false;
//因Excel中也有DataTable, 所以必須指定為System的
System.Data.DataTable dt = new System.Data.DataTable("Sheet Name");
//declare column
DataColumn column;
int colNum = data[0].Length;
for (int i = 1; i <= colNum; i++)
{
column = new DataColumn();
//System.Double, System.DateTime, System.Int16, System.Int32, System.Int64
column.DataType = System.Type.GetType("System.String");
//set column name
column.ColumnName = "Column" + i.ToString();
dt.Columns.Add(column);
}
//Fill data into table
int rowNum = data.Length;
for (int i = 0; i < rowNum; i++)
{
System.Data.DataRow dr = dt.NewRow();
for (int j = 1; j <= colNum; j++)
{
if (data[i][j - 1] != null && data[i][j - 1] != string.Empty)
dr["Column" + j.ToString()] = data[i][j - 1].ToString();
}
dt.Rows.Add(dr);
}
//Call Export function
ExportExcel(dt, excelSheet1);
//Set Columns Width
excelSheet1.Cells.EntireColumn.AutoFit();
//Save file
excelSheet1.SaveAs(FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Release resource
IntPtr t = new IntPtr(excelApp1.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
static void Main(string[] args)
{
Program p = new Program();
object[][] data = new object[2][] { new object[] { "a", "b", "c" }, new object[] { 1, 2, 3 } };
p.ExportSetting(data, @"D:\test.xls");
}
}
}
--------------------------------------------------------------------------------------------
Method2
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
namespace ExportExcel3
{
class Program
{
static void Main(string[] args)
{
try
{
string[][] data = new string[][] { new string[] { "a1", "b1" }, new string[] { "a2", "b2" } };
//建立DataTable並設定excel的sheet名稱
string sheetName = "name1";
System.Data.DataTable dt = new System.Data.DataTable(sheetName);
int colNum = data[0].Length;
//設定column name
for (int i = 1; i <= colNum; i++)
dt.Columns.Add("Col" + i.ToString());
int rowNum = data.Length;
for (int i = 0; i < rowNum; i++)
{
System.Data.DataRow dr = dt.NewRow();
for (int j = 1; j <= colNum; j++)
dr["Col" + j.ToString()] = data[i][j - 1].ToString();
dt.Rows.Add(dr);
}
//create excel file
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=@E:\test.xls;Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
Conn.Open();
//use SQL string to create excel
string str = "Create table " + sheetName + "(";
for (int i = 1; i <= colNum; i++)
{
str += "Col" + i.ToString() + " VARCHAR";
if (i != colNum)
str += ", ";
else
str += ") ";
}
System.Data.OleDb.OleDbCommand odbcmd = new System.Data.OleDb.OleDbCommand(str, Conn);
try { odbcmd.ExecuteNonQuery(); }
catch (Exception ex) {/*...*/ }
//finally { Conn.Close(); }
//insert data into excel
string strInsert = "Insert into " + sheetName + "(";
for (int i = 1; i <= colNum; i++)
{
strInsert += "Col" + i.ToString();
if (i != colNum)
strInsert += ", ";
else
strInsert += ") VALUES( ";
}
for (int i = 1; i <= colNum; i++)
{
strInsert += "@Col" + i.ToString();
if (i != colNum)
strInsert += ", ";
else
strInsert += ") ";
}
System.Data.OleDb.OleDbCommand olecmd = new System.Data.OleDb.OleDbCommand(strInsert, Conn);
for (int i = 1; i <= colNum; i++)
{
olecmd.Parameters.Add("@Col" + i.ToString(), System.Data.OleDb.OleDbType.VarChar);
olecmd.Parameters["@Col" + i.ToString()].SourceColumn = "Col" + i.ToString();
}
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
da.InsertCommand = olecmd;
da.Update(dt);
}
catch (Exception e) { }
}
}
}
--------------------------------------------------------------------------------------------
Method3
using System;
using Microsoft.Office.Interop.Excel;
namespace ExportExcel2
{
class Program
{
static void Main(string[] args)
{
Application excelApp = new Application();
//指定已存在的excel file
_Workbook excelBook = excelApp.Workbooks.Add(@"D:\test.xls");
//指定foucs在第幾個sheet
Worksheet excelSheet = (Worksheet)(excelBook.Worksheets[1]);
//寫入字串, excel座標是從1開始
excelApp.Cells[1, 1] = "a";
//save file
excelBook.SaveCopyAs(@"D:\test.xls");
//不顯示儲存提示
excelBook.Saved = true;
excelApp.Quit();
//release resource
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
}
--------------------------------------------------------------------------------------------
Method4
Application excelApp = new Application();
//指定已存在的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[,] 型態
http://wenku.baidu.com/view/15cd59aadd3383c4bb4cd2b1.html
留言列表
DATABASES (4)
