DotNet 操纵Excel总结之一
一.生成Excel.dll 的方法
利用DotNet 中自带的工具在命令提示符下执行tlbimp excel.exe.这样就不会因为你的Excel是xp或2000的不同要去找不同的*.olb文件,还有一点就是因为在2000以后的版本中没有了excel9.olb这个文件了。
通过执行tlbimp excel.exe后我们会得到excel.dll文件。
只要有了这个Excel.dll,现在我们就能使用Excel的各种操作函数了。
二.基本操作
1.创建Application对象
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
return;
}
2.得到WorkBook对象, 可以用两种方式
//之一: 打开已有的文件
Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:\\test.xls",Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//之二:新建一个文件
Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);
3.指定要操作的Sheet,两种方式
//之一:
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
或Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets["Sheet1"];
//之二:
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;
//之一:
Excel.Range range1 = xlsSheet.get_Range("C2", Type.Missing);
string temp1 = range1.Value2;
//之二:
Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
string temp2 = range2.Value2;
Excel.Range range3 = xlsSheet.get_Range("A1", Type.Missing);
range3.Value2 = "Hello World!";
range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
range3.Font.Color = Color.Red.ToArgb();
range3.Font.Name = "Arial";
range3.Font.Size = 9;
range3.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
range3.VerticalAlignment = Excel.Constants.xlCenter;
range3.Interior.Color = Color.FromArgb(192, 192, 192).ToArgb();
range3.Columns.AutoFit();//adjust the column width automatically
int matrixHeight = 20;
int matrixWidth = 20;
string[,] martix = new string[matrixHeight, matrixWidth];
for (int i = 0; i < matrixHeight; i++)
{
for (int j = 0; j < matrixWidth; j++)
{
martix[i, j] = String.Format("{0}_{1}", i + 1, j + 1);
}
}
string startColName = GetColumnNameByIndex(0);
string endColName = GetColumnNameByIndex(matrixWidth - 1);
int columnIndex = 3;
int rowIndex = 3;
string colName = GetColumnNameByIndex(columnIndex);
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.ColumnWidth = 20;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Rows.RowHeight = 40;
//单格颜色
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.Interior.Color =
olor.Blue.ToArgb();
//第行到第行的颜色
xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();
//第n列的颜色
xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color = Color.Pink.ToArgb();
if (File.Exists(@"E:\test1.xls"))
{
File.Delete(@"E:\test1.xls");
}
xlsBook.SaveAs(@"E:\test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
xcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);
xlsBook = null;
xlsApp = null;
GC.Collect();