Java Processing Excel Data (Parsing Excel with POI)

Preface

At the end of the post-graduate re-examination, I saw the list of applicants arranged by last name on the school's official website, but the website did not give everyone's major, only the number, and I also know the list of re-examination for this major, so I want to know the list of applicants for this major.Specifically, scan each piece of information in the re-examination list to pick out the existing information from the total list of applicants.This involves reading and writing office files, processing documents in Microsoft Office format, and Java provides a more complete open source project, POI, which is an open source project of the Apache Software Foundation.POI provides APIs for Java programs to read and write files in Microsoft Office format.Read and write to Excel is implemented below. Refer to the official API documentation for more POI usage: https://poi.apache.org/apidocs/4.1/.

About POI

POI provides a bunch of tools for working with Office files, roughly categorized as follows

  • HSSF - Provides the ability to read and write files in Microsoft Excel format.
  • XSSF - Provides the ability to read and write files in Microsoft Excel OOOXML format.
  • HWPF - Provides the ability to read and write files in Microsoft Word format.
  • HSLF - Provides the ability to read and write files in Microsoft PowerPoint format.
  • HDGF - Provides the ability to read and write files in Microsoft Visio format.

Here we are mainly used for HSSF, refer to the official API documentation above for other file handling, and must introduce dependencies when using

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

For non-maven projects, click here to download the https://www.kumapai.com/open/1658-poi/3-17 jar package.

  • Document structure class for Excel provided by HSSF:
  • HSSFWorkbook excel document object
  • sheet of HSSFSheet excel
  • Rows of HSSFRow excel
  • Cell of HSSFCell excel
  • HSSFFont excel font
  • HSSFName name
  • HSSFDataFormat date format
  • HSSFHeader sheet header
  • HSSFFooter sheet tail
  • HSSFCellStyle cell style
  • HSSFDateUtil Date
  • HSSFPrintSetup Printing
  • HSSFErrorConstants Error Information Table

Code

The return value uses a two-dimensional list to store a sheet, and the following reads an excel file

public static List<List<String>> readExcle(String fileName) throws Exception {

        //new an input stream
        FileInputStream inputStream = new FileInputStream(fileName);
        //Newa workbook
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        //Create a sheet object with a sheet index as the parameter
        HSSFSheet sheet = workbook.getSheetAt(0);
        //NewOut a two-dimensional array of tables
        List<List<String>> allData = new ArrayList<List<String>>();

        for (Row row:sheet) {
            List<String> oneRow = new ArrayList<String>();
            //Do not read headers
            if(row.getRowNum()==0)
                continue;
            for (Cell cell : row) {
                cell.setCellType(cell.CELL_TYPE_STRING);
                oneRow.add(cell.getStringCellValue().trim());
            }
            allData.add(oneRow);
        }

        for (int i = 0; i < allData.size(); i++) {
            System.out.println(allData.get(i));
        }
        //Close workbook
        workbook.close();
        return allData;
    }

Write to Excel file

 public static boolean writeExcel(List<List<String>> result,String[] sheetHead,String fileName){
        //Create A workbook for an excel
        HSSFWorkbook workbook=new HSSFWorkbook();
        //Create a sheet in workbook
        HSSFSheet sheet = workbook.createSheet();
        //Create line 0 in sheet
        HSSFRow row=sheet.createRow(0);
        
        //Set Header
        for (int i = 0; i < 10; i++) {
            row.createCell(i).setCellValue(sheetHead[i]);
        }
        //Fill in data
        for (int i = 0; i < result.size(); i++) {
            HSSFRow row1 = sheet.createRow(i + 1);
            for (int j = 0; j <result.get(i).size() ; j++) {
                row1.createCell(j).setCellValue(result.get(i).get(j));
            }
        }
        //write file
        try {
            FileOutputStream file=new FileOutputStream(fileName);
            workbook.write(file);
            workbook.close();
            return true;
        }catch (IOException e){
            e.printStackTrace();
            return false;
        }
    }

Tags: Java Excel Apache Maven

Posted on Mon, 29 Apr 2019 18:10:36 -0700 by bambambanana