Java Excel 操作

Java Excel 操作

pom.xml

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

操作

import com.npy.computer.student.model.Student;
import com.npy.computer.student.service.StudentService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

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

/**
 * @author NNShadow
 * @date 2019/11/14 12:50
 */
@RestController
public class ExcalImport {
    @Autowired
    private StudentService studentService;

    public void readExcel(File file) throws Exception {
        InputStream inputStream = new FileInputStream(file);
        String fileName = file.getName();
        Workbook wb = null;
        //.xls 使用
        wb = new HSSFWorkbook(inputStream);
        //.xlsx 使用
//        wb=new XSSFWorkbook(inputStream);
        //对应 Excel 中的表格 id
        Sheet sheet = wb.getSheetAt(0);
        //获取第一行
        int firstRowIndex = sheet.getFirstRowNum();
        //获取最后一行
        int lastRowIndex = sheet.getLastRowNum();
        //第一行普遍是标题，故不用
        for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                //Student 为实例化的对象，可根据自己需求构建
                Student student = new Student();
                Cell cell = row.getCell(0);
                student.setStudentId(cell.toString());
                cell = row.getCell(1);
                student.setStudentName(cell.toString());
                cell = row.getCell(2);
                student.setGender(cell.toString());
                cell = row.getCell(3);
                student.setBorn(cell.toString());
                cell = row.getCell(4);
                student.setNation(cell.toString());
                cell = row.getCell(5);
                student.setBirthPlace(cell.toString());
                cell = row.getCell(6);
                student.setLive(cell.toString());
                display(student);
            }
        }
    }

    public void display(Student student) {
        System.out.println("excel display: "
                + student.getStudentId() + " "
                + student.getStudentName() + " "
                + student.getGender() + " "
                + student.getBorn() + " "
                + student.getNation() + " "
                + student.getBirthPlace() + " "
                + student.getLive() + " ");
    }

    public void make() throws Exception {
        //读取 excal
        File file = new File("文件所在位置");
        readExcel(file);
        System.out.println("finish");
    }
}