package eu.sorescu
import groovy.transform.Memoized
import org.xml.sax.SAXException;
import javax.xml.parsers.ParserConfigurationException;
import java.util.zip.ZipFile;
class XlsxReader {
ZipFile zf;
public XlsxReader(File file) { this.zf = new ZipFile(file); }
public Node getXml(String path) throws IOException, ParserConfigurationException, SAXException {
new XmlParser(false, false).parse(zf.getInputStream(zf.getEntry(path)))
}
@Memoized
public String[] getSharedStrings() {
return this.getXml("xl/sharedStrings.xml").si*.t*.text()
}
public ArrayList<String> getSheetNames() throws IOException {
return this.getXml("xl/workbook.xml").sheets.sheet*.'@name'
}
@Memoized
public static int[] l2c(String l) {
String letters = l.replaceAll(/\d+/, '')
int row = (l.replaceAll(/[^\d]+/, '') as int) - 1
if (letters.length() == 1) return [row, letters.codePointAt(0) - 65];
if (letters.length() == 2) return [row, (letters.codePointAt(1) - 64) * 26 + (letters.codePointAt(0) - 65)]
throw new RuntimeException("Not supported: " + letters)
}
public List<List<String>> getSheet(String sheetName) {
def rid = this.getXml("xl/workbook.xml").sheets.sheet.find { it.'@name' == sheetName }.'@r:id'
String sheetPath = this.getXml("xl/_rels/workbook.xml.rels").Relationship.find { it.'@Id' == rid }.'@Target'
List<List<String>> result = new ArrayList<>();
def res = this.getXml('xl/' + sheetPath)
res.sheetData.row.each { row ->
row.c.each { c ->
if (c.v) {
int[] coordinates = l2c(c.'@r' as String)
String value = c.v.text()
if (c.'@t' == 's') value = sharedStrings[value as int]
try {
if (!result[coordinates[0]]) result[coordinates[0]] = []
result[coordinates[0]][coordinates[1]] = String.valueOf(value).intern()
} catch (Throwable ignored) {
println sheetName + " " + sheetPath + " " + coord + " " + value
}
}
}
}
for (int i = 0; i < result.size(); i++)
result[i] = result[i] ?: []
return result
}
}