关于C#中读写Microsoft Office Excel的说明

Application方式读写

添加COM程序集引用：Microsoft Excel 14.0 Object Library
添加using：Microsoft.Office.Interop.Excel

一、读取DataTable数据：

// 定义数据存储表
DataTable dataTable = new DataTable();
// Excel文件路径
string file = "C:/data.xls";
// 打开Excel应用程序
Application app = new Application();
// 设置Excel应用程序不可见
app.Visible = false;
// 读取Excel工作簿
Workbooks books = app.Workbooks;
_Workbook book = books.Add(file);
// 读取Excel工作表
Sheets sheets = book.Worksheets;
_Worksheet sheet = sheets.get_Item(1) as _Worksheet;
// 读取列名
for (int i = 0, length = sheet.Columns.Count; i < length; i++)
{
    dataTable.Columns.Add(sheet.Cells[1, i + 1].ToString());
}
// 读取行数据
for (int i = 0, iLength = sheet.Rows.Count; i < iLength; i++)
{
    object[] obj = new object[sheet.Columns.Count];
    for (int j = 0, jLength = sheet.Columns.Count; j < jLength; j++)
    {
        obj[j] = sheet.Cells[i + 2, j + 1];
    }
    dataTable.Rows.Add(obj);
}
// 不保存原始数据状态下关闭Excel应用程序
app.UserControl = false;
app.Quit();
return dataTable;

二、写入DataTable数据：

// 要写入的数据表
DataTable dataTable = new DataTable();
// Excel文件保存路径
string file = "C:/data.xls";
// 打开Excel应用程序
Application app = new Application();
// 设置Excel应用程序不可见
app.Visible = false;
// 读取Excel工作簿
Workbooks books = app.Workbooks;
_Workbook book = books.Add(XlWBATemplate.xlWBATWorksheet);
// 读取Excel工作表
Sheets sheets = book.Worksheets;
_Worksheet sheet = sheets.get_Item(1) as _Worksheet;
// 写入列名
for (int i = 0, length = dataTable.Columns.Count; i < length; i++)
{
    sheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
}
// 写入行数据
for (int i = 0, iLength = dataTable.Rows.Count; i < iLength; i++)
{
    for (int j = 0, jLength = dataTable.Columns.Count; j < jLength; j++)
    {
        sheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j].ToString();
    }
}
// 保存Excel工作簿
book.SaveCopyAs(file);
book.Saved = true;
// 关闭Excel应用程序
app.UserControl = false;
app.Quit();

ADO.NET方式读写

一、获取Sheet名：

// 定义Sheet数据表名存储数组
string[] sheets = new string[] { };
// Excel文件路径
string file = "C:/data.xls";
// OleDb链接字符串
string connection = "Provider=Microsoft.Jet.OLEDB.4.0;";
connection += "Data Source=" + file + ";";
connection += "Extended Properties=Excel 8.0;";
// 打开OleDb链接
using (OleDbConnection con = new OleDbConnection(connection))
{
    con.Open();
    // 读取数据架构信息
    DataTable dataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    // 读取Sheet数据表名
    sheets = new string[dataTable.Rows.Count];
    for (int i = 0, length = dataTable.Rows.Count; i < length; i++)   
    {
        sheets[i] = dataTable.Rows[i]["TABLE_NAME"].ToString();
    }
    // 关闭OleDb链接
    con.Close();
}
return sheets;

二、读取DataTable数据：

// 定义数据存储表
DataTable dataTable = new DataTable();
// Excel文件路径
string file = "C:/data.xls";
// OleDb链接字符串
string connection = "Provider=Microsoft.Jet.OLEDB.4.0;";
connection += "Data Source=" + file + ";";
connection += "Extended Properties=Excel 8.0;";
// 打开OleDb链接
using (OleDbConnection con = new OleDbConnection(connection))
{
    con.Open();
    // 查询对应Sheet数据表的数据
    string sql = "select * from [sheet$]";
    // 读取表数据
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con))
    {
        adapter.Fill(dataTable);
    }
    // 关闭OleDb链接
    con.Close();
}
return dataTable;