Java reads Excel and operates data

Using the POI Open API in Java to manipulate data in Excel

Official website: http://poi.apache.org/

Wrote a Java Excel demo, the code is as follows;

Package Com.lc_kykz.test;import Java.io.bufferedreader;import Java.io.fileinputstream;import java.io.IOException; Import Java.io.inputstream;import Java.util.date;import Java.util.stringtokenizer;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;/* * @Author: Gavin * @Function: Read Excel and manipulate data * @Date: 2015-6-18 */public CLA SS Excelreader {//create file input stream private BufferedReader reader = null;//file type private String filetype;//file binary input stream private inputstre AM is = null;//current sheetprivate int currsheet;//current position private int currposition;//sheet quantity private int numofsheets;//Hssfwor Kbookhssfworkbook Workbook = null;//Set the cell to separate the private static String Excel_line_delimiter = ",";// The constructor creates a excelreaderpublic Excelreader (String inputfile) throws IOException, Exception {//Determines if the parameter is empty or meaningless if (inputfile = = null | | Inputfile.trim(). Equals ("")) {throw new IOException ("NO input file!!!");} The suffix name of the obtained filename is assigned to Filetypefiletype = inputfile.substring (Inputfile.lastindexof (".") + 1);//Set start behavior 0currPosition = 0;// Set current position to 0currSheet = 0;//Create file input stream is = new FileInputStream (inputfile);//Determine file format if (filetype.equalsignorecase ("xls")) {// If the Excel file is created Hssfworkbook read workbook = new Hssfworkbook (IS);//Set sheet number numofsheets = Workbook.getnumberofsheets ();} else {throw new Exception ("File Type incorrrect!");}} A line of public String ReadLine () throws IOException {//Excel file to read the file via POI if (filetype.equalsignorecase ("xls")) {// The current Sheethssfsheet sheet = Workbook.getsheetat (Currsheet) is obtained based on the Currsheet value, or whether the current line is to the end of the previous sheet if (Currposition > Sheet.getlastrownum ()) {//Current line position clear 0 currposition = 0;//Determine if there is also Sheetwhile (Currsheet < numOfSheets-1) {//Get the next one Sheetcurr Sheet + = 1;sheet = Workbook.getsheetat (Currsheet);//Whether the current number of rows has reached the end of the file if (Currposition > Sheet.getlastrownum ()) {// The current sheet points to the next sheetcurrsheet++;continue;} else {//Gets the current number of rows int row = currposition;currposition++;//read current row data return GetLine (sheet, row);}} return null;} Gets the current number of rows int row = currposition;currposition++;//reads the current row data return GetLine (sheet, row);} return null;} Returns a row of data for sheet private String getLine (hssfsheet sheet, int row) {//a row sheet Hssfrow = rowline (row) based on the number of rows;//Create Build character creation Buffer StringBuffer = new StringBuffer ();//Gets the number of columns of the current row int filledcolumns = Rowline.getlastcellnum (); Hssfcell cell = null;//loops through all columns for (int i = 0; i < filledcolumns; i++) {//get current Cellcell = Rowline.getcell ((short) i); String Cellvalue = null;if (cell! = NULL) {//determines the current cell's Typeswitch (Cell.getcelltype ()) {//cell's type is Numericcase Hssfcell. Cell_type_numeric: {//Determines whether the current cell is Dateif (hssfdateutil.iscelldateformatted (cell)) {//If it is a date type, gets the date value of the cell date Date = Cell.getdatecellvalue ();//convert date to cost format string cellvalue = Cell.getdatecellvalue (). toLocaleString (); else {//if it is a pure number//Gets the value of the current cell, cast to Intinteger num = new Integer ((int) cell.getnumericcellvalue ()); cellvalue = String.val Ueof (num);} BreaK;} The cell's TYPE is stringcase hssfcell.cell_type_string://gets the current cell string cellvalue = Cell.getstringcellvalue (). ReplaceAll (" "", "" "); break;//the default cell value Default:cellvalue =" ";}} else {cellvalue = "";} Insert a separator buffer.append (cellvalue) between each field. Append (excel_line_delimiter);//Remove the last delimiter buffer.substring (0, Buffer.length ()-1);} return buffer.tostring ();} Close resource public void Closeresource () {try {if (is! = null) Is.close ();} catch (IOException e) {e.printstacktrace ()} Finally{try {if (reader! = null) Reader.close ();} catch (IOException e) {e.printstacktrace ()}}} Test casepublic static void Main (string[] args) {try {excelreader er = new Excelreader ("D://gavin/xxx.xls"); String line = Er.readline ();//After getting Excel data, write the code of the data, such as: Insert database and so on} catch (Exception e) {e.printstacktrace ();}}}

Java reads Excel and operates data