0

I wrote a program than can extract usernames and add them to an excel sheet through xml; however, every time there's more than 21 users, the excel becomes corrupted. If I edit the .xls file through notepad, I can fix it or corrupt it by simply removing or adding more than 22 users.

The Excel files that always get corrupted if I insert more than 21 users through xml.

This is the code that adds users, but it doesn't matter since even doing it manually can replicate the corruption issue.

 public static void XlFormat(string[] nameSplit, DataTable dt, string[] lines, String path)
    {
        lines = new string[dt.Rows.Count];
        for (int i = 0; i < dt.Rows.Count; i++) //When I change the condition to i < 22 or lower, it will create a perfect file. More than 22 and it gets corrupted

        {
            lines[i] = "<Row><Cell ss:StyleID=\"s59\"><Data ss:Type=\"String\">" + dt.Rows[i][1] + "</Data></Cell>" +
                       "<Cell ss:StyleID=\"s59\"><Data ss:Type=\"String\">" + dt.Rows[i][2] + "</Data></Cell>" +
                       "<Cell ss:StyleID=\"s59\"><Data ss:Type=\"String\">" + dt.Rows[i][3] + "</Data></Cell>" +
                       "<Cell ss:StyleID=\"s59\"><Data ss:Type=\"String\">" + dt.Rows[i][4] + "</Data></Cell>" +
                       "<Cell ss:StyleID=\"s64\"/><Cell ss:StyleID=\"s65\"/></Row>";
        }

        /* Insert the data into designated points in excel_format.txt using regular */
        /* expressions, including the cells for each user. It should be all in one  */
        /* final string.                                                            */
        string linesCombined = "";
        for (int i = 0; i < lines.Count(); i++)
        {
            linesCombined += lines[i];
        }
        string xmlLines = Regex.Replace(excelFormat, "--- INSERT USERS HERE ---", linesCombined);
        xmlLines = Regex.Replace(xmlLines, "--- INSERT LEADER HERE ---", nameSplit[2]);
        xmlLines = Regex.Replace(xmlLines, "--- INSERT DATE HERE ---", DateTime.Now.Date.ToString());
        xmlLines = Regex.Replace(xmlLines, "--- INSERT EMAIL HERE ---", nameSplit[1]);
        xmlLines = Regex.Replace(xmlLines, "--- INSERT GROUP HERE ---", nameSplit[0]);

        /* Write the final string to an XLS file. This file type will open in Excel as a */
        /* spreadsheet even though it was written as an XML file.                      */
        System.IO.File.Delete(@"" + path + nameSplit[0] + ".xls");
        System.IO.File.WriteAllText(@"" + path + nameSplit[0] + ".xls", xmlLines);
    }

When did I ever limit my XLS to 22 users?!

Pokarface
  • 41
  • 1
  • 5
  • Can we see the value of excelFormat? – Dale Myers Jun 12 '15 at 21:41
  • Are you sure it isn't the actual 22nd item itself? That is, have you tried different pieces of data through this code and directly into the sheet as your 22nd item? – David W Jun 12 '15 at 21:43
  • Hi! Yes, it's not the 22nd. Originally I thought it was a character causing it or more data being extracted for a particular user, but the reality is that my code creates multiple XL sheets. All the ones that have 22 or less users work perfectly fine, all the ones that have 22 or more brake! Fixing it is as simple as removing the additional users through notepad, but I don't want that =-( – Pokarface Jun 12 '15 at 21:48
  • @Velox Ok, added excel_format to the shared files. This is the same format that creates the ones that are not corrupted when it has 22 or less users. Changing the extension to .xls shows you how it actually looks before it adds the rest of the fields and users. – Pokarface Jun 12 '15 at 21:53

1 Answers1

1

Look at the file you give with the excelFormat item I would guess it is due to the following line in it:

<Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="68" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">

You can see that it has a mention of an ExpandedRowCount which is set to 68. A quick search for <Row in that same file gives 44 results. If you add your 22 lines this brings you up to 66 which is only 2 short. I'm not quite sure where this goes wrong since there is still a difference of 2, but I'd guess that this is your issue. Try changing the ExpandedRowCount attribute to be something higher and test again (with less than 22 items, exactly 22 items, and more than 22 items).

Dale Myers
  • 2,703
  • 3
  • 26
  • 48
  • Good catch.... if `ExpandedRowCount` or `ExpandedColumnCount` are not correct the sheet is invalid. Source: https://msdn.microsoft.com/en-us/Library/aa140066%28v=office.10%29.aspx – Ahmed Masud Jun 13 '15 at 00:51
  • My hero =-D Where did you learn XML? I swear I couldn't recall when exactly I limited it (maybe it auto-limited itself?). If StackOverflow had a paypal option to reward answer givers, I'd give you a tip =-) – Pokarface Jun 13 '15 at 00:57