locked
Problems came up in the following areas during load > Table RRS feed

  • Question

  • User-1970886443 posted

    Hi - the below method creates/exports a Microsoft Excel 97-2003 Worksheet file that gets saved to a server location, and then the app also tries to open the file for the user to view. 

    static public void Export(string filename, List<DataTable> tables)
            {
                //int sheetNumber = 1;
    
                StringBuilder sb = new StringBuilder();
    
                sb.AppendLine("<?xml version='1.0'?>");
                sb.AppendLine("<?mso-application progid='Excel.Sheet'?>");
                sb.AppendLine("<s:Workbook xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:s=\"urn:schemas-microsoft-com:office:spreadsheet\">");
                sb.AppendLine("  <x:ExcelWorkbook>");
                sb.AppendLine("    <x:WindowHeight>7000</x:WindowHeight>");
                sb.AppendLine("    <x:WindowTopX>100</x:WindowTopX>");
                sb.AppendLine("    <x:WindowTopY>200</x:WindowTopY>");
                sb.AppendLine("    <x:WindowWidth>8000</x:WindowWidth>");
                sb.AppendLine("    <x:ActiveSheet>0</x:ActiveSheet>");
                sb.AppendLine("    <x:ProtectStructure>False</x:ProtectStructure>");
                sb.AppendLine("    <x:ProtectWindows>False</x:ProtectWindows>");
                sb.AppendLine("  </x:ExcelWorkbook>");
                foreach (DataTable table in tables)
                {
                    //string sheetName = string.Format("Sheet_{0}", sheetNumber++);
                    string sheetName = table.TableName;
                    sb.AppendLine(string.Format("  <s:Worksheet s:Name=\"{0}\">", sheetName));
                    sb.AppendLine("    <s:Table>");
    
    
                    sb.AppendLine("      <s:Row>");
                    foreach (DataColumn column in table.Columns)
                    {
                        sb.AppendLine("        <s:Cell >");
                        sb.AppendLine("          <s:Data s:Type=\"String\">" + column.ColumnName + "</s:Data>");
                        sb.AppendLine("        </s:Cell>");
                    }
                    sb.AppendLine("      </s:Row>");
    
                    foreach (DataRow row in table.Rows)
                    {
                        sb.AppendLine("      <s:Row>");
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            string value = (row[i] == System.DBNull.Value) ? string.Empty : row[i].ToString();
    
                            sb.AppendLine("        <s:Cell>");
                            sb.AppendLine("          <s:Data s:Type=\"String\">" + value + "</s:Data>");
                            sb.AppendLine("        </s:Cell>");
                        }
                        sb.AppendLine("      </s:Row>");
                    }
    
                    sb.AppendLine("    </s:Table>");
                    sb.AppendLine("  </s:Worksheet>");
                }
    
                sb.AppendLine("</s:Workbook>");
    
                System.IO.File.WriteAllText(filename, sb.ToString());
            }
            #endregion exportExcel

    However upon opening the file this message box appears:

    The file format and extension of 'export_xxx.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway

    I click - Yes and get this error box:

    Problems During Load

    Problems came up in the following areas during load:

    Table

    if I open the logfile with the errors:

    XML PARSE ERROR: Missing attribute quote
    Error occurs at or below this element stack:
    <ss:Workbook>
    <ss:Worksheet>
    <ss:Table>
    <ss:Row>
    <ss:Cell>
    <ss:Data>

    Has anyone ever experienced this issue before? I have Excel 2016 installed. I can open the file in Notepad and XMLSpy, could it just be corrupt data in one of the cells? 

    Wednesday, June 6, 2018 2:40 PM

All replies

  • User283571144 posted

    Hi pmcm,

    According to your codes, I suggest you could firstly check the Worksheet name value isn't null.

    If the worksheets name is null, the excel couldn't open the xls file like below:

    If I set the sheet name it will be work well.

    More details, you could refer to below codes:

           protected void Button1_Click(object sender, EventArgs e)
            {
                List<DataTable> d1 = new List<DataTable>() { DataTableExmaple.LoadData(), DataTableExmaple.LoadData() };
                Export(@"D:\export_xxx.xls", d1);
            }
            static public void Export(string filename, List<DataTable> tables)
            {
                //int sheetNumber = 1;
    
                StringBuilder sb = new StringBuilder();
    
                sb.AppendLine("<?xml version='1.0'?>");
                sb.AppendLine("<?mso-application progid='Excel.Sheet'?>");
                sb.AppendLine("<s:Workbook xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:s=\"urn:schemas-microsoft-com:office:spreadsheet\">");
                sb.AppendLine("  <x:ExcelWorkbook>");
                sb.AppendLine("    <x:WindowHeight>7000</x:WindowHeight>");
                sb.AppendLine("    <x:WindowTopX>100</x:WindowTopX>");
                sb.AppendLine("    <x:WindowTopY>200</x:WindowTopY>");
                sb.AppendLine("    <x:WindowWidth>8000</x:WindowWidth>");
                sb.AppendLine("    <x:ActiveSheet>0</x:ActiveSheet>");
                sb.AppendLine("    <x:ProtectStructure>False</x:ProtectStructure>");
                sb.AppendLine("    <x:ProtectWindows>False</x:ProtectWindows>");
                sb.AppendLine("  </x:ExcelWorkbook>");
                int sheetNumber = 0;
                foreach (DataTable table in tables)
                {
                    string sheetName = string.Format("Sheet_{0}", sheetNumber++);
                    //string sheetName = table.TableName;
                    sb.AppendLine(string.Format("  <s:Worksheet s:Name=\"{0}\">", sheetName));
                    sb.AppendLine("    <s:Table>");
    
    
                    sb.AppendLine("      <s:Row>");
                    foreach (DataColumn column in table.Columns)
                    {
                        sb.AppendLine("        <s:Cell >");
                        sb.AppendLine("          <s:Data s:Type=\"String\">" + column.ColumnName + "</s:Data>");
                        sb.AppendLine("        </s:Cell>");
                    }
                    sb.AppendLine("      </s:Row>");
    
                    foreach (DataRow row in table.Rows)
                    {
                        sb.AppendLine("      <s:Row>");
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            string value = (row[i] == System.DBNull.Value) ? string.Empty : row[i].ToString();
    
                            sb.AppendLine("        <s:Cell>");
                            sb.AppendLine("          <s:Data s:Type=\"String\">" + value + "</s:Data>");
                            sb.AppendLine("        </s:Cell>");
                        }
                        sb.AppendLine("      </s:Row>");
                    }
    
                    sb.AppendLine("    </s:Table>");
                    sb.AppendLine("  </s:Worksheet>");
                }
    
                sb.AppendLine("</s:Workbook>");
    
                System.IO.File.WriteAllText(filename, sb.ToString());
            }

    Best Regards,

    Brando

    Thursday, June 7, 2018 6:17 AM
  • User-1970886443 posted

    Thanks for replying. The worksheet name value is being set to 'Requests' so it's not that. I've scanned your code snippet to what I have and they both match. Could the data be corrupted? I have other reports that I can and generate and open through the application with no issues.

    Thursday, June 7, 2018 2:40 PM
  • User283571144 posted

    Hi pmcm,

    Thanks for replying. The worksheet name value is being set to 'Requests' so it's not that. I've scanned your code snippet to what I have and they both match. Could the data be corrupted? I have other reports that I can and generate and open through the application with no issues.

    According to your description, I guess this issue is related with your data.

    Could you please post more details information about the export data.

    Does it contain the special charactor or some thing else?

    Best Regards,

    Brando

    Friday, June 22, 2018 1:26 AM