Export data issue

Shankar M

8y1.1kLearn iOS Programming

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

23 Replies

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

Nitin Sontakke8y ago
I get that too, but you will see the data.
Shankar M8y ago
getting this error
Nitin Sontakke8y ago
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 M8y ago
Kindly repost the code again here 3 types of code is here
Nitin Sontakke8y ago
Have you already tried the code i posted yesterday, which was copied from yours?
Shankar M8y ago
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 < dtMainSQLData.Rows.Count + 1; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 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 Sontakke8y ago
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 M8y ago
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);

}
Nitin Sontakke8y ago
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.
Shankar M8y ago
Only issue with this code is , If have 10 rows in database it export 20 rows in excel. It means exporting 2 times
Shankar M8y ago
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 Sontakke8y ago
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 Sontakke8y ago
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 M8y ago
Nothing its blank page
Nitin Sontakke8y ago
You already tried clicking 'OK'? And what do you see?
Shankar M8y ago
The size of the excel which is genreated is 6kb and i tired to open in notepad ++, it seems like all invaild charcters.
Nitin Sontakke8y ago
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 M8y ago
Hi Rajneesh Chaubey,

Code executes without any error but not able to open the excel sheet
Rajneesh Chaubey8y ago
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 Sontakke8y ago
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 M8y ago
If i am remove ds.Tables.Add(dt) it throws an error DataSet needs to have at least one DataTable
Rajneesh Chaubey8y ago
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 Sontakke8y ago
You already have da.Fill(dt) twice there.

And then again there is ds.Tables.Add(dt), that becomes 3 times, right?