none
Space character got removed automatically while generating Excel using html text. RRS feed

  • Question

  • hi,

    I am generating excel from data table by converting it into HTML text. one the column contains multiple space in between words in the actual table, but those space got reduced as single space in the generated excel. I am using following code to generate Excel.

    StreamWriter sw = new StreamWriter(str_ExportFileName, true);
                sw.Write("<html xmlns:x='urn:schemas-microsoft-com:office:excel'>");
                sw.Write("<head>");
                sw.Write("<meta charset=\"utf-8\" /> ");
                sw.Write("<style>  .txt " + "\r\n" + " {mso-style-parent:style0;mso-number-format:\"" + @"\@" + "\"" + ";} " + "\r\n" + "</style>");
                sw.Write("<!--[if gte mso 9]>");
                sw.Write("<xml>");
                sw.Write("<x:ExcelWorkbook>");
                sw.Write("<x:ExcelWorksheets>");

                sw.Write("<x:ExcelWorksheet>");
                sw.Write("<x:Name>Sheet1</x:Name>");
                sw.Write("<x:WorksheetOptions>");
                sw.Write("<x:Panes>");
                sw.Write("</x:Panes>");
                sw.Write("</x:WorksheetOptions>");
                sw.Write("</x:ExcelWorksheet>");
                sw.Write("</x:ExcelWorksheets>");
                sw.Write("</x:ExcelWorkbook>");
                sw.Write("</xml>");
                sw.Write("<![endif]-->");
                sw.Write("</head>");
                sw.Write("<body>");
                sw.Write("<table style='font-family:\"Calibri\", Courier, monospace; font-size:15px' >");
                sw.Write("<thead>");
                sw.Write("<tr>");
                foreach (DataColumn c in dt.Columns)
                {
                    sw.Write("<th>");
                    sw.Write(c.ColumnName);
                    sw.Write("</th>");
                }
                sw.Write("</tr>");
                sw.Write("</thead>");
                sw.Write("<tbody>");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    sw.Write("<tr>");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        sw.Write("<td class='txt'>");
                        sw.Write(Convert.ToString(dt.Rows[i][j]));
                        sw.Write("</td>");
                    }
                    sw.Write("</tr>");
                }
                sw.Write("</tbody>");
                sw.Write("</table>");
                sw.Write("</body>");
                sw.Write("</html>");
             
                sw.Close();

    Thanks

    Tuesday, July 30, 2019 10:35 AM

Answers

  • Hi,

    You are writing HTML and HTML will always reduce all whitespace to a single space. You could use the HTML &nbsp; to hardcode multiple spaces. Just do something like:

    string value = Convert.ToString(dt.Rows[i][j]);

    value = value.Replace( " ", "&nbsp;");

    sw.Write(value);

    Kind regards,

    Johnny Hooyberghs

    Tuesday, July 30, 2019 10:41 AM