html导出excel数据

傷城~ 2023-02-20 13:57 5阅读 0赞

# **html导出excel数据** #

1、controller

/\*\*  
     \* 导出数据  
     \* @return  
     \* @throws IOException  
     \*/  
    @GetMapping(value = "group/exportGroup")  
    public ResponseEntity<byte\[\]> exportGroup() throws IOException \{  
        Workbook workbook = groupService.exportGroup();  
        ByteArrayOutputStream os = new ByteArrayOutputStream();  
        workbook.write(os);  
        byte\[\] bytes = os.toByteArray();  
        HttpHeaders headers = new HttpHeaders();  
        //下载显示的文件名，解决中文名称乱码问题  
        String fileName = new String("团体信息.xlsx".getBytes("UTF-8"),"iso-8859-1");  
        headers.setContentDispositionFormData("attachment", fileName);  
        return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);  
    \}

2、service

@Override  
    public Workbook exportGroup() \{  
        List<Group> groups = groupMapper.findGroups();  
  
        XSSFWorkbook workbook = new XSSFWorkbook();  
        String\[\] titles = new String\[\]\{"编号","团体名称","简称","代表色","成立时间","经纪公司","定位城市"\};  
        XSSFSheet sheet = ExcelUtils.getSheet(workbook, "团体信息", titles);  
  
        //新增数据行，并且设置单元格数据  
        int rowNum = 1;  
        for(Group group : groups)\{  
            XSSFRow row = sheet.createRow(rowNum);  
            row.createCell(0).setCellValue(group.getId());  
            row.createCell(1).setCellValue(group.getName());  
            row.createCell(2).setCellValue(group.getAbbreviation());  
            row.createCell(3).setCellValue(group.getColor());  
            String dateStr = CommonUtils.dateToString(group.getEstablishedTime(), "yyy-MM-dd");  
            row.createCell(4).setCellValue(dateStr);  
            row.createCell(5).setCellValue(group.getEconomicCompany());  
            row.createCell(6).setCellValue(group.getCity());  
            rowNum++;  
        \}  
  
        return workbook;  
    \}

3、工具包

@Slf4j
    public class ExcelUtils<T> {
    
        private HSSFWorkbook workbook;
        private HSSFSheet sheet;
        private String bDate;
        private int year;
    
    
        /** excel总行数 */
        private Integer lineNumber= 0;
        /** 成功导入个数 */
        private Integer caseCount= 0;
    
        public Integer getLineNumber() {
            return lineNumber;
        }
    
        public Integer getCaseCount() {
            return caseCount;
        }
    
        public void addCaseCount() {
            caseCount++;
        }
    
        /**
         * 创建一个Sheet
         * @param workbook
         * @param sheetName
         * @param titles
         * @return
         */
        public static XSSFSheet getSheet(XSSFWorkbook workbook, String sheetName, String... titles){
            XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sheetName);
            createTitle(workbook, sheet, titles);
            return sheet;
        }
    
    
    
        /***
         * 创建表头
         * @param workbook
         * @param sheet
         */
        private static void createTitle(XSSFWorkbook workbook, XSSFSheet sheet, String... titles) {
            //创建表头
            XSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            sheet.setColumnWidth(2, 12 * 256);
            sheet.setColumnWidth(3, 17 * 256);
            sheet.setColumnWidth(4, 17 * 256);
            sheet.setColumnWidth(5, 30 * 256);
    
            //设置为居中加粗
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setFont(font);
    
            XSSFCell cell;
            for(int i=0; i<titles.length; i++){
                cell = row.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(style);
            }
        }
    
    
       public static void main(String[] args) {
            String  name = "secfsefse.xls";
            System.out.println();
        }
    
        /**
         * excel文件转java对象
         * @param is excel输出流
         * @param fileName 文件名
         * @param excelMapper 对象映射对象
         * @param args 需要获取的行数
         * @throws IOException
         */
        public void excelToObject(InputStream is, String fileName, ExcelDeal excelMapper, int... args) throws IOException, ServiceException {
            Workbook workbook = null;
            // XSSFWorkbook
            if(".xls".equals(fileName.substring(fileName.lastIndexOf(".")))){
                workbook = new HSSFWorkbook(is);
            }else {
                workbook = new XSSFWorkbook(is);
            }
            if (workbook == null){
                log.info("上传文件格式有误！");
                return;
            }
    
            //遍历xlsx中的sheet
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
               // XSSFSheet sheet = workbook.getSheetAt(numSheet);
               Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                // 对于每个sheet，读取其中的每一行
                lineNumber = sheet.getLastRowNum();
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
    
                    List<String> curarr=new ArrayList<>();
                    for(int columnNum = 0; columnNum<args.length ; columnNum++){
                       Cell cell = row.getCell(args[columnNum]);
                        curarr.add(trimStr(getValue(cell)));
                    }
                    excelMapper.rowDealWith(curarr);
                }
            }
        }
    
        /**
         * 判断后缀为xlsx的excel文件的数据类
         * @param row
         * @return
         */
        @SuppressWarnings("deprecation")
       public static String getValue(Cell row) {
            if(row==null){
                return "---";
            }
            if (row.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(row.getBooleanCellValue());
            } else if (row.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                double cur=row.getNumericCellValue();
                long longVal = Math.round(cur);
                Object inputValue = null;
                if(Double.parseDouble(longVal + ".0") == cur){
                    inputValue = longVal;
                } else {
                    inputValue = cur;
                }
                return String.valueOf(inputValue);
            } else if(row.getCellType() == XSSFCell.CELL_TYPE_BLANK || row.getCellType() == XSSFCell.CELL_TYPE_ERROR){
                return "---";
            }
            else {
                return String.valueOf(row.getStringCellValue());
            }
        }
    
    
        /**
         * 判断后缀为xls的excel文件的数据类型
         * @param hssfCell
         * @return
         */
       @SuppressWarnings("deprecation")
        public static String getValue(HSSFCell hssfCell) {
            if(hssfCell==null){
                return "---";
            }
            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(hssfCell.getBooleanCellValue());
            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
                double cur=hssfCell.getNumericCellValue();
                long longVal = Math.round(cur);
                Object inputValue = null;
                if(Double.parseDouble(longVal + ".0") == cur)
                    inputValue = longVal;
                else
                    inputValue = cur;
                return String.valueOf(inputValue);
            } else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BLANK || hssfCell.getCellType() == hssfCell.CELL_TYPE_ERROR){
                return "---";
            }
            else {
                return String.valueOf(hssfCell.getStringCellValue());
            }
        }
    
        /**
         * 字符串修剪  去除所有空白符号 ， 问号 ， 中文空格
         * @param str
         * @return
         */
        public static String trimStr(String str){
            if(str==null){
                return null;
            }
            return str.replaceAll("[\\s\\?]", "").replace("　", "");
        }
    
    }