Hi, comments below ...
On 17/09/2020 11:48, Andreas Reichel wrote:
I found when creating a sheet as simple as you suggest, POI wasn't able find the validations in the original either. Looking at the unpacked xml for the sheet, the validation is defined in a weird looking block:Stuart, maybe you could provide the simpliest possible test case, e. g. creating your form with only 2 columns and 1 row, add the validation and then:
<ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"
xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main";><x14:dataValidations
count="1"
xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main";><x14:dataValidation
type="list" allowBlank="1" showInputMessage="1"
showErrorMessage="1"><x14:formula1><xm:f>Sheet2!$A$1</xm:f></x14:formula1><xm:sqref>A1</xm:sqref></x14:dataValidation></x14:dataValidations></ext> </extLst>(I added the file to the github test case here: https://github.com/stuzart/poi-google-export-testcase/blob/master/src/test/resources/original-very-simple.xlsx)
1) extract the XML after your created the template 2) extract the XML after you have exporting it from Google spreadsheets 3) extract the XML after importing and re-exporting it from Libreoffice Then compare the XML files and see what is actually different.
So, sticking with the original files I have unpacked the xlsx and taken a look at the xml differences. There are actually a lot of differences, too much to do a simple diff. However manually inspecting, I've noticed a difference in the dataValidation validation block for the sheet1.xml that looks suspicous. In the problematic exported case, it seems to be missing the count="1" attribute, whereas the other cases that work do have this.
i.eoriginal - https://github.com/stuzart/poi-google-export-testcase/blob/master/src/test/resources/original-unpacked/xl/worksheets/sheet1.xml#L17 exported - https://github.com/stuzart/poi-google-export-testcase/blob/master/src/test/resources/exported-unpacked/xl/worksheets/sheet1.xml#L1003 resaved - https://github.com/stuzart/poi-google-export-testcase/blob/master/src/test/resources/resaved-unpacked/xl/worksheets/sheet1.xml#L1007
I'm not familiar with the format enough to know if this is legal or not.
I work on a tool that is based on embedding OWL ontologies (semantic web stuff) into spreadsheets, allowing scientists (mostly biologists) to annotate data in a way that is user friendly and familiar to them, and hides the nasty stuff. We use a trick with data validation to map human readable labels, with their sementic URI identifier, and then be able to export this again. Using data validations allows us to do this without any additional macros or vb scripting. The exported annotations and data can then be fed into a database as a graph, and be queried and reasoned across. You can find out more at https://rightfield.org.uk/Also I do not understand yet, why do you want to read the validation in POI again? In my limited understanding, the Google Spreadsheet will validate the user inputs already and assure, you will read a valid file?
Possible work-arounds in the meantime: 1) when opening the Google Spreadsheet file, read the Validations from the original XLSX file in parallel and merge that information (e.g. take the data from the Google Spreadsheet, but the structure and definitions from the original XLSX). 2) alternatively, run it automated through Libreoffice first (it has a command-line/shell only option)
Yes, option 2 is something I am thinking of as a fallback option. many thanks, Stuart
Best regards Andreas On Thu, 2020-09-17 at 11:28 +0100, Stuart Owen wrote:Hi, I've encountered an odd problem reading validations from an xlsx spreadsheet uploaded to Google Drive, and then exported.To cut a long story short, my particular use-case involves a pipeline of creating an Excel spreadsheet, which is then shared and used via Google Sheets to allow scientists to collaborate and annotate biological data (using data validations), and is then exported and the annotations read via Apache POI. I have found that Apache POI can detect the validations in the original spreadsheet, but not in the exported version. The validations are read via XSSFSheet.getDataValidations(). The validations however, can be read fine when opening in either Excel or LibreOffice, and if resaved can then again be read by Apache POI. To clarify things, I've put together a test case to demonstrate the problem at https://github.com/stuzart/poi-google-export-testcase I'm not sure if this is a problem with Google export, or Apache POI. thanks, Stuart ------------------------------------------------------------------- --To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
