Export Data To Excel file from a table in sql server using C#

Posted by Tiju.thomas under C# on 5/25/2009 | Views : 97576 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Friends,
Can any one please help me with the code in C# to export data from a table having 5 columns in Microsoft SQL Server into a new Microsoft Excel file(i.e the excel file should be created and saved at the run time, not to be exported to an existing excel file).
Thanks and Regards,
Tiju.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Lina99 on: 2/11/2011 [Member] Starter | Points: 25

0	I want to introduce one article for you, which shows us how to operate a small tool to export data to Word, PDF, Excel and other format from database. I think this article is very good and useful! You can read it on http://www.codeproject.com/KB/cs/Excel_PDF_Word_ExportWiz.aspx Tiju.thomas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Prabhakar on: 2/11/2011 [Member] [MVP] Starter | Points: 25

0	Hi please Check This Link http://www.dotnetfunda.com/articles/article1150-all-in-one-export-data-in-aspnet-gridview-to-doc-gridview-to-excel-gr-.aspx Best Regard's Prabhakar Tiju.thomas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Vuyiswamb on: 5/25/2009 [Member] [MVP] [Administrator] NotApplicable

0	Good Day Please find the Articles on this link http://dotnetguts.blogspot.com/2007/12/export-datagrid-to-excel-in-aspnet.html and this might help too public void Exel2Sql() { OdbcConnection connection; SqlBulkCopy bulkCopy; string ConnectionString = @"server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;"; string connstr = @"Driver={Microsoft Excel Driver (.xls)};DriverId=790;Dbq=c:\contact.xls"; using (connection = new OdbcConnection(connstr)) { OdbcCommand command = new OdbcCommand("Select FROM [Sheet1$]", connection); //you can change [Sheet1$] with your sheet name connection.Open(); // Create DbDataReader to Data Worksheet using (OdbcDataReader dr = command.ExecuteReader()) { // Bulk Copy to SQL Server using (bulkCopy = new SqlBulkCopy(ConnectionString)) { bulkCopy.DestinationTableName = "Names"; //"Names" is the sql table where you want to copy all data. bulkCopy.WriteToServer(dr); } dr.Close(); } } bulkCopy.Close(); connection.Close(); } and if you want to create an excell file on fly http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx http://www.codeproject.com/KB/cs/Excel_and_C_.aspx http://support.microsoft.com/kb/302084 or Workbook book = new Workbook(); Worksheet sheet = book.Worksheets.Add("Sample"); WorksheetRow row = sheet.Table.Rows.Add(); row.Cells.Add("Hello World"); book.Save(@"c:\test.xls"); Remember this should be the step before you export. Thank you for Posting at Dotnetfunda Vuyiswa Maseko Thank you for posting at Dotnetfunda [Administrator] Tiju.thomas, if this helps please login to Mark As Answer. \| Alert Moderator

Good Day

Please find the Articles on this link

http://dotnetguts.blogspot.com/2007/12/export-datagrid-to-excel-in-aspnet.html
and this might help too


public void Exel2Sql()

{

OdbcConnection connection;

SqlBulkCopy bulkCopy;

string ConnectionString = @"server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;";

string connstr = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls";

using (connection = new OdbcConnection(connstr))

{

OdbcCommand command = new OdbcCommand("Select * FROM [Sheet1$]", connection);



//you can change [Sheet1$] with your sheet name



connection.Open();



// Create DbDataReader to Data Worksheet



using (OdbcDataReader dr = command.ExecuteReader())

{

// Bulk Copy to SQL Server



using (bulkCopy = new SqlBulkCopy(ConnectionString))

{

bulkCopy.DestinationTableName = "Names";

//"Names" is the sql table where you want to copy all data.



bulkCopy.WriteToServer(dr);

}

dr.Close();

}



}



bulkCopy.Close();

connection.Close();

}

and if you want to create an excell file on fly

http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx

http://www.codeproject.com/KB/cs/Excel_and_C_.aspx

http://support.microsoft.com/kb/302084

or


Workbook book = new Workbook();

Worksheet sheet = book.Worksheets.Add("Sample");

WorksheetRow row = sheet.Table.Rows.Add();

row.Cells.Add("Hello World");

book.Save(@"c:\test.xls");

Remember this should be the step before you export.

Thank you for Posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

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

Latest Posts