0

I'm tring to export data to a formated XLS, but I seem to have some kind of limit issue. It exports some datasets and some it doesn't.
I've gone through the resulted XML and commented most of the rows, then unkommented them one after one until the document opened sussesfully. I know of the row and column limitations but the resulted document fails to open in Excel far before those. I'm talking about failing when more then 34 excel rows (about 500 formated XML lines).

Here is my full export code:

private void GenerateConfigReport(HttpContext context)
{
    GIS34PortalService client = new GIS34PortalService();
    DataTable features = client.GetFeatures(objectId, "id");
    string filename = "ConfigRapport";            

    #region header

    StringBuilder header = new StringBuilder();
    header.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
    header.AppendLine("<?mso-application progid=\"Excel.Sheet\"?>");
    header.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
    header.AppendLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
    header.AppendLine("  <Author>Amit Bracha</Author>");
    header.AppendLine("  <LastAuthor>Amit Bracha</LastAuthor>");
    header.AppendLine("  <Created>" + DateTime.Now + "</Created>");
    header.AppendLine("  <LastSaved>" + DateTime.Now + "</LastSaved>");
    header.AppendLine("  <Company>Landinspektørfirmaet LE34 A/S</Company>");
    header.AppendLine("  <Version>12.00</Version>");
    header.AppendLine(" </DocumentProperties>");
    header.AppendLine(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">");
    header.AppendLine("  <RemovePersonalInformation/>");
    header.AppendLine(" </OfficeDocumentSettings>");
    header.AppendLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
    header.AppendLine("  <WindowHeight>7875</WindowHeight>");
    header.AppendLine("  <WindowWidth>14895</WindowWidth>");
    header.AppendLine("  <WindowTopX>360</WindowTopX>");
    header.AppendLine("  <WindowTopY>300</WindowTopY>");
    header.AppendLine("  <ProtectStructure>False</ProtectStructure>");
    header.AppendLine("  <ProtectWindows>False</ProtectWindows>");
    header.AppendLine(" </ExcelWorkbook>"); 

    #endregion

    #region styles

    StringBuilder styles = new StringBuilder();
    styles.AppendLine(" <Styles>");
    ...
    styles.AppendLine(" </Styles>"); 

region

    #region title

    StringBuilder title = new StringBuilder();
    title.AppendLine(" <Worksheet ss:Name=\"Ark1\">");
    title.AppendLine("  <Names>");
    title.AppendLine("   <NamedRange ss:Name=\"GIS34_Skade_V1_alle_koder\" ss:RefersTo=\"=Ark1!R5C2:R28C2\"/>");
    title.AppendLine("  </Names>");
    title.AppendLine("  <Table ss:ExpandedColumnCount=\"16\" ss:ExpandedRowCount=\"34\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:StyleID=\"s16\" ss:DefaultRowHeight=\"15\">");
    title.AppendLine("   <Column ss:StyleID=\"s16\" ss:AutoFitWidth=\"0\" ss:Width=\"30\"/>");
    title.AppendLine("   <Column ss:StyleID=\"s16\" ss:AutoFitWidth=\"0\" ss:Width=\"135\" ss:Span=\"2\"/>");
    title.AppendLine("   <Column ss:Index=\"5\" ss:StyleID=\"s19\" ss:AutoFitWidth=\"0\" ss:Width=\"30\"/>");
    title.AppendLine("   <Column ss:StyleID=\"s16\" ss:AutoFitWidth=\"0\" ss:Width=\"135\"/>");
    title.AppendLine("   <Column ss:StyleID=\"s19\" ss:AutoFitWidth=\"0\" ss:Width=\"30\"/>");
    title.AppendLine("   <Column ss:StyleID=\"s16\" ss:AutoFitWidth=\"0\" ss:Width=\"135\"/>");
    title.AppendLine("   <Column ss:StyleID=\"s19\" ss:AutoFitWidth=\"0\" ss:Span=\"3\"/>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" ss:StyleID=\"s18\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s126\"><Data ss:Type=\"String\">LE34</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s87\"><Data ss:Type=\"String\">Kunde:</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">Assens Kommune</Data></Cell>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" ss:StyleID=\"s18\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s128\"><Data ss:Type=\"String\">GIS34</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s87\"><Data ss:Type=\"String\">Kodeliste:</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">" + objectName + "</Data></Cell>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:MergeAcross=\"6\" ss:StyleID=\"s37\"><Data ss:Type=\"String\">Udformning af lagkontrol: </Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:MergeAcross=\"6\" ss:StyleID=\"s37\"><Data ss:Type=\"String\">Lagkontrollen kan udformes så den give en meningsfuld oversigt over opgaverne.</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:MergeAcross=\"6\" ss:StyleID=\"s37\"><Data ss:Type=\"String\">Der defineres de lag som matcher de teams som skal løse opgaverne.</Data><NamedCell ss:Name=\"GIS34_Skade_V1_alle_koder\"/></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:Index=\"5\" ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:Index=\"7\" ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:Index=\"9\" ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:Height=\"20.0625\" ss:StyleID=\"s17\">");
    title.AppendLine("    <Cell ss:StyleID=\"s33\"/>");
    title.AppendLine("    <Cell ss:StyleID=\"s24\"><Data ss:Type=\"String\">Features</Data><NamedCell ss:Name=\"GIS34_Skade_V1_alle_koder\"/></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s25\"><Data ss:Type=\"String\">Attributter</Data></Cell>");
    title.AppendLine("    <Cell ss:StyleID=\"s26\"><Data ss:Type=\"String\">Menu items</Data></Cell>");
    title.AppendLine("    <Cell ss:Index=\"6\" ss:StyleID=\"s27\"><Data ss:Type=\"String\">Lagkontrol</Data></Cell>");
    title.AppendLine("    <Cell ss:Index=\"8\" ss:StyleID=\"s28\"><Data ss:Type=\"String\">WebGIS</Data></Cell>");
    title.AppendLine("   </Row>");
    title.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:StyleID=\"s17\"/>");

    #endregion

    #region content

    StringBuilder content = new StringBuilder();

    foreach (DataRow feature in features.Rows)
    {
        content.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
        content.AppendLine("    <Cell ss:Index=\"2\" ss:MergeAcross=\"2\" ss:StyleID=\"s36\"><Data ss:Type=\"String\">" + (feature["name"].ToString() + " (" + feature["geometry_type"].ToString().ToLower() + ")") + "</Data></Cell>");
        content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
        content.AppendLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\"></Data></Cell>");
        content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
        content.AppendLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\"></Data></Cell>");
        content.AppendLine("   </Row>");

        DataTable attributtes = client.GetAttributes(feature["id"].ToString(), "id");

        foreach (DataRow attributte in attributtes.Rows)
        {
            content.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
            content.AppendLine("    <Cell ss:Index=\"2\" ss:StyleID=\"s29\"></Cell>");
            content.AppendLine("    <Cell ss:StyleID=\"s30\"><Data ss:Type=\"String\">" + (attributte["name"].ToString() + " (" + attributte["type"].ToString().ToLower() + ")") + "</Data></Cell>");
            content.AppendLine("    <Cell ss:StyleID=\"s30\"/>");
            content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
            content.AppendLine("    <Cell ss:StyleID=\"s20\"/>");
            content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
            content.AppendLine("    <Cell ss:StyleID=\"s20\"/>");
            content.AppendLine("   </Row>");

            if (attributte["type"].ToString().ToLower() == "menu")
            {
                DataTable menuItems = client.GetAttributeInfo(attributte["id"].ToString(), attributte["type"].ToString(), "id");

                foreach (DataRow menuItem in menuItems.Rows)
                {
                    content.AppendLine("   <Row ss:AutoFitHeight=\"0\">");
                    content.AppendLine("    <Cell ss:Index=\"2\" ss:StyleID=\"s29\"></Cell>");
                    content.AppendLine("    <Cell ss:StyleID=\"s30\"/>");
                    content.AppendLine("    <Cell ss:StyleID=\"s30\"><Data ss:Type=\"String\">" + (menuItem["name"].ToString() + (menuItem["value"].ToString().ToLower().Contains("true") ? " (default)" : "")) + "</Data></Cell>");
                    content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
                    content.AppendLine("    <Cell ss:StyleID=\"s20\"/>");
                    content.AppendLine("    <Cell ss:StyleID=\"s16\"/>");
                    content.AppendLine("    <Cell ss:StyleID=\"s20\"/>");
                    content.AppendLine("   </Row>");
                }
            }
        }
    }
    content.AppendLine("  </Table>");

    #endregion

    #region footer

    StringBuilder footer = new StringBuilder();
    footer.AppendLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
    footer.AppendLine("   <PageSetup>");
    footer.AppendLine("    <Header x:Margin=\"0.3\"/>");
    footer.AppendLine("    <Footer x:Margin=\"0.3\"/>");
    footer.AppendLine("   </PageSetup>");
    footer.AppendLine("   <Selected/>");
    footer.AppendLine("   <FreezePanes/>");
    footer.AppendLine("   <FrozenNoSplit/>");
    footer.AppendLine("   <SplitHorizontal>7</SplitHorizontal>");
    footer.AppendLine("   <TopRowBottomPane>7</TopRowBottomPane>");
    footer.AppendLine("   <ProtectObjects>False</ProtectObjects>");
    footer.AppendLine("   <ProtectScenarios>False</ProtectScenarios>");
    footer.AppendLine("  </WorksheetOptions>");
    footer.AppendLine(" </Worksheet>");
    footer.AppendLine("</Workbook>");

    #endregion

    context.Response.ContentType = "application/excel";
    context.Response.AddHeader("Content-disposition", "attachment; filename=\"report.xls\"");
    string excel = header.ToString() + styles.ToString() + title.ToString() + content.ToString() + footer.ToString();
    HttpContext.Current.Response.BinaryWrite(UTF8Encoding.UTF8.GetBytes(excel));
}

Here is the result XML: http://www.gis34.dk/docs/report.xls
Does anyone have a clue to why Excel won't open this document?

Improve this question
2
  • Could it be because of ø ? Commented May 26, 2014 at 13:51
  • @Sinatr: No, i've been through that. It prints the special chars when the content is short enough. Commented May 27, 2014 at 17:42

2 Answers 2

Reset to default
1

ExpandedRowCount is your issue. Apparently it doesn't match the actual number of rows.

I removed it, and it opened just fine.

A better option would be to output the actual number of rows.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I cannot believe I overlooked this! I removed ExpandedRowCount and ExpandedColumnCount and the document opens as it should. Never underestimate a extra pair of eyes. Thanks man!
1

I don't think it is a limitation as we know Excel is not limited at such few number of rows. It is almost certainly a problem with the data/escaping of special characters... The way you are adding lines with AppendLine is not safe... Aren't there '<' or other invalid XML characters in your data?

I would advise you to use a proper XLS component so that all this problems would be taken care of... There are LOTs of free, open-source and commercial offers out there.

Take a look at this: http://nugetmusthaves.com/Tag/Excel

Improve this answer

2 Comments

Loudenvier, The reason I didn't use a library was that the resulted XML is quite finely formated and it will be almost impossible to do it with a library, I think.
@Ambran are you generating Excel files to be read by human beings? You're probably 100% right that you won't have fine control of how the library generates the Excel file, but if you want to stick with your handmade (and possibly limited) approach, consider using LINQ to XML, or at least some XmlDocument, or else any misbehaving "text" will break your excel file... But it is nice to see it was a simpler problem after all!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.