How to create Excel file 31055.xlsx in application path

Posted by Ahmedsa under C# on 1/23/2017 | Points: 10 | Views : 244 | Status : [Member] | Replies : 1
I work in windows form c# visual studio 2015

I have excel file xlsx and name is 3105.xlsx and i need to create it in
application path

how to create excel file in application path

my code

public void CreateSheetIfNotExists()  
{
using (System.Data.OleDb.OleDbConnection databaseConnection = new System.Data.OleDb.OleDbConnection())
{

DataTable schemaTable = default(DataTable);
databaseConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=D:\\Book3105.xlsx;Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;";

databaseConnection.Open();

schemaTable = databaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] {
null,
null,
"Sheet1$"
});

if (schemaTable.Rows.Count == 0)
{
string SQLDDLCommand = "CREATE TABLE [Sheet1] (UserID INTEGER, UserName CHAR(255))";
System.Data.OleDb.OleDbCommand excelCommand = new System.Data.OleDb.OleDbCommand(SQLDDLCommand, databaseConnection);
excelCommand.ExecuteNonQuery();

}

databaseConnection.Close();
}
}





Responses

Posted by: A2H on: 1/23/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use the below code to get the executable path
//get the executable path
string pathofexecutable = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);

Your updated Code

public void CreateSheetIfNotExists()
{
using (System.Data.OleDb.OleDbConnection databaseConnection = new System.Data.OleDb.OleDbConnection())
{
//get the executable path
string pathofexecutable = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
DataTable schemaTable = default(DataTable);
databaseConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + pathofexecutable + "\\Book3105.xlsx;Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;";
databaseConnection.Open();
schemaTable = databaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, "Sheet1$" });
if (schemaTable.Rows.Count == 0)
{
string SQLDDLCommand = "CREATE TABLE [Sheet1] (UserID INTEGER, UserName CHAR(255))";
System.Data.OleDb.OleDbCommand excelCommand = new System.Data.OleDb.OleDbCommand(SQLDDLCommand, databaseConnection);
excelCommand.ExecuteNonQuery();

}
databaseConnection.Close();
}
}



Thanks,
A2H
My Blog

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response