Export Datatable to excel

Question

3.00/5 (1 vote)

See more:

How to export data from datatable into excel file with a proper output. means that first row of excel record should be the header and the display data should show all the value instead of rounding up the values. please help :)ldt_Temp (datatable),as_OutputDir (exported dir)

C#

object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;

            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

            // Headers.  
            for (int i = 0; i < ldt_Temp.Columns.Count; i++)
            {
                ws.Cells[1, i + 1] = ldt_Temp.Columns[i].ColumnName;
            }
            // Content.  
            for (int i = 0; i < ldt_Temp.Rows.Count; i++)
            {
                for (int j = 0; j < ldt_Temp.Columns.Count; j++)
                {
                    ws.Cells[i + 2, j + 1] = ldt_Temp.Rows[i][j].ToString();
                }
            }
            ws.Name = ldt_Temp.TableName;

          
            wb.SaveAs(as_OutputDir, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            wb.Close(true, misValue, misValue);
            app.Quit();

Posted 3-Oct-13 16:19pm

Member 2321293

Updated 3-Oct-13 16:56pm

Ron Beyer

v3

Add a Solution

Comments

PIEBALDconsult 3-Oct-13 23:53pm

I use OleDb and the ACE engine to write to Excel and I haven't noticed any rounding. Do you have any examples?

Kipperr 7-Mar-17 6:17am

Here is how I exported my DataTable into an Excel in C#.
I short, the following is the key part:

sheet.InsertDataTable(dataTable,
new InsertDataTableOptions() { ColumnHeaders = true });

That API comes from this Excel library for .NET.

7 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Naresh.Thakor · Answer 1 · 2013-10-03T19:42:00

Here i have one more solution to export data table to excel. You need to just create a string as per your requirement. The most important benefits of this method is file accessibility. that means if you have a Open Office instead of MS-Office this will work fine.

C#

string FileName = "Test.xls";
string fullpath = path + @"\" + FolderName + FileName;

int tblborder = 2;
StreamWriter SW;
SW = File.CreateText(fullpath);
StringBuilder objSB = new StringBuilder();
objSB.Append("<Table border=" + tblborder + "  width=100%>");

objSB.Append("<tr>");

for (int i = 0; i < ldt_Temp.Columns.Count; i++)
{
    objSB.Append("<th valign=middle>" + ldt_Temp.Columns[i].ColumnName + "</th>");
}

objSB.Append("</tr>");
objSB.Append("<tr>");

for (int i = 0; i < ldt_Temp.Rows.Count; i++)
{
      for (int j = 0; j < ldt_Temp.Columns.Count; j++)
      {
            objSB.Append("<tr>");
                objSB.Append("<td align=center>" + ldt_Temp.Rows[i][j].ToString() + "</td>");
        objSB.Append("</tr>");
      }
}

objSB.Append("</Table>");
SW.Write(objSB.ToString());
SW.Close();
Response.Redirect(filePath);

kesav aggarwal · Answer 2 · 2013-10-03T17:07:00

Solution 1

the only way that the value will not be rounded up is to convert all the numeric values into the string values so that they can't be altered otherwise they will be rounded up automatically.

Posted 3-Oct-13 17:07pm

kesav aggarwal

Comments

Member 2321293 3-Oct-13 23:23pm

is you mean this part "ws.Cells[i + 2, j + 1] = ldt_Temp.Rows[i][j].ToString();" or change the datatable columns into system.string format first? but i did so.. work the same

kesav aggarwal 4-Oct-13 11:39am

best way is to convert the data to string when retrieving from database so that the normal operations on database can be performed. Excel will accept the string value easily and values in the cell will be in numeric format.

Rakesh Meel · Answer 3 · 2013-10-03T19:56:00

Solution 3

visit here...

Export to EXCEL from Datatable in C#.Net[^]

or

http://social.msdn.microsoft.com/Forums/vstudio/en-US/af96f5cd-8f4e-4187-b615-4f9deb23e694/export-datatable-to-excel-using-c-with-leading-zeros?forum=csharpgeneral[^]

Posted 3-Oct-13 19:56pm

Rakesh Meel

berrymaria · Answer 4 · 2013-10-03T20:06:00

Solution 4

Hi, See this link:

Export Datatable to Excel in C#[^]
Export dataset to excel in windows application [^]
How to export DataTable to Excel in C#[^]

Hope it Helps! :)

Posted 3-Oct-13 20:06pm

berrymaria

Updated 3-Oct-13 20:08pm

v3

Member 2321293 · Answer 5 · 2013-10-03T20:19:00

and i also find out a way to rearrange each number format for each cells, loop each cells and get the length of digit and create the format dynamically way (inside second loop)

C#

string ls_Test = cells value
     string part = ls_Test.Substring(0, ls_Test.IndexOf('.'));
     string ls_FormatDeicmal = ls_Test.Substring(part.Length + 1, ls_Test.Length - (part.Length + 1));
     string ls_DigiFormat = "";
     for (int f = 0; f < ls_FormatDeicmal.Length; f++)
     {
         ls_DigiFormat += "0";
     }
     ls_DigiFormat = "0." + ls_DigiFormat; // 0.000000000, 0.00, 0.00000
     ws.Cells[i + 2, j + 1].NumberFormat = ls_DigiFormat;

Diana Tsax · Answer 6 · 2013-10-03T22:32:00

public void ExportToExcel()
       {
           try
           {
               this.OrderGridView.AllowPaging = false;
               this.OrderGridView.AllowSorting = false;
               this.OrderGridView.EditIndex = -1;

               // Let's bind data to GridView
               this.RefreshGrid();

// IF YOU DO NOT NEED SOME COLUMNS LIKE THE FIRST TWO IN MY EXAMPLE ADD THE FOLLOWING TWO ROWS

               OrderGridView.Columns.RemoveAt(1);
               OrderGridView.Columns.RemoveAt(0);

               OrderGridView.DataBind();
               // Let's output HTML of GridView
               Response.Clear();
               Response.ContentType = "application/vnd.xls";
               Response.AddHeader("content-disposition",
                       "attachment;filename=MyOrder.xls");
               Response.Charset = "";
               StringWriter swriter = new StringWriter();
               HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
               OrderGridView.RenderControl(hwriter);
               Response.Write(swriter.ToString());
               Response.Flush();
               Response.End();
               //HttpContext.Current.ApplicationInstance.CompleteRequest();
           }
           catch (Exception exe)
           {
               throw exe;
           }
       }

And then call the function on button click:

protected void btnExcel_Click(object sender, ImageClickEventArgs e)
       {
           ExportToExcel();
           this.OrderGridView.AllowPaging = true;
           this.OrderGridView.AllowSorting = true;
           this.OrderGridView.EditIndex = -1;
           this.RefreshGrid();
       }

I hope it helps you!

crazie.coder · Answer 7 · 2013-10-03T22:54:00

Solution 7

Hi,

Try code from below link,

http://juhi2012.wordpress.com/2013/10/02/datatable-to-excel-file-without-using-a-loop/[^]

It's worked for me.

Posted 3-Oct-13 22:54pm

crazie.coder