Necessity
Excel is a common document format, usually most of the customers are very proficient in using Excel. For us, customers can free us from training. Some systems often need to record considerable amounts of data, which are typically saved in some specific format. And Excel is a fairly specification data saving method, at least it is easy to format, and the customer can also enter the data into the Excel file in a particular format. So, in summary, we need to know how to read the Excel file with pure Java.
2. Purpose
We hope that users will only entertarate the purpose of Excel, and only one is to import these data into the database. Databases are often more complicated, and there are different different database products, which undoubtedly increases the difficulties of our import. From the Excel file, the data entered by the user is often not directly used, and the determination of logic processing or error, or the default correction. If you can use Java to directly read the Excel file, it will undoubtedly allow us to solve these problems more calm.
3. Other methods
There is also a method to read the data from the Excel file, that is to use ODBC. Then use the JDBC-ODBC bridge to read the data from the Excel file to Java, but this method must be equipped with ODBC. It is a very troublesome thing for the Excle file with ODBC, and it is difficult to manipulate, so this can only It is a temporary solution.
4. Poi
POI is Apache's Jakata project, and POI represents Poor Obfuscation Implementation, ie poor fuzzy implementation. The goal of POI is to provide a set of Java APIs to make Microsoft OLE 2 Compound Document format Microsoft Office files easy to operate. Some POI APIs are only developed for the most commonly used Microsoft Office files Word and Excel; other APIs are used for Universal OLE 2 Compound Document and property files. Poi can be downloaded to www.apache.org. Compiled JAR mainly there are 4: POI package, POI Browser package, POI HDF package, POI HSSF routine package. When actually run, you need a POI package. POI is an open source project and has got hundreds of volunteers' continuous updates. You can get source code and documentation on http://jakarta.apache.org/builds/jakarta-poi/. With the POI package, we can not only manipulate the Excel document, but also manipulate the Word document, and other OLE2 format documents.
5. HSSF
HSSF represents Horrible Spreadsheet Format (terrible spreadsheet format). The API provides programmers to read and write or operate the Microsoft Excel 97-2002 file. These APIs are provided by the Poi bag. The actual use is that we only need the POI package.
6. Operate the Excel file
HSSF is provided to the user's objects used in org.apache.poi.hssf.userModel packets, the main parts include Excell objects, style, and format, and auxiliary operation. There are several objects:
HSSFWORKBOOK EXCELL document object
HSSFSHEET Excell's form
HSSFrow Excell
HSSFCELL Excell's plaid unit
HSSFFONT Excell font
HSSFNAME name
HSSFDataFormat date format
There are 2 items in POI1.7:
HSSFHEADER Sheet head
HSSFFOOTER Sheet
And this style
HSSFCellStyle Cell style auxiliary operations
HSSFDateutil dates
HSSFPrintSetup print
HSSFERRORCONSTANTS error message table
What we can often use or read an Excel file, look at an example:
POIFSFileSystem fs = new POIFSFileSystem (new FileInputStream ( "workbook.xls")); HSSFWorkbook wb = new HSSFWorkbook (fs); HSSFSheet sheet = wb.getSheetAt (0); HSSFRow row = sheet.getRow (2); HSSFCell cell = row .getcell (short); if (cell == null) {Cell = row.createcell ((Short) 3);} Cell.SetCellType (hssfcell.cell_type_string); cell.setcellValue ("a test"); / / WRITE The OUTPUT TO A File FileOutputStream Fileout = New FileoutputStream ("Workbook.xls"); wb.write (fileout); fileout.close ();
This is the basic read and write method, where we noticed several key objects in HSSF. The HSSFWorkbook represents the entire document, this workbook concept is the concept of Excel itself. If you don't know, you will check the Help documentation of Excel. HSSFSHEET represents a specific form, in the Excel, there may be several tables in a Workbook, and the order of these tables starts from 0. "Hssfsheet sheet = wb.getsheetat (0);" This statement is to establish a table object, the Sheet object. After the Sheet object is established, you can read Cell, ie the content of the table.
Reading an Excel file through POI is as simple as it is.
7. An example This example is GUI-based, the function is to read an Excel file and output into an HTML or text file (you need yourself). And can generate an INSERT statement. To use the POI package. Package testxls;
Import java.io. *; import javax.swing. *; import javax.swing.filechooser.filefilter; import org.apache.poi.hssf.usermodel. *; import org.apache.poi.poifs.FileSystem. *;
/ ** *
Title: Take the XLS spreadsheet content p> *
Description: This class is to read information from Microsoft's spreadsheet, * currently supports Excel97, Excel2000. Use apache's POI package. p> *
Copyright: Copyright (c) 2003 p> *
company: p> * jakelong@163.com * @version 1.0 * / public class testXLS {file testfile = null; Private hssfsheet sheet; string field = "; string tbname =";
/ / -------------------------------------------------------------------------------------------- -------- Public TestXLS () {} // ---------------------------------- --------- Public File Filechooer () {// File Selector
Jfilechooser Chooser = new jfilechooser ();
XlsfileFiliter Filter1 = new xlsfilefiliter ();
chooser.addChoosableFileFilter (filter1); chooser.setAcceptAllFileFilterUsed (false); int result = chooser.showOpenDialog (chooser); testfile = chooser.getSelectedFile (); if (result == chooser.CANCEL_OPTION) System.exit (0); return testfile }
/ / -------------------------------------------------------------------------------------------- --- Public HSSFSHEET GETSHEET (file f) {// Get a Sheet object.
HSSFWorkbook wb = null; POIFSFileSystem xlsf = null; try {xlsf = new POIFSFileSystem (new FileInputStream (f)); wb = new HSSFWorkbook (xlsf);} catch (IOException ex) {System.err.println ( "Error: File Stream Enter incorrectly. "); Ex.printstacktrace ();} hssfsheet sheet = wb.getsheetat (0); return sheet;}
// ----------------------------------- public string [] [] OutResult () {int LENGTH [ ] = outresultlength (); String Result [] [] = new string [length [0]] [length [1]];
INT lastrow = sheet.getlastrownum () 1; for (int i = 0; i / / -------------------------------------------------------------------------------------------- -------------------------------- Public int [] OutResultlength () {int lastrow = sheet.getlastrownum () 1 ; // It is calculated from zero. HSSFROW ROW = Sheet.Getrow (0); int lastcell = row.getlastCellnum (); int A [] = {lastrow, lastcell}; return a;} // -------------- -------------------------------------------------- ------ Public Void Printrs (String [] rs) {for (int i = 0; i / / -------------------------------------------------------------------------------------------- ----------- Public void outprutxlscontent (String [] XLS) {string [] [] content = XLS; Jfilechooser Chooser = new jfilechooser (); int result = chooser.showSaveDialog (chooser); File testfile1 = chooser.getSelectedFile (); if (result == chooser.CANCEL_OPTION) {System.exit (0);} try {DataOutputStream out = new DataOutputStream (new FileOutputStream (testfile1) ); String filename = testfile.getname (); string sql1 = filename "/ n