CODEWITHSUNDEEP
c#asp.netexceloledb
Sanprof
Sanprof

Reputation: 409

Cannot create excel file using Microsoft.Jet.OLEDB.4.0

I found in several sources how to ctreate excel file, for example

but when I tried to use suggested code I have got an error on command Open() of OleDbConnection.

System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'D:\Import2013\Imported\254\template.xls'. Make sure the object exists and that you spell its name and the path name correctly.

That is a code I used.

string subFolder = Session["LoginID"] != null ? Server.MapPath( "Imported" ) + "\\" + Convert.ToString( Session["LoginID"] ) : "";
if (string.IsNullOrWhiteSpace( subFolder ))
{
    Response.Redirect( "Default.aspx" );
    Response.End();
}
StringBuilder commandText = new StringBuilder( "CREATE TABLE [Imported] (" );
if (!Directory.Exists( subFolder ))
    Directory.CreateDirectory( subFolder );
string fileName = subFolder + "\\template.xls";
if (File.Exists( fileName ))
    File.Delete( fileName );
var connectionString = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=YES;Mode=Write;IMEX=1\"", fileName );
using (var connection = new OleDbConnection( connectionString ))
{
    for (int i = 0; i < lvFieldNames.Count; i++)
    {
        commandText.Append( "[" + lvFieldNames[i].FieldName + "] varchar(64)" );
        if (i < lvFieldNames.Count - 1)
            commandText.Append( ", " );
    }
    commandText.Append( ");" );
    using (var cmd = new OleDbCommand( commandText.ToString(), connection ))
    {
        if (connection.State != System.Data.ConnectionState.Open)
            connection.Open(); //I have got an error on this line
        cmd.ExecuteNonQuery();
    }
    connection.Close();
}

Please, explain what I did wrong.

Upvotes: 0

Views: 2468

Answers (1)

Steve
Steve

Reputation: 216273

Using IMEX=1 in the connection string puts the Excel File in read only mode.

See ConnectionStrings.com

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

To create the Excel file you need IMEX=0 or IMEX=2

Upvotes: 1

Related Questions