Why does my Excel gets corrupted every time I insert more than 21 users by typing XML or through C#?

Question

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?!

Answer 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).