Wie Verwenden Von OLE DB Zu Erstellen, Die Excel-Datenbank

Sah ich viel code gepostet auf verschiedenen websites, und ging schließlich mit einer version, die funktionierte wirklich gut für das, was ich brauchte.

Ich wünschte, ich Lesezeichen den link, so konnte ich es hier posten, aber nach so vielen vergeblichen versuchen scheint es, ich habe aufgehört zu verfolgen.

Jedenfalls, wenn es anderen hilft, hier ist die ExcelWriter Klasse landete ich mit (wie um die Klasse zu verwenden ist sofort nach):

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

}

BEISPIEL:

Hier ist, wie diese Klasse verwenden, um schreiben Sie einen gesamten Datensatz in eine Excel-Arbeitsmappe:

  ///<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();
    }
  }

Ich hoffe, andere bekommen eine Menge Gebrauch davon!

~JoeP

Fand ich eine funktionierende Lösung, einfach entfernen IMEX parameter wie diese :

string connString = String.Format(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" + 
    "Extended Properties=\"Excel 8.0;HDR={1}\"",
    file,
    (containsHeader ? "Yes" : "No")
);

Als ich versuchte, Excel 12.0, es wird eine Datei erstellt, aber ich kann nicht öffnen Sie es in der Excel-Anwendung.

Sowieso, das Zeug funktioniert für XLS-Dateien.