虽然Office2007的文件格式是表转的zip+xml，但手工写解析仍然是件非常痛苦的事情。因暂时还没有找到好用的Office2007的解析器，就仿照Excel2003将Excel当做OLE-DB来使用。
平台：VS2008 Excel2007
操作代码如下：
using System;
namespace CsConsole
{
class Program
{
const string ConnectionStringFormat = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
const string FileName = @"C:\Documents and Settings\Administrator\桌面\test.xlsx";
static void Main(string[] args)
{
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(string.Format(ConnectionStringFormat, FileName));
connection.Open();
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("select count(*) from [Sheet1$]", connection);
object ret = command.ExecuteScalar();
connection.Close();
Console.WriteLine(ret);
}
}
}
链接字符串从http://www.connectionstrings.com/excel-2007获取，强烈推荐该站点！
如果哪位朋友有好用的Office2007的解析组件，请分享一下，谢谢！
/// <summary>得到Excel的数据库连接字符串，适用于所有Excel表格格式</summary>
/// <remarks> ref: http://www.connectionstrings.com/excel-2007 </remarks>
static string GetDbConnectionString(string fileName)
{
return string.Format(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{0}\";" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", fileName);
}
static string GetSqlSelect(string sheetName, IEnumerable<string> columnNames)
{
return
"SELECT " + string.Join(", ", from c in columnNames select '[' + c + ']') +
" FROM [" + sheetName + "$]"; // Excel以OleDb方式打开时，每个表单名会以字符'$'结尾
}
/// <summary>得到Excel表中所有的表单名</summary>
/// <remarks> ref: http://www.cnblogs.com/dachie/archive/2010/05/17/1737497.html </remarks>
static IEnumerable<string> GetSheetNames(OleDbConnection connection)
{
var schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "Table" });
return
from DataRow item in schema.Rows
let name = item["TABLE_NAME"].ToString()
select name.Remove(name.Length - 1); // 移除表单名后面的'$'
}
/// <summary>得到Excel指定表单中所有的列名称</summary>
/// <remarks> ref: http://www.cnblogs.com/dachie/archive/2010/05/17/1737497.html </remarks>
static IEnumerable<string> GetColumnNames(OleDbConnection connection, string sheetName)
{
var schema = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, sheetName + '$', null });
return
from DataRow item in schema.Rows
let name = item["COLUMN_NAME"].ToString()
select name;
}
评论