Excel动态字段文件下载功能

活着z

于 2024-01-03 14:24:58 发布

阅读量418

点赞数 5

文章标签： excel windows microsoft

本文链接：https://blog.csdn.net/weixin_45818966/article/details/135362139

版权

本文介绍了如何在Java中下载Excel模板，包括查询列信息、组装表头、处理数据（时间过滤、部门字段检查）、下拉框数据管理、隐藏列设置，以及使用EasyExcel进行数据导出，同时涉及到文件导入监听类的实现。

摘要由CSDN通过智能技术生成

1.下载excel模板数据

关于没有实体类下载Excel数据的情况。

1.查询列信息

 List<TableColumns> tableColumns = tableColumnsMapper.selectByParam(tableName, null);

2.组装head

//处理表头
    private List<List<String>> composeHead(String displayName,String startDate, String endDate
            ,List<TableColumns> tableColumns) {
        List<List<String>> head = new ArrayList<>();
        List<String> infoHead = new ArrayList<>();
        infoHead.add("表单名称");
        infoHead.add("日期范围");
        infoHead.add("患者id");
        head.add(infoHead);

        String excelName = displayName + "导出模板";
        String dateRange = startDate + "——" + endDate;

            List<String> dataHeadOne = new ArrayList<>();
        dataHeadOne.add(excelName);
        dataHeadOne.add(dateRange);
        dataHeadOne.add("患者姓名");
        head.add(dataHeadOne);

        tableColumns.forEach(tableColumn->{
            List<String> dataHead = new ArrayList<>();
            dataHead.add(excelName);
            dataHead.add(dateRange);
            dataHead.add(tableColumn.getDisplayName());
            head.add(dataHead);
        });

        return head;
    }

3.处理数据

private List<List<String>> handleData(String tableName,Tables table,String startDate, String endDate
            ,List<TableColumns> tableColumns,boolean isBlank) {
        if(StringUtils.isEmpty(table.getColumnDate())){
            throw new MessageException(1000,"该数据源未设置时间过滤字段，请补全！");
        }
        if(StringUtils.isEmpty(table.getDeptCodeColumn())){
            throw new MessageException(1000,"该数据源未设置部门字段，请补全！");
        }
        List<List<String>> datas = new ArrayList<>();
        List<Map<String, Object>> mapList = tableDataMapper.selectDatasByTime(tableName, table.getDeptCodeColumn()
                ,table.getColumnDate(), startDate, endDate);
        if(isBlank) {
            mapList = mapList.stream().filter(map -> {
                for (TableColumns column : tableColumns) {
                    String columnName = column.getColumnName();
                    if (!column.getColumnVisible() || "patientid".equalsIgnoreCase(columnName) || "name".equalsIgnoreCase(columnName)) {
                        continue;
                    }
                    Object value = map.get(columnName);
                    if (null == value || value.toString().equalsIgnoreCase("")) {
                        return true;
                    }
                }
                return false;
            }).collect(Collectors.toList());
        }
        mapList.forEach(map->{
            List<String> data = new ArrayList<>();
            //患者id、患者姓名
            data.add(map.get("patientid").toString());
            data.add(null != map.get("pname")?map.get("pname").toString():"");
           
            for(int i=0;i<tableColumns.size();i++){
                TableColumns tableColumn = tableColumns.get(i);
                String value = Optional.ofNullable(map.get(tableColumn.getColumnName())).orElse("").toString();
                if(StringUtils.isNotEmpty(value)
                        && ("DATE".equals(tableColumn.getColumnType()) || "TIMESTAMP".equals(tableColumn.getColumnType()))){
                    String format = StringUtils.isEmpty(tableColumn.getColumnFormat())
                            ? "yyyy-MM-dd HH:mm:ss" : tableColumn.getColumnFormat();
                    if("yyyy-MM-dd HH24:mi:ss".equalsIgnoreCase(format) || "YYYY-MM-DD HH24:MI:SS".equalsIgnoreCase(format) || "yyyy-MM-dd HH24:mm:ss".equalsIgnoreCase(format) ){
                        format = "yyyy-MM-dd HH:mm:ss";
                    }
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
                    value = simpleDateFormat.format(((Date) map.get(tableColumn.getColumnName())));
                }
                if(StringUtils.isNotEmpty(value) && "BIGDECIMAL".equals(tableColumn.getColumnType())){

                        value = new BigDecimal(value).stripTrailingZeros().toPlainString();

                }
                data.add(getDicItemText(tableColumn.getDicCode(),value));
            }
            datas.add(data);
        });

        return datas;
    }

4.处理下拉框数据

//获取下拉框数据
    private Map<Integer, String[]> handDropDown(List<TableColumns> tableColumns) {
        Map<Integer,String[]> dropDown = new HashMap<>();
        for(int i=0;i<tableColumns.size();i++){
            TableColumns column = tableColumns.get(i);
            if(StringUtils.isEmpty(column.getDicCode())){
                continue;
            }
            String dicCode = column.getDicCode();
            dropDown.put(2+i+1,getDicText(dicCode));
        }
        return dropDown;
    }

    //获取字典所有字典项值
    private String[] getDicText(String code){
        List<String> values = new ArrayList<>();
        try {
            Dictionary dictionary = DictionaryController.instance().get(code);
            List<DictionaryItem> items = dictionary.getSlice(null, SliceTypes.ALL, null);
            for(DictionaryItem item:items){
                values.add(item.getText());
            }
        } catch (ControllerException e) {
            e.printStackTrace();
        }
        return values.toArray(new String[0]);
    }

5.设置隐藏列

 //设置隐藏列
    private List<Integer> handHideColumns(List<TableColumns> tableColumns) {
        List<Integer> result = new ArrayList<>();
        for(int i=0;i<tableColumns.size();i++){
            TableColumns column = tableColumns.get(i);
            if(column.getColumnVisible()){
                continue;
            }
            result.add(2+i+1);
        }
        return result;
    }

6.输出

//输出
        org.springframework.core.io.Resource resource = new DefaultResourceLoader().getResource("template/templateOne.xlsx");     
        String fileName = displayName + "_" + DateTime.now().toString("yyyy-MM-dd");
        try {
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.error("转化URLEncoder失败:",e);
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        try {
            EasyExcel.write(response.getOutputStream())
                    .withTemplate(resource.getInputStream())
                    .head(head)
                    .sheet(displayName+"模板")
                    .registerWriteHandler(new DataSuppleCellStyleStrategy(dropDown,null,hideColumns))//样式
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30, (short) 25))//行高
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))//列宽
                    .doWrite(datas);
        } catch (IOException e) {
            log.error("导出失败:",e);
            throw new MessageException(1000,"导出失败");
        }
    }

7.Excel数据处理类

public class DataSuppleCellStyleStrategy extends AbstractCellStyleStrategy {
    private WriteCellStyle headWriteCellStyle;
    private WriteCellStyle contentWriteCellStyle;
    private short textFormat;
    private Map<String,CellStyle> headCellStyleMap;
    private Map<String,CellStyle> contentCellStyleMap;
    private Map<Integer, String[]> map;
    private List<ExcelDataCache.RowData> errorDatas;
    private List<Integer> hideColumns;
    private Integer startRow = 8;
    private Integer endRow = 2000;

    public DataSuppleCellStyleStrategy(Map<Integer, String[]> map,List<ExcelDataCache.RowData> errorDatas
            ,List<Integer> hideColumns) {
        super();
        this.map = map;
        this.errorDatas = errorDatas;
        this.hideColumns = hideColumns;
    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        headWriteCellStyle = getHeadStyle();
        contentWriteCellStyle = getDataStyle();
        textFormat = workbook.createDataFormat().getFormat("@");//设置文本
        headCellStyleMap = new HashMap<>();
        contentCellStyleMap = new HashMap<>();
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        int cellRowNum = cell.getRow().getRowNum();
        int cellColumnIndex = cell.getColumnIndex();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        if(cellColumnIndex < 1 && cellRowNum < 7){
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            if(!headCellStyleMap.containsKey("one")){
                CellStyle cellStyle = StyleUtil.buildHeadCellStyle(cell.getSheet().getWorkbook(), headWriteCellStyle);
                headCellStyleMap.put("one",cellStyle);
            }
            cell.setCellStyle(headCellStyleMap.get("one"));
        }else {
            if(!headCellStyleMap.containsKey("two")) {
                CellStyle cellStyle = StyleUtil.buildHeadCellStyle(cell.getSheet().getWorkbook(), headWriteCellStyle);
                headCellStyleMap.put("two", cellStyle);
            }
            cell.setCellStyle(headCellStyleMap.get("two"));
        }
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        Workbook workbook = cell.getSheet().getWorkbook();
        String key = "one";
        WriteFont writeFont = null;
        int cellRowNum = cell.getRow().getRowNum();
        int cellColumnIndex = cell.getColumnIndex();
        boolean locked = cellColumnIndex <= 1;
        if(locked){
            key = "two";
        }
        if(!CollectionUtils.isEmpty(errorDatas)){
            Optional<ExcelDataCache.RowData> any = errorDatas.stream()
                    .filter(errorData -> errorData.getRow() == cellRowNum + 1 && errorData.getLine() == cellColumnIndex + 1)
                    .findAny();
            //设置错误数据单元格字体为红色
            if(any.isPresent()){
                writeFont = new WriteFont();
                writeFont.setColor(IndexedColors.RED.getIndex());
                key = "three";
                if(locked){
                    key = "four";
                }
            }
        }
        createContentCellStyle(workbook,key,writeFont,locked);
        cell.setCellStyle(contentCellStyleMap.get(key));
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        super.afterSheetCreate(writeWorkbookHolder, writeSheetHolder);
        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
            if(entry.getValue().length > 30){//限制下拉最多30个
                continue;
            }
            /***起始行、终止行、起始列、终止列**/
            CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, entry.getKey()-1
                    , entry.getKey()-1);
            /***设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            /***处理Excel兼容性问题**/
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
        //设置隐藏列
        hideColumns.forEach(hideColumn->{
            sheet.setColumnHidden(hideColumn-1,true);
        });
        //保护锁定单元格
        sheet.protectSheet("******");
    }

    private WriteCellStyle getHeadStyle(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWrapped(false);//自动换行
        headWriteCellStyle.setLocked(true);//锁定
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//中间对齐
        return headWriteCellStyle;
    }

    private WriteCellStyle getDataStyle(){
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        writeCellStyle.setLocked(false);//不锁定
        return writeCellStyle;
    }

    private void createContentCellStyle(Workbook workbook,String key,WriteFont writeFont,boolean locked){
        WriteCellStyle writeCellStyle = getDataStyle();
        writeCellStyle.setDataFormat(textFormat);
        writeCellStyle.setLocked(locked);
        if(null != writeFont) {
            writeCellStyle.setWriteFont(writeFont);
        }
        if(!contentCellStyleMap.containsKey(key)){
            CellStyle cellStyle = StyleUtil.buildContentCellStyle(workbook, writeCellStyle);
            contentCellStyleMap.put(key,cellStyle);
        }
    }
}

8.样式处理类

public class SimpleRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {
    private Short headRowHeight;
    private Short contentRowHeight;

    public SimpleRowHeightStyleStrategy(Short headRowHeight, Short contentRowHeight) {
        this.headRowHeight = headRowHeight;
        this.contentRowHeight = contentRowHeight;
    }

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        if (headRowHeight != null) {
            row.setHeightInPoints(headRowHeight);
        }
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        if (contentRowHeight != null) {
            row.setHeightInPoints(contentRowHeight);
        }
    }

}

2.文件导入

文件导入监听类




public class ExcelReadListener<T> extends AnalysisEventListener<T> {


    /**
     * 数据
     */
    private ArrayList<T> dataList = new ArrayList();

    private ArrayList<Map<String,Map<String,Object>>> dataRecordList = new ArrayList<>();

    private ArrayList<Map<String,Integer>> rowNumMapList = new ArrayList<>();
    private ArrayList<Map<String,Integer>> lineNumMapList = new ArrayList<>();
    /**
     * 解析失败信息
     */
    private ArrayList<String> convertFailList = new ArrayList();

    //
    private Map<String, TableColumns> columnsMap;

    public ExcelReadListener() {
    }

    private int count = 0;

    private Map<Integer,Object> headRow ;

    public Map<Integer, Object> getHeadRow() {
        return headRow;
    }

    /**
     * 每一条数据解析都会来调用
     *
     * @param data    single data
     * @param context excel reader
     */

    @Override
    public void invoke(T data, AnalysisContext context) {
        if(count ==0){
            Map<Integer,Object> row = (Map<Integer,Object>)data;
            String dateRange = row.get(1).toString();
            String[] date = dateRange.split("——");
            ExcelDataCache.startDate = date[0];
            ExcelDataCache.endDate = date[1];
        }else if(count == 1){
            headRow = (Map<Integer,Object>)data;
        }else{
            Map<Integer,Object> row = (Map<Integer,Object>)data;
            Iterator<Integer> iterator = row.keySet().iterator();
            Map<String,Map<String,Object>> rowData = new HashMap<>();
            while (iterator.hasNext()){
                Map<String,Object> map = new HashMap<>();
                Integer next = (Integer) iterator.next();
                Object value = row.get(next);
                String fieldName = (String)headRow.get(next);
                if(columnsMap!=null){
                    if (!columnsMap.containsKey("患者姓名")){
                        TableColumns tableColumns=new TableColumns();
                        tableColumns.setColumnName("name");
                        tableColumns.setDisplayName("患者姓名");
                        columnsMap.put("患者姓名",tableColumns);
                    }
                    TableColumns tableColumns;
                    if("患者id".equals(fieldName)){
                        tableColumns = columnsMap.values().stream()
                                .filter(v->"patientid".equalsIgnoreCase(v.getColumnName())).findAny().get();
                    }else if("患者姓名".equals(fieldName)){
                        tableColumns = columnsMap.values().stream()
                                .filter(v->"name".equalsIgnoreCase(v.getColumnName())).findAny().get();
                    }else {
                        tableColumns = columnsMap.get(fieldName);
                    }
                    if(tableColumns!=null){
                        fieldName = tableColumns.getColumnName();
                        map.put("value",value);
                        map.put("row",count + 7);
                        map.put("line",next + 1);
                        rowData.put(fieldName,map);

                    }
                }
            }
            dataRecordList.add(rowData);
        }
        dataList.add(data);
        count = count + 1;
    }

    public void setColumnsMap(Map<String, TableColumns> columnsMap){
        this.columnsMap = columnsMap;
    }
    /**
     * 所有数据读取完后执行
     *
     * @param context excel reader
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("原数据第【")
                    .append(excelDataConvertException.getRowIndex() + 1)
                    .append("】行，第【")
                    .append(excelDataConvertException.getColumnIndex() + 1)
                    .append("】列解析异常");
            convertFailList.add(stringBuffer.toString());
        }
    }


    /**
     * 获取数据
     *
     * @return
     */
    public ArrayList<T> getDataList() {
        return dataList;
    }

    /**
     * 获取数据
     *
     * @return
     */
    public ArrayList<Map<String,Map<String,Object>>> getDataRecordList() {
        return dataRecordList;
    }

    public ArrayList<Map<String, Integer>> getRowNumMapList() {
        return rowNumMapList;
    }

    public ArrayList<Map<String, Integer>> getLineNumMapList() {
        return lineNumMapList;
    }

    /**
     * 获取解析失败
     *
     * @return
     */
    public ArrayList<String> getConvertFailList() {
        return convertFailList;
    }
}