Need to export data from sql server to excel sheet in window

Question

Kindly someone help me on this by proving code

Leon D · Answer

Export data to Excel is very simple with Spire.XLS library. First add reference to Spire.Xls.dll (Which you can get from https://www.nuget.org/packages/Spire.XLS/), then refer to below code: DataTable dt = dataSet.Tables[0]; //export datatable to excel Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.InsertDataTable(dt, true , 1, 1); workbook.SaveToFile( "insertTableToExcel.xls" ,ExcelVersion.Version97to2003);

Leon D · Answer

I found the reason. When creating the workbook, its default version is 2003. Please specify the version when creating the workbook object, like this: //export datatable to excel Workbook workbook = new Workbook(); workbook.Version = ExcelVersion.2013; Worksheet sheet = workbook.Worksheets[0]; sheet.InsertDataTable(dt, true , 1, 1); workbook.SaveToFile( "insertTableToExcel.xlsx" ,ExcelVersion.Version2013);

Cynthia Sathuragiri · Answer

Hi Shankar, Here i am sharing you the script to export data from Sql table to excel sheet: Before running the script ,ensure that the AccessDatabaseEngine.exe is installed in your machine SET NOCOUNT ON; Exec sp_configure 'show advanced options', 1; RECONFIGURE; GO Exec sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1; GO --File Should be already present Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\Path.....\FileName.xlsx;Extended Properties=Excel 12.0')...[Sheet1$] select columnName from TableName Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\Path...\FileName.xlsx;Extended Properties=Excel 12.0')...[Sheet2$] select columnName from TableName -- Sheet Should be already Present along with headers EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0; GO Exec sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;

Shankar M · Answer

checked withthis code workbook.SaveToFile("insertTableToExcel.xlsx", ExcelVersion.Version2013); same result

Shankar M · Answer

Thanks Leon and others for helping me on this issue.

Shankar M · Answer

Thanks now it exports all the data . But when I open that excel file getting an error called

Leon D · Answer

Because you specified the result excel file format as .xls, .xls file can only contain 65536 rows. Please change the format to xlsx with the code I sent before. It should work.

Shankar M · Answer

It fetches only 65536 records

Leon D · Answer

Change the result excel file format to xlsx like this and see if it works: workbook.SaveToFile("insertTableToExcel.xlsx", ExcelVersion.Version2013);

Shankar M · Answer

Hi Leon D, Actually I am having 1 lakh of record in my database but it exports only 65536 records to excel

Leon D · Answer

You're very welcome, glad to know my code works :)

Leon D · Answer

Hi Shankar M, You already had the code to connect to sql server and read data into datatable, after that you can just use spire.xls and below code to insert that datatable into xls file. Sorry I don't have sql server database, so cannot provide you the full code. Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.InsertDataTable(datatable, true, 1, 1); workbook.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003);

Shankar M · Answer

Thanks leon, code works

Shankar M · Answer

Hi Leon D, Can you help me with complete code, i tired using tht snippet but getting errror

Shankar M · Answer

Response command is not working in windows application

Cynthia Sathuragiri · Answer

Hi Shankar, Here is the code to export data from Sql Server to Excel file. Hope it helps! string constr = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM tableName")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); using (XLWorkbook wb = new XLWorkbook()) { wb.Worksheets.Add(dt, "tbName"); Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } } } } }

Shankar M · Answer

I am using below code SqlConnection con = new SqlConnection("database connection string"); SqlCommand cmd = new SqlCommand("Select name,address from employee", con); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataTable dt = new DataTable(); da.Fill(dt); DataSet ds = new DataSet("New_DataSet"); ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; da.Fill(dt); ds.Tables.Add(dt); ExcelLibrary.DataSetHelper.CreateWorkbook("D:\Employee.xls", ds); MessageBox.Show("Exported"); } catch (Exception ec) { MessageBox.Show(ec.Message); } but when I am exporting data to excel sheet it copies the same date three times in output