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