How to create excel file using c# windows form

ahmed salah

9y3.7kVisual Basic .NET

I try to create excel file using c# windows form visual studio 2015

i need to create excel file with excel 2007 with extension xlsx

after i click the button and got to path i need to create i not found file found in path

so that what is wrong in code bellow :

public Boolean AddExcelRow2007(String strFilePath)
{
if (!File.Exists(strFilePath)) return false;
string strExcelConn = "Provider = Microsoft.ACE.OLEDB.12.0;" +
"Data Source =" +strFilePath + "; Excel 12.0; HDR = YES;";
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
try
{
cmdExcel.Connection = connExcel;
//Check if the Sheet Exists
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema =
connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
DataRow[] dr =
dtExcelSchema.Select("TABLE_NAME = 'tblData'");
//if not Create the Sheet
if (dr == null || dr.Length == 0)
{
cmdExcel.CommandText = "CREATE TABLE[tblData]" +
" (ID varchar(10), Name varchar(50));";
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
}
//Add New Row to Excel File
cmdExcel.CommandText = "INSERT INTO[tblData]" + "(ID, Name) values('0', 'Start')";
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
return true;
}
catch
{
return false;
}
finally
{
cmdExcel.Dispose();
connExcel.Dispose();
}
}

and after that i call

private void button6_Click_1(object sender, EventArgs e)
{
AddExcelRow2007("D:\\Book300.xlsx");
}

But excel file not created

so that what is wrong in code above

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Can you check with the below link:
https://www.youtube.com/watch?v=8kA6V4YG1i4
Laxmidhar Sahoo7y ago
You can create by save as option of the worksheet using bellow code
Microsoft.Office.Interop.Excel.Application xlApp;
           Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
           Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
           Microsoft.Office.Interop.Excel.Range range;

           string str;
           int rCnt = 0;
           int cCnt = 0;
           string excelpath = fname;
           xlApp = new Microsoft.Office.Interop.Excel.Application();
           xlWorkBook = xlApp.Workbooks.Open(@excelpath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
           xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Alex Smith9y ago
Hi, check this link for generating Excel from database.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
ahmed salah9y ago
my problem only in creating excel so that i try with another way
in the code here excel file created but not open so that can you tell me why not open after created
my code as bellow
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:\\Book310.xlsx;Extended Properties=Excel 12.0;";

               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();
           }
       }
after that file 310 created but not open
when open file i get this error
Amit Gupta9y ago
Hey Ahmed,
Please review your code and question, as you are asking 'excel file was not created' and in your code, you are returning false, if file doesnot exists

if (!File.Exists(strFilePath)) return false;

Please check the below link for your reference:
http://csharp.net-informations.com/excel/csharp-create-excel.htm