Recently, because of the needs of the project, a Java read an example of reading an Excel file is written, there is a lot of works on the date and the formula. When starting, I really cost a lot of work, huh, huh. Later, after I found it, I finally had a little smaller, and I posted it with everyone. However, there is still a WARING: That is to read Merge data [Warning] Unknown PTG 2D (45) I don't know who has a solution, I will throw a brick first, and I have jade, although I'm coming over.
Package com.zzxy.model; import java.io.fileinputstream; import java.io.fileoutputstream;
Import java.text.decimalformat;
import java.util.Date; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.formula.AreaPtg; import org.apache.poi.hssf.usermodel.HSSFCell; import org .apache.poi.hssf.usermodel.HSSFCellStyle; 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; // Import Org.apache.poi.hssf.usermodel.hssfdataFormat; Import Org.Apache.poi.hssf.Record.calccountRecord;
/ ** * * Author * * Java reads Excel file * * A level of an Excel file: Excel file -> Worksheet -> Row -> cells correspond to POI, for: Workbook-> Sheet-> row-> Cell * * / public class poitest {
Public static string outputfile = "c: //temp/test.xls";
Public static string filetoberead = "c: //temp/test.xls";
Public void createExcel () {Try {// Create a new Excel workbook hssfworkbook workbook = new hssfworkbook (); // Construction a worksheet in the Excel workbook, named the default value // To create a new one "Benefit Indicators" worksheet, its statement is: // hssfsheet sheet = workbook.createsheet; hssfsheet sheet = workbook.createsheet (); // Create a row in the index 0 position (top line HSSFROW ROW = Sheet.Createrow ((Short) 0); // Create a single element (left upper end) hssfcell cell = row.createcell (short) 0); / / Define cell type Cell.setcellType (HSSFCELL.CELL_TYPE_STRING); // Enter some content in cell Cell.SetcellValue ("SWEATER"); // New output file stream FileOutputStream Fout = new fileoutputstream (OutputFile); // Put the corresponding Excel work Book Disk Workbook.write (fout); fout.flush (); // End, turn off file fout.close (); system.out.println ("File Generate ...");} catch (Exception E) { System.out.println ("has run xlcreate ():" e);}}
/ ** * reads Excel, traversing each smashing information, and judge whether it is a mobile phone number, and display the correct mobile phone number * * Note: 1.Sheet, start with Workbook.getnumberofsheets () -1 end * 2.Row, at 0, endfirstrown End * 3.Cell, start with 0 start (getFirstCellnum), endlastcellnum end, ended, endless, unknown, difference, may be long * / Public void readExcel () {// will be represented in the number of 1.3922433397E10 transformation to 13922433397 // DecimalFormat DF = New DecimalFormat ("#");
Try {// Create a reference to Excel workbook files hssfworkbook workbook = new fileinputstream (new fileInputstream (filetoberead); //system.out.println ("====sheetsnum=== " Workbook.getNumberofsheets ()); // Get the number of sheet for (int numSheets = 0; numSheets //System.out.println (">> getFirstCellnum <<< " zwrightnum ()); //system.out.println (">> getLastcellnum <<<" zytrastCellnum ()); For (Short Cellnumofrow = 0; cellnumofrow <= arow .getlastcenum (); cellnumofrow ) {//system.out.println (">>Rownumofsheet "; //system.out.println (">> >> cellNumOfRow <<< " cellNumOfRow); if (! null = aRow.getCell (cellNumOfRow)) {HSSFCell aCell = aRow.getCell (cellNumOfRow); int cellType = aCell.getCellType (); switch (cellType) {case HSSFCell .CELL_TYPE_NUMERIC: // Numeric String strCell = String.valueOf (aCell.getNumericCellValue ()); if (HSSFDateUtil.isCellDateFormatted (aCell)) {// double sa = aCell.getNumericCellValue (); // Date dd = HSSFDateUtil.getJavaDate ( SA); // hssfdataformat tt = new hssfdataformat (Workbook); //dd.tostring (); system.out.println (acell.getdatecellvalue ()); else sys tem.out.println (strCell); break; case HSSFCell.CELL_TYPE_STRING: // String strCell = aCell.getStringCellValue (); System.out.println (strCell); break; case HSSFCell.CELL_TYPE_FORMULA: // formula // strCell = aCell.getCellFormula (); strCell = String.valueOf (aCell.getNumericCellValue ()); System.out.println (strCell); break; case HSSFCell.CELL_TYPE_BLANK: // blank strCell = aCell.getStringCellValue (); System.out. PRINTLN (STRCELL); BREAK; Default: system.out.println ("-------------- format reading is incorrect!"); // Other format data}}}} } } System.out.println ("// sheet end ///"); } catch (exception e) {system.out.println ("readExcelerror" E); } // Read the value of the cell of the specified line and column, pay attention: line, column subscript is the Public Void getSpecial (short nuMrow, short number) starting from 0 {Try {// Create a reference to Excel workbook files HSSFWORKBOOK workbook = new HSSFWorkbook (new FileInputStream (fileToBeRead)); if (! null = workbook.getSheetAt (0)) {HSSFSheet aSheet = workbook.getSheetAt (0); // get a sheet if (null = aSheet.getRow (numRow! )) {Hssfrow, = asheet.Gtrow (Numrow); if (null! = Zfell) {hssfcell acell = arow.getcell (Numcol); int celltype = acell.getcellType (); switch (celltype) { case 0: // Numeric String strCell = String.valueOf (aCell.getNumericCellValue ()); if (HSSFDateUtil.isCellDateFormatted (aCell)) System.out.println ( "00000000000000000" aCell.getDateCellValue ()); else System.out .println (strCell); Break; Case 1: // string strchool = Acell.getstringcellValue (); System.out.println ("11111111111111111" strCell); Break; Case 2: // Formul a aSheet.setDisplayFormulas (true); strCell = String.valueOf (aCell.getNumericCellValue ()); System.out.println ( "2222222222222222222" strCell); break; case 3: // blank strCell = aCell.getStringCellValue (); System.out.println ("3333333333333333333" strCell); Break; default: system.out.println ("-------------- format reading is incorrect! "); // other format data}}}}}} } catch (exception e) {system.out.println ("readExcelerror" E); }
