DataTable to Excel Format Issue

Gamzun

Im converting Datatable to excel like this way Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; string sep = ""; foreach (object column in Columns) { string[] value = column.ToString().Split(','); Response.Write(sep + value[0]); sep = "\t"; } Response.Write("\n"); foreach (DataRow dr in dt1.Rows) { sep = ""; for (i = 1; i < dt1.Columns.Count; i++) { Response.Write(sep + dr[i].ToString()); sep = "\t"; } Response.Write("\n"); } Response.End(); it converting fine but i would like to format the excel how's that possible. Thanks in advance byee

Christian Graus

Make a comma the seperator, Excel will hopefully open as a CSV

Christian Graus Please read this if you don't understand the answer I've given you "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Herman T Instance

Create a CSSclass and then add data with mso-number-format:.... in the css class. More Info to be found here[^] We style Date Fields with MSo-Number-Formatting via a static class

 public static void styleDataTable(Table table)
    {
        foreach (TableRow row in table.Rows)
        {
            foreach (TableCell cell in row.Cells)
            {
                if (IsDate(cell.Text))
                {
                    cell.CssClass = "tdDate";
                    cell.Width = Unit.Pixel(80);
                }
            }
        }
    }

In the DataTableToExcel Page we place in code behind:

Sesame.styleDataTable(htmlToExport);

(htmlToExport is a created Table based on the datatable)

Gamzun

thanks to both of you, i fixed the style by putting the data into html table and then wrote it in repose object, it was easy :) thanks again