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;
