Java uses POI to operate Excel

Time:2019-10-9

1. POI operation Excel

1.1. Dependence

org.apache.poi
            poi
            4.1.0
        

        
            org.apache.poi
            poi-ooxml
            4.1.0
        
        
            org.apache.poi
            poi-ooxml-schemas
            4.1.0

1.2. Read Excel

1.2.1. Excel file content

file

1.2.2. Code

/**
     * Read Excel
     */
    public static void readExcel() {
        InputStream inputStream = null;
        XSSFWorkbook xssfWorkbook = null;
        try {

            String past = "/ operate excel. xlsx";
            inputStream = new FileInputStream(past);
            xssfWorkbook = new XSSFWorkbook(inputStream);
            // Get the number of sheets
            int numberOfSheets = xssfWorkbook.getNumberOfSheets();
            // Gets the specified sheet
            System.out.println(numberOfSheets);

            // Get by specifying a name
            XSSFSheet sheet = xssfWorkbook. getSheet ("notebook");
            // Acquisition by subscription
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(1);
            if (sheetAt != null) {
                // The last line has data
                int lastRowNum = sheetAt.getLastRowNum();
                XSSFRow row;
                short lastCellNum;
                XSSFCell cell;

                for (int i = 0; i <= lastRowNum; i++) {
                    // Gets the specified row
                    row = sheetAt.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    // The last column contains data
                    lastCellNum = row.getLastCellNum();
                    for (int j = 0; j <= lastCellNum; j++) {
                        cell = row.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        // Data type
                        CellType cellType = cell.getCellType();
                        // String
                        if (CellType.STRING == cellType) {
                            System.out.println(cell.toString());
                        }
                        // Numbers
                        else if (CellType.NUMERIC == cellType) {
                            try {
                                System.out.println(cell.getDateCellValue());
                            } catch (Exception e) {
                                System.out.println(cell.toString());
                            }
                        }
                        //……
                        else {
                            System.out.println(cell.toString());
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

1.2.3. Console output

2
Signature
Note Classification
Creation time
Founder
Owner
Xiao Ming's Note
Learning Note
Tue Sep 03 00:00:00 CST 2019
Xiaoming
Xiaoming
Xiaoming的Personal note
Personal note
Sun Sep 08 00:00:00 CST 2019
Xiaoming
Xiaoming

1.3. Generating Excel

1.3.1. Code

/**
     * Generating Excel
     */
    public static void creatExcel() {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        // Create a sheet
        XSSFSheet Sheet1 = xssfWorkbook. createSheet ("the first new sheet");

        // Set the height and width, or set them separately for each row and column
        // Parameter is the number of characters
        sheet1.setDefaultColumnWidth(20);
        sheet1.setDefaultRowHeight((short) (33 * 20));
        // The second parameter is 1/256 of the character width.
        sheet1.setColumnWidth(5, 30 * 256);

        // Setting Cell Style
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();

        // Font Style
        Font fontStyle = xssfWorkbook.createFont();
        fontStyle.setBold(true);
        // Font
        FontStyle. setFontName ("isoline");
        // Size
        fontStyle.setFontHeightInPoints((short) 11);
        // Adding font styles to cell styles
        cellStyle.setFont(fontStyle);

        // Level in the middle
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // Vertical Centralization
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // Set cell fill color
        DefaultIndexedColorMap defaultIndexedColorMap = new DefaultIndexedColorMap();
        XSSFColor clr = new XSSFColor(defaultIndexedColorMap);
        byte[] bytes = {
                (byte) 217,
                (byte) 217,
                (byte) 217
        };
        clr.setRGB(bytes);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(clr);

        // Set the cell not to be locked, editable, but editable with this style
        cellStyle.setLocked(false);
        // Lock the entire sheet non-editable
        sheet1.protectSheet("1231312");


        // Create a row of data
        XSSFRow row;
        XSSFCell cell;
        row = sheet1.createRow(0);
        cell = row.createCell(0);
        // Setting values
        cell.setCellValue("2");


        // Merge cells
        CellRangeAddress CRA = new CellRangeAddress (1, 1, 0, 3); //start row, terminate row, start column, terminate column
        sheet1.addMergedRegion(cra);
        // Setting the style of merged cells
        // Use the RegionUtil class to add borders for merged cells
        // lower border
        RegionUtil.setBorderBottom(BorderStyle.MEDIUM_DASHED, cra, sheet1);
        // Left border
        RegionUtil.setBorderLeft(BorderStyle.MEDIUM_DASHED, cra, sheet1);
        row = sheet1.getRow(1);

        // Set the text style in the merge cell
        // But the border style of this cell overrides the merged cell style set above.
        CellUtil.getCell(row, 0).setCellStyle(cellStyle);


        // Setting the style of a single cell
        row = sheet1.createRow(2);
        cell = row.createCell(0);
        cell.setCellStyle(cellStyle);

        // Setting up data validation
        // Sequence Check
        String[] strArray = {
                Monday,
                Tuesday,
                Wednesday
        };
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet1);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strArray);
        CellRangeAddressList addressList = new CellRangeAddressList(3, 3, 0, 2);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        // Display error alert box
        validation.setShowErrorBox(true);
        Validation. createErrorBox ("Error Tips", "Only the specified content can be selected! ";
        // Set the right side of the cell to display clipping symbols, showing available options, default to true
        validation.setSuppressDropDownArrow(true);
        // Display prompt information
        validation.setShowPromptBox(true);
        Validation. createPromptBox ("Tips", "Choose Week to Fill in! ";
        sheet1.addValidationData(validation);

        // Protective Workbook Can't Be Modified
        xssfWorkbook.lockStructure();
        // I don't know what this is for.
        xssfWorkbook.lockRevision();
        // Lock the window size of excel, not unlimited horizontal and vertical stretching.
        xssfWorkbook.lockWindows();

        XssfWorkbook. createSheet ("second person sheet");


        OutputStream outputStream = null;
        try {
            OutputStream = new FileOutputStream ("/create excel.xlsx");
            xssfWorkbook.write(outputStream);
            outputStream.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

1.3.2. Generate excel file content

file
file

1.4. Reference

https://www.cnblogs.com/gavincoder/p/9051803.html

https://blog.csdn.net/sxdtwym/article/details/54379592

https://blog.csdn.net/lipinganq/article/details/78090553