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

Source: Internet
Author: User

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);

 

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

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

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

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