excel多sheet导出工具类——java

excel多sheet导出工具类

1、多个sheet导出工具类：

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入、导出
 */
public class ExcelDataHandleUtil {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    private final static String SUFFIX_XLS = ".xls";
    private final static String SUFFIX_XLSX = ".xlsx";

    /**
     * 解析Excel数据
     */
    public static List<Map<String, Object>> analysisExcel(MultipartFile file, String[] keyList) throws Exception {

        // 1、用HSSFWorkbook打开或者创建“Excel文件对象”
        Workbook workbook = null;
        //获得文件名
        String fileName = file.getOriginalFilename();
        // 判断后缀
        if (fileName.endsWith(XLS)) {
            //2003
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            //2007
            workbook = new XSSFWorkbook(file.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }

        // 2、用HSSFWorkbook对象返回或者创建Sheet对象
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        List<Map<String, Object>> list = new ArrayList<>();
        // 3、用Sheet对象返回行对象，用行对象得到Cell对象
        for (int i = 1; i <= rows; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                // 4、对Cell对象读写。
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < keyList.length; j++) {
                    map.put(keyList[j], getCellValue(row.getCell(j)));
                }
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 导出excel文件2007
     */
    public static void exportExcel(HttpServletResponse response, List<Map> list, String[] keyList, String[]
            columnNameList, String fileName) throws Exception {
        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet("Sheet1");

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

//        HSSFCell cell = row.createCell(0);
        XSSFCell cell = row.createCell(0);

        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            cell = row.createCell(i + 1);
        }

        // 数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < keyList.length; j++) {
                String key = keyList[j];
                // 第四步，创建单元格，并设置值
                if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                    row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                }
            }
        }
        // 第六步,输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        // SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
        // String fileName =  df.format(new Date());// new Date()为获取当前系统时间
        response.setHeader("Content-disposition", "attachment; filename=" +
                URLEncoder.encode(fileName, "UTF-8") + SUFFIX_XLSX);
        response.setContentType("application/msexcel");
        workbook.write(output);
        output.close();
    }

    /**
     * 导出excel文件2003
     */
    public static void exportExcelXLS(HttpServletResponse response, List<Map> list, String[] keyList, String[]
            columnNameList, String fileName) throws Exception {
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

        HSSFCell cell = row.createCell(0);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            cell = row.createCell(i + 1);
        }

        // 数据
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < keyList.length; j++) {
                String key = keyList[j];
                // 第四步，创建单元格，并设置值
                if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                    row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                }
            }
        }
        // 第六步,输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
        //String fileName = df.format(new Date());// new Date()为获取当前系统时间
        response.setHeader("Content-disposition", "attachment; filename=" +
                URLEncoder.encode(fileName, "UTF-8") + SUFFIX_XLS);
        response.setContentType("application/msexcel");
        workbook.write(output);
        output.close();
    }

    /**
     * 多个sheet导出
     * 导出excel文件2003
     */
    public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
                                   String sheetTitle, List<Map<String, Object>> list, String[] keyList, String[]
                                           columnNameList) {
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
        HSSFRow row = sheet.createRow(0);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        HSSFCell cell = row.createCell(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 13);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 列头
        for (int i = 0; i < columnNameList.length; i++) {
            cell.setCellValue(columnNameList[i]);
            cell.setCellStyle(style);
            row.setHeight((short) (30 * 20));
            cell = row.createCell(i + 1);
        }

        // 数据
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                Map<String, Object> map = list.get(i);
                for (int j = 0; j < keyList.length; j++) {
                    String key = keyList[j];
                    // 第四步，创建单元格，并设置值
                    if (map.get(key) != null && StringUtils.isNotBlank(map.get(key).toString())) {
                        row.createCell(j).setCellValue(String.valueOf(map.get(key)));
                    }
                }
            }
        }
    }

    /**
     * 获得Cell内容
     *
     * @param cell 单元格
     * @return 数据
     */
    private static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("##0.####").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

}

2、工具类使用

 @ApiOperation(value = "资产中心-资产包-导出")
    @ApiResponse(code = 200, message = "返回结果")
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void export(@RequestBody AssetPackageVersionSearchDto dto, HttpServletResponse response)
            throws IOException {

        if (dto.getProjectMainId() == null) {
            throw new LogicException(500, "项目id不能为空");
        }
        // 导出查询
        AssetPackageVersionResultDto resultDto = this.assetPackageClient.queryDetail(dto).getResult();

 		// 获取枚举数据，进行枚举数据匹配
        Map<String, String> operateStatusMap = getOperateStatus();
        for (AssetUnitResultDto assetUnitResultDto : resultDto.getAssetUnitList()) {
            // 资产状态替换
            if (operateStatusMap.containsKey(assetUnitResultDto.getOperateStatus().toString())) {
                assetUnitResultDto.setOperateStatusName(
                        operateStatusMap.get(assetUnitResultDto.getOperateStatus().toString()));
            }
        }

        try {

            String s1 = JSON.toJSONString(resultDto.getAssetPackageList());
            String s2 = JSON.toJSONString(resultDto.getAssetUnitList());
            // 资产包
            List<Map<String, Object>> assetPackageList = JSONObject.parseObject(s1, new TypeReference<List<Map<String,
                    Object>>>() {
            });
            // 资产单元
            List<Map<String, Object>> assetUnitList = JSONObject.parseObject(s2, new TypeReference<List<Map<String,
                    Object>>>() {
            });

            // 资产包字段名
            String[] assetPackageKey = {"assetPackageCode", "assetPackageName", "firstFormatName", "buildingArea",
                    "selfBuildingArea", "saleableBuildingArea", "matchingArea", "governmentRepurchaseArea",
                    "otherArea", "assetLocation", "remark"};
            // 资产包表格列头-顺序需要一致
            String[] assetPackageColumnName = {"资产包编码", "资产包名称", "汇总业态", "总建筑面积(㎡)",
                    "自持建筑面积(㎡)", "可售建筑面积(㎡)", "配套面积(㎡)", "政府回购面积(㎡)",
                    "其他面积(㎡)", "资产包坐落", "备注"};

            // 资产单元字段名
            String[] assetUnitKey = {"assetUnitName", "assetUnitCode", "buildingName", "assetPackageName",
                    "assetPackageCode", "operateStatusName", "firstFormatName", "buildingFormatName",
                    "secondFormatName",
                    "buildingArea", "selfBuildingArea", "saleableBuildingArea", "matchingArea",
                    "governmentRepurchaseArea", "otherArea", "remark"};
            // 资产单元表格列头-顺序需要一致
            String[] assetUnitColumnName = {"资产单元名称", "资产单元编码", "所属楼栋", "所属资产包名称",
                    "所属资产包编码", "资产状态", "汇总业态", "建筑业态", "营销业态", "总建筑面积(㎡)",
                    "自持建筑面积(㎡)", "可售建筑面积(㎡)", "配套面积(㎡)", "政府回购面积(㎡)",
                    "其他面积(㎡)", "备注"};

            // 导出
            HSSFWorkbook workbook = new HSSFWorkbook();
            ExcelDataHandleUtil.exportExcel(workbook, 0, "资产包", assetPackageList,
                    assetPackageKey, assetPackageColumnName);
            ExcelDataHandleUtil.exportExcel(workbook, 1, "资产单元", assetUnitList,
                    assetUnitKey, assetUnitColumnName);

            // 定义文件名称
            String fileName = "资产包数据导出" + DateUtils.dateTimeNow();
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String dlfileName = URLEncoder.encode(fileName, "utf-8").replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + dlfileName + ".xls");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            throw new LogicException(500, "导出失败,请重试!");
        }

    }

    /**
     * 获取资产状态
     *
     * @return 数据
     */
    public Map<String, String> getOperateStatus() {
        DicCodeVo dicCodeVo = DicCodeVo.init()
                // 资产状态
                .addDic("assetPackageOperateStatus", DictPathConstant.ASSET_PACKAGE_OPERATE_STATUS);
        Map<String, String> map = Maps.newHashMap();
        dicCodeVo.getDic().entrySet().forEach(d -> {
            // 资产状态
            if ("assetPackageOperateStatus".equals(d.getKey())) {
                d.getValue().getChildren().forEach(v -> {
                    map.put(v.getValue(), v.getName());
                });
            }
        });
        return map;
    }