title: How To Use OleDb To Create Excel Database
body: <p>My task is to take an Access Database and create an Excel file, but I can not seem to <strong>CREATE</strong> the Excel file that OleDb will use.</p>
  
  <p>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.</p>
  
  <p>Right now, I have one hurtle I can not get over: If the Excel file does not exist, I cannot create it!</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p>If the Excel file does NOT exist, whenever I call <code>Open()</code> I get the following OleDbException:</p>
  
  <blockquote>
    <p>"<em>The Microsoft Access database engine could not find the object 'C:\Users\cp-jpool\Documents\Ecat5.xlsx'. Make sure the object exists and that you spell its name and the path name correctly. If 'C:\Users\cp-jpool\Documents\Ecat5.xlsx' is not a local object, check your network connection or contact the server administrator.</em>"</p>
  </blockquote>
  
  <p>So, the file does not exist, huh? Well, I tried creating it by modifying my <code>CTor()</code> to be:</p>
  
  <pre><code>public OleBase(string connectionString) {
    conStr = connectionString;
    object[] param = new object[] { null, null, null, "TABLE" };
    oleCon = new OleDbConnection(conStr);
    if (-1 &lt; 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);
  }
  </code></pre>
  
  <p>Now whenever this code attempts to call the <code>Open()</code> method of the OleDbConnection, I get this different OleDbException:</p>
  
  <blockquote>
    <p><em>"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."</em></p>
  </blockquote>
  
  <p>I even tried creating the Excel file using a <strong>StreamWriter</strong> to populate it with basic headers:</p>
  
  <pre><code>public OleBase(string connectionString) {
    conStr = connectionString;
    object[] param = new object[] { null, null, null, "TABLE" };
    oleCon = new OleDbConnection(conStr);
    if (-1 &lt; 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("&lt;?xml version=\"1.0\"?&gt;&lt;?mso-application progid=\"Excel.Sheet\"?&gt;");
        xls.WriteLine("&lt;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\"&gt;");
        xls.WriteLine("&lt;ss:Styles&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"Default\" ss:Name=\"Normal\"&gt;&lt;ss:Alignment ss:Vertical=\"Bottom\"/&gt;&lt;ss:Borders/&gt;&lt;ss:Font/&gt;&lt;ss:Interior/&gt;&lt;ss:NumberFormat/&gt;&lt;ss:Protection/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"BoldColumn\"&gt;&lt;ss:Font x:Family=\"Swiss\" ss:Bold=\"1\"/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"StringLiteral\"&gt;&lt;ss:NumberFormat ss:Format=\"@\"/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"Decimal\"&gt;&lt;ss:NumberFormat ss:Format=\"0.0000\"/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"Integer\"&gt;&lt;ss:NumberFormat ss:Format=\"0\"/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;ss:Style ss:ID=\"DateLiteral\"&gt;&lt;ss:NumberFormat ss:Format=\"mm/dd/yyyy;@\"/&gt;&lt;/ss:Style&gt;");
        xls.WriteLine("&lt;/ss:Styles&gt;");
        xls.WriteLine("&lt;/ss:Workbook&gt;");
        xls.Flush();
        xls.Close();
      }
    }
    oleCon.Open();
    tableNames = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, param);
  }
  </code></pre>
  
  <p>This code generated yet another OleDbException message:</p>
  
  <blockquote>
    <p><em>"External table is not in the expected format."</em></p>
  </blockquote>
  
  <p>That I can see, OleDbConnection does not have a method of creating the file, so how do I <strong>CREATE</strong> this Excel file so that I can use it?</p>