Home > Developer > ASP

Export data to an excel table on the jsp page

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

The Excel report method is too simple. One method can only be used on the window platform (because jdbc-odbc bridge is used) and various formulas or methods in Excel cannot be used. Therefore, today, we will introduce a component named POI from apache (the most popular WEB server platform on Unix). It can process word or Excel files on UNIX or window platforms, instead of relying on window com, you can set storage grids and print formats. Today, I will introduce the reading, adding, modifying, and deleting functions of related data, if you study other functions, please Email me (ljj@mlc.edu.tw), share with you!
I. Need to use the file: jakarta-poi-1.8.0-dev-20020917.jar
The latest version 1.8.0 (but not the official version) is available almost every day. The official version is 1.5.0.
Http://jakarta.apache (the most popular WEB server platform for Unix). org/builds/jakarta-poi/nightly/
Copy the file to the place indicated by classpath
2. If you are interested, refer
Http://jakarta.apache (the most popular WEB server platform for Unix). org/poi/
The third party first sets up an Excel file named book1.xls. The content is as follows:
----------------------------------
Total project unit price
CPU 7000 5 35000
Hard Disk 2500 2 5000
Memory 1600 3 4800
----------------------------------
The total column space is set formula, unit price * quantity
 
Iv. Data Reading examples
<% @ Page contentType = "text/html; charset = MS950" import = "java. util. *, java. io. *" %>
<% @ Page import = "org. apache (the most popular WEB server platform on Unix ). poi. poifs. filesystem. *, org. apache (the most popular WEB server platform on Unix ). poi. hssf. usermodel. * "%>
<Html>
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = MS950">
<Title> read an Excel file </title>
</Head>
<Body>
<Table border = "1" width = "100%">
<%
FileInputStream finput = new FileInputStream (application. getRealPath ("/") + "book1.xls ");
// Set FileINputStream to read the Excel file
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;
// Declare a column
HSSFCell cell = null;
// Declare a bucket
Short I = 0;
Short y = 0;
// Read data of all cells in a nest-like ring
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> ");

// Determine the format of the bucket
Switch (cell. getCellType ())
{
Case HSSFCell. CELL_TYPE_NUMERIC:
Out. print (cell. getNumericCellValue ());
// GetNumericCellValue () returns the double value. If you do not want a decimal point, convert it to int.
Break;
Case HSSFCell. CELL_TYPE_STRING:
Out. print (cell. getStringCellValue ());
Break;
Case HSSFCell. CELL_TYPE_FORMULA:
Out. print (cell. getNumericCellValue ());
// Read the calculated value of the formula bucket.
// To read the formula content, use cell. getCellFormula ()
Break;
Default:
Out. print ("unknown format ");
Break;
}
Out. println ("</td> ");
}
Out. println ("</tr> ");
}
%>
</Table>
</Body>
</Html>
 
5. Add example
<% @ Page contentType = "text/html; charset = MS950" import = "java. util. *, java. io. *" %>
<% @ Page import = "org. apache (the most popular WEB server platform on Unix ). poi. poifs. filesystem. *, org. apache (the most popular WEB server platform on Unix ). poi. hssf. usermodel. * "%>
<Html>
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = MS950">
<Title> insert data to an Excel file </title>
</Head>
<Body>
<%
FileInputStream finput = new FileInputStream (application. getRealPath ("/") + "book1.xls ");
// Set FileINputStream to read the Excel file
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;
// Declare a column
HSSFCell cell = null;
// Declare a bucket
Short I = 4;
Row = sheet. createRow (I );
// Create a new column. Note that it is the fifth column (the column and storage space are calculated from 0)
Cell = row. createCell (short) 0 );
Cell. setEncoding (HSSFCell. ENCODING_UTF_16 );
// Set the string of the bucket to store dual-bit Elements
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 the storage space as the formula storage space and enter the formula
Cell. setCellFormula ("B" + (I + 1) + "* C" + (I + 1 ));
Try
{
FileOutputStream fout = new FileOutputStream (application. getRealPath ("/") + "book1.xls ");
Wb. write (fout );
// Storage
Fout. close ();
Out. println ("Storage successful <a href1_book1.xls> book1.xls </a> ");
}
Catch (IOException e)
{
Out. println ("error generated, error message:" + e. toString ());
}
%>
</Body>
</Html>
 
6. Delete and modify documents
<% @ Page contentType = "text/html; charset = MS950" import = "java. util. *, java. io. *" %>
<% @ Page import = "org. apache (the most popular WEB server platform on Unix ). poi. poifs. filesystem. *, org. apache (the most popular WEB server platform on Unix ). poi. hssf. usermodel. * "%>
<Html>
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = MS950">
<Title> Delete and modify documents to an Excel file </title>
</Head>
<Body>
<%
FileInputStream finput = new FileInputStream (application. getRealPath ("/") + "book1.xls ");
// Set FileINputStream to read the Excel file
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;

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

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

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

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

    Open a Ticket

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

    Learn More