我的任务是获取访问数据库并创建一个Excel文件，但我似乎无法创建OleDb将使用的Excel文件。
Excel 文件名将由运行该工具的工程师提供。Access 数据库中的每个数据表都将成为 Excel 文件中的工作表。
现在，我有一个无法克服的难题：如果 Excel 文件不存在，我就无法创建它！
internal const string XL_FMT = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Mode=ReadWrite;Extended Properties="Excel {1};HDR={3};IMEX=1;"";
internal DataTable tableNames;
internal OleDbConnection oleCon;
private string conStr;
public OleBase(string connectionString) {
conStr = connectionString;
// Using the debugger, conStr is:
// "Provider=Microsoft.ACE.OLEDB.12.0;" +
// "Data Source=C:\Users\cp-jpool\Documents\Ecat5.xlsx;" +
// "Mode=ReadWrite;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1;""
object[] param = new object[] { null, null, null, "TABLE" };
oleCon = new OleDbConnection(conStr);
oleCon.Open();
tableNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, param);
}
如果 Excel 文件不存在，每当我调用Open()时，我都会得到以下 OleDbException：
">Microsoft Access 数据库引擎找不到对象'C：\Users\cp-jpool\Documents\Ecat5.xlsx'。确保对象存在，并且正确拼写其名称和路径名。如果'C：\Users\cp-jpool\Documents\Ecat5.xlsx'不是本地对象，请检查网络连接或与服务器管理员联系。
所以，该文件不存在，是吗？好吧，我尝试通过将我的CTor()修改为：
public OleBase(string connectionString) {
conStr = connectionString;
object[] param = new object[] { null, null, null, "TABLE" };
oleCon = new OleDbConnection(conStr);
if (-1 < conStr.IndexOf(";IMEX=1;")) {
string dsString = "Data Source=";
int dsIndex = conStr.IndexOf(dsString);
string conSub = conStr.Substring(dsIndex + dsString.Length);
int firstCol = conSub.IndexOf(';');
string xlPath = conSub.Substring(0, firstCol);
if (!File.Exists(xlPath)) {
File.Create(xlPath);
}
}
oleCon.Open();
tableNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, param);
}
现在，每当此代码尝试调用 OleDbConnection 的 Open() 方法时，我都会得到这个不同的 OleDbException：
"Microsoft Access 数据库引擎无法打开或写入文件''。它已经由其他用户专门打开，或者您需要权限才能查看和写入其数据。
我甚至尝试使用StreamWriter创建Excel文件，以使用基本标头填充它：
public OleBase(string connectionString) {
conStr = connectionString;
object[] param = new object[] { null, null, null, "TABLE" };
oleCon = new OleDbConnection(conStr);
if (-1 < conStr.IndexOf(";IMEX=1;")) {
string dsString = "Data Source=";
int dsIndex = conStr.IndexOf(dsString);
string conSub = conStr.Substring(dsIndex + dsString.Length);
int firstCol = conSub.IndexOf(';');
string xlPath = conSub.Substring(0, firstCol);
using (StreamWriter xls = new StreamWriter(xlPath, false, Encoding.UTF8)) {
xls.WriteLine("<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>");
xls.WriteLine("<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ");
xls.WriteLine("xmlns:o="urn:schemas-microsoft-com:office:office" ");
xls.WriteLine("xmlns:x="urn:schemas-microsoft-com:office:excel" ");
xls.WriteLine("xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">");
xls.WriteLine("<ss:Styles>");
xls.WriteLine("<ss:Style ss:ID="Default" ss:Name="Normal"><ss:Alignment ss:Vertical="Bottom"/><ss:Borders/><ss:Font/><ss:Interior/><ss:NumberFormat/><ss:Protection/></ss:Style>");
xls.WriteLine("<ss:Style ss:ID="BoldColumn"><ss:Font x:Family="Swiss" ss:Bold="1"/></ss:Style>");
xls.WriteLine("<ss:Style ss:ID="StringLiteral"><ss:NumberFormat ss:Format="@"/></ss:Style>");
xls.WriteLine("<ss:Style ss:ID="Decimal"><ss:NumberFormat ss:Format="0.0000"/></ss:Style>");
xls.WriteLine("<ss:Style ss:ID="Integer"><ss:NumberFormat ss:Format="0"/></ss:Style>");
xls.WriteLine("<ss:Style ss:ID="DateLiteral"><ss:NumberFormat ss:Format="mm/dd/yyyy;@"/></ss:Style>");
xls.WriteLine("</ss:Styles>");
xls.WriteLine("</ss:Workbook>");
xls.Flush();
xls.Close();
}
}
oleCon.Open();
tableNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, param);
}
此代码生成了另一个 OleDbException 消息：
"外部表的格式不符合预期。">
我可以看到，OleDbConnection 没有创建文件的方法，那么如何创建此 Excel 文件以便我可以使用它？
我查看了各种网站上发布的大量代码，最终采用了一个非常适合我需求的版本。
我希望我为链接添加了书签，以便我可以在此处发布它，但是经过如此多的失败尝试，我似乎停止了跟踪。
无论如何，如果它对其他人有帮助，这是我最终得到的ExcelWriter类(如何使用该类紧随其后(：
public class ExcelWriter : IDisposable {
private XmlWriter writer;
private static readonly DateTime NODATE = new DateTime(1900, 1, 1);
public enum CellStyle { General, Number, Currency, DateTime, ShortDate };
public ExcelWriter(string outputFileName) {
if (!String.IsNullOrEmpty(outputFileName)) {
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
writer = XmlWriter.Create(outputFileName, settings);
} else {
throw new Exception("Output path not supplied.");
}
}
public void Close() {
if (writer != null) {
writer.Close();
writer = null;
} else {
throw new NotSupportedException("Already closed.");
}
}
public static bool HasValue(object obj) {
if (obj != null) {
if (obj != DBNull.Value) {
string txt = obj.ToString();
return (0 < txt.Length);
}
}
return false;
}
public void WriteStartDocument() {
if (writer != null) {
writer.WriteProcessingInstruction("mso-application", "progid="Excel.Sheet"");
writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyles();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteEndDocument() {
if (writer != null) {
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
private void WriteExcelStyleElement(CellStyle style) {
if (writer != null) {
writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
writer.WriteEndElement();
}
}
private void WriteExcelStyleElement(CellStyle style, string NumberFormat) {
if (writer != null) {
writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
writer.WriteEndElement();
writer.WriteEndElement();
}
}
private void WriteExcelStyles() {
if (writer != null) {
writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyleElement(CellStyle.General);
WriteExcelStyleElement(CellStyle.Number, "General Number");
WriteExcelStyleElement(CellStyle.DateTime, "General Date");
WriteExcelStyleElement(CellStyle.Currency, "Currency");
WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");
writer.WriteEndElement();
}
}
public void WriteStartWorksheet(string name) {
if (writer != null) {
writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteEndWorksheet() {
if (writer != null) {
writer.WriteEndElement();
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteExcelColumnDefinition(int columnWidth) {
if (writer != null) {
writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteValue(columnWidth);
writer.WriteEndAttribute();
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteExcelUnstyledCell(string value) {
if (writer != null) {
writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
writer.WriteValue(value);
writer.WriteEndElement();
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteStartRow() {
if (writer != null) {
writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteEndRow() {
if (writer != null) {
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteExcelStyledCell(object value, CellStyle style) {
if (writer != null) {
writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
switch (style) {
case CellStyle.General:
writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
if (!HasValue(value)) {
value = String.Empty; // DBNull.Value causes issues in an Excel cell.
}
break;
case CellStyle.Number:
case CellStyle.Currency:
writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
if (!HasValue(value)) {
value = 0;
}
break;
case CellStyle.ShortDate:
case CellStyle.DateTime:
writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
if (!HasValue(value)) {
value = NODATE;
}
break;
}
writer.WriteValue(value);
writer.WriteEndElement();
writer.WriteEndElement();
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
public void WriteExcelAutoStyledCell(object value) {
if (writer != null) { //write the <ss:Cell> and <ss:Data> tags for something
if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
value is UInt16 || value is UInt32 || value is UInt64 || value is Byte) {
WriteExcelStyledCell(value, CellStyle.Number);
} else if (value is Single || value is Double || value is Decimal) { //we'll assume it's a currency
WriteExcelStyledCell(value, CellStyle.Currency);
} else if (value is DateTime) { //check if there's no time information and use the appropriate style
WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
} else {
WriteExcelStyledCell(value, CellStyle.General);
}
} else {
throw new NotSupportedException("Cannot write after closing.");
}
}
#region IDisposable Members
public void Dispose() {
if (writer != null) {
writer.Close();
writer = null;
}
}
#endregion
}
例：
下面介绍如何使用此类将整个数据集写入 Excel 工作簿：
/// <summary>
/// Saves data to the Access database to the Excel file specified by the filename
/// </summary>
public void Save(string excelFile) {
using (ExcelWriter writer = new ExcelWriter(excelFile)) {
writer.WriteStartDocument();
foreach (DataTable table in Dataset.Tables) {
writer.WriteStartWorksheet(string.Format("{0}", SafeName(table.TableName))); // Write the worksheet contents
writer.WriteStartRow(); //Write header row
foreach (DataColumn col in table.Columns) {
writer.WriteExcelUnstyledCell(col.Caption);
}
writer.WriteEndRow();
foreach (DataRow row in table.Rows) { //write data
writer.WriteStartRow();
foreach (object o in row.ItemArray) {
writer.WriteExcelAutoStyledCell(o);
}
writer.WriteEndRow();
}
writer.WriteEndWorksheet(); // Close up the document
}
writer.WriteEndDocument();
writer.Close();
}
}
我希望其他人从中得到很多用法！
~乔·
我找到了一个有效的解决方案，只需像这样删除IMEX参数：
string connString = String.Format(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" +
"Extended Properties="Excel 8.0;HDR={1}"",
file,
(containsHeader ? "Yes" : "No")
);
当我尝试Excel 12.0时，它会创建一个文件，但我无法在Excel应用程序中打开它。
无论如何，这些东西适用于XLS文件。
使用模板(已经存在的空 Excel 文件(将该文件复制到具有所需名称的新 Excel 文件中。那是你的创造！
要启用对文件的读写，您可以使用属性：
模式Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCELFILENAME;Mode=ReadWrite;Extended Properties='Excel 12.0 Macro;HDR=YES;'