Export more than 100k data sql server to excel using C#

Question

private void button1_Click(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["db"].ConnectionString;

            SqlConnection con = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Sheet1$", con);

            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            //BindingSource bsource = new BindingSource();
            //bsource.DataSource = dt;
            //dataGridView1.DataSource = bsource;
            //sda.Update(dt);

            DataSet ds = new DataSet();
            ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
            sda.Fill(dt);
            ds.Tables.Add(dt);
            ExcelLibrary.DataSetHelper.CreateWorkbook("D:\\ChallanBulkUpload\\ChallanBulkUpload\\ChallanBulkUpload\\UploadFile\\challan.xls", ds);

        }

"For this reason my code doesn't work" what error do you get? have you tried selecting and inserting your data in batches? — user1666620
– user1666620, Commented Dec 2, 2015 at 10:45
when i click button , it show me This webpage is not available — Md. Mehedi Hassan
– Md. Mehedi Hassan, Commented Dec 2, 2015 at 11:06
@Md.MehediHassan ok. really, what kind of debugging have you been doing? did you set breakpoints and step through your code? do you catch exceptions and log them to a logfile? — user1666620
– user1666620, Commented Dec 2, 2015 at 11:19
Now i tried different way. I'm updated the code that i posted. Please see it. But give me error like Invalid cell value when i want to upload more than 50k data. But 35k data upload successfully.I want to upload more than 100k data. what's the problem ? please help me. I can't understand the problem. — Md. Mehedi Hassan
– Md. Mehedi Hassan, Commented Dec 2, 2015 at 17:55

Poiter · Accepted Answer · 2015-12-03 15:51:03Z

1

In my experience, using oledb to process excel files is a bit buggy. I switched over to using OpenXml (http://www.microsoft.com/en-us/download/details.aspx?id=30425). Maybe also a solution for you ....

Update: You can also use excel interop to process excel files (xls), see https://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

For this to work, Excel has to be installed on the system.

edited Dec 3, 2015 at 15:51

answered Dec 2, 2015 at 11:07

Poiter

4912 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scott Chamberlain Over a year ago

FYI: OpenXml can not create xls files, it can only create the newer xlsx format that Microsoft switched to starting with Office 2003.

Md. Mehedi Hassan Over a year ago

so, tell me what can i do ? I want to export more than 100k data from sql server to excel sheet with header. This will be C# windows forms application under button click.

PhillipH Over a year ago

You are not saying why @Poiter answer is not sufficient for you. You have not stated you need XLS format, and have not provided any feedback as to why the suggested answer is incorrect.

Collectives™ on Stack Overflow

Export more than 100k data sql server to excel using C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related