apache POI personal notes crossfish21

basic operation

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;

/**
*Jakarta that registers customer-related setting values for each user in the DB
*/
public class Class01 {

  /*
     *Returns a Cell.
     *
     * @param args
     *line
     */
  public static void main(String[] args) {

//        aaa();   //Create a new book
//        bbb();   //Import existing workbook
//        blankCheck();   //Blank check
        rowAndColumn();   //Row and column manipulation
//        book();   //Book operation
//        cell();  //Cell operation



    }

  public static void aaa() {

        try(XSSFWorkbook book1 = new XSSFWorkbook()) {

            XSSFSheet sheet1 = book1.createSheet("Sheet 01");
            //Create a sheet. Sheet does not exist in the initial state

            XSSFRow row01 = sheet1.createRow(0);
            //Row instantiation. The first line becomes 0

            XSSFCell cell01 = row01.createCell(0);
            //Cell instantiation. The first column becomes 0

            cell01.setCellValue("AAA");  //Set cell value


            XSSFCell cell02 = row01.createCell(1);
            cell02.setCellValue("BBB");

            XSSFCell cell03 = row01.createCell(2);
            cell03.setCellFormula("A1&B1");  //Set cell formula. It seems that "=" is not necessary


            try(FileOutputStream out = new FileOutputStream("C:/work/aaa.xlsx")) {
                book1.write(out);
                //Output as a new book. If you specify an existing file path, it will be overwritten.
            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
   }



    public static void bbb(){  //Import existing workbook

        try(FileInputStream input = new FileInputStream("C:/work/bbb.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                //* It seems that there is no problem reading even if the sheet is protected.

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  //Sheet"Sheet1"Get
                Row row02 = sheet1.getRow(1);  //Get the second line
                Cell cell01 = row02.getCell(0);  //Get cell A1 in the second row
                Cell cell02 = row02.getCell(1);  //Get cell B1 in the second row
                Cell cell03 = row02.getCell(2);

                String str1 = cell01.getStringCellValue();
                //Get the cell string. Note that if the cell value is a numerical value, a read error will occur.
                System.out.println(str1);

                Double double01 = cell02.getNumericCellValue();
                //Get the number
                System.out.println(double01);

                try {
                    String str3 = cell03.getCellFormula();
                    //Get the formula. An error will occur if anything other than a formula is included.
                    System.out.println(str3);
                } catch (IllegalStateException e) {
                    //IllegalStateException occurs when you get a formula from a cell that contains something other than a formula
                    e.printStackTrace();
                }


                //Get cell parameters
                System.out.println(cell01.getColumnIndex());  //Get the column number. Note that the numbers start from 0
                System.out.println(cell01.getRowIndex());  //Get the line number. Note that the numbers start from 0
                System.out.println(cell01.getAddress());  //Get the cell address. This will get the Excel address as it is

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public static void blankCheck(){  //Check if the cell is blank

        try(FileInputStream input = new FileInputStream("C:/work/bbb.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");

                //What if a blank cell
                Row row03 = sheet1.getRow(2);  //Get the 3rd line
                Cell cell01 = row03.getCell(0);
                Cell cell02 = row03.getCell(1);
                Cell cell03 = row03.getCell(2);

                if (cell01 == null || cell01.getCellType() == Cell.CELL_TYPE_BLANK) {
                //This will determine if it is blank. Is null checking necessary??
                    System.out.println("cell" + cell01.getAddress() + "Is blank");
                }else{
                    String str1 = cell01.getStringCellValue();
                    System.out.println(str1);
                }


                if (cell02 == null || cell02.getCellType() == Cell.CELL_TYPE_BLANK) {
                    System.out.println("cell" + cell02.getAddress() + "Is blank");
                }else{
                    Double double01 = cell02.getNumericCellValue();
                    System.out.println(double01);
                }

                if (cell03 == null || cell03.getCellType() == Cell.CELL_TYPE_BLANK) {
                    System.out.println("cell" + cell03.getAddress() + "Is blank");
                }else{
                    String str4= cell03.getCellFormula();
                    System.out.println(str4);
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    public static void rowAndColumn(){

        try(FileInputStream input = new FileInputStream("C:/work/Rows and columns.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  //Sheet"Sheet1"Get
//                Row row05 = sheet1.getRow(4);  //Get the 5th line
//                row05.setZeroHeight(true);
//                //You want to hide. Lines that are already hidden do not result in an error
//
//                Row row08 = sheet1.getRow(7);
//                row08.setZeroHeight(false);
//                //Redisplay hidden columns. Lines that are not hidden do not result in an error
//
//                Row row11 = sheet1.getRow(10);
//                sheet1.removeRow(row11);
//                //Deleted the value of the entire 11th line(Seems to be).. The row itself is not deleted
//                //The row itself is not deleted, but it seems that all cell formats and borders have been cleared.
//
//                Row row12 = sheet1.getRow(11);
//                row12.setHeightInPoints(50.25F);
//                //Set the row height. It must be float. 0.Set in 25 units. Even if you make finer settings, it can be rounded.
//
//                sheet1.shiftRows(9,14,-5);
//                //Move the 10th to 15th lines up by 5 lines. Possible even if there is a merged cell
//                //The cell format and ruled lines are also reflected as they are
//
//                book1.setForceFormulaRecalculation(true);
//                //If the calculation result of the formula becomes strange due to the movement of the line or the deletion of the contents of the line, it is recommended to recalculate as above.




                // ※  Row row05 = sheet1.getRow(4);It seems that it is possible to operate in units of rows, but not in units of columns.

                sheet1.setColumnHidden(4, true);  //Hide the 5th column
                sheet1.setColumnHidden(7, false);  //Redisplay the third column




                try(FileOutputStream out = new FileOutputStream("C:/work/Rows and columns.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    public static void book(){  //Book related

        try(FileInputStream input = new FileInputStream("C:/work/ccc.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  //Sheet"Sheet1"Get
                Row row01 = sheet1.getRow(0);  //Get the first line

                book1.setForceFormulaRecalculation(true);  //Perform recalculation


                try(FileOutputStream out = new FileOutputStream("C:/work/ccc.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    public static void cell(){  //Cell related

        try(FileInputStream input = new FileInputStream("C:/work/cell.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  //Sheet"Sheet1"Get


                Row row11 = sheet1.getRow(10);  //Get line 11
                Cell cell01 = row11.getCell(0);
                Cell cell02 = row11.createCell(1);  //If you want to set the value, use createCell
//                cell02.setCellFormula(cell01.getCellFormula());
                //A copy of the formula. However, since you can get a string, you cannot copy the relative reference formula.
                copyCell2Cell(cell01,cell02,book1);


                try(FileOutputStream out = new FileOutputStream("C:/work/cell.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Copy formulas by relative reference

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellFormulaCopy {

	public static void main(String[] args) {

		try(FileInputStream input = new FileInputStream("C:/work/cell.xlsx")){

			try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

				XSSFSheet sheet1 = book1.getSheet("Sheet1");  //Sheet"Sheet1"Get

				Row row11 = sheet1.getRow(10);  //Get line 11
				Cell cell01 = row11.getCell(0);
				Cell cell02 = row11.createCell(1);  //If you want to set the value, use createCell

				cell02.setCellFormula(cell01.getCellFormula());
				//A copy of the formula. However, since you can get a string, you cannot copy the relative reference formula.

				copyFormula(cell01,cell02,book1);
				//Copy the formula by relative reference. Copy the formula of copyFormula to cell02

				try(FileOutputStream out = new FileOutputStream("C:/work/cell.xlsx")) {
					book1.write(out);
				} catch (Exception e) {
					e.printStackTrace();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	public static void copyFormula(Cell srcCell, Cell destCell, XSSFWorkbook book) {
	//Copy formulas by relative reference
	//srcCell copy source destCell copy destination

		String formula = srcCell.getCellFormula();
		EvaluationWorkbook ew;
		FormulaRenderingWorkbook rw;
		Ptg[] ptgs;

		ew = XSSFEvaluationWorkbook.create((XSSFWorkbook) book);
		ptgs = FormulaParser.parse(formula, (XSSFEvaluationWorkbook) ew, FormulaType.CELL, 0);
		//I don't know the details, but the last parameter specifies the sheet number starting from 0.
		rw = (XSSFEvaluationWorkbook) ew;

		for (Ptg ptg : ptgs) {
		//Coordinate calculation
			int shiftRows = destCell.getRowIndex() - srcCell.getRowIndex();
			int shiftCols = destCell.getColumnIndex() - srcCell.getColumnIndex();

			if (ptg instanceof RefPtgBase) {
				RefPtgBase ref = (RefPtgBase) ptg;

				if (ref.isColRelative()) {
					ref.setColumn(ref.getColumn() + shiftCols);
				}

				if (ref.isRowRelative()) {
					ref.setRow(ref.getRow() + shiftRows);
				}

			} else if (ptg instanceof AreaPtg) {
				AreaPtg ref = (AreaPtg) ptg;

				if (ref.isFirstColRelative()) {
					ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
				}

				if (ref.isLastColRelative()) {
					ref.setLastColumn(ref.getLastColumn() + shiftCols);
				}

				if (ref.isFirstRowRelative()) {
					ref.setFirstRow(ref.getFirstRow() + shiftRows);
				}

				if (ref.isLastRowRelative()) {
					ref.setLastRow(ref.getLastRow() + shiftRows);
				}
			}
		}

		destCell.setCellFormula(FormulaRenderer.toFormulaString(rw, ptgs));
	}
}

Recommended Posts

apache POI personal notes crossfish21
DDD personal notes
play framework personal notes
[Apache POI] Corresponding Excel version
Apache POI Addictive Point List
First Play Framework personal notes
How to use Apache POI
Manipulate Excel with Apache POI
Apache POI Excel in Kotlin
Excel output using Apache POI!
[Apache POI] Judgment of unnecessary cells
Sort in List, for personal notes
Notes for reading and generating xlsx files from Java using Apache POI