Excel工具

Excel工具

1、解析POI单元格，获取文本值

public static String getCellStringValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        switch (cell.getCellType()){
            case FORMULA: cellValue = ((XSSFCell) cell).getCTCell().getV(); break;
            case NUMERIC:
                String cellStr = cell.toString();
                boolean match = ReUtil.isMatch("\\d{2}-[\\u2E80-\\u9FFF]{1,2}月-\\d{4}", cellStr);
                if(match){
                    String[] split = cellStr.split("-");
                    String month = monthMap.getString(split[1]);
                    cellValue = split[2] + "-" + month + "-" + split[0];
                }else {
                    cellValue = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                }
                break;
            case STRING: cellValue = cell.getStringCellValue();break;
            case BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue());break;
            case BLANK:
            case _NONE:
            case ERROR: break;
            default: throw new IllegalArgumentException("解析单元格失败，没有找到匹配的类型");
        }

        return cellValue;
    }

---

2、数据导出、文件下载

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

@Slf4j
public class FileDownloadUtil {

    /**
     * 导出excel文件
     * @param response HttpServletResponse
     * @param fileName 要导出的文件全名称
     * @param titleMap LinkedHashMap 标题行: <字段名, 中文名>
     * @param dataList <xmp>JSON.parseObject(JSON.toJSONString(list), new TypeReference<List<Map<String, Object>>>() {});</xmp>
     */
    public static void downloadExcel(HttpServletResponse response,
                                     String fileName,
                                     Map<String, String> titleMap,
                                     List<Map<String, Object>> dataList) throws IOException {
        Assert.notEmpty(dataList, "数据为空, 不允许导出!");
        String[] titleArray = titleMap.keySet().toArray(new String[]{});

        Workbook workbook = new XSSFWorkbook();

        int step = 200000;
        if(dataList.size() > step){
            int count = dataList.size() / step + (dataList.size() % step > 0 ? 1 : 0);
            for (int i = 0; i < count; i++) {
                Sheet sheet = workbook.createSheet("导出数据" + (i + 1) + "-" + count);
                List<Map<String, Object>> subList = dataList.subList(i * step, Math.min((i + 1) * step, dataList.size()));
                fillSheet(workbook, titleMap, sheet, titleArray, subList);
            }
        }else {
            Sheet sheet = workbook.createSheet("导出数据");
            fillSheet(workbook, titleMap, sheet, titleArray, dataList);
        }

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset-urf-8");
        response.setHeader("content-type","application/octet-stream");
        response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        log.info("{} 导出成功", fileName);
    }

    public static void fillSheet(Workbook workbook, Map<String, String> titleMap, Sheet sheet, String[] titleArray, List<Map<String, Object>> dataList){
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(19);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        for(int i = 0; i < titleArray.length; i ++){
            Cell cell = titleRow.createCell(i, CellType.STRING);
            cell.setCellValue(titleMap.get(titleArray[i]));
            cell.setCellStyle(cellStyle);

            sheet.setColumnWidth(i, 20 * 256);
        }

        //数据行
        for (int i = 0; i < dataList.size(); i++) {
            Row sheetRow = sheet.createRow(i + 1);
            Map dataRow = dataList.get(i);
            for (int j = 0; j < titleArray.length; j++) {
                Object valueObject = dataRow.get(titleArray[j]);
                String valueString = valueObject == null ? "" : valueObject.toString();
                sheetRow.createCell(j, CellType.STRING).setCellValue(valueString);
            }
        }
    }

    /**
     * 下载 classPath: /model/下的excel模板文件
     * @param response HttpServletResponse
     * @param fileName 文件全名称
     * @throws IOException
     */
    public static void downloadTemplate(HttpServletResponse response, String fileName) throws IOException {
        ClassPathResource resource = new ClassPathResource("model/" + fileName);
        InputStream inputStream = resource.getInputStream();

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-type","application/octet-stream");
        response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        ServletOutputStream outputStream = response.getOutputStream();
        IoUtil.copy(inputStream, outputStream);	//Hutool
        response.flushBuffer();
        outputStream.close();
        log.info("{} 导出成功", fileName);
    }
}