Java - Excel导入工具类 ExcelImportUtils

仲孙向明

2023-12-01

Excel操作依赖

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

Excel导入工具类

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelImportUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelImportUtils.class);

    /**
     * 导入Excel
     * @param filePath excel文件路径
     * @return Workbook
     */
    public static Workbook importExcel(String filePath) {
        if (StringUtils.isBlank(filePath) || "null".equals(filePath)) {
            return null;
        }
        String suf = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(suf)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(suf)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            logger.error(">>> Excel表格读取错误", e);
            return null;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导入Excel
     * @param file excel文件
     * @return Workbook
     */
    public static Workbook importExcel(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            return null;
        }
        String fileName = file.getOriginalFilename();
        String suf = fileName.substring(fileName.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(FileUtils.multipartFileToFile(file));
            if (".xls".equals(suf)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(suf)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is .close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 读取表格数据
     * @param cell 一格
     * @return String
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String cellValue;
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC:// 数字类型
                boolean cellDateFormatted = HSSFDateUtil.isCellDateFormatted(cell);
                if (cellDateFormatted) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf;
                    short dataFormat = cell.getCellStyle().getDataFormat();
                    if (dataFormat == 14 || dataFormat == 179 || dataFormat == 177) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    } else if (dataFormat == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (dataFormat == 22) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    } else {
                        try {
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        } catch (Exception e) {
                            throw new RuntimeException(">>> 日期格式错误");
                        }
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else {// 处理数值格式
                    double numericCellValue = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.######");
                    cellValue = df.format(numericCellValue);
                }
                break;
            case STRING:// 字符串类型
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:// 布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:// 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:// 空值
                cellValue = null;
                break;
            case _NONE:// 空值
                cellValue = null;
                break;
            case ERROR:// 异常
                cellValue = "#非法字符#";
                break;
            default:
                cellValue = "#未知类型#";
                break;
        }
        return cellValue;
    }

}

Controller使用案例

import com.egovalley.utils.ExcelExportUtils;
import com.egovalley.utils.ExcelImportUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/excel")
@SuppressWarnings("unchecked")
public class ExcelController {

    private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);

    @Value("${phone.ownership.url}")
    private String phoneOwnershipUrl;
    @Value("${phone.ownership.fileDir}")
    private String fileDir;
    @Value("${phone.ownership.suffix}")
    private String fileSuffix;

    @RequestMapping("/import")
    @ResponseBody
    public Map<String, Object> excelImportTest(@RequestParam("file") MultipartFile file, @RequestParam("name") String name) {
        logger.info(">>> name = " + name + "; file = " + file);
        Map<String, Object> resultMap = new HashMap<>();
        if (file == null || file.isEmpty()) {
            resultMap.put("resCode", "301");
            resultMap.put("resMsg", "文件缺失, 请检查后重新上传");
            return resultMap;
        }
//        Workbook wb = ExcelImportUtils.importExcel("E:\\lky\\test.xlsx");
        Workbook wb = ExcelImportUtils.importExcel(file);
        if (wb != null) {
            List<String> list = new ArrayList<>();
            Sheet sheet = wb.getSheetAt(0);// 只读第一个sheet
            Row row;
            int rowNum = sheet.getPhysicalNumberOfRows();// 获取最大行数
            for (int i = 1; i < rowNum; i++) {// 第一行是抬头, 从第二行开始读
                row = sheet.getRow(i);
                if (row != null) {
                    int colNum = row.getPhysicalNumberOfCells();// 获取最大列数
                    if (colNum == 1) {
                        list.add(ExcelImportUtils.getCellValue(row.getCell(0)));
                    } else {
                        resultMap.put("resCode", "303");
                        resultMap.put("resMsg", "上传文件有误, 请检查后重试");
                        return resultMap;
                    }
                } else {
                    resultMap.put("resCode", "304");
                    resultMap.put("resMsg", "上传文件有误, 请检查后重试");
                    return resultMap;
                }
            }
            // 号码归属地过滤
            logger.info(">>> excel过滤号码集: " + list);
            Map<String, Object> filterMap = new HashMap<>();
            filterMap.put("telList", list);
            try {
                /*String resStr = HttpClientUtils.sendPostRequestByJson(phoneOwnershipUrl, filterMap, false);
                Map<String, Object> resMap = JsonUtils.jsonToMap(resStr);
                logger.info(">>> 过滤结果: " + resMap);*/

                // 模拟
                Map<String, Object> resMap = new HashMap<>();
                resMap.put("resCode", "200");
                Map<String, String> p1 = new HashMap<>();
                p1.put("telephone", "18812345678");
                p1.put("ownership", "上海-01");
                Map<String, String> p2 = new HashMap<>();
                p2.put("telephone", "18887654321");
                p2.put("ownership", "上海-02");
                Map<String, String> p3 = new HashMap<>();
                p3.put("telephone", "18888888888");
                p3.put("ownership", "上海-03");
                List<Map<String, String>> lists = new ArrayList<>();
                lists.add(p1);
                lists.add(p2);
                lists.add(p3);
                resMap.put("resultList", lists);
                logger.info(">>> 过滤结果: " + resMap);

                if ("200".equals(resMap.get("resCode"))) {
                    ExcelExportUtils export = new ExcelExportUtils(fileDir + "测试" + fileSuffix, "号码归属地");
                    List<Map<String, String>> resultList = (List<Map<String, String>>) resMap.get("resultList");
                    String[] titleName = {"电话号码", "归属地"};
                    String[] titleColumn = {"telephone", "ownership"};
                    int[] titleSize = {20, 20};
                    export.exportExcel(titleColumn, titleName, titleSize, resultList);
                    resultMap.put("resCode", "200");
                } else {
                    logger.info(">>> 号码归属地过滤失败");
                    resultMap.put("resCode", "400");
                    resultMap.put("resMsg", "号码归属地过滤失败, 请稍后再试");
                }
            } catch (Exception e) {
                logger.error(">>> 号码归属地过滤异常", e);
                resultMap.put("resCode", "500");
                resultMap.put("resMsg", "系统正忙, 请稍后再试");
            }
        } else {
            resultMap.put("resCode", "302");
            resultMap.put("resMsg", "上传文件有误, 请检查后重试");
        }
        return resultMap;
    }

}

Java - Excel导入工具类 ExcelImportUtils

相关阅读

相关文章

相关问答

相关文档