.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

.NET ProgrammingC#

Last Comment

NickMalloy

8/22/2022 - Mon

NickMalloy

12/27/2017

ASKER

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)

12/27/2017

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.

ASKER CERTIFIED SOLUTION

Snarf0001

12/27/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Norie

12/27/2017

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.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

NickMalloy

1/2/2018

ASKER

Thanks for the help