POI reads cell information and cell formulas, and poi reads cell formulas.

POI reads cell information and cell formulas, and poi reads cell formulas.

Java EXCEL tools are generally POI and JXL. I am a loyal fan of POI. (In fact, I have never used JXL ).

Most of the current excel versions are later than 07, so I usually use POI on the basis of 07.

Cells have Styles, values, and value types.

Style copy is encapsulated into a function:

Public XSSFCellStyle cloneAllCellStyle (XSSFCell sourceCell, XSSFWorkbook targetWb) {// create a style XSSFCellStyle tempStyle = targetWb. createCellStyle (); // style // numeric format, create character and numeric format DataFormat format = targetWb. createDataFormat (); // font XSSFFont font = targetWb. createFont (); try {tempStyle. setDataFormat (format. getFormat (sourceCell. getCellStyle (). getDataFormatString ();} catch (NullPointerException e) {tempStyle. setDataFormat (short) 0);} font. setColor (sourceCell. getCellStyle (). getFont (). getXSSFColor (); font. setBold (sourceCell. getCellStyle (). getFont (). getBold (); font. setBoldweight (sourceCell. getCellStyle (). getFont (). getBoldweight (); try {font. setCharSet (sourceCell. getCellStyle (). getFont (). getCharSet ();} catch (POIXMLException e) {font. setCharSet (0);} // font. setCharSet (sourceCell. getCellStyle (). getFont (). getCharSet (); font. setFamily (sourceCell. getCellStyle (). getFont (). getFamily (); font. setFontHeight (sourceCell. getCellStyle (). getFont (). getFontHeight (); font. setFontHeightInPoints (sourceCell. getCellStyle (). getFont (). getFontHeightInPoints (); font. setFontName (sourceCell. getCellStyle (). getFont (). getFontName (); font. setItalic (sourceCell. getCellStyle (). getFont (). getItalic (); font. setStrikeout (sourceCell. getCellStyle (). getFont (). getStrikeout (); // font. setThemeColor (sourceCell. getCellStyle (). getFont (). getThemeColor (); font. setTypeOffset (sourceCell. getCellStyle (). getFont (). getTypeOffset (); font. setUnderline (sourceCell. getCellStyle (). getFont (). getUnderline (); tempStyle. setAlignment (sourceCell. getCellStyle (). getAlignment (); tempStyle. setverticalignment (sourceCell. getCellStyle (). getVerticalAlignment (); tempStyle. setBorderBottom (sourceCell. getCellStyle (). getBorderBottom (); tempStyle. setBorderLeft (sourceCell. getCellStyle (). getBorderLeft (); tempStyle. setBorderRight (sourceCell. getCellStyle (). getBorderRight (); tempStyle. setBorderTop (sourceCell. getCellStyle (). getBorderTop (); tempStyle. setBottomBorderColor (sourceCell. getCellStyle (). getBottomBorderXSSFColor (); tempStyle. setLeftBorderColor (sourceCell. getCellStyle (). getLeftBorderXSSFColor (); tempStyle. setRightBorderColor (sourceCell. getCellStyle (). getRightBorderXSSFColor (); tempStyle. setTopBorderColor (sourceCell. getCellStyle (). getTopBorderXSSFColor (); tempStyle. setFillBackgroundColor (sourceCell. getCellStyle (). getFillBackgroundColorColor (); tempStyle. setFont (font); try {tempStyle. setFillForegroundColor (sourceCell. getCellStyle (). getFillForegroundColorColor ();} catch (NullPointerException e) {tempStyle. setFillForegroundColor (IndexedColors. WHITE. getIndex ();} tempStyle. setFillPattern (sourceCell. getCellStyle (). getFillPattern (); tempStyle. setRotation (sourceCell. getCellStyle (). getRotation (); tempStyle. setHidden (sourceCell. getCellStyle (). getHidden (); tempStyle. setWrapText (sourceCell. getCellStyle (). getWrapText (); tempStyle. setIndention (sourceCell. getCellStyle (). getIndention (); tempStyle. setLocked (sourceCell. getCellStyle (). getLocked (); return tempStyle ;}

Call to directly obtain the style content of a cell.

Type of cell value retrieved: cell. getCellType ()

Obtain different values based on different value types:

    switch (cell.getCellType()) {                        case Cell.CELL_TYPE_BLANK:                            tempValue.add("");                            break;                        case Cell.CELL_TYPE_BOOLEAN:                            tempValue.add(cell.getBooleanCellValue());                            break;                        case Cell.CELL_TYPE_ERROR:                            tempValue.add(cell.getErrorCellString());                            break;                        case Cell.CELL_TYPE_FORMULA:                            tempValue.add(cell.getCellFormula());                            map.put("formulaFlag", true);                            break;                        case Cell.CELL_TYPE_NUMERIC:                            tempValue.add(cell.getNumericCellValue());                            break;                        case Cell.CELL_TYPE_STRING:                            tempValue.add(cell.getStringCellValue());                            break;                        default:                            break;                        }

Create content

// Workspace XSSFWorkbook targetWb = new XSSFWorkbook (); // sheet XSSFSheet targetSheet = targetWb. createSheet ("Row summary"); // Delete sheet targetWb. removeSheetAt (index); // index indicates the number of sheets, which starts from 0. // rowXSSFRow row = targetSheet. createRow (I + num1-startRow + 1); // cell XSSFCell cell = row. createCell (j); // j rows

Ii. Operate cell functions

POI can read the function, write the function into the cell, and then calculate the function in excel. The position of the function operation cell is usually fixed, so the operation cell cannot be changed.

1. Read and Write Functions

cell.getCellFormula()

 

In the above Code, get the function content, type: string.

Write function:

cell.setCellFormula((String)cellValues.get(j));

 

 

2. Obtain the value after function compute:

Write directly in some places:

Cell. getNumberValue (); in this way, sometimes an error is reported when the cell content is not worthwhile.

Finally, an exception is thrown.

Of course, sometimes the value cannot be read. The read value is 0.0 (double)

Reading a function deserves another method:

  XSSFFormulaEvaluator evaluator=new XSSFFormulaEvaluator(targetWb);                                CellValue tempCellValue = evaluator.evaluate(cell);                                                                  double cellValue1 =tempCellValue.getNumberValue();                                   

 

How do you get the excel value and then write it to another cell? before writing the value, we recommend that you change the value type of the cell to the numeric type:

   cell.set(XSSFCell.CELL_TYPE_NUMERIC);