如何使用 OleDb 创建 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;'