Re: Save Data to Excel File

[Arran Pearce]

Can you create multiple sheets using ADO?

 

[Guest]

Dear Arran Pearce,
You can create multiple sheets using this code

for(int i=1;i<=10;++i)
{
strSheetName = "Sheet" + i.ToString();

strCreate = "CREATE TABLE " +
strSheetName +
"(RollNo char(255), FirstName char(255), " +
"MiddleName char(255), LastName char(255))";

objCmd.CommandText = strCreate;
objCmd.ExecuteNonQuery();
}

--
Thanks & Regards,
John Paul. A
MCP

Can you create multiple sheets using ADO?

Dear Perin,
In my C# Project, I have done this work.
Create a Data View Depending upon the filter options. Then apply this C# code. hope this will work.

//---------CODE STARTS------------------------------

//Creating the connecting string
System.Data.OleDb.OleDbConnection objConn =
new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
strFilePathAndName +
";" +
"Extended Properties=Excel 8.0;");

objConn.Open();

//Creating the command
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();

objCmd.Connection = objConn;

//Setting the sheet name
strSheetName = “Sheet1â€;

//Creating the first row in the Excel Sheet
strCreate = "CREATE TABLE " +
strSheetName +
"(RollNo char(255), FirstName char(255), " +
"MiddleName char(255), LastName char(255))";

objCmd.CommandText = strCreate;
objCmd.ExecuteNonQuery();

//Navigating the Data View to fill into a Excel file

foreach(DataRowView drv in dvFinal)
{
strInsert = "Insert into " +
strSheetName +
"(RollNo, FirstName, MiddleName, LastName)" +
"values ('" +
drv[0].ToString() +
"', '" +
drv[1].ToString() +
"', '" +
drv[2].ToString() +
"', '" +
drv[3].ToString() +
"')";
objCmd.CommandText = strInsert;
objCmd.ExecuteNonQuery();
}

//Closing the connection
objConn.Close();
}

//---------CODE ENDS -------------------------------

 

[Arran Pearce]

Thanks for that!