jayq_java_newtravel/src/main/java/com/template/controller/ExcelController.java

package com.template.controller;

import com.template.annotation.PassToken;
import com.template.api.ExcelControllerAPI;
import com.template.model.pojo.SystemUser;
import com.template.services.SystemUserService;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RestController;

import java.io.FileInputStream;
import java.io.IOException;

@RestController
public class ExcelController implements ExcelControllerAPI {
    @Autowired
    SystemUserService systemUserService;

    /**
     * .xlsx格式
     * @param
     */
    @Override
    @PassToken
    public void toExcel(String fileUrl) {
        //创建工作簿对象
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(new FileInputStream(fileUrl));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //获取工作簿下sheet的个数
        int sheetNum = xssfWorkbook.getNumberOfSheets();
        //遍历工作簿中的所有数据
        for (int i = 0; i < sheetNum; i++) {
            //读取第i个工作表
            System.out.println("读取第"+(i+1)+"个sheet");
            XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
            //获取最后一行的num，即总行数。此处从0开始
            int maxRow = sheet.getLastRowNum();
//            从第二行开始获取数据
            for (int row = 1; row <= maxRow; row++) {
                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                int maxRol = sheet.getRow(row).getLastCellNum();
                System.out.println("--------第" + row + "行的数据如下--------");
//                for (int rol = 0; rol < maxRol; rol++){
//                    System.out.print(sheet.getRow(row).getCell(rol) + "  ");
//                }
                DataFormatter dataFormatter = new DataFormatter();
//                获取手机号
                XSSFCell cell = sheet.getRow(row).getCell(3);
//                获取的手机号格式有问题需要转换
                dataFormatter.addFormat("###########",null);
                String phone = dataFormatter.formatCellValue(cell);
//                获取名字
                String name = sheet.getRow(row).getCell(1).toString();
//                获取备注
                String remarks = sheet.getRow(row).getCell(4).toString();
//                获取性别
                String genderName = sheet.getRow(row).getCell(2).toString();
                int gender;
                if ("女".equals(genderName)) {
                    gender=1;
                }else {
                    gender=2;
                }

                SystemUser systemUser = new SystemUser();
                systemUser.setRoleId(Long.valueOf(row));
                systemUser.setAccountName(name);
                systemUser.setAccountNumber(row+"");
                systemUser.setAccountPassword("123456");
                systemUser.setPhoneNumber(phone);
                systemUser.setEmail("123456@123456");
                systemUser.setRemarks(remarks);
                systemUser.setGender(gender);
                systemUser.setDeleted(0L);
                systemUser.setWechatId("123456789");

                systemUserService.save(systemUser);

            }
        }
    }
}