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

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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.

NickMalloy

ASKER

Thanks for the help