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:
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. 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) 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] >
