【POI】常用excel操作方法-阿里云开发者社区

工具类

public class ExcelUtils {

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

    /**
     * 模板下载
     */
    public static Response  downLoad(String modelName, HttpServletResponse response) throws IOException {
        ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        org.springframework.core.io.Resource[] resources = null;
        String fileName = "";
        resources = resourcePatternResolver.getResources(CLASS_PATH + TEMPLATE + modelName);
        fileName = modelName;
        org.springframework.core.io.Resource resource = resources[0];
        URI uri = resource.getURI();
        logger.info(uri.toString());
        InputStream inputStream = null;
        OutputStream out = null;
        try {
            //根据文件在服务器的路径读取该文件转化为流
            inputStream = resource.getInputStream();
            //创建一个Buffer字符串
            byte[] buffer = new byte[1024];
            //设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType(MULTIPART_FORM_DATE);
            //设置文件头：最后一个参数是设置下载文件名（设置编码格式防止下载的文件名乱码）
            response.setHeader(CONTENT_DISPOSITION, ATTACHMENT_FILENAME+new String( fileName.getBytes(UTF_8), CHARSET_NAME ));
            out = response.getOutputStream();
            int b = 0;
            while (b != -1){
                b = inputStream.read(buffer);
                //写到输出流(out)中
                out.write(buffer,0,b);
            }
            return new Response(RespCode.DOWNLOAD_MODEL_FINISH);
        }catch (Exception e){
            e.printStackTrace();
            return new Response(RespCode.DOWNLOAD_MODEL_FAIL);
        }finally {
            try {
                inputStream.close();
                out.close();
                out.flush();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    /**
     * 文件导出
     * fieldList 需要导出字段集合
     * lists 导出数据集合  比如List<FieldDefinitionExcelVo>
     * object 导出接口对象 比如 FieldDefinitionExcelVo
     * excelName 导出报表名称
     * response 响应对象
     * @return
     */
    public static void export(List<String> fieldList,List lists,Object object,String excelName,HttpServletResponse response) {
        IExcelWriter xlsWriter = ExcelFactory.getXlsxWriter();
        try {
            Field[] declared = object.getClass().getDeclaredFields();
            Map<String, Boolean> requireMap = getRequireMap(declared, fieldList);
            Map<String, Integer> orderMap = getOrderMap(declared, fieldList);
            for (Object vo : lists) {
                commonFieldExcel(vo,requireMap,orderMap);
            }
            xlsWriter.target(response,excelName);
            xlsWriter.write(lists);
        }catch (Exception e){
            logger.error("导出失败");
            throw new BizException(RespCode.EXPORT_FAILED);
        }finally {
            xlsWriter.flush();
        }
    }

    /**
     * 获取 writeRequire 映射
     * @param declared
     * @param fieldList
     * @return
     */
    private static Map<String,Boolean> getRequireMap(Field[] declared, List<String> fieldList){
        Map<String,Boolean> require = new HashMap();
        for (Field field : declared) {
            require.put(field.getName(),fieldList.contains(field.getName()));
        }
        return require;
    }
    private static <T>T commonFieldExcel(T vo, Map<String, Boolean> requireMap, Map<String, Integer> orderMap) throws NoSuchFieldException, IllegalAccessException {
        Field[] declaredFields = vo.getClass().getDeclaredFields();
        for (Field voo : declaredFields) {
            if(requireMap.containsKey(voo.getName())){
                Boolean bool = requireMap.get(voo.getName());
                Integer order = orderMap.get(voo.getName());
                Field f = vo.getClass().getDeclaredField(voo.getName());
                ExcelField annotation = f.getAnnotation(ExcelField.class);
                if(StringUtils.isEmpty(annotation)){
                    continue;
                }
                InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                Field hField = handler.getClass().getDeclaredField(MEMBER_VALUES);
                hField.setAccessible(true);
                Map memberValues = (Map) hField.get(handler);
                memberValues.put(WRITE_REQUIRE, bool);
                memberValues.put(ORDER, order);
            }
        }
        return vo;
    }
    /**
     * 获取 order 映射
     * @param declared
     * @param fieldList
     * @return
     */
    private static Map<String,Integer> getOrderMap(Field[] declared, List<String> fieldList){
        Map<String,Integer> orderMap = new HashMap();
        for (int i = 0; i < fieldList.size(); i++) {
            orderMap.put(fieldList.get(i),i);
        }
        for (Field field : declared) {
            putMapValue(orderMap,field.getName());
        }
        return orderMap;
    }
    /**
     * 设置order值
     * @param order
     * @param value
     */
    private static void putMapValue(Map<String, Integer> order, String value){
        if(!order.containsKey(value)){
            order.put(value,ZERO);
        }
    }
}

//判断模板是否匹配
private Boolean scanModelType(Sheet sheet,String moduleName) {
        try {
            InputStream inputStream;
            inputStream = new ClassPathResource(TEMPLATE + moduleName).getInputStream();
            assert inputStream != null;
            Workbook sheets = WorkbookFactory.create(inputStream);
            Sheet model = sheets.getSheetAt(0);
            String modelType = getModelType(model);
            Row cells = sheet.getRow(1);
            for (int i = 1; i < cells.getLastCellNum(); i++) {
                String s = ExcelResolveUtils.getCellValue(cells.getCell(i));
                if (!modelType.contains(s)) {
                    return false;
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
 /**
     * 模板字段映射
     */
    private Map<String, Integer> getModelMap(Sheet sheet) {
        Map<String, Integer> map = new HashMap<String, Integer>(10);
        Row cells = sheet.getRow(1);
        for (int i = 0; i < cells.getLastCellNum(); i++) {
            String s = ExcelResolveUtils.getCellValue(cells.getCell(i));
            map.put(s.trim(), i);
        }
        return map;
    }

通用导入

Workbook workbook=null;
        try {
        workbook = WorkbookFactory.create(file.getInputStream());
        // 获取当前sheet index索引
        int activeSheetIndex;
        activeSheetIndex = workbook.getActiveSheetIndex();
        Sheet sheet = workbook.getSheetAt(activeSheetIndex);
        // 新的模板判断
        Boolean bool = Workbook workbook=null;
        try {
        workbook = WorkbookFactory.create(file.getInputStream());
        int activeSheetIndex;
        activeSheetIndex = workbook.getActiveSheetIndex();
        Sheet sheet = workbook.getSheetAt(activeSheetIndex);
        // 新的模板判断
        Boolean bool = scanModelType(sheet);
        if (!bool) {
            logger.error("批量创建失败");
            return new Response(RespCode.DOWNLOAD_MODEL_Submit);
        }
        // 判断类型 ，选择分支
        List<List<ResourceInfoResult>> fieldDefinitionList = getList(sheet, actionType);
        return new Response(fieldDefinitionList);
        } catch (Exception e) {
            logger.error("导入失败");
            throw new BizException(RespCode.UPLOAD_IN_FALSE);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    logger.error("批量创建失败：{}", e.getMessage());
                }
            }

工具类


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * Created by Administrator on 2019/5/14.
 */
public class ExcelResolveUtils {
    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 判断合并了行
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并区域
     *
     * @param sheet
     * @return
     */
    public static MergedRegion getMergedRegion(Sheet sheet, Cell firstCell) {
        MergedRegion mergedRegion = new MergedRegion();
        int row = firstCell.getRowIndex();
        int column = firstCell.getColumnIndex();
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    mergedRegion.setLength(lastColumn - firstColumn + 1);
                    mergedRegion.setWidth(lastRow - firstRow + 1);
                    return mergedRegion;
                }
            } else {
                mergedRegion.setLength(1);
                mergedRegion.setWidth(1);
            }
        }
        return mergedRegion;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     *
     * @param sheet
     * @return
     */
    public static boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {

        if (cell == null) return BLANK_SPACE;

        if (cell.getCellType() == CellType.STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == CellType.BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == CellType.FORMULA) {

            try {
                return String.valueOf(cell.getNumericCellValue());
            } catch (IllegalStateException e) {
                return String.valueOf(cell.getRichStringCellValue());
            }

        } else if (cell.getCellType() == CellType.NUMERIC) {

            return String.valueOf(cell.getNumericCellValue());

        }
        return " ";
    }

    /**
     * 解决数值类型后面带.0问题
     *
     * @param cell
     * @return
     */
    public static String getCellValueNoDouble(Cell cell) {

        if (cell == null) return BLANK_SPACE;

        if (cell.getCellType() == CellType.STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == CellType.BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == CellType.FORMULA) {

            return cell.getCellFormula();

        } else if (cell.getCellType() == CellType.NUMERIC) {
            if ((long) cell.getNumericCellValue() == cell.getNumericCellValue()) {
                return String.valueOf((long) cell.getNumericCellValue());
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        }
        return " ";
    }

    /**
     * 解决数值类型后面带.0问题
     *
     * @param cell
     * @return
     */
    public static String getCellValueNoDoubleAndFormula(Cell cell) {

        if (cell == null) return BLANK_SPACE;

        if (cell.getCellType() == CellType.STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == CellType.BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == CellType.FORMULA) {
            return  String.valueOf(cell.getNumericCellValue());

        } else if (cell.getCellType() == CellType.NUMERIC) {
            if ((long) cell.getNumericCellValue() == cell.getNumericCellValue()) {
                return String.valueOf((long) cell.getNumericCellValue());
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        }
        return " ";
    }


    /**
     * 合并单元格处理,获取合并行
     *
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    xr = lastR;
                }
            }

        }
        return xr;

    }

    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     *
     * @param listCombineCell 存放合并单元格的list
     * @param cell            需要判断的单元格
     * @param sheet           sheet
     * @return
     */
    public String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet)
            throws Exception {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for (CellRangeAddress ca : listCombineCell) {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            } else {
                cellValue = "";
            }
        }
        return cellValue;
    }

    public static boolean isContainChinese(String str) {
        Pattern p = Pattern.compile(REGEX_CHINESE);
        Matcher m = p.matcher(str);
        return m.find();
    }

    public static BigDecimal doubleToBigDecimalDefault0(Double value) {
        if (null == value) {
            return BigDecimal.ZERO;
        } else {
            return BigDecimal.valueOf(value);
        }
    }

    public static BigDecimal doubleToBigDecimalDefault1(Double value) {
        if (null == value) {
            return BigDecimal.ONE;
        } else {
            return BigDecimal.valueOf(value);
        }
    }

    public static BigDecimal doubleToBigDecimalDefaultNull(Double value) {
        if (null == value) {
            return null;
        } else {
            return BigDecimal.valueOf(value);
        }
    }


}

【POI】常用excel操作方法

通用导入

热门文章

最新文章

相关电子书