.net command application export data to excel

I'm trying to export data to excel from a datatable. currently all the data goes into column one and I can't figure out why. Can someone help?

  public static void ExportToExcel(System.Data.DataTable dt, string excelFilePath = null)
        {
          //  string FileName = "Test.xls";
            string fullpath = excelFilePath;

            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 < dt.Columns.Count; i++)
            {
                objSB.Append("<th valign=middle>" + dt.Columns[i].ColumnName + "</th>");
            }

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

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

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



        }

Open in new window

NickMalloySystems LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NickMalloySystems LeadAuthor Commented:
I've also tried this but it only prints one row in excel, not all the rows

 public static void WriteToExcel(System.Data.DataTable dt, string fileFullName)
        {
            excel.Application XlObj = new excel.Application();
            XlObj.Visible = false;
            excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
            excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
            object misValue = System.Reflection.Missing.Value;


            try
            {
                int row = 1; int col = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    //adding columns
                    WsObj.Cells[row, col] = column.ColumnName;
                    col++;
                }
                //reset column and row variables
                col = 1;
                row++;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //adding data
                    foreach (var cell in dt.Rows[i].ItemArray)
                    {
                        WsObj.Cells[row, col] = cell;
                        col++;
                    }
                    col = 1;
                    row++;
                }
                WbObj.SaveAs(fileFullName, excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            finally
            {
                WbObj.Close(true, misValue, misValue);
            }
        }

Open in new window

zephyr_hex (Megan)DeveloperCommented:
Both of those are looping over the columns defined in your datatable.  So, what is the structure of your datatable?  You can drop a breakpoint and inspect the content  / structure of the datatable in debug.
Snarf0001Commented:
You're specifically adding another "<tr>" element on the pass of every data column.
They're just a bit out of order. Try this:

  public static void ExportToExcel(System.Data.DataTable dt, string excelFilePath = null)
        {
          //  string FileName = "Test.xls";
            string fullpath = excelFilePath;

            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 < dt.Columns.Count; i++)
            {
                objSB.Append("<th valign=middle>" + dt.Columns[i].ColumnName + "</th>");
            }
            objSB.Append("</tr>");

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

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

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



        }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
The 2nd code works for me though I do have a problem opening the file after but I think that's because of the filename/format I used when testing.

Have you tried stepping through the code? Perhaps even with Excel visible to see what's actually happening there.
NickMalloySystems LeadAuthor Commented:
Thanks for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.