Java Read xlsx

Source: Internet
Author: User

(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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.