How to use Apache POI

Today I would like to briefly explain how to use POI.

Please refer to the official website below for details. Apache POI

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.

Recommended Posts

How to use Apache POI
How to use Apache Derby on Eclipse
How to use Map
How to use with_option
How to use fields_for
How to use java.util.logging
How to use map
How to use collection_select
How to use Twitter4J
How to use active_hash! !!
How to use MapStruct
How to use TreeSet
[How to use label]
How to use hashes
How to use JUnit 5
How to use Dozer.mapper
How to use Gradle
How to use org.immutables
How to use java.util.stream.Collector
How to use VisualVM
How to use Map
How to use Chain API
[Java] How to use Map
How to use Priority Queuing
[Rails] How to use enum
How to use java Optional
How to use JUnit (beginner)
How to use Ruby return
[Rails] How to use enum
How to use @Builder (Lombok)
[Swift] How to use UserDefaults
How to use java class
How to use Swift UIScrollView
How to use Big Decimal
How to use String [] args
[Java] How to use string.format
How to use rails join
How to use Java Map
Ruby: How to use cookies
How to use dependent :: destroy
How to use Eclipse Debug_Shell
[Rails] How to use validation
How to use Java variables
[Rails] How to use authenticate_user!
[Rails] How to use "kaminari"
How to use GC Viewer
[Java] How to use Optional ①
How to use Lombok now
[Creating] How to use JUnit
[Rails] How to use Scope
How to use the link_to method
How to use arrays (personal memorandum)
How to use scope (JSP & Servlet)
How to use the include? method
[Rails] How to use devise (Note)
How to use the form_with method
How to use EventBus3 and ThreadMode
How to use Spring Data JDBC
How to use binding.pry [53 days left]
How to use Java HttpClient (Post)
[Java] How to use join method