Excel工具类 支持复杂表头

Excel导出

1.png

import cn.com.scooper.smdp.rest.common.method.CommonMethod;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Excel工具类
 * 注意T的字段命名需要符合驼峰命名
 *
 * @author lin
 * @date 2018-06-27
 */
public class ExcelUtil<T> {

    /**
     * 时间格式
     */
    private final static String TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
    /**
     * 默认列的宽度
     */
    private final static int COLUMN_WIDTH = 24;
    /**
     * 表头字体大小
     */
    private final static int TITLE_FONT_SIZE = 11;
    /**
     * 表头单元格
     **/
    private final static int TITLE_CELL = 0;
    /**
     * 内容单元格
     **/
    private final static int CONTENT_CELL = 1;
    /**
     * 复杂表头单元格的默认宽度
     */
    private final static int COLUMN_COMPLEX_WIDTH = 6;

    /**
     * 导出只有一行表头
     * 2003版本的xls
     *
     * @param title       表格标题名
     * @param headers     表格头部标题集合
     * @param headerWords 表格头部标题的字段名
     * @param filePath    文件的绝对路径
     * @param dataSet     需要显示的数据集合
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public String exportExcel(String title, String[] headers, String[] headerWords, Collection<T> dataSet, String
            filePath) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个sheet
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(COLUMN_WIDTH);
        // 生成标题样式
        HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cellHeader;
        for (int i = 0; i < headers.length; i++) {
            cellHeader = row.createCell(i);
            cellHeader.setCellStyle(titleStyle);
            cellHeader.setCellValue(new HSSFRichTextString(headers[i]));
        }
        //填充内容
        fillContent(headerWords, dataSet, workbook, sheet, 0);
        File file = new File(filePath);
        if (!CommonMethod.isHasFolder(file)) {
            CommonMethod.createFolder(file);
        }
        //文件的路劲
        StringBuffer fileUrl = new StringBuffer();
        //文件名称
        String fileName = title + TimeUtils.getTimeStamp() + ".xls";
        fileUrl.append(filePath);
        fileUrl.append(fileName);
        String fileUrlStr = fileUrl.toString();
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(new File(fileUrlStr));
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return fileName;
    }

    /**
     * 设置样式
     **/
    private HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int cellType) {
        HSSFCellStyle style = null;
        if (cellType == TITLE_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setTopBorderColor(HSSFColor.BLACK.index);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setRightBorderColor(HSSFColor.BLACK.index);
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //垂直居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //自动换行
            style.setWrapText(true);
            // 生成标题字体
            HSSFFont font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontName("宋体");
            font.setColor(HSSFColor.WHITE.index);
            font.setFontHeightInPoints((short) TITLE_FONT_SIZE);
            // 把字体应用到当前的样式
            style.setFont(font);
        } else if (cellType == CONTENT_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.WHITE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //设置自动换行
            style.setWrapText(true);
            // 生成内容字体
            HSSFFont font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            style.setFont(font);
        }
        return style;
    }

    /**
     * 填充sheet内容
     *
     * @param headerWords       填充数据的字段
     * @param dataSet           数据集
     * @param workbook          工作上下文
     * @param sheet             sheet
     * @param contentStartIndex 从第contentStartIndex+1行开始填充数据
     */
    private void fillContent(String[] headerWords, Collection<T> dataSet, HSSFWorkbook workbook, HSSFSheet sheet,
                             Integer contentStartIndex) {
        // 内容样式
        HSSFCellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        T t;
        HSSFRichTextString richString;
        //字段名
        String fieldName;
        //get方法名称
        String getMethodName;
        //单元格
        HSSFCell cell;
        Class tCls;
        Method getMethod;
        Object value;
        //单元格的文本
        String textValue;
        HSSFRow row;
        SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);
        contentStartIndex = contentStartIndex == null ? 0 : contentStartIndex;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index + contentStartIndex);
            t = (T) it.next();
            for (int i = 0; i < headerWords.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(contentStyle);
                fieldName = headerWords[i];
                getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try {
                    tCls = t.getClass();
                    getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    value = getMethod.invoke(t, new Object[]{});
                    // 判断值的类型后进行强制类型转换
                    textValue = null;
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Float) {
                        textValue = String.valueOf((Float) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        textValue = String.valueOf((Double) value);
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    }
                    if (value instanceof Boolean) {
                        textValue = "是";
                        if (!(Boolean) value) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        textValue = sdf.format((Date) value);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        if (value != null) {
                            textValue = value.toString();
                        }
                    }
                    if (textValue != null) {
                        richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出有复杂表头的excel表格
     *
     * @param title       表名
     * @param headers     表头
     * @param headnums    表头分布
     * @param dataSet     填充数据
     * @param filePath    文件路径
     * @param headerWords 表格头部标题的字段名
     * @return
     */
    public String exportComplexExcel(String title, List<String[]> headers, List<String[]> headNums, Collection<T> dataSet,
                                     String filePath, String[] headerWords) {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个sheet
        HSSFSheet sheet = workbook.createSheet(title);
        // 生成标题样式
        HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(COLUMN_COMPLEX_WIDTH);
        //制作表头
        String[] header;
        String[] headNum;
        int length = headers.size();
        for (int i = 0; i < length; i++) {
            header = headers.get(i);
            headNum = headNums.get(i);
            HSSFRow row = sheet.createRow(i);
            HSSFCell cellHeader;
            for (int j = 0; j < header.length; j++) {
                cellHeader = row.createCell(j);
                cellHeader.setCellStyle(titleStyle);
                cellHeader.setCellValue(new HSSFRichTextString(header[j]));
            }
            // 动态合并单元格
            for (int j = 0; j < headNum.length; j++) {
                String[] temp = headNum[j].split(",");
                int startRow = Integer.parseInt(temp[0]);
                int overRow = Integer.parseInt(temp[1]);
                int startCol = Integer.parseInt(temp[2]);
                int overCol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
            }
        }
        //填充内容
        fillContent(headerWords, dataSet, workbook, sheet, headers.size() - 1);
        File file = new File(filePath);
        if (!CommonMethod.isHasFolder(file)) {
            CommonMethod.createFolder(file);
        }
        //文件的路劲
        StringBuffer fileUrl = new StringBuffer();
        //文件名称
        String fileName = title + TimeUtils.getTimeStamp() + ".xls";
        fileUrl.append(filePath);
        fileUrl.append(fileName);
        String fileUrlStr = fileUrl.toString();
        FileOutputStream fileOutputStream = null;
        try {
            //生成文件
            fileOutputStream = new FileOutputStream(new File(fileUrlStr));
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return fileName;
    }
 /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String formatCell(HSSFCell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                //日期格式的处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                return String.valueOf(cell.getNumericCellValue());
            //字符串
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

            // 公式
            case HSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();

            // 空白
            case HSSFCell.CELL_TYPE_BLANK:
                return "";
            // 布尔取值
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() + "";
            //错误类型
            case HSSFCell.CELL_TYPE_ERROR:
                return cell.getErrorCellValue() + "";
            default:
                return "";
        }
}

复杂表头数据

 //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
        String[] headers1 = {"台站信息", "台站信息", "台站信息", "台站信息", "台站信息", "台站信息", "台站信息", "台站信息",
                "台站信息", "台站信息", "地震信息", "地震信息", "地震信息", "地震信息", "地震信息", "地震信息", "地震信息",
                "测点信息", "测点信息", "记录信息", "记录信息", "记录信息", "记录信息", "记录信息", "记录信息", "记录信息",
                "记录信息", "记录信息", "记录信息", "记录信息", "记录信息"};
        //“起始行，截止行，起始列，截止列”
        String[] headnum1 = {"0,0,0,9", "0,0,10,16", "0,0,17,18", "0,0,19,30"};
        //表头2
        String[] headers2 = {"台站序号", "台站名称", "代码", "经度", "纬度", "高程", "场地类型", "观测对象", "仪器型号", "仪器编号", "地震时间",
                "地震时间", "震级", "震中位置", "震中位置", "震中地点", "震源深度", "测点位置", "震中距离", "原始事件名", "灵敏度(v/g)",
                "灵敏度(v/g)", "灵敏度(v/g)", "最大加速度(cm/s^2)", "", "", "主要频率（Hz）", "主要频率（Hz）",
                "主要频率（Hz）", "记录长度", "烈度"};
        String[] headnum2 = {"1,2,0,0", "1,2,1,1", "1,2,2,2", "1,2,3,3", "1,2,4,4", "1,2,5,5", "1,2,6,6", "1,2,7,7",
                "1,2,8,8", "1,2,9,9", "1,1,10,11", "1,2,12,12", "1,1,13,14", "1,2,15,15", "1,2,16,16", "1,2,17,17",
                "1,2,18,18", "1,2,19,19", "1,1,20,22", "1,1,23,25", "1,1,26,28", "1,2,29,29", "1,2,30,30"};
        //表头3
        String[] headers3 = {"", "", "", "", "", "", "", "", "", "", "发震时间", "发震时刻", "", "经度", "纬度", "", "", "", "",
                "", "东西", "北南", "垂直", "东西", "北南", "垂直", "东西", "北南", "垂直", "", ""};
        String[] headnum3 = {"2,2,10,10", "2,2,11,11", "2,2,13,13", "2,2,14,14", "2,2,20,20", "2,2,21,21", "2,2,22,22",
                "2,2,23,23", "2,2,24,24", "2,2,25,25", "2,2,26,26", "2,2,27,27", "2,2,28,28"};
        //表头字段
        String[] headerWords = {"stationId", "stationNameChs", "stationCode", "stationLng", "stationLat", "stationElevation",
                "placeTypeString", "placeClassify", "instrumentModel", "instrumentNumber", "seismicTimeDay", "seismicTimeClock",
                "magnitude", "lng", "lat", "locationStation", "depth", "measurePlace", "centerDistance", "eventId", "pgvEw", "pgvNs",
                "pgvV", "pgaEw", "pgaNs", "pgaV", "basicFrequencyEw", "basicFrequencyNs", "basicFrequencyV", "duration",
                "instrumentIntensity"};
        //文件的名称
        String absoluteStrFilePath = request.getServletContext().getRealPath("/").replace("rest", "web") +
                CommonConstant.FILE_TEMP_FOLDER;
        ExcelUtil<RecordVo> excelUtil = new ExcelUtil<RecordVo>();
        //表头
        List<String[]> headers = new ArrayList<>();
        headers.add(headers1);
        headers.add(headers2);
        headers.add(headers3);
        List<String[]> headnums = new ArrayList<>();
        headnums.add(headnum1);
        headnums.add(headnum2);
        headnums.add(headnum3);
        String fileName = excelUtil.exportComplexExcel("记录单", headers, headnums, result, absoluteStrFilePath, headerWords);
        relativeFileUrl = request.getContextPath().replace("rest", "web") +
                "/" + CommonConstant.FILE_TEMP_FOLDER + "/" + fileName;

excel 导入

public Integer uploadEarthquakeExcel(MultipartHttpServletRequest request) throws IOException {
        int result = 0;
        // 获取上传的文件
        if (request.getFileMap().size() > 1) {
            logger.error("上传文件数量：" + request.getFileMap().size() + ">1");
            return 0;
        }
        for (Map.Entry<String, MultipartFile> entity : request.getFileMap().entrySet()) {
            MultipartFile mf = entity.getValue();
            String fileName = mf.getOriginalFilename();
            if (!fileName.endsWith(".xls")) {
                logger.error("文件格式错误：" + fileName + "请选择.xls");
                return 0;
            }
            POIFSFileSystem fs = new POIFSFileSystem(entity.getValue().getInputStream());
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet hssfSheet = wb.getSheetAt(0);
            if (hssfSheet != null) {
                //遍历excel,从第二行开始 即 rowNum=1,逐个获取单元格的内容,然后进行格式处理,最后插入数据库
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    System.out.println(ExcelUtil.formatCell(hssfRow.getCell(0)));
                    System.out.println(ExcelUtil.formatCell(hssfRow.getCell(1)));
                }
            }
        }
        return result;
    }