c# - How To Use OleDb To Create Excel Database

Question

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.

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.

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

If the Excel file does NOT exist, whenever I call Open() I get the following OleDbException:

"The Microsoft Access database engine could not find the object 'C:Userscp-jpoolDocumentsEcat5.xlsx'. Make sure the object exists and that you spell its name and the path name correctly. If 'C:Userscp-jpoolDocumentsEcat5.xlsx' is not a local object, check your network connection or contact the server administrator."

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

Now whenever this code attempts to call the Open() method of the OleDbConnection, I get this different OleDbException:

"The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."

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

This code generated yet another OleDbException message:

"External table is not in the expected format."

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?

Answer 1

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.

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

}

EXAMPLE:

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