Avatar of NickMalloy
NickMalloy
Flag for United States of America asked on

.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#

Avatar of undefined
Last Comment
NickMalloy

8/22/2022 - Mon
NickMalloy

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)

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

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

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

ASKER
Thanks for the help