.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

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

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); }

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: 2017-12-27

LVL 44

zephyr_hex (Megan)DeveloperCommented: 2017-12-27

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.

LVL 25

Snarf0001Commented: 2017-12-27

You're specifically adding another "<tr>" element on the pass of every data column.
They're just a bit out of order. Try this:

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

LVL 40

NorieAnalyst Assistant Commented: 2017-12-27

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: 2018-01-02

Thanks for the help

.net command application export data to excel

Who is Participating?