java读取Excel报表数据

Excel内容：

姓名	性别	班级	语文	数学	英语
张三	男	1	96	100	96
李四	男	1	95	99	95
王五	女	1	93	99	92
赵六	女	1	91	98	90

 

一. 导入依赖

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

二. 编写代码

package com.test;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileInputStream;

@RestController
@RequestMapping("/test")
public class TestReadExcel {

    @GetMapping("/test1")
    public void test1() throws Exception {
        File excel = new File("C:\\Users\\lenovo\\Desktop\\test.xlsx");

        String[] split = excel.getName().split("\\.");
        Workbook wb;
        if ("xls".equals(split[1])) {
            FileInputStream fis = new FileInputStream(excel);
            wb = new HSSFWorkbook(fis);
        } else if ("xlsx".equals(split[1])) {
            wb = new XSSFWorkbook(excel);
        } else {
            throw new RuntimeException();
        }

        // 开始解析
        Sheet sheet = wb.getSheetAt(0);
        int firstRowIndex = sheet.getFirstRowNum() + 1; // 不获取表头数据
        int lastRowIndex = 10000;   // 截止行数
        System.out.println("姓名  性别  班级  语文  数学  英语");
        for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
            Row row = sheet.getRow(rIndex);
            if (row != null) {
                System.out.print(row.getCell(0).toString() + "  ");
                System.out.print(row.getCell(1).toString() + "  ");
                System.out.print(row.getCell(2).toString() + "  ");
                System.out.print(row.getCell(3).toString() + "  ");
                System.out.print(row.getCell(4).toString() + "  ");
                System.out.print(row.getCell(5).toString() + "  ");
                System.out.println();
            }
        }
        wb.close();
    }

}

三. 打印输出

姓名  性别  班级  语文  数学  英语
张三  男  1.0  96.0  100.0  96.0  
李四  男  1.0  95.0  99.0  95.0  
王五  女  1.0  93.0  99.0  92.0  
赵六  女  1.0  91.0  98.0  90.0