I have a simple Excel spreadsheet that I'm opening using the Infragistics.Documents.Excel.Workbook class. I'm then inserting a single row, and then resaving to a new file.
The problem is that the saved worksheet has the maximum 16384 columns in the spreadsheet. The original only had 26 columns.
I'm having this issue in a lot of different workbooks that I use the row insert logic in. I narrowed it down to this simple example which is attached in the "Excel Insert Bug13.xlsx" file. If I simply create a new worksheet from scratch, then I can properly save it programatically without the extra columns. My issue is that I've already created 40 different worksheets where I am inserting data and the problem occurs in most of them. As you can see from the attached example, I've tried to narrow down the problem as much as I could and I still can't figure out what is causing this behavior.
Here is my code for duplicating this:
Workbook m_Workbook = Workbook.Load(@"C:\Workspace\Excel Insert Bug13.xlsx");
m_Workbook.Worksheets[0].Rows.Insert(0);
m_Workbook.Save(@"C:\Workspace\Output.xlsx");
I am using version 15.1.20151.2112.
Thanks,
Paul
Hi Paul,
This seems to be a development issue and I have logged it in our internal system. I have also created a support case for you CAS-164186-H1G8V6 where you will be informed about the progress of the issue. Please let me know if you have further assistance on the matter.
I was able to find a workaround for this. I still need development to look into this issue, but I figured I would post my workaround for anyone else having this problem.
This appears to happen when selecting an entire row and then copying the formatting to another row(s). The solution is to clear the formatting in the columns that aren't being used. To do this, select all columns from the last used column to the highest possible column (in Excel 2007+, this is XFD). Selecting all columns can be accomplished by hitting F5 and then typing in the Column reference (e.g. AA:XFD). Then in the Home menu, select Clear -> Clear All. I originally thought that deleting these column would work; however, it does not. Clear All must be used.
Hi,
Thank you for your feedback on this. Actually the way the Excel library works is that if you try to reference the 20th column in a worksheet with 10 columns, you will not get an error, but this column will be created:
worksheet.columns[19] will create the 20th element in the collection of columns. this may be a reason for the result you see. Pasting a format to the whole row may apply it to all possible columns as well. Later you open and modify the worksheet in the library and the library goes through every single column so that to apply the format. As mentioned earlier this will create a worksheet with the max number of columns, as it happens.
Considering this I think the development issue will be marked as "not a bug" when dev team have a look at it.