We have to process inbound client Excel files. One client highlights changed data in yellow. I have been asked to find the highlighted yellow cells, and import only the rows that have changes.
I am using Alteryx, but that is largely immaterial; The problem is not how to code this, but how to find the relevant highlighted rows and columns in the Excel internal file structure.
I know that an Excel workbook is a zipped set of XML files. I can copy the .xlsx file to .zip, and expand it, and process the individual XML worksheets, for example I can load ".\xl\worksheets\sheet1.xml". In there, I can see the cell locations, and I can see some XML attributes. If the cell has a style, it seems to get the s="" attribute.
I assume this relates to a style setting. I can open the style sheet, but I cannot relate the value in the s attribute to entries in the style sheet file. For example, I have a value of s="219" for a highlighted cell, but there are not 219 styles in the style file, so the s value is not an index.
Does anyone know how Excel files work 'under the hood', so that I can relate the worksheet cell data to the style data to find highlighted cells?
I am not looking for "have you tried an Excel macro?" or "use Python?" or other suggestions/guesses. Please, with great respect to your time and expertise, only answer if you know about Excel's internal file structure and you know the answer to this particular, admittedly very niche, query.