The method of Excel reporting, one that is too simple, one that can only be used on window platforms (because of the use of Jdbc-odbc Bridge), and cannot use various formulas or methods within Excel, so today we introduce an Apache component called POI, It can handle Word or Excel files on UNIX or window platforms without having to rely on window COM and can be formatted, printed, and so on; today I will introduce the relevant information read, add, modify and delete the function, if you netizens study other functions, Please email me (ljj@mlc.edu.tw) and share it with you.
One, need to use the file: Jakarta-poi-1.8.0-dev-20020917.jar
Almost every day has the latest version of 1.8.0 (but the unofficial version), the official version is 1.5.0
http://jakarta.apache.org/builds/jakarta-poi/nightly/
Copy the file to where Classpath is pointing
Two, interested friends can refer to
http://jakarta.apache.org/poi/
Third, the first to create a named Book1.xls Excel file, the contents are as follows
----------------------------------
Total Item Price Quantity
CPU 7000 5 35000
HDD 2500 2 5000
Memory 1600 3 4800
----------------------------------
Where the total field is set the formula, unit price * Quantity
Iv. Examples of data reading
<%@ page contenttype= "text/html;charset=ms950" import= "java.util.*,java.io.*"%>
<%@ page import= "org.apache.poi.poifs.filesystem.*,org.apache.poi.hssf.usermodel.*"%>
<meta http-equiv= "Content-type" content= "text/html; charset=ms950 ">
<title> read Excel Files </title>
<body>
<table border= "1" width= "100%" >
<%
FileInputStream finput = new FileInputStream (Application.getrealpath ("/") + "Book1.xls");
Set FileInputStream to read Excel files
Poifsfilesystem fs = new Poifsfilesystem (finput);
Hssfworkbook wb = new Hssfworkbook (FS);
Hssfsheet sheet = wb.getsheetat (0);
Read the first worksheet and declare it as sheet
Finput.close ();
Hssfrow Row=null;
Declaring a column
Hssfcell Cell=null;
Declaring a storage cell
Short i=0;
Short y=0;
Read all the cell data in a nested loop
For (I=0;i<=sheet.getlastrownum (); i++)
{
Out.println ("<tr>");
Row=sheet.getrow (i);
For (Y=0;y<row.getlastcellnum (); y++)
{
Cell=row.getcell (y);
Out.print ("<td>");
To determine the format of a storage cell
Switch (Cell.getcelltype ())
{
Case Hssfcell.cell_type_numeric:
Out.print (Cell.getnumericcellvalue ());
Getnumericcellvalue () will return the double value, if you do not want to appear decimal, please transform to int
Break
Case hssfcell.cell_type_string:
Out.print (Cell.getstringcellvalue ());
Break
Case Hssfcell.cell_type_formula:
Out.print (Cell.getnumericcellvalue ());
read out the value of the formula cell after the calculation
To read out the formula, use Cell.getcellformula ()
Break
Default
Out.print ("unknown format");
Break
}
Out.println ("</td>");
}
Out.println ("</tr>");
}
%>
</table>
</body>
V. New examples of data
<%@ page contenttype= "text/html;charset=ms950" import= "java.util.*,java.io.*"%>
<%@ page import= "org.apache.poi.poifs.filesystem.*,org.apache.poi.hssf.usermodel.*"%>
<meta http-equiv= "Content-type" content= "text/html; charset=ms950 ">
<title> Insert data to excel file </title>
<body>
<%
FileInputStream finput = new FileInputStream (Application.getrealpath ("/") + "Book1.xls");
Set FileInputStream to read Excel files
Poifsfilesystem fs = new Poifsfilesystem (finput);
Hssfworkbook wb = new Hssfworkbook (FS);
Hssfsheet sheet = wb.getsheetat (0);
Read the first worksheet and declare it as sheet
Finput.close ();
Hssfrow Row=null;
Declaring a column
Hssfcell Cell=null;
Declaring a storage cell
Short i=4;
Row=sheet.createrow (i);
Create a new column, note that the Fifth column (column and cell are starting from 0)
Cell=row.createcell ((short) 0);
Cell.setencoding (HSSFCELL.ENCODING_UTF_16);
Set the string for this cell to store the two bits
Cell.setcellvalue ("display card");
Cell=row.createcell ((short) 1);
Cell.setcellvalue (1700);
Cell=row.createcell ((short) 2);
Cell.setcellvalue (8);
Cell=row.createcell ((short) 3);
Set this cell as a formula cell and enter the formula
Cell.setcellformula ("B" + (i+1) + "*c" + (i+1));
Try
{
FileOutputStream fout=new FileOutputStream (Application.getrealpath ("/") + "Book1.xls");
Wb.write (Fout);
Stores
Fout.close ();
OUT.PRINTLN ("Storage success <a href= ' Book1.xls ' >book1.xls</a>");
}
catch (IOException E)
{
OUT.PRINTLN ("Generate Error, error message:" +e.tostring ());
}
%>
</body>
Vi. Data deletion and modification examples
<%@ page contenttype= "text/html;charset=ms950" import= "java.util.*,java.io.*"%>
<%@ page import= "org.apache.poi.poifs.filesystem.*,org.apache.poi.hssf.usermodel.*"%>
<meta http-equiv= "Content-type" content= "text/html; charset=ms950 ">
<title> Delete, modify data to Excel file </title>
<body>
<%
FileInputStream finput = new FileInputStream (Application.getrealpath ("/") + "Book1.xls");
Set FileInputStream to read Excel files
Poifsfilesystem fs = new Poifsfilesystem (finput);
Hssfworkbook wb = new Hssfworkbook (FS);
Hssfsheet sheet = wb.getsheetat (0);
Read the first worksheet and declare it as sheet
Finput.close ();
Hssfrow Row=null;
Declaring a column
Hssfcell Cell=null;
Declaring a storage cell
Row=sheet.getrow ((short) 4);
Remove the Fifth Column
if (row!=null)
Sheet.removerow (row);
First, detect the fifth column, and if you do, delete column fifth.
Row=sheet.getrow ((short) 3);
Remove the fourth column
Cell=row.getcell ((short) 2);
Remove the third storage cell
Cell.setcellvalue (7);
Set the cell value to 7
Cell=row.getcell ((short) 3);
Cell.setcellformula (Cell.getcellformula ());
The last two behaviors take out the formula cell and recalculate (because the value of the calculated formula has just been updated)
If you do not, the calculated value of the formula will not be updated
Try
{
FileOutputStream fout=new FileOutputStream (Application.getrealpath ("/") + "Book1.xls");
Wb.write (Fout);
Stores
Fout.close ();
OUT.PRINTLN ("Storage success <a href= ' Book1.xls ' >book1.xls</a>");
}
catch (IOException E)
{
OUT.PRINTLN ("Generate Error, error message:" +e.tostring ());
}
%>
</body>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
