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: https://github.com/cjallen-epscor/ERCore-3.0/commit/985a330c3a5eb1a6e266c52120186c3ee8422525

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 https://github.com/cjallen-epscor/ERCore-3.0/commit/985a330c3a5eb1a6e266c52120186c3ee8422525 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.

[iserna]

This has been resolved in NM. Closing.

[khuffman]

In RI we are having a similar issue with our Accomplishment xls export of Table A, and the individual download of Table A's xls (via Admin View) see attached screenshot. Notice the text under the table shows up as corrupted, also the following sheets in the same xls file B,C,D,E have misconfigured table headers, etc.But when I download the Table B, C,D,E individually each by going to Admin Views, then each table looks perfectly fine (except TableA) What is strange is that just like NM's example above this issue only affects certain reporting years, the long text in Notes (2) looks perfectly fine, and all the tables in the xls file look great. But for another reporting year the text under (2) is corrupted and any of the sheets following after Table A look misconfigured etc.

As a solution I edited these two files: er/static/files/Report-template.xls er/static/files/Salary-Support-template.xls I split up the long text under Notes (2) under the Table A, into two rows and it solved our problem.

[aturling]

I'm now having the same issue with Table D - External Engagements - again for only one reporting year. I had tested everything with the most recent two reporting years and it was fine, but I just checked 3 years ago and the overall table is corrupted (same error as above with the weird text in the notes section). But the individual table D download is fine (from /reporting/external-engagement); it's only the combined file that has the error.

[tschet]

I’m out of the office today, but I can look at the problem on Monday.

Douglas T

On Jun 22, 2018, at 11:01 AM, Amy Walsh notifications@github.com wrote:

I'm now having the same issue with Table D - External Engagements - again for only one reporting year. I had tested everything with the most recent two reporting years and it was fine, but I just checked 3 years ago and the overall table is corrupted (same error as above with the weird text in the notes section). But the individual table D download is fine (from /reporting/external-engagement); it's only the combined file that has the error.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[aturling]

I experimented with this a little more this afternoon and I don't think it's related to table D. After testing a bunch of things, one thing that seems to consistently work is to change how the departments are written in Table A.

If I comment out this line in /pages/salary-support.inc:

$worksheet->setCellValue('B'.$row, $department);

the templates work for all reporting years. I also get it work by replacing $department with a shortened version:

$department = $user['department']?$user['department']:'?';
$department = substr($department, 0, 10);

So I wonder if some of the really long departments are the issue. That would explain why the error is present some years and not others (as the participants change from year to year).

I couldn't find one single breaking point for all years; one year broke when I allowed more than 62 characters in the department name (but up to 62 worked), and another year broke when I allowed more than 50.

[khuffman]

@aturling I started looking at this issue again: I do not think the department name is causing this problem, at least not on our site. I say this because on my laptop (a clone of our live site but has the template for Table A that has the text under the table) I changed the department names for 3 users to a string that is really long, 254 char. long. and I can not reproduce this problem. But looking at the /pages/salary-support.inc code I did notice something strange, why is this line here? We are not using $output in this function so why is $output there? even if this line is needed it contains an error, this: $user['uid'] is wrong, correct line should look like this: $output .= "<h3>".l($user['lname'] . ', ' . $user['fname'], 'user/'.$uid)."</h3>";

see this commit

Also I noticed that the $p array variable being used here. Has $p been declared some where first? I don't see it.

Anyway I still don't know what is causing this problem. But above are things I found weird.