How to create Excel file 31055.xlsx in application path

Posted by Ahmedsa under C# on 1/23/2017 | Points: 10 | Views : 1384 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts