Create a workbook

First, create a workbook to work on One caveat is that if you want to read an existing file, you need to create a FileInputStream first. In the case of writing, it is okay if you generate a FileOutputStream when you want to write

//For writing
    Workbook workbook = new XSSFWorkbook();//For xlsx and xlsm files

    //Various processing

    //Output destination generation
    String filePath = "Excel file.xlsx"
    tyr(FileOutputStream output = new FileOutputStream(filePash)){
        //writing
        workbook.write(output);
    }

//For reading
    //Input source generation
    String filePath = "Excel file.xlsx"
    tyr(FileInputStream input = new FileInputStream(filePash)){
        //workbook generation
        Workbook workbook = WorkbookFactory.create(input);
    
    //Various processing
    }

If you want to work with old xls files, use HSSF Workbook instead of XSSF Workbook to generate the workbook.

After this, you will basically create an instance using a workbook.

Create a sheet

Create a sheet to work on When writing, use createSheet to generate a sheet, and when reading, use getSheet or getSheetAt to generate a sheet.

//For writing
    Sheet sheet = workbook.createSheet("Sheet name");

//For reading
    Sheet sheet = workbook.getSheetAt(0);//Get the 0th sheet
    //Or
    Sheet sheet = workbook.getSheet("Sheet name")//Sheet nameがわかっている場合はこちらで

When actually writing and reading characters, a dedicated instance will be created using the sheet created this time.

Create a Row

In POI, it becomes an image that there is no content just by creating a sheet. So you need to generate a row


//For writing
    Row row = sheet.createRow(0);//Generate line 0(Lines count from line 0)

//For reading
    Row row = sheet.getRow(0);//Read line 0

The caveat is that you have to create a Row in advance. As a usage, I think that you will decide the maximum line somewhere and generate as many lines as there are ...

Generate a cell

Generate a Cell for each Row Can't generate cells without rows


//For writing
    Cell cell = row.createCell(0);

//For reading
    Cell cell = row.getCell(0);

Characters, colors, fonts, etc. will be set for this cell.

Manipulate characters

If you can do this, the rest is easy

//For writing
    cell.setCellValue("AIUEO");

//For reading
    String cellValue = cell.getStringCellValue();

Use getxxxCellValue method for reading Data type can be specified for xxx

Set the font

Fonts belong to the workbook, so you need to generate them from the workbook Please note that if you make a mistake in the scope of the font, the font will be set in all cells. If you want to make the color and thickness different, please generate different Fonts.

Font font = workbook.createFont();

//Make it bold
    font.setBold(true);

//Change the color of letters
    //First generate the color
    XSSFColor color = new XSSFColor();
    color.setARGBHex("FF0000"/*red*/);//When specifying by color code
    //Or
    XSSFColor color = new XSSFColor(new java.awt.Color(255,0,0)/*red*/);//When specifying in RGB
       
    //Set the color for the font
    XSSFFont font = (XSSFFont) workbook.createFont();
    font.setColor(color);

POI may need to downcast if you get an instance from a workbook If you receive it as Font type (interface) instead of XSSFFont type, you may get a compile error in the part of the method you want to use.

Set the background color of the cell

We will set the cell style and apply the cell style to the cell. The cell style is a workbook property, so you need to get it from the workbook Like fonts, if you want different background colors for each cell, you need to generate different cell styles.



//First generate the color
XSSFColor color = new XSSFColor();
color.setARGBHex("FF0000"/*red*/);

//Generate cell style
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();

//Set the color for the cell style
cellStyle.setFillForegroundColor(color);

//Set how to fill
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//Simply fill

//Apply cell style to cell
cell.setCellStyle(cellStyle);

I'm doing a downcast when generating a cell style From the original Java idea, it is better to receive it by interface type (CellStyle), but since there are methods that can be used only with XSSFCellStyle, it is received by XSSFCellStyle.

Ruled line settings

Ruled lines (borders) are set using cell style. Then apply the cell style to the cell ...

//Generate cell style
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();

//Ruled line settings
cellStyle.setBorderTop(BorderStyle.THIN/*Frequently used thin lines*/);//Top border settings
cellStyle.setBorderLeft(BorderStyle.THIN);//Left ruled line setting
cellStyle.setBorderRight(BorderStyle.THIN);//Right ruled line setting
cellStyle.setBorderBottom(BorderStyle.THIN);//Bottom border setting

//Apply cell style to cell
cell.setCellStyle(cellStyle);

Automatic adjustment of cell width

Set the cell width for the sheet If you have Japanese, the cell width will be a little narrower unless you explicitly set the font.

//Generate font
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("Yu Gothic");//You can use the font name by copying it directly from Excel.

//Generate cell styles and apply font settings
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFont(font);

//Automatically adjusts the width of all cells
sheet.autoSizeColumn();

bonus

・ It is recommended to generate the required number of rows and cells in advance. If you generate it with the cell style applied, all you have to do is change the values in the cell! I get an exception when I try to access a row that has not been generated

-When trying to set a value in a cell, nothing is entered if it is null

-If you want to put something like a DB column name in an Excel file, you need to create that part yourself.

How to use Apache POI