Repairing a corrupted Microsoft Excel .xlsx file using Calc

The crash report showed an exception type of "EXC_CRASH (SIGABRT)" and "Application Specific Information" was "[50842] stack overflow".

If I tried opening the workbook with the Apache OpenOffice 4.1.2 on the same system, it displayed "Loading..." and then seemed to get stuck there. However, when I returned to the system about 15 minutes later, the spreadsheet I needed to access was open within the OpenOffice Calc application. All of the sheets within the workbook were accessible and I clicked on File, then chose Save and then saved the file with the default file type of "ODF Spreadsheet (.ods)", so that I would have a readable copy of my data that I could at least open with OpenOffice. I then clicked on File again and this time chose Save As and used a file type of "Microsoft Excel 2003 XML (.xml)". I received a warning that "This document may contain formatting or content that cannot be saved in the Microsoft Excel 2003 XML file format. Do you want to save the document in this format anyway?" I chose "Keep Current Format" to save the spreadsheet in the .xml format.

I was then able to open the new .xml file produced by OpenOffice in Microsoft Excel for Mac 2011. However, an HTML Import Errors window appeared with the message "Problems came up in the following areas during load: Table".

The warning also noted "A log file has been created listing the load warnings. This file has been saved to the following location: Macintosh HD:private:var:folders:vv:8657ktws30b3n8vb38v2qp1mnjntt3:T:TemporaryItems:HtmlErr.log." When I issued a ls -lh /private/var/folders/vv/8657ktws30b3n8vb38v2qp1mnjntt3/T/TemporaryItems/HtmlErr.log command in a Terminal window, I saw a 1.8M file had been created. When I examined the HtmlErr.log with a text editor, I saw numerous entries listed with "Bad Value" as the reason for the entry.

So, within OpenOffice, I resaved the .ods file with a file type of "Microsoft Excel 97/2000/XP (.xls)". I received a "This document may contain formatting or content that cannot be saved in the Microsoft Excel 97/2000/XP file format" warning; I chose the "Keep Current Format" option to save in that format anyway. I then saw the message "Warning saving the document MSGRS: The document contains more rows than supported in the selected format. Additional rows were not saved."

I clicked on OK to proceed. I had 1,257 rows in one of the sheets in the workbook. When I opened the .xls file produced by OpenOffice Calc in Microsoft Excel for Mac 2011, I saw all 1,257 rows were still present in the sheet I was most concerned about. And another sheet, which contained 1,295 rows showed all of those rows in the .xls file open in Excel, too, so it didn't appear that I had actually lost any rows that contained data..

However, in two columns in the spreadsheet, I found #N/A. In those same columns Calc was showing #NAME? in the cells for those columns in the .xlsx file I had been able to open with OpenOffice Calc. For the first column Calc showed the formula =IF(ISBLANK(G3);datedif(F3;TODAY();"d");datedif(F3;G3;"d")), with the cell references increasing as I went down the column, e.g. G3, G4, G5, etc. In the second column the formula was =IF(ISBLANK(G3);0;IF(ISBLANK(H3);datedif(G3;TODAY();"d");datedif(G3;H3;"d"))) with the cell references likewise incremented for subsequent rows below row 3. When I opened an old version of the .xlsx file in Excel rather than the one that was causing Excel to crash, I saw commas in places in the formula where I used the DATEDIF function Calc showed semicolons. Calc normally uses semicolons in places where Excel uses commas, so I don't know why the formulae weren't handled correctly during the conversion.

I use the DATEDIF function in the formulae to calculate the number of days between two dates. I calculate the number of days between the submission date for a request and the approval date and then the number of days between the approval date and the completion date. But DATEDIF is not among the Calc: Date & Time functions, which explains why Calc was displaying #NAME? in all the cells where I used that function. Comments to the unable to use datediff function in apache openoffice calc posting on the Super User site include one by ristoi noting "DATEDIFF seems to be OpenOffice Base/SQL function, not Calc function. It can be used in Calc as Basic macro custom function."

I fixed the problem in the spreadsheet in Excel, by pasting in the correct formulas in row 3 where the formulae are first applied and then copied them down through the other rows by selecting the relevant cells in row 3 to highight them, then clicking on Edit and Copy, then selecting all of the cells in subsequent rows where I needed to paste the formulae than clicking on Edit and selecting Paste Special then choosing Forumlas then clicking on OK.

But, since I used the same forumlae in several worksheets in the workbook, rather than manually fix the issue in each one, I thought I would see if I could open the original .xlsx file in LibreOffice, which, like Apache OpenOffice, is a free and open-source (FOSS) office suite. I downloaded LibreOffice Fresh for Mac OS X and installed it. I was able to open the .xlsx file that Microsoft Excel would not open and, since LibreOffice supports the DATEDIF function, when I opened the file in the LibreOffice Calc application, the correct values were displayed in the cells where I used the DATEDIF function. And, since LibreOffice Calc can save files with a file type of "Microsoft Excel 2007-2013 XML (.xlsx)", I simply resaved the file after opening it. I received a "This document may contain formatting or content that cannot be saved in the currently selected file format "Microsoft Excel 2007-2013 XML" message, but I saved it in that format by clicking on "Use Microsoft Excel 2007-2013 XML Format".

When I opened the file in LibreOffice, it opened immediately, unlike with OpenOffice where it took between 10 and 15 minutes to open. After I resaved the file with LibreOffice, I was able to open it with Microsoft Excel for Mac 2011. All of my data was present and the cells containing a forumula using the DATEDIF function displayed the correct results. The only changes I had to make to get back to the original state of the worksheet I had been working on when Excel crashed previously was to stretch a few columns to make them as wide as they had been before and to right-click (tapping with 2 fingers) on a few rows and choose Row Height and then setting the row height to be 0.17" to reduce the height of the row to the default value used for other rows in the worksheet.

 

Justdeals Daily Electronics Deals