In the first part, we got hold of the streams of sheets and strings, now to get the values of cells we will iterate through the xml elements like we normally do. There’s only one thing to be aware of, integers are saved in the sheet file but strings are accessed by indexes from the sharedstring file.
Heres how an excel cell looks in xml :
c – is the element name
r – is the attribute with cell id, ex ‘A1’
t – is the attribute for type, if it equals ‘s’ this is a string
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
public static Map<String,String> getCellValues(InputStream input, List<String> sharedStrings) throws XMLStreamException, FactoryConfigurationError{ HashMap<String,String> cellValues = new HashMap<String,String>(); boolean isString = false; String cellID = null; XMLStreamReader xmlStreamReader = XMLInputFactory.newInstance().createXMLStreamReader(input); while (xmlStreamReader.hasNext()) { xmlStreamReader.next(); if (xmlStreamReader.getEventType() == XMLStreamConstants.START_ELEMENT) { if (xmlStreamReader.getLocalName().equals("c") && xmlStreamReader.getAttributeCount() > 0) { for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { if( xmlStreamReader.getAttributeLocalName(i).equals("t") && xmlStreamReader.getAttributeValue(i).equals("s")){ isString = true; } if(xmlStreamReader.getAttributeLocalName(i).equals("r")){ cellID = xmlStreamReader.getAttributeValue(i); } } } } if ((xmlStreamReader.getEventType() == XMLStreamConstants.CHARACTERS) && (xmlStreamReader.getText().trim().length() > 0)) { if(isString){ int sharedStringKey = Integer.valueOf(xmlStreamReader.getText()); cellValues.put(cellID, sharedStrings.get(sharedStringKey)); isString = false; }else { cellValues.put(cellID, xmlStreamReader.getText()); } } } return cellValues; } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public static void main(String[] args) throws IOException, XMLStreamException, FactoryConfigurationError { String filename = "/home/metin/Desktop/test.xlsx"; ZipFile zipFile = new ZipFile(filename); ArrayList<InputStream> list = getSheetsXML(zipFile); List<String> input = getSharedStrings(zipFile); // iterate in a loop for all sheets System.out.println(getCellValues(list.get(0), input).toString()); zipFile.close(); } |