POI导入demo

前言

使用上篇博文的导入方法，写一个简单的导入demo。其实有了工具类之后就没啥难度了，也就只简单的拿数据。先写个简单的，然后想办法实现动态读取吧，这样读取其实还是比较烦的，每次该模板都要改代码，说到底我还是比较懒的。

excel文件

实体类

package cc.vvxtoys.poi;

public class Student {
    
    private String id;
    private String stcode;
    private String stname;
    private String sex;
    private String phone;
    private String school;
    private String address;
    private String birthday;
    public String getBirthday() {
        return birthday;
    }
    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getStcode() {
        return stcode;
    }
    public void setStcode(String stcode) {
        this.stcode = stcode;
    }
    public String getStname() {
        return stname;
    }
    public void setStname(String stname) {
        this.stname = stname;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getSchool() {
        return school;
    }
    public void setSchool(String school) {
        this.school = school;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Student(String id, String stcode, String stname, String sex, String phone, String school, String address) {
        super();
        this.id = id;
        this.stcode = stcode;
        this.stname = stname;
        this.sex = sex;
        this.phone = phone;
        this.school = school;
        this.address = address;
    }
    public Student() {
        super();
    }
    
}

service

package cc.vvxtoys.poi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cc.vvxtoys.util.CommonTools;

public class ExcelService {
    private static ImportExcelUtils utils = new ImportExcelUtils();

    public static Object getExcelParser(String path, String sheet, String start, String end) throws Exception {
        InputStream is = null;
        if (utils.isEmpty(path)) {
            return new FileNotFoundException("file not found");
        } else {
            is = new FileInputStream(path);
        }
        if (path.endsWith(ImportExcelUtils.EXCEL_2003)) {
            HSSFWorkbook workbook = new HSSFWorkbook(is);
            return importXls(start, end, workbook, sheet);
        } else if (path.endsWith(ImportExcelUtils.EXCEL_2007)) {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            return importXlsx(start, end, workbook, sheet);
        } else {
            is.close();
            return new Exception("type error");
        }

    }

    public static Map<String, Object> importXls(String start, String end, HSSFWorkbook workbook, String sheet) {
        Map<String, Object> result = new HashMap<String, Object>();
        List<Student> sList = new ArrayList<>();
        HSSFSheet hssfSheet = null;
        // 如果传过来sheet页的名称,取当前sheet页,否则遍历所有sheet
        if (!utils.isEmpty(sheet)) {
            hssfSheet = workbook.getSheet(sheet);
        } else {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                hssfSheet = workbook.getSheetAt(i);
                int maxIndex = hssfSheet.getLastRowNum();// 获取最大下标
                int startIndex = 0;
                int endIndex = 0;
                if (utils.isEmpty(start)) {
                    start = String.valueOf(0);
                }
                if (utils.isEmpty(end)) {
                    end = maxIndex + String.valueOf(1);
                }
                if (Integer.parseInt(start) <= 1) {
                    startIndex = 1;
                } else {
                    startIndex = Integer.parseInt(start) - 1;
                }
                if (Integer.parseInt(end) > maxIndex) {
                    endIndex = maxIndex + 1;
                } else {
                    endIndex = Integer.parseInt(end);
                }
                for (int j = startIndex; j < endIndex; j++) {
                    Student student = new Student();
                    HSSFRow row = hssfSheet.getRow(j);
                      //空行
                    if (utils.isBlank(row)) {
                        break;
                    }
                    HSSFCell c1 = row.getCell(0);
                    HSSFCell c2 = row.getCell(1);
                    HSSFCell c3 = row.getCell(2);
                    HSSFCell c4 = row.getCell(3);
                    HSSFCell c5 = row.getCell(4);
                    HSSFCell c6 = row.getCell(5);
                    HSSFCell c7 = row.getCell(6);
                    student.setId(CommonTools.get32UUID());//获取uuid 这个方法就不列出了，很简单
                    if(!utils.isEmpty(c1)){
                        String stcode = utils.getValue(c1);
                        student.setStcode(stcode);
                    }
                    if(!utils.isEmpty(c2)){
                        String stname = utils.getValue(c2);
                        student.setStname(stname);
                    }
                    if(!utils.isEmpty(c3)){
                        String sex = utils.getValue(c3);
                        student.setSex(sex);
                    }
                    if(!utils.isEmpty(c4)){
                        String phone = utils.getValue(c4);
                        student.setPhone(phone);
                    }
                    if(!utils.isEmpty(c5)){
                        String birthday = utils.getValue(c5).replaceAll("/", "-");
                        student.setBirthday(birthday);
                    }
                    if(!utils.isEmpty(c6)){
                        String school = utils.getValue(c6);
                        student.setSchool(school);
                    }
                    if(!utils.isEmpty(c7)){
                        String address = utils.getValue(c7);
                        student.setAddress(address);
                    }
                    sList.add(student);
                }
            }
        }
        result.put("sList", sList);
        return result;
    }

    public static Map<String, Object> importXlsx(String start, String end, XSSFWorkbook workbook, String sheet) {
        Map<String, Object> result = new HashMap<String, Object>();
        List<Student> sList = new ArrayList<>();
        XSSFSheet xssfSheet = null;
        // 如果传过来sheet页的名称,取当前sheet页,否则遍历所有sheet
        if (!utils.isEmpty(sheet)) {
            xssfSheet = workbook.getSheet(sheet);
        } else {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                xssfSheet = workbook.getSheetAt(i);
                int maxIndex = xssfSheet.getLastRowNum();// 获取最大下标
                int startIndex = 0;
                int endIndex = 0;
                if (utils.isEmpty(start)) {
                    start = String.valueOf(0);
                }
                if (utils.isEmpty(end)) {
                    end = maxIndex + String.valueOf(1);
                }
                if (Integer.parseInt(start) <= 1) {
                    startIndex = 1;
                } else {
                    startIndex = Integer.parseInt(start) - 1;
                }
                if (Integer.parseInt(end) > maxIndex) {
                    endIndex = maxIndex + 1;
                } else {
                    endIndex = Integer.parseInt(end);
                }
                for (int j = startIndex; j < endIndex; j++) {
                    Student student = new Student();
                    XSSFRow row = xssfSheet.getRow(j);
                    XSSFCell c1 = row.getCell(0);
                    XSSFCell c2 = row.getCell(1);
                    XSSFCell c3 = row.getCell(2);
                    XSSFCell c4 = row.getCell(3);
                    XSSFCell c5 = row.getCell(4);
                    XSSFCell c6 = row.getCell(5);
                    XSSFCell c7 = row.getCell(6);
                    
                    student.setId(CommonTools.get32UUID());//uuid
                    if(!utils.isEmpty(c1)){
                        String stcode = utils.getValue(c1);
                        student.setStcode(stcode);
                    }
                    if(!utils.isEmpty(c2)){
                        String stname = utils.getValue(c2);
                        student.setStname(stname);
                    }
                    if(!utils.isEmpty(c3)){
                        String sex = utils.getValue(c3);
                        student.setSex(sex);
                    }
                    if(!utils.isEmpty(c4)){
                        String phone = utils.getValue(c4);
                        student.setPhone(phone);
                    }
                    if(!utils.isEmpty(c5)){
                        String birthday = utils.getValue(c5).replaceAll("/", "-");
                        student.setBirthday(birthday);
                    }
                    if(!utils.isEmpty(c6)){
                        String school = utils.getValue(c6);
                        student.setSchool(school);
                    }
                    if(!utils.isEmpty(c7)){
                        String address = utils.getValue(c7);
                        student.setAddress(address);
                    }
                    sList.add(student);
                }
            }
        }
        result.put("sList", sList);
        return result;
    }

    public static void main(String[] args) throws Exception {
        String path = "D:\\Desktop\\2.xlsx"; //文件路径
        String start = "0"; //开始条数
        String end = "10";//结束条数
        String sheet = null;//sheet页名称
        ExcelService service = new ExcelService();
        Map<String, Object> result = (Map<String, Object>) service.getExcelParser(path, sheet, start, end);
        
    }

}

转载于:https://www.cnblogs.com/vvxtoys/p/8252856.html