如何使用的OleDb创建的Excel数据库 [英] How To Use OleDb To Create Excel Database
本文介绍了如何使用的OleDb创建的Excel数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的任务是把Access数据库，并创建一个Excel文件，但我似乎无法为创建的Excel文件，这些OleDb的将使用。
My task is to take an Access Database and create an Excel file, but I can not seem to CREATE the Excel file that OleDb will use.
Excel文件名称将通过运行工具的工程师提供。 。在Access数据库中的每个数据表将成为Excel文件中的工作表
The Excel file name will be provided by the Engineer running the tool. Each Data Table in the Access Database will become a WorkSheet in the Excel file.
现在，我有一个猛冲我不能克服：如果Excel文件不存在，我不能创建了！
Right now, I have one hurtle I can not get over: If the Excel file does not exist, I cannot create it!
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文件不存在，每当我称之为打开（）我得到以下OleDbException：
If the Excel file does NOT exist, whenever I call Open() I get the following OleDbException:
的 Microsoft Access数据库引擎不能找对象'C：\Users\cp-jpool\Documents\Ecat5.xlsx'。请确保该对象存在并且，您拼写其名称和正确的路径名。如果'C：\Users\ CP-jpool\Documents\Ecat5.xlsx'不是一个本地对象，请检查您的网络连接或与服务器管理员联系。的
因此，该文件不存在，是吧？
So, the file does not exist, huh? Well, I tried creating it by modifying my CTor() to be:
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的方法，我得到这个不同OleDbException：
Now whenever this code attempts to call the Open() method of the OleDbConnection, I get this different OleDbException:
的Microsoft Access数据库引擎无法打开或写入文件''。它已经被其他用户以独占方式打开，或者您需要查看权限和写入的数据。的
我甚至尝试使用创建Excel文件中的的StreamWriter 基本标题来填充它：
I even tried creating the Excel file using a StreamWriter to populate it with basic headers:
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消息，该代码：
This code generated yet another OleDbException message:
的外部表不是预期的格式。的
这是我所看到的，OleDbConnection的没有创建文件的方法，让我怎么创建这个Excel文件，以便我可以用它？
That I can see, OleDbConnection does not have a method of creating the file, so how do I CREATE this Excel file so that I can use it?
推荐答案
我看了大量的代码张贴在不同的网站，并最终与该工作了，我需要真正好的一个版本了。
I looked at lots of code posted on various websites, and eventually went with a version that worked really good for what I needed.
我希望我书签链接，所以我可以张贴在这里，但经过了这么多失败的尝试，它似乎我停止跟踪。
I wished I bookmarked the link so I could post it here, but after so many failed attempts it appears I stopped keeping track.
无论如何，如果它可以帮助别人，这里是我结束了（如何使用类紧随）的 ExcelWriter 类：
Anyway, if it helps others, here is the ExcelWriter class I ended up with (how to use the class is immediately following):
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工作簿：
Here is how to use this class to write an entire DataSet to an Excel workbook:
/// <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();
}
}
我希望其他人得到了很多用法出于此！
I hope others get a lot of usage out of this!
〜的Joep
这篇关于如何使用的OleDb创建的Excel数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
