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

Shankar M

8y1.5kLearn iOS Programming

Kindly someone help me on this by proving code

17 Replies

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

Leon D8y ago
Accepted 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);
+1
Shankar M8y ago
Thanks Leon and others for helping me on this issue.
0
Shankar M8y ago
Thanks now it exports all the data . But when I open that excel file getting an error called

0
Leon D8y ago
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);
+1
Shankar M8y ago
checked withthis code

workbook.SaveToFile("insertTableToExcel.xlsx", ExcelVersion.Version2013);

same result
+1
Leon D8y ago
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.
0
Shankar M8y ago
It fetches only 65536 records
0
Leon D8y ago
Change the result excel file format to xlsx like this and see if it works:

workbook.SaveToFile("insertTableToExcel.xlsx", ExcelVersion.Version2013);
0
Shankar M8y ago
Hi Leon D,

Actually I am having 1 lakh of record in my database but it exports only 65536 records to excel
0
Leon D8y ago
You're very welcome, glad to know my code works :)
0
Leon D8y ago
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);
0
Shankar M8y ago
Thanks leon, code works
0
Shankar M8y ago
Hi Leon D,

Can you help me with complete code, i tired using tht snippet but getting errror
0
Shankar M8y ago
Response command is not working in windows application
0
Cynthia Sathuragiri8y ago
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();
}
}
}
}
}
}
0
Shankar M8y ago
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
0
Cynthia Sathuragiri8y ago
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;
+2