Re: Perculiar problem reading validations from a XLSX exported from Google Sheets

On 17/09/2020 16:26, Stuart Owen wrote:
> Hi,
>
> comments below ...
>
> On 17/09/2020 11:48, Andreas Reichel wrote:
>> 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:
> 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:
> <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.e
> original - 
> 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.

to add, I've tried validating the xml against its schema, and it appears 
to be legal.

>
>> 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?
> 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/
>>
>> 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]
>