SpringBoot （导入导出）上传、下载

SpringBoot （导入导出）上传、下载

XSSFWorkbook（Excel-2003）

HSSFWorkbook（Excel-2007）

上传

   public WrapperResponse getDownData(MultipartFile file) throws IOException {
        WrapperResponse objectWrapperResponse = new WrapperResponse<>();
        List<String> mobString = new ArrayList<>();
        StringBuffer stringBuffer = new StringBuffer();
        InputStream is  =file.getInputStream() ;
        try {
            Workbook wb = null;
//             wb = new XSSFWorkbook(is);
            wb = new HSSFWorkbook(is);
            // 读取Excel里面客户的信息
            Sheet sheet = wb.getSheetAt(0);
            // 得到Excel的行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < physicalNumberOfRows; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Cell cells = row.getCell(2);
                cells.setCellType(CellType.STRING);
                if (i == 1 || i == physicalNumberOfRows) {
                    if (!String.valueOf(cells.getStringCellValue()).equals("mob")) {
                        stringBuffer.append(cells.getStringCellValue());
                        mobString.add(String.valueOf(cells.getStringCellValue()));
                    }
                } else {
                    if (!String.valueOf(cells.getStringCellValue()).equals("mob")) {
                        stringBuffer.append(",");
                        stringBuffer.append(cells.getStringCellValue());
                        mobString.add(String.valueOf(cells.getStringCellValue()));
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        objectWrapperResponse.setData(stringBuffer.toString());
        objectWrapperResponse.setMessage("下载成功!");
        return objectWrapperResponse;
    }

下载

    public void downDateText(HttpServletResponse httpServletResponse) throws MbsException {

        String downName = "批量发送短信人员手机号码登记模板.xlsx";
        String[] titles = {"name", "certno", "mob"};
        httpServletResponse.setHeader("Content-disposition", "attachment;fileName="+ downName);
        httpServletResponse.setContentType("application/vnd.ms-template;charset=UTF-8");
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("手机号码登记");
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = null;
            HSSFCellStyle headStyle = wb.createCellStyle();
            headStyle.setFillForegroundColor((short) 70);
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            HSSFDataFormat dataFormat = wb.createDataFormat();
            headStyle.setDataFormat(dataFormat.getFormat("@"));
            // 居中
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFDataFormat format = wb.createDataFormat();
            //设置单元格格式为常规
            headStyle.setDataFormat(format.getFormat("@"));
            //设置头格式(第一行)
            HSSFCellStyle columnHeadStyle = wb.createCellStyle();
            // 居中
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(headStyle);
            }
            OutputStream os = new BufferedOutputStream(httpServletResponse.getOutputStream());
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            throw new MbsException(e.getMessage());
        }


    }