Export data issue

Question

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); } using above query when I am exporting data to excel sheet it copies the same date three times in output

Nitin Sontakke · Answer

Can you try code I posted? Or please post here the code that you have currently. I am sure it is now modified from what was originally posted.

Nitin Sontakke · Answer

I get that too, but you will see the data.

Shankar M · Answer

getting this error

Nitin Sontakke · Answer

Here, posting it again: SqlCommand cmd = new SqlCommand( "Select name,id from employee" , con); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet( "New_DataSet" ); ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; da.Fill(ds); con.Close(); con.Dispose(); ExcelLibrary.DataSetHelper.CreateWorkbook( "D:\Employee.xls" , ds); MessageBox.Show( "Exported" ); } catch (Exception ec) { MessageBox.Show(ec.Message); }

Shankar M · Answer

Kindly repost the code again here 3 types of code is here

Nitin Sontakke · Answer

Have you already tried the code i posted yesterday, which was copied from yours?

Shankar M · Answer

Hi All, This is the another code which is used to export data to excel works fine, but the issue is I am having more that lakhs of records in my database . This code takes approx 1hr to generate excel. SqlConnection sqlCon = new SqlConnection(conString); sqlCon.Open(); SqlDataAdapter da = new SqlDataAdapter("select * from employee", sqlCon); System.Data.DataTable dtMainSQLData = new System.Data.DataTable(); da.Fill(dtMainSQLData); DataColumnCollection dcCollection = dtMainSQLData.Columns; // Export Data into EXCEL Sheet Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); // ExcelApp.Cells.CopyFromRecordset(objRS); for (int i = 1; i { for (int j = 1; j { if (i == 1) ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString(); else ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs("D:\test.xlsx"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); MessageBox.Show("Data Exported Successfully into Excel File"); Suggest me any other solution

Nitin Sontakke · Answer

The following works, I tested it. SqlCommand cmd = new SqlCommand( "Select name,id from employee" , con); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet( "New_DataSet" ); ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; da.Fill(ds); con.Close(); con.Dispose(); ExcelLibrary.DataSetHelper.CreateWorkbook( "D:\Employee.xls" , ds); MessageBox.Show( "Exported" ); } catch (Exception ec) { MessageBox.Show(ec.Message); } I still get the formatting error, but data is exported successfully!

Shankar M · Answer

This is the code i am using SqlCommand cmd = new SqlCommand("Select name,id 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); con.Close(); con.Dispose(); ds.Tables.Add(dt); ExcelLibrary.DataSetHelper.CreateWorkbook("D:\Employee.xls", ds); MessageBox.Show("Exported"); } catch (Exception ec) { MessageBox.Show(ec.Message); }

Shankar M · Answer

Only issue with this code is , If have 10 rows in database it export 20 rows in excel. It means exporting 2 times

Shankar M · Answer

Already I have that code its working for me but my main database having lakhs of data, this code takes too much of time to create excel sheet , thats why i trying with this code.

Nitin Sontakke · Answer

And I see this article where file is created by iterating through rows: http://www.c-sharpcorner.com/blogs/create-excelxls-and-xlsx-file-from-c-sharp-using-excellibrary-in-window-application1

Nitin Sontakke · Answer

I am also getting some formatting errors, but the following piece of code does export to Excel successfully: using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace Export2Excel { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click( object sender, EventArgs e) { using (SqlConnection cn = new SqlConnection( "Server=.;Database=master;Integrated Security=True;" )) { using (SqlCommand cmd = new SqlCommand( "select name from sys.databases" , cn)) { cn.Open(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); ExcelLibrary.DataSetHelper.CreateWorkbook(AppDomain.CurrentDomain.BaseDirectory + "\Export2Excel.xls" , ds); } } } } }

Shankar M · Answer

Nothing its blank page

Nitin Sontakke · Answer

You already tried clicking 'OK'? And what do you see?

Shankar M · Answer

The size of the excel which is genreated is 6kb and i tired to open in notepad ++, it seems like all invaild charcters.

Nitin Sontakke · Answer

This, I believe, is because actually the created file is in OfficeXML format to the best of my knowledge. You can confirm what I am saying by opening it in Notepad or Notepad++. Just click OK and file opens correctly in MS-Excel as well.

Shankar M · Answer

Hi Rajneesh Chaubey, Code executes without any error but not able to open the excel sheet

Rajneesh Chaubey · Answer

Do this: 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( "New_DataSet" ); DataSet ds = new DataSet( "New_DataSet" ); //Creating locale for each ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; da.Fill(dt); con.close(); //Adding tables ds.Tables.Add(dt); ExcelLibrary.DataSetHelper.CreateWorkbook( "D:\Employee.xls" , ds); MessageBox.Show( "Exported" ); } catch (Exception ec) { MessageBox.Show(ec.Message); }

Nitin Sontakke · Answer

As per documentation, you can pass both datatable or dataset to .Fill method. Why not just pass the dataset and in debug see it has populated a datatable. To my knowledge it should. Then remove all other code.

Shankar M · Answer

If i am remove ds.Tables.Add(dt) it throws an error DataSet needs to have at least one DataTable

Rajneesh Chaubey · Answer

What are the values coming in datatable and in dataset? Have you debugged that? Also add this to your code see if it is working: dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; da.Fill(dt);

Nitin Sontakke · Answer

You already have da.Fill(dt) twice there. And then again there is ds.Tables.Add(dt), that becomes 3 times, right?

Export data issue

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (23)