上传、下载excel

╰半夏微凉° 2022-12-09 01:58 234阅读 0赞

/*****************************下载excel模板****************************************/
    @Override
        public void downloadExcel(HttpServletResponse response) { 
            String[] title = { "车站编号", "车站IP", "监视器编号", "电视墙IP", "客户端IP", "备注"};
            String[][] data = new String[1][];
            data[0] = new String[6];
            data[0][0] = "1";
            data[0][1] = "127.0.0.0";
            data[0][2] = "100";
            data[0][3] = "128.0.0.0";
            data[0][4] = "129.0.0.0";
            data[0][5] = "植物园站";
            ServletOutputStream out = null;
            Workbook hssfWorkbook = ExcelUtil.getHSSFWorkbook("modbus映射表导入模板", title, data, null);
            try { 
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("modbus映射表导入模板" + ""
                        + ".xls", "UTF-8"));
                out = response.getOutputStream();
                hssfWorkbook.write(out);
            } catch (Exception e) { 
                throw new BusinessException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), ErrorCode
                        .SYSTEM_CREATE_EXCEL_ERROR.getMessage(), e);
            }finally { 
                if(null != out){ 
                    try { 
                        out.flush();
                        out.close();
                    }catch (IOException e){ 
                        throw new BusinessException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), ErrorCode
                                .SYSTEM_CREATE_EXCEL_ERROR.getMessage(), e);
                    }
    
                }
            }
        }
    /*****************************导出excel信息****************************************/
        @Override
        public void exportStationExcel(HttpServletResponse response) { 
            String[] title = { "车站编号", "车站IP", "监视器编号", "电视墙IP", "客户端IP", "备注"};
            List<StationInfo> stationExcelDTOS= stationInfoMapper.findAll();
            String[][] data = null;
            if(!CollectionUtils.isEmpty(stationExcelDTOS)){ 
                int size = stationExcelDTOS.size();
                data = new String[size][];
                StationInfo stationExcelDTO;
                for(int j=0;j<size;j++){ 
                    stationExcelDTO= stationExcelDTOS.get(j);
                    data[j] = new String[6];
                    data[j][0] = Integer.toString(stationExcelDTO.getStationId());
                    data[j][1] = stationExcelDTO.getStationIp();
                    data[j][2] = Integer.toString(stationExcelDTO.getMonitorId());
                    data[j][3] = stationExcelDTO.getTvmsIp();
                    data[j][4] = stationExcelDTO.getModbusClientIp();
                    data[j][5] = stationExcelDTO.getDescription();
                }
            }
            
    /*****************************导入excel信息****************************************/
     @Override
        @Transactional
        public void importExcel(MultipartFile file) { 
            try { 
                List<StationInfo> stationInfos = new ArrayList<>();
                InputStream in = file.getInputStream();
                List<List<Object>> lists = ExcelUtil.getInfoListByExcel(in, file.getOriginalFilename());
                if (!CollectionUtils.isEmpty(lists)) { 
                    stationInfos = convertPoliceStation(lists);
                }
                stationInfoMapper.deleteAllInBatch();
                stationInfoMapper.saveStationInfoList(stationInfos);
            } catch (BusinessException bEx) { 
                throw bEx;
            } catch (Exception e) { 
                throw new BusinessException(ErrorCode.SYSTEM_IMPORT_EXCEL_ERROR.getCode(), ErrorCode
                        .SYSTEM_IMPORT_EXCEL_ERROR.getMessage(), e);
            }
    
        }

excel工具类

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    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.usermodel.HSSFCell;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    
    
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.MessageSource;
    import org.springframework.context.i18n.LocaleContextHolder;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.PostConstruct;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    
    
    @Component
    public class ExcelUtil { 
    
        /** * 2003- 版本的excel */
        private final static String EXCEL_2003_L = ".xls";
    
        /** * 2007- 版本的excel */
        private final static String EXCEL_2007_U = ".xlsx";
    
        private static ExcelUtil excelUtil;
    
        @Autowired
        private MessageSource messageSource;
    
        @PostConstruct
        public void init() { 
            excelUtil = this;
            excelUtil.messageSource = this.messageSource;
        }
    
        /** * 导出Excel * * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */
        public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) { 
    
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            if (wb == null) { 
                wb = new HSSFWorkbook();
            }
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            //声明列对象
            HSSFCell cell = null;
            //创建标题
            for (int i = 0; i < title.length; i++) { 
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }
            //创建内容
            if ((values == null || values.length == 0) || (values.length == 1 && values[0].length == 0)) { 
                return wb;
            }
    
            for (int i = 0; i < values.length; i++) { 
                row = sheet.createRow(i + 1);
                for (int j = 0; j < values[i].length; j++) { 
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
            return wb;
        }
    
    
        /** * 解析excel内容 * * @param in, fileName * @return java.util.List<java.util.List<java.lang.Object>> * @throws Exception */
        public static List<List<Object>> getInfoListByExcel(InputStream in, String fileName) throws Exception { 
            List<List<Object>> list = null;
    
            //创建Excel工作薄
            Workbook work = getWorkbook(in, fileName);
            if (null == work) { 
                String msg = excelUtil.messageSource.getMessage(
                        ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getMessage(), null,
                        LocaleContextHolder.getLocale());
                throw new BusinessBranchException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), msg);
            }
            try { 
                Sheet sheet = null;
                Row row = null;
                Cell cell = null;
    
                list = new ArrayList<List<Object>>();
                //遍历Excel中所有的sheet
                for (int i = 0; i < work.getNumberOfSheets(); i++) { 
                    sheet = work.getSheetAt(i);
                    int rowNum = 0;
                    if (sheet == null) { 
                        continue;
                    }
                    //读取第一行，确保所有列被读到
                    Row row0 = sheet.getRow(0);
                    //总共列数
                    int firstCellNum = row0.getFirstCellNum();//0
                    int lastCellNum = row0.getLastCellNum();//6
                    //遍历当前sheet中的所有行。sheet.getLastRowNum()返回最后一行的索引，即比行总数小1
                    for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { 
                        //读取一行
                        row = sheet.getRow(j);
                        List<Object> li = new ArrayList<>();
                        if (row == null) { 
                            continue;
                        }
                        //遍历所有的列。row.getLastCellNum()返回的是最后一列的列数，即等于总列数
                        for (int y = firstCellNum; y < lastCellNum; y++) { 
                            cell = row.getCell(y);
                            if (null != cell) { 
                                li.add(getCellValue(cell));
                            } else { 
                                li.add(null);
                            }
                        }
                        list.add(li);
                    }
                }
            } catch (Exception e) { 
                String msg = excelUtil.messageSource.getMessage(
                        ErrorCode.SYSTEM_CREATE_PARSE_LINE_ERROR.getMessage(),
                        null, LocaleContextHolder.getLocale());
                throw new BusinessException(ErrorCode.SYSTEM_CREATE_PARSE_LINE_ERROR.getCode(), msg, e);
            }
            return list;
        }
    
        /** * 描述：根据文件后缀，自适应上传文件的版本 * * @param inStr,fileName * @return * @throws Exception */
        public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception { 
            Workbook wb = null;
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if (EXCEL_2003_L.equals(fileType)) { 
                //2003-
                wb = new HSSFWorkbook(new POIFSFileSystem(inStr));
            } else if (EXCEL_2007_U.equals(fileType)) { 
                //2007+
                wb = new XSSFWorkbook(inStr);
            } else { 
                String msg = excelUtil.messageSource.getMessage(
                        ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getMessage(), null,
                        LocaleContextHolder.getLocale());
                throw new BusinessBranchException(ErrorCode.SYSTEM_CREATE_EXCEL_ERROR.getCode(), msg);
            }
            return wb;
        }
    
        /** * 描述：对表格中数值进行格式化 * * @param cell * @return */
        public static Object getCellValue(Cell cell) { 
            Object value = "";
            //格式化number String字符
            DecimalFormat df = new DecimalFormat("0");
            //日期格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
            //格式化数字
            DecimalFormat df2 = new DecimalFormat("0.00");
    
            switch (cell.getCellType()) { 
                case STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case NUMERIC:
                    if (CommonConstant.CELL_DECIMAL_FORMAT.equals(cell.getCellStyle().getDataFormatString())) { 
                        value = df.format(cell.getNumericCellValue());
                    } else if (CommonConstant.CELL_SIMPLE_DATE.equals(cell.getCellStyle().getDataFormatString())) { 
                        value = sdf.format(cell.getDateCellValue());
                    } else { 
                        value = df2.format(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case BLANK:
                    value = "";
                    break;
                default:
                    break;
            }
            if(null != value){ 
                return value.toString().replaceAll("：", ":");
            }else { 
                return value;
            }
    
        }
    
        /** * 设置生成的Excel的具体样式 * * @param workbook * @return org.apache.poi.ss.usermodel.CellStyle */
        public static CellStyle getColumnTopStyle(Workbook workbook) { 
            CellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            //设置自动换行
            cellStyle.setWrapText(false);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            return cellStyle;
        }
    }