Java Read xlsx

(1) Importing Maven

<!--reading Excel files--
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

(2) Code

public static void Main (string[] args) {
Decompression BOOK1.XLSX
ZipFile Xlsxfile;
try {
Xlsxfile = new ZipFile ("/users/chengtao/downloads/Instructor entry time. xlsx"));
Documentbuilderfactory dbf = Documentbuilderfactory.newinstance ();

First read the Sharedstrings.xml this file backup
ZipEntry sharedstringxml = xlsxfile.getentry ("Xl/sharedstrings.xml");
InputStream Sharedstringxmlis = Xlsxfile.getinputstream (sharedstringxml);
Document sharedstring;
sharedstring = Dbf.newdocumentbuilder (). Parse (Sharedstringxmlis);
NodeList str = sharedstring.getelementsbytagname ("T");
String sharedstrings[] = new String[str.getlength ()];
for (int n = 0; n < str.getlength (); n++) {
element element = (Element) str.item (n);
Sharedstrings[n] = element.gettextcontent ();
}
Find the Workbook.xml in the Unzip folder, this file contains several sheet in this worksheet
ZipEntry workbookxml = xlsxfile.getentry ("Xl/workbook.xml");
InputStream Workbookxmlis = Xlsxfile.getinputstream (workbookxml);
Document doc = Dbf.newdocumentbuilder (). Parse (Workbookxmlis);
Get a total of a few sheet
NodeList nl = doc.getelementsbytagname ("sheet");

for (int i = 0; i < nl.getlength (); i++) {
element element = (Element) nl.item (i);//Convert node to element to get the properties of each node
System.out.println (Element.getattribute ("name"));//The value of the Name property of the output sheet node
Next, go to the decompression folder to find the corresponding name value of the XML file, such as in Workbook.xml <sheet name= "Sheet1"
Sheetid= "1" r:id= "RId1"/> Node
Then you can find the Sheet1.xml in the Xl/worksheets folder, which is the contents of the table contained in the XML folder.
ZipEntry sheetxml = xlsxfile.getentry ("xl/worksheets/" + element.getattribute ("name"). toLowerCase () + ". xml");
InputStream Sheetxmlis = Xlsxfile.getinputstream (sheetxml);
Document Sheetdoc = Dbf.newdocumentbuilder (). Parse (Sheetxmlis);
NodeList RowData = Sheetdoc.getelementsbytagname ("row");
hashmap<string, string> nameandentrytimemap = new hashmap<string,string> ();
for (int j = 0; J < Rowdata.getlength (); j + +) {
                System.out.println ("No." + J + "line-------------");
Get each row
The format of the row:
/*
* <row r= "1" spans= "1:3" >r represents the first row, spans indicates a few columns <c r= "A1"
* t= "S" >//r represents the list of this column
*, t= "s" personally think that the contents of this cell can be found in the Sharedstrings.xml file, the corresponding node
* Subscript is the value of the V node, that is, 0, if there is no t attribute, then the value of V is the contents of the cell <v>0</v> </c> <c r= "B1"
* t= "s" > <v>1</v> </c> <c r= "C1" t= "S" > <v>2</v> </c> </row>
*/
Element row = (Element) Rowdata.item (j); Get each row
NodeList columndata = Row.getelementsbytagname ("C");//Get the columns in each row based on the row

String name_key = null; Name
String time_value = null; Time of entry
for (int k = 0; k < columndata.getlength (); k++) {
Element column = (element) Columndata.item (k);
NodeList values = Column.getelementsbytagname ("V");
Element value = (element) values.item (0);
if (Column.getattribute ("t") = null & Column.getattribute ("T"). Equals ("S")) {
If it is a shared string, look for the value of the column in Sharedstring.xml.
                       System.out.println ("n" + k + "column" +sharedstrings[integer.parseint (Value.gettextcontent ())] + "");
if (k ==0) {
Name_key = Sharedstrings[integer.parseint (Value.gettextcontent ())];
}else if (k==2) {
Time_value = Sharedstrings[integer.parseint (Value.gettextcontent ())];
}
} else {
if (value = null) {
System.out.print (value.gettextcontent () + "");
}else {
System.out.println ("J:" + j + "K:" + K + "null");
}
}
}
Nameandentrytimemap.put (Name_key, Time_value);
System.out.println ();
}
System.out.println (NAMEANDENTRYTIMEMAP);
}
} catch (Zipexception e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (Saxexception e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (Parserconfigurationexception e) {
TODO auto-generated Catch block
E.printstacktrace ();
}

}

Java Read xlsx