Comment Utiliser OleDb Pour Créer La Base De Données Excel

J'ai regardé beaucoup de code publié sur divers sites web, et finalement allé avec une version qui fonctionne vraiment très bien pour ce que j'en avais besoin.

J'ai souhaité que j'en signet le lien afin que je puisse le poster ici, mais après de nombreuses tentatives infructueuses, il semble que j'ai arrêté de garder la trace.

De toute façon, si elle aide les autres, voici la ExcelWriter classe j'ai fini avec (comment utiliser la classe est immédiatement suivante):

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
}

EXEMPLE:

Ici est de savoir comment utiliser cette classe pour écrire un ensemble de données dans un classeur 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();
}
}

J'espère que d'autres d'obtenir beaucoup de l'utilisation de ce!

~JoeP

J'ai trouvé une solution qui fonctionne, il suffit de retirer la société IMEX paramètre comme ceci :

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

Quand j'ai essayé Excel 12.0, il crée un fichier mais je ne peux pas l'ouvrir dans l'Application Excel.

De toute façon, ça fonctionne pour les fichiers XLS.