Read Excel and convert it to XML

1. Preface

During project development, you need to read the Excel document, convert the content in the Excel document into an XML document, and save it in a local file.

For example, to read an Excel file in the following format:

PassCodeThe following code segment in the instance can run empty rows in Excel:

 
If(Cell =Null){
Item. settext ("");
E. addcontent (item );
Cellnum ++;//If an empty Column exists, increase cellnum by 1. This step is important.
Continue;
}
 

2. sample code.

 Package Edu. SJTU. erplab. JDOM;

 Import Java. Io. file;
 Import Java. Io. fileinputstream;
 Import Java. Io. filenotfoundexception;
 Import Java. Io. fileoutputstream;
 Import Java. Io. ioexception;
 Import Java. Io. inputstream;
 Import Java. Text. simpledateformat;
 Import Java. util. date;
 Import Org. Apache. Poi. hssf. usermodel. hssfcell;
 Import Org. Apache. Poi. hssf. usermodel. hssfdateutil;
Import Org. Apache. Poi. hssf. usermodel. hssfrow;
 Import Org. Apache. Poi. hssf. usermodel. hssfsheet;
 Import Org. Apache. Poi. hssf. usermodel. hssfworkbook;
 Import Org. JDOM. Document;
 Import Org. JDOM. element;
 Import Org. JDOM. Output. format;
 Import Org. JDOM. Output. xmloutputter;

 /**  
* Excel worksheet operations
 */ 
 Public   Class Excelreaderxmlwriter2 {

 Public   Static   Void Main (string [] ARGs ){
 Try {
Inputstream stream = New Fileinputstream ("D: \ userinfo2.xls ");
File F = New File ("D: \ temp5.xml "); // Create a new file object and save the parsed XML to the modified file.  
 Writerxml (stream, F ); //  Write data into text in XML format  
 } Catch (Filenotfoundexception e ){
System. Out. println ("the specified path file is not found! ");
E. printstacktrace ();
} Catch (Ioexception e ){
 //  Todo auto-generated Catch Block  
 E. printstacktrace ();
}
}

 Private   Static   Void Writerxml (inputstream stream, file F)
 Throws Ioexception {
Fileoutputstream fo = New Fileoutputstream (f ); //  Get input stream  
 Document Doc = readexcell (Stream ); //  Read Excel Functions 
 Format = format. getcompactformat (). setencoding ("gb2312 ")
. Setindent ("");
Xmloutputter xmlout = New Xmloutputter (format ); //  Line feed after the element, each layer of elements is reduced to four cells  
 Xmlout. Output (Doc, FO );
Fo. Close ();
}

 Private   Static Document readexcell (inputstream stream ){
Element root = New Element ("list ");
Document Doc = New Document (Root );
 Try {
Hssfworkbook WB = New Hssfworkbook (Stream );
 Int Wblength = WB. getnumberofsheets ();
 For ( Int I = 0; I <wblength; I ++ ){
Hssfsheet shee = WB. getsheetat (I );
 Int Length = Shee. getlastrownum ();
System. Out. println ("number of rows:" + length );
 For ( Int J = 1; j <= length; j ++ ){
Hssfrow ROW = Shee. getrow (j );
 If (ROW = Null ){
 Continue ;
}
 Int Cellnum = row. getphysicalnumberofcells (); //  Obtains the position of the last cell in a row.  
 System. Out. println ("Number of columns cellnum:" + cellnum );
 //  Int cellnum = 16;  
 Element E = Null ;
E = New Element ("user ");
 //  Element [] Es = new element [16];  
                      For ( Int K = 0; k <cellnum; k ++ ){
Hssfcell cell = row. getcell ((Short ) K );
String temp = get (k );
System. Out. Print (K + "" + temp + ":");
Element item = New Element (temp );
 If (Cell = Null ){
Item. settext ("");
E. addcontent (item );
Cellnum ++; //  If an empty Column exists, increase cellnum by 1. This step is important.  
                             Continue ;
}

 Else {
String cellvalue = "";
 Switch (Cell. getcelltype ()){
 //  If the current cell type is Numeric  
                              Case Hssfcell. cell_type_numeric:
 Case Hssfcell. cell_type_formula :{
 //  Judge whether the current cell is date 
                                  If (Hssfdateutil. iscelldateformatted (cell )){
 //  If the data type is date, convert it to data format

  //  Method 1: the data format is time, minute, and second: 0:00:00
  //  Cellvalue =  
 Cell. getdatecellvalue (). tolocalestring ();

 //  Method 2: This data format does not contain time, minute, and second: 2011-10-12  
 Date = cell. getdatecellvalue ();
Simpledateformat SDF = New Simpledateformat (
"Yyyy-mm-dd ");
Cellvalue = SDF. Format (date );
Item. settext (cellvalue );

}
 //  If it is a pure number  
                                  Else {
 // Obtains the value of the current cell.  
 Cellvalue = string. valueof (( Int ) Cell. getnumericcellvalue ());
Item. settext (cellvalue );
}
 Break ;
}
 //  If the current cell type is strin  
                              Case Hssfcell. cell_type_string:
 // Obtains the current cell string.  
 Cellvalue = cell. getrichstringcellvalue ()
. Getstring ();
Item. settext (cellvalue );
 Break ;
 //  Default cell value  
                              Default :
Cellvalue = "";
Item. settext (cellvalue );
}
E. addcontent (item );
System. Out. println (cellvalue );
}
}
Root. addcontent (E );

}

}
} Catch (Exception e ){

}
 Try {
Stream. Close ();
} Catch (Ioexception e ){

}
 Return Doc;
}

 Private   Static String get ( Int K ){
String test = "";
 Switch (K ){
 Case 0:
Test = "username ";
 Break ;
 Case 1:
Test = "password ";
Break ;
 Case 2:
Test = "sex ";
 Break ;
 Case 3:
Test = "Age ";
 Break ;
 Case 4:
Test = "Birthday ";
 Break ;
 Default :
}
Return Test;

}

}