none
Issues writing Excel 2007 files using ADO.NET RRS feed

  • Question

  • I am trying to produce Excel 2007 workbooks directly from an application without requiring the machine have Excel installed.  I haven't been having a lot of success.

    I have done the same for Excel 97-2003 files without issue for a number of years.

     

    I have included a short C# program below that will produce both an Excel 2003 and an Excel 2007 file with identical data.

    It seems to run fine, but the resulting XLSX file won't open in Excel 2007. It says:

      Excel cannot open the file ... because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

    Then you can only click Ok and it doesn't open the file.

     

    At first I thought the file was just bad, but I always get this result.  I tried renaming it to *.XLS and then Excel 2007 says:

    The file you are trying to open, ..., is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

    Then if you click Yes, it opens the file and everything is there.  Strange!

     

    Then when investigating the new XLSX file format I learned it is really a ZIP file.  So I renamed it to ZIP and then can open it in Explorer and see folders and files that don't mean much to me, but it does appear to be an XLSX file.

     

    Has anyone got any idea why the XLSX file isn't quite correct?  Has anyone gotten this to work?

     

    FYI, I am running on Vista x64, but the application was set to build an x86 app so it would always run in 32bit.

     

    Thanks,

    Scott

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.OleDb;

    using System.Windows.Forms; // for MessageBox

    namespace TestExcel

    {

    public static class MainEntry

    {

    [STAThread]

    static void Main()

    {

    try

    {

    string[] connectStrings = new string[] {

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\TEMP\\TestExcel2003Output.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;\";",

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel 12.0;HDR=Yes;\";"

    };

    string dropTableStmt = "DROP TABLE [test]";

    string createTableStmt = "CREATE TABLE [test] ( [Integer] int, [String] varchar(40), [Double] float, [Date] datetime, [Boolean] bit )";

    string insertStmt = "INSERT INTO [test] ([Integer], [String], [Double], [Date], [Boolean]) VALUES ({0}, '{1}', {2}, '{3}', {4})";

    object[] data = new object[] {

    new object[] { 2628013, "Anderson", 0.617715356, new DateTime( 2008, 5, 5 ), true },

    new object[] { 2628015, "Rainaud", 0.64933168, new DateTime( 2007, 4, 10 ), false },

    new object[] { 2628017, "Dennis", 0.62140731, new DateTime( 2006, 3, 15 ), true },

    new object[] { 2628019, "Schoenster", 0.599058708, new DateTime( 2005, 2, 20 ), false },

    new object[] { 2628041, "Ganun", 0.593402527, new DateTime( 2004, 1, 25 ), true }

    };

    foreach( string connect in connectStrings )

    {

    OleDbConnection con = new OleDbConnection( connect );

    con.Open();

    if( con.State == ConnectionState.Open )

    {

    OleDbCommand cmd = con.CreateCommand();

    cmd.CommandTimeout = 0;

    try

    {

    // Only need this on runs subsequent to first time

    cmd.CommandText = dropTableStmt;

    cmd.ExecuteNonQuery();

    }

    catch

    {

    // First run will cause exception because table (worksheet) doesn't exist

    }

    cmd.CommandText = createTableStmt;

    cmd.ExecuteNonQuery();

    foreach( object[] row in data )

    {

    cmd.CommandText = String.Format( insertStmt, row[0], row[1], row[2], row[3], row[4] );

    cmd.ExecuteNonQuery();

    }

    cmd.Dispose();

    if( con.State == ConnectionState.Open )

    con.Close();

    con.Dispose();

    }

    }

    }

    catch( Exception ex )

    {

    MessageBox.Show( ex.Message );

    }

    }

    }

    }

     

    Thursday, May 15, 2008 5:15 PM

Answers

  • When you open zip file, do you set XML files inside of BIN files? I believe by default you will get XLSB file.

    Try to use next connection string, specifying XML file format

     

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\";"

     

    Friday, May 16, 2008 9:55 AM
    Moderator

All replies

  • Oh,  if anyone can think of a better place to post this please tell me!

     

    Thursday, May 15, 2008 5:17 PM
  • When you open zip file, do you set XML files inside of BIN files? I believe by default you will get XLSB file.

    Try to use next connection string, specifying XML file format

     

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\";"

     

    Friday, May 16, 2008 9:55 AM
    Moderator
  • That worked, THANK YOU!

     

    You are right, the BIN files inside were binary.

     

    Being new to Excel 2007 I had no idea there was more than one new file format.

    I wasn't having any trouble reading XLSX files using the connection string with "Excel 12.0" instead of "Excel 12.0 Xml" so it didn't even lead me to wonder.

     

    Thanks again,

    Scott

    Friday, May 16, 2008 12:46 PM