Excel output using Apache POI!

(For my notes ... but I hope it helps someone ...)

Excel output that is occasionally encountered in business applications. Hmm. I like CSV.

so, Since Maven is used, it is defined in pom.xml.

pom.xml


    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.17</version>
    </dependency>

Define and Maven install.

Maven dependency


poi-ooxml-3.17.jar
poi-3.17.jar
commons-codec-1.10.jar
commons-collections4-4.1.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
curvesapi-1.04.jar

Will be included. (If it's a closed environment, you have to collect it from scratch ...)

The source is ↓

Excel output


   private static void outputExcel() throws Exception {

        String outputFilePath = "[Output destination]\\tmp.xlsx";
        Workbook book = null;
        FileOutputStream fout = null;

        try {
            book = new SXSSFWorkbook();

            Font font = book.createFont();
            font.setFontName("MS gothic");
            font.setFontHeightInPoints((short) 9);

            DataFormat format = book.createDataFormat();

            //Style for header string
            CellStyle style_header = book.createCellStyle();
            style_header.setBorderBottom(BorderStyle.THIN);
            App.setBorder(style_header, BorderStyle.THIN);
            style_header.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
            style_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style_header.setVerticalAlignment(VerticalAlignment.TOP);
            style_header.setFont(font);

            //Style for strings
            CellStyle style_string = book.createCellStyle();
            App.setBorder(style_string, BorderStyle.THIN);
            style_string.setVerticalAlignment(VerticalAlignment.TOP);
            style_string.setFont(font);

            //Style for strings with line breaks
            CellStyle style_string_wrap = book.createCellStyle();
            App.setBorder(style_string_wrap, BorderStyle.THIN);
            style_string_wrap.setVerticalAlignment(VerticalAlignment.TOP);
            style_string_wrap.setWrapText(true);
            style_string_wrap.setFont(font);

            //Style for integers
            CellStyle style_int = book.createCellStyle();
            App.setBorder(style_int, BorderStyle.THIN);
            style_int.setDataFormat(format.getFormat("#,##0;-#,##0"));
            style_int.setVerticalAlignment(VerticalAlignment.TOP);
            style_int.setFont(font);

            //Decimal style
            CellStyle style_double = book.createCellStyle();
            App.setBorder(style_double, BorderStyle.THIN);
            style_double.setDataFormat(format.getFormat("#,##0.0;-#,##0.0"));
            style_double.setVerticalAlignment(VerticalAlignment.TOP);
            style_double.setFont(font);

            //Style for circle display
            CellStyle style_yen = book.createCellStyle();
            App.setBorder(style_yen, BorderStyle.THIN);
            style_yen.setDataFormat(format.getFormat("\"\\\"#,##0;\"\\\"-#,##0"));
            style_yen.setVerticalAlignment(VerticalAlignment.TOP);
            style_yen.setFont(font);

            //Style for percentage display
            CellStyle style_percent = book.createCellStyle();
            App.setBorder(style_percent, BorderStyle.THIN);
            style_percent.setDataFormat(format.getFormat("0.0%"));
            style_percent.setVerticalAlignment(VerticalAlignment.TOP);
            style_percent.setFont(font);

            //Style for date and time display
            CellStyle style_datetime = book.createCellStyle();
            App.setBorder(style_datetime, BorderStyle.THIN);
            style_datetime.setDataFormat(format.getFormat("yyyy/mm/dd hh:mm:ss"));
            style_datetime.setVerticalAlignment(VerticalAlignment.TOP);
            style_datetime.setFont(font);

            Row row;
            int rowNumber;
            Cell cell;
            int colNumber;

            //Creating a sheet(Try to make 3 sheets)
            Sheet sheet;

            for (int i = 0; i < 3; i++) {
                sheet = book.createSheet();
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
                }

                //Sheet name setting
                book.setSheetName(i, "Sheet" + (i + 1));

                //Creating a header line
                rowNumber = 0;
                colNumber = 0;
                row = sheet.createRow(rowNumber);
                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("No.");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("String");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("Character string with line breaks");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("integer");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("Decimal");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("Circle");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("percent");

                cell = row.createCell(colNumber++);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("Date and time");

                cell = row.createCell(colNumber);
                cell.setCellStyle(style_header);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("Circle(8%Tax included)");

                //Fixed window frame
                sheet.createFreezePane(1, 1);

                //Auto filter settings in header line
                sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, colNumber));

                //Automatic adjustment of column width
                for (int j = 0; j <= colNumber; j++) {
                    sheet.autoSizeColumn(j, true);
                }

                //Data row generation(Try to make 10 lines)
                for (int j = 0; j < 10; j++) {
                    rowNumber++;
                    colNumber = 0;
                    row = sheet.createRow(rowNumber);
                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_int);
                    cell.setCellType(CellType.NUMERIC);
                    cell.setCellValue(j + 1);

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_string);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue("this is" + (j + 1) + "The data in the line.");

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_string_wrap);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue("this is\n" + (j + 1) + "Line\n data.");

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_int);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue((j + 1) * 1000);

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_double);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue((double) (j + 1) * 1000);

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_yen);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue((j + 1) * 1000);

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_percent);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue((double) (j + 1));

                    cell = row.createCell(colNumber++);
                    cell.setCellStyle(style_datetime);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(new Date());

                    cell = row.createCell(colNumber);
                    cell.setCellStyle(style_yen);
                    cell.setCellType(CellType.FORMULA);
                    cell.setCellFormula("ROUND(" + App.getExcelColumnString(colNumber - 3) + (rowNumber + 1) + "*1.08, 0)");

                    //Automatic adjustment of column width
                    for (int k = 0; k <= colNumber; k++) {
                        sheet.autoSizeColumn(k, true);
                    }
                }
            }

            //Try erasing sheet 3
            book.removeSheetAt(2);

            //File output
            fout = new FileOutputStream(outputFilePath);
            book.write(fout);
        }
        finally {
            if (fout != null) {
                try {
                    fout.close();
                }
                catch (IOException e) {
                }
            }
            if (book != null) {
                try {
                    /*
Because SXSSFWorkbook generates a lot of temporary files at the cost of saving memory space
You need to dispose and delete temporary files when you no longer need them
                     */
                    ((SXSSFWorkbook) book).dispose();
                }
                catch (Exception e) {
                }
            }
        }
    }

    private static void setBorder(CellStyle style, BorderStyle border) {
        style.setBorderBottom(border);
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
    }

    private final static String[] LIST_ALPHA = {
        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
    };

    private static String getExcelColumnString(int column) {
        String result = "";

        if (column >= 0) {
            if (column / App.LIST_ALPHA.length > 0) {
                result += getExcelColumnString(column / App.LIST_ALPHA.length - 1);
            }
            result += App.LIST_ALPHA[column % App.LIST_ALPHA.length];
        }

        return result;
    }

It looks like VBA. That said, I have to look at the pages that are helpful.

Recommended Posts

Excel output using Apache POI!
Excel operation using Apache POI
[Java] Creating an Excel file using Apache POI
Manipulate Excel with Apache POI
Apache POI Excel in Kotlin
common export to excel using poi
[Java] Handle Excel files with Apache POI
How to output Excel and PDF using Excella
[Java] Text extraction from PowerPoint (ppt) using Apache POI
Replace text in Excel file autoshapes with Apache POI
Apache POI Addictive Point List
Data processing using Apache Flink
apache POI personal notes crossfish21
Sign XML using Apache Santuario
I tried using Apache Wicket
How to use Apache POI
Csv output processing using super-csv
Output Excel with formulas with XlsMapper
Create an excel file with poi
[Apache POI] Judgment of unnecessary cells
Notes for reading and generating xlsx files from Java using Apache POI