Poi read Excel

Let's look at the code first:
Generally, traversal uses two methods: 1. Get the total number of rows and the number of columns in each row, and then loop. 2: Use Iteration
First, let's look at the first one:

Package com. Golden. test;

Import java. Io. file;
Import java. Io. fileinputstream;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfworkbook;

/**
*
* @ Author Cui suqiang
*
*/
Public class poireadxls2 {
Public static void main (string [] ARGs ){
File F = new file ("C: \ a.xls ");
Try {
Fileinputstream is = new fileinputstream (f );
Hssfworkbook WBS = new hssfworkbook (is );
Hssfsheet childsheet = WBS. getsheetat (0 );
// System. Out. println (childsheet. getphysicalnumberofrows ());
System. Out. println ("number of rows" + childsheet. getlastrownum ());
For (Int J = 0; j <childsheet. getlastrownum (); j ++ ){
Hssfrow ROW = childsheet. getrow (j );
// System. Out. println (row. getphysicalnumberofcells ());
// System. Out. println ("Number of columns" + row. getlastcellnum ());
If (null! = Row ){
For (int K = 0; k <row. getlastcellnum (); k ++ ){
Hssfcell cell = row. getcell (k );
If (null! = Cell ){
Switch (cell. getcelltype ()){
Case hssfcell. cell_type_numeric: // number
System. Out. Print (cell. getnumericcellvalue ()
+ "");
Break;
Case hssfcell. cell_type_string: // string
System. Out. Print (cell. getstringcellvalue ()
+ "");
Break;
Case hssfcell. cell_type_boolean: // Boolean
System. Out. println (cell. getbooleancellvalue ()
+ "");
Break;
Case hssfcell. cell_type_formula: // Formula
System. Out. Print (cell. getcellformula () + "");
Break;
Case hssfcell. cell_type_blank: // null
System. Out. println ("");
Break;
Case hssfcell. cell_type_error: // fault
System. Out. println ("");
Break;
Default:
System. Out. Print ("unknown type ");
Break;
}
} Else {
System. Out. Print ("-");
}
}
}
System. Out. println ();
}
} Catch (exception e ){
E. printstacktrace ();
}
}
}
Obtain the Excel file and read it. This is very simple. There are two key points: Maybe some of them are used in this way on the Internet.

System. Out. println ("number of rows" + childsheet. getlastrownum ());
System. Out. println (childsheet. getphysicalnumberofrows ());
System. Out. println ("Number of columns" + row. getlastcellnum ());
System. Out. println (row. getphysicalnumberofcells ());

I don't know what the difference is if everyone copies the code for use. There are too many differences I don't know, but one thing I found is that if there are rows or columns in the middle, getphysicalnumberofrows and getphysicalnumberofcells won't be able to read all rows and columns.

Furthermore, you must determine the cell format switch (cell. getcelltype (). Different cell formats use different methods. Add the end type, just in case.

In addition, the numbers are classified into pure numbers and time formats:

Case hssfcell. cell_type_numeric: // numeric type
If (hssfdateutil. iscelldateformatted (cell )){
// If it is of the date type, obtain the date value of the cell.
Value = hssfdateutil. getjavadate (cell. getnumericcellvalue (). tostring ();
} Else {// pure number
Value = string. valueof (cell. getnumericcellvalue ());
}

There is also an iteration method:
Package com. Golden. test;

Import java. Io. file;
Import java. Io. fileinputstream;
Import java. Io. ioexception;
Import java. Io. inputstream;
Import java. util. iterator;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
Import org. Apache. Poi. poifs. filesystem. poifsfilesystem;

/**
*
* @ Author Cui suqiang
*
*/
Public class poireadxls {
@ Suppresswarnings ({"unchecked", "deprecation "})
Public static void main (string [] ARGs ){
File F = new file ("C: \ a.xls ");
Try {
Inputstream input = new fileinputstream (f );
Poifsfilesystem FS = new poifsfilesystem (input );
Hssfworkbook WB = new hssfworkbook (FS );
Hssfsheet sheet = WB. getsheetat (0 );
Iterator rows = sheet. rowiterator ();
While (rows. hasnext ()){
Hssfrow ROW = (hssfrow) rows. Next ();
// System. Out. Print ("row:" + row. getrownum () + "");
Iterator cells = row. celliterator ();
While (cells. hasnext ()){
Hssfcell cell = (hssfcell) cells. Next ();
// System. Out. println ("column:" + cell. getcellnum ());
Switch (cell. getcelltype ()){
Case hssfcell. cell_type_numeric: // number
System. Out. Print (cell. getnumericcellvalue () + "");
Break;
Case hssfcell. cell_type_string: // string
System. Out. Print (cell. getstringcellvalue () + "");
Break;
Case hssfcell. cell_type_boolean: // Boolean
System. Out. println (cell. getbooleancellvalue () + "");
Break;
Case hssfcell. cell_type_formula: // Formula
System. Out. Print (cell. getcellformula () + "");
Break;
Case hssfcell. cell_type_blank: // null
System. Out. println ("");
Break;
Case hssfcell. cell_type_error: // fault
System. Out. println ("");
Break;
Default:
System. Out. Print ("unknown type ");
Break;
}
}
System. Out. println ();
}
} Catch (ioexception ex ){
Ex. printstacktrace ();
}
}

}

In this way, if the data is compact and easy to use, but I found that if it is a blank row or an empty column, it will be separated. Try it on your own.

In addition, we can also see that the Excel file is obtained through file. If you want to reference it to struts2, this is very simple, because the file or file array is defined in the action during struts2 upload.

Disclaimer: The copyright of the iteye article belongs to the author and is protected by law. You shall not reprint this document without the written consent of the author.
Recommendation Link

Import jxl .*;
Import jxl. format. underlinestyle;
Import jxl. Write .*;
Import jxl. Write. number;
Import jxl. Write. boolean;
Import java. Io .*;
Class Excel {
Public Excel (){
}
/**
* Reading Excel
*
* @ Param filepath
*/
Public static void readexcel (string filepath ){
Try {
Inputstream is = new fileinputstream (filepath );
Workbook RWB = Workbook. getworkbook (is );
// Sheet ST = RWB. getsheet (& quot; 0 & quot;) There are two ways to obtain the sheet table: 1 is the name, 2 is the subscript, starting from 0
Sheet ST = RWB. getsheet (0 );
Cell c00 = ST. getcell (1, 0 );
// A general method for obtaining cell values. A string is returned.
String strc00 = c00.getcontents ();
// Obtain the specific cell type value
If (c00.gettype () = celltype. Label ){
Labelcell labelc00 = (labelcell) c00;
Strc00 = labelc00.getstring ();
}
// Output
System. Out. println (strc00 );
// Close
RWB. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
}
/**
* Excel output
*
* @ Param OS
*/
Public static void writeexcel (outputstream OS ){
Try {
/**
* You can only create a workbook using the factory method provided by the API, instead of using the writableworkbook constructor,
* Because the constructor of the writableworkbook class is of the protected type
* Method (1) read writableworkbook WWB from the target file directly =
* Workbook. createworkbook (new file (targetfile); Method (2) is shown in the following example:
* Write writableworkbook directly to the output stream.
*
*/
Writableworkbook WWB = Workbook. createworkbook (OS );
// Create an Excel worksheet and specify the name and position
Writablesheet Ws = WWB. createsheet (& quot; test sheet 1 & quot;, 0 );
Writablesheet WS2 = WWB. createsheet (& quot; test sheet 2 & quot;, 1 );
// ************** Add data to the worksheet *****************
// 1. Add a label object
Label Label = new label (0, 0, & quot; this is a label Test & quot ;);
WS. addcell (Label );
Label label2 = new label (0, 0, & quot; this is a label Test2 & quot ;);
Ws2.addcell (label2 );
// Add a formatting object with a font
Writablefont WF = new writablefont (writablefont. Times, 18,
Writablefont. Bold, true );
Writablecellformat WCF = new writablecellformat (WF );
Label labelcf = new label (1, 0, & quot; this is a label Test & quot;, WCF );
WS. addcell (labelcf );
// Add a formatting object with a font color
Writablefont WFC = new writablefont (writablefont. Arial, 10,
Writablefont. no_bold, false, underlinestyle. no_underline,
Jxl. format. colour. Red );
Writablecellformat wcffc = new writablecellformat (WFC );
Label labelcf = new label (1, 0, & quot; this is a label cell & quot;, wcffc );
WS. addcell (labelcf );
// 2. Add a number object
Number labeln = new number (0, 1, 3.1415926 );
WS. addcell (labeln );
// Add a number object with formatting
Numberformat NF = new numberformat (& quot ;#. ##& quot ;);
Writablecellformat wcfn = new writablecellformat (NF );
Number labelnf = new jxl. Write. Number (1, 1, 3.1415926, wcfn );
WS. addcell (labelnf );
// 3. Add a Boolean object
Boolean labelb = new jxl. Write. boolean (0, 2, false );
WS. addcell (labelb );
// 4. Add a datetime object
Jxl. Write. datetime labeldt = new jxl. Write. datetime (0, 3,
New java. util. Date ());
WS. addcell (labeldt );
// Add a dateformat object with formatting
Dateformat df = new dateformat (& quot; dd mm yyyy hh: mm: SS & quot ;);
Writablecellformat wcfdf = new writablecellformat (DF );
Datetime labeldtf = new datetime (1, 3, new java. util. Date (), wcfdf );
WS. addcell (labeldtf );
// Add an image object. jxl only supports images in PNG format.
// File image = new file (& quot; F: \ 2.png& quot ;);
// Writableimage wimage = new writableimage (0, 1, 2, 2, image );
// Ws. addimage (wimage );
// Write a worksheet
WWB. Write ();
WWB. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
}
/**
* After the object is copied, modify it. file1 is the object to be copied, and file2 is the object created after the modification.
* The original cell Formatting cannot be removed. We can still add the new cell modifier so that the cell content can be displayed in different forms.
*
* @ Param file1
* @ Param file2
*/
Public static void modifyexcel (File file1, file file2 ){
Try {
Workbook RWB = Workbook. getworkbook (file1 );
Writableworkbook WWB = Workbook. createworkbook (file2, RWB); // copy
Writablesheet Ws = WWB. getsheet (0 );
Writablecell WC = ws. getwritablecell (0, 0 );
// Determine the cell type and convert it accordingly
If (WC. GetType () = celltype. Label ){
Label Label = (Label) WC;
Label. setstring (& quot; the value has been modified & quot ;);
}
WWB. Write ();
WWB. Close ();
RWB. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
}
// Test
Public static void main (string [] ARGs ){
Try {
// Read Excel
Excel. readexcel (& quot; F:/testread.xls & quot ;);
// Output Excel
File filewrite = new file (& quot; F:/testwrite.xls & quot ;);
Filewrite. createnewfile ();
Outputstream OS = new fileoutputstream (filewrite );
Excel. writeexcel (OS );
// Modify the Excel file
// Excel. modifyexcel (new file (& quot;), new file (& quot ;));
} Catch (exception e ){
E. printstacktrace ();
}
}
}
/*
* 2. perform related tests in JSP and create a writeexcel. jsp & lt; % response. Reset (); // clear the buffer
* Response. setcontenttype (& quot; application/vnd. MS-Excel & quot;); file filewrite = new
* File (& quot; F:/testwrite.xls & quot;); filewrite. createnewfile (); New
* Fileoutputstream (filewrite); excelhandle. writeexcel (New
* Fileoutputstream (filewrite); % & gt;
* Browse writeexcel. jsp in IE to dynamically generate the Excel document, where response. setcontenttype
* (& Quot; application/vnd. MS-Excel & quot;); the statement is required to ensure no garbled characters. Enter & lt; % @ page contenttype in JSP.

* = & Quot; application/vnd. MS-Excel; charset = GBK & quot; % & gt; No.

Original http://cuisuqiang.iteye.com/blog/759437