一
首先Add References Excel.dll，注意在MS office 2003的版本中，可能找不到Excel.dll,這時需要從Excel.exe中扣出來，怎么扣？網(wǎng)上有很多方法，扣出來的同時也把Excel.dll變成了受托管的。
1.讀取Excel File。
其實讀取Excel和讀取數(shù)據(jù)庫的方式差不多：
#region
1 string StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=NO'";
2 OleDbConnection MyConn = new OleDbConnection(StrConn);
3 MyConn.Open();
4 string StrCmd = "select * from [sheet1$]";
5 OleDbDataAdapter MyCommand = new OleDbDataAdapter(StrCmd,MyConn);
6 DataSet Ds = new DataSet();
7 MyCommand.Fill(Ds,"NameTB");
8 DataTable dt=Ds.Tables["NameTB"]
＃endregion
如果Excel第一行是表頭的話，可以把StrConn里面的HDR=NO改成，HDR=YES; 當然這樣讀出來的數(shù)據(jù)有時候是有問題的，比如本來只有3行的數(shù)據(jù)，卻讀來有>3行，我還不知道是什么原因，哪位知道告訴我謝謝。那么這個時候可以用下面的方法判斷真實的行數(shù)：
1 int b = 0;
2 if (Ds.Tables["NameTB"].Rows[Ds.Tables["NameTB"].Rows.Count - 1][0].ToString() != string.Empty)
3 b = Ds.Tables["NameTB"].Rows.Count;
4 else
5 {
6 while (Ds.Tables["NameTB"].Rows[b][0].ToString() != string.Empty)
7 {
8 b++;
9 }
10 }
這里的Excel中的數(shù)據(jù)是一列多行。同時在從Table 中取出來用的時候要保證Ds.Tables["NameTB"].Rows[i][0].ToString() .Trim()!= string.Empty。
2.生成Excel并插入數(shù)據(jù)。
有時我們需要把檢索出來的數(shù)據(jù)導出到Excel。下面說說如何生成,插入數(shù)據(jù)到Excel. 首先Add References Excel.dll，引用using Excel;
下面的代碼是將ListView中的數(shù)據(jù)導入到Excel：
1 System.Reflection.Missing miss = System.Reflection.Missing.Value;
2 Excel.ApplicationClass m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = false;
3 Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
4 Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
5 Excel.Worksheet m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
6 Excel.Range ER = null;
16 ER = m_objSheet.get_Range((object)"A1", System.Reflection.Missing.Value);
17 ER.Value2 = "Last Name";
18 ER.ColumnWidth = 12;
19 ER = m_objSheet.get_Range((object)"B1", System.Reflection.Missing.Value);
20 ER.Value2 = "Frst Name";
21 ER.ColumnWidth = 15;
22 ER = m_objSheet.get_Range((object)"C1", System.Reflection.Missing.Value);
23 ER.Value2 = "Address";
24 ER.ColumnWidth = 35;
43 m_objSheet.get_Range("A1", "D1").Font.Bold = true;
45 m_objSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
47 for (int i = 2; i < this.lvResult.Items.Count + 2; i++)
48 {
50 m_objExcel.Cells[i, 1] = this.lvResult.Items[i - 2].SubItems[1].Text.ToString().Trim();
51 m_objExcel.Cells[i, 2] = this.lvResult.Items[i - 2].SubItems[2].Text.ToString().Trim();
52 m_objExcel.Cells[i, 3] = this.lvResult.Items[i - 2].SubItems[3].Text.ToString().Trim();
59 }
//生成Excel.
61 m_objBook.SaveAs(SavePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
62 //釋放資源。
63 m_objBook.Close(false, miss, miss);
64 m_objBooks.Close();
65 m_objExcel.Quit();
66
67 System.Runtime.InteropServices.Marshal.ReleaseComObject(ER);
68 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
69 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
70 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
71 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
72 GC.Collect();
對IO操作一般都要考慮到異常，自己添加。在網(wǎng)上也找了一些例子，覺得上面的資源釋放機制是相對比較好的。
二
C#讀寫EXCEL方法一
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.IO;
namespace ExcelTest
{
class Program
{
static void Main(string[] args)
{
////創(chuàng)建Application對象
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
return;
}
xlsApp.Visible = true;
//得到WorkBook對象, 可以用兩種方式
//之一: 打開已有的文件
//Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:\Documents and Settings\daniel.chen\Desktop\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);
//指定要操作的Sheet，兩種方式
//之一：
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
//之二：
//Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;
//指定單元格，讀取數(shù)據(jù)，兩種方法
//之一：
Excel.Range range1 = xlsSheet.get_Range("C2",Type.Missing);
Console.WriteLine(range1.Value2);
//之二：
Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
Console.WriteLine(range2.Value2);
//在單元格中寫入數(shù)據(jù)
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.Orientation = 90; //vertical
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
//在某個區(qū)域?qū)懭霐?shù)據(jù)數(shù)組
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);
//取得某個區(qū)域，兩種方法
//之一：
Excel.Range range4 = xlsSheet.get_Range("A1",Type.Missing);
range4 = range4.get_Resize(matrixHeight,matrixWidth);
//之二：
//Excel.Range range4 = xlsSheet.get_Range(String.Format("{0}{1}", startColName, 1), String.Format("{0}{1}", endColName, martixHeight));
range4.Value2 = martix;
range4.Font.Color = Color.Red.ToArgb();
range4.Font.Name="Arial";
range4.Font.Size = 9;
range4.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
//設置column和row的寬度和顏色
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 = Color.Blue.ToArgb();//單格顏色
xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();//第5行到第7行的顏色
//xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color=Color.Pink.ToArgb();//第n列的顏色如何設置？？
//保存，關(guān)閉
if (File.Exists(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls"))
{
File.Delete(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls");
}
xlsBook.SaveAs(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit();
GC.Collect();
Console.ReadKey();
}
//將column index轉(zhuǎn)化為字母，至多兩位
public static string GetColumnNameByIndex(int index)
{
string[] alphabet = new string[] {"","A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ,"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
string result = "";
int temp = index / 26;
int temp2 = index % 26 + 1;
if (temp > 0)
{
result += alphabet[temp];
}
result += alphabet[temp2];
return result;
}
}
}
C#讀寫EXCEL方法二
通過數(shù)據(jù)庫方式
OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;");
DataTable dt = new DataTable();
try
{
ada.Fill(dt);
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
dataGrid1.DataSource = dt;
}
