XLS Accomplishments error

[cjallen-epscor]

@iserna and I have gotten a bug that has reemerged. and I have been banging my head on this issue all day yesterday.

Hope @khuffman @aturling @tschet has some insight.

Here at NM our Year 1 and Year 2 accomplishments tables download fine.
Upon going to year 3 and Year 4 we get the following error "Excel could not open EPSCoR Reporting Core Tables .. because some content is unreadable..."

We had this issue happen before to Table B last summer (June 19, 2015)

To further investigate this issue last summer I opened the "unreadable" excel file into Google Sheets from which I was able to see this quarkiness:

I simply truncated that cell (up to '...NSF.') and it worked (for awhile).

Fast forward to now (Feb 2016) we are experiencing the same errors but, as mentioned in my opening, it only occurs for Years 3 and 4.

Google Sheets shows the following for today [note (5) is strange again]:

I have not upgraded much in terms of modules/software (PHP, phpExcel Library, etc).

Our Database server has the following:

• Server: Localhost via UNIX socket
• Server type: MariaDB
• Server version: 5.5.41-MariaDB - MariaDB Server
• Server charset: UTF-8 Unicode (utf8)

---

Although - I did find a solution I think; and that was to update our er.module file to output in the new(er) XLSX format using the newer 'Excel2007' writer.

I created a branch for that here:
cjallen-epscor@985a330

So, my question to you all: Are any of you getting the strange "Excel could not open EPSCoR Reporting Core Tables .. because some content is unreadable..." error on your accomplishments tables?

[khuffman]

I just downloaded Year1,2,3,4 and 5 (so 5 separate xls files) of accomplishments tables from our site. They all open just fine and I do not get any errors. I only have linux machines, so I use LibreOffice Calc to open xls files. Have you tried to open your "corrupted" xls file in LibreOffice? what happens?

So I don't think we have this problem here, I'm pretty sure if we had this problem, Sally would have noticed and told me. I will ask her just to make sure.
PS. I have not updated our er.module to use the new(er) newer 'Excel2007' writer, we still use the 'Excel5'

[cjallen-epscor]

thanks @khuffman

Maybe it's a combination of Mac OSX with Excel.
Here at NM we have both Excel 2011 and 2016 and I get the same error.
I will try LibreOffice and see if I get any errors.

[khuffman]

@cjallen-epscor more I think about it, I think there must be a problem with your xls files for those particular reporting years. Maybe the problem is some where in your participants' name or institution name. Can you look at views of those particular problematic year3 and 4 "participants" and "institutions" and see if you see any weird names/characters? (or if you have access to mysql drupal database can you look at your "user" table and see if you see any weirdness)

[cjallen-epscor]

@khuffman -- thanks for the input. I have checked our users/institutions and did not find anything strange.
I looked in the database as well and did not find anything unusual. I am able to open the files using Libre Office and Google Sheets ok.

We did notice though that we are able to download individual Tables ok (under 'EPSCoR->Admin Views').

My only best guess is the Excel (.xls) files that are being populated on the Accomplishments page are not being properly packaged together thus we are unable to open that file here at NM (using Excel for Mac 2011 with the latest available version :: 14.5.5).

For now, using my commit at cjallen-epscor@985a330 works ok for NM.

[aturling]

I use Excel for Mac OS (2011, 14.6.1) and haven't run into this issue. I just downloaded the MO Track 1 tables to check (under /reporting/excel/download, both this year's and last year's), and the files opened without errors. Our version has comment (5) on Table B truncated to "...defined by NSF."

[cjallen-epscor]

@aturling - I did not know there was a 14.6.1 version of Excel. I will update my version here and report back what I find.

[aturling]

Actually now I see that (5) isn't cut off at "...NSF." The text isn't wrapping so it looks like that, but if I manually make that row larger, I can see the rest of the text (all the way to "...not in this table.")

[aturling]

Are you able to load the blank template in your version of Mac Excel?
/sites/all/modules/er/static/files/Report-template.xls

[cjallen-epscor]

@aturling - Yes, I am able to download the blank template ok with my version of Mac Excel.

[cjallen-epscor]

I am narrowing down the error. The error now occurs on Table D: External Engagements.

This is what I am getting now:

[khuffman]

@cjallen-epscor If you are sure the problem is TableD related: then try this:
For those reporting years that cause you the problem, you need to check/verify every single External Engagements xls file that your users have uploaded to your site. I assume you do allow your users to upload their External Engagements in xls format using the template that er-core provides?
Download and open each xls file your users uploaded and verify that what your user uploaded is in fact a filled in copy of the empty template that er-core provides, and not some regular xls file the user made themself. I recall we had some issue with TableD I think the numbers were all crazy, and it was because one of our users had uploaded their own made xls file.

[cjallen-epscor]

@khuffman - I checked and we only had two External Engagements that had .xls uploads attached. On a dev server I removed those two excel sheets and I still get the same Microsoft Error.

I am able to open all documents in Libre Office and google sheets and there are no weird symbols being populated. However, Google Sheets does create strange border to the right of the notes (Libre does not)

My two discoveries/solutions here in NM:

1. Remove the brackets around [nsf_frameworkforaction_0808.pdf] on note (2) of table D of 'Report-template.xls' -- this generates the tables fine with ER as is. If you notice in my opening statement of this issue I revealed that this happened to us here last summer (Table B had brackets before; me removing them solved the issue temporarily).
   I have no idea why this works. I'm assuming our setup here in NM does not like the brackets.
2. Use the newer 'Excel2007' writer.