IO-导出文件(自定义标题和头以及内容)

╰半橙微兮° 2023-10-04 18:54 27阅读 0赞

很久之前写好的工具类 现分享给大家使用(使用超简单)

首先上工具类代码(代码可自行调整)

public static int DEFAULT_COLOUMN_WIDTH = 17;
    
        static List<String> dutyList = Arrays.asList("请假", "病假", "事假", "上班", "早退", "调休", "婚假", "缺勤");
    
    
        /**
         * @param title
         * @param ja
         * @param headMap
         */
        public static ByteArrayOutputStream exportListExport(String title, JSONArray ja, Map<String, String> headMap, Map<String, Integer> lengthMap) {
            int colWidth = 0;
            // 声明一个工作薄
            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
            workbook.setCompressTempFiles(true);
            //表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = getCellStyle(workbook, titleStyle);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle = getCellStyle2(workbook, headerStyle);
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle = getCellStyle1(workbook, cellStyle);
            //带颜色的单元格样式
            CellStyle cellStyleColor = workbook.createCellStyle();
            cellStyleColor = getCellStyle4(workbook, cellStyleColor);
            // 生成一个(带标题)表格
            Sheet sheet = workbook.createSheet();
            //设置列宽
            int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                String fieldName = iter.next();
                properties[ii] = fieldName;
                headers[ii] = headMap.get(fieldName);
                int bytes = 0;
                if (lengthMap != null && lengthMap.get(fieldName) != null) {
                    bytes = lengthMap.get(fieldName);
                } else {
                    bytes = fieldName.getBytes().length;
                }
                arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                ii++;
            }
            // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Object obj : ja) {
                if (rowIndex == 65535 || rowIndex == 0) {
                    if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                    //标题行
                    Row titleRow = sheet.createRow(0);//表头 rowIndex=0
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                    //表头行
                    Row headerRow = sheet.createRow(1); //列头 rowIndex =1
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);
                    }
                    rowIndex = 2;//数据内容从 rowIndex=2开始
                }
                JSONObject jo = JSONObject.fromObject(obj);
                Row dataRow = sheet.createRow(rowIndex);
                for (int i = 0; i < properties.length; i++) {
                    String cellValue = "";
                    Cell newCell = dataRow.createCell(i);
                    Object o = jo.get(properties[i]);
                    if (o == null || "null".equals(o.toString())) cellValue = "";
                    else if (o instanceof Date) cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ").format(o);
                    else if (o instanceof Float || o instanceof Double)
                        cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                    else cellValue = o.toString();
                    String[] arr = cellValue.split(",");
                    if (arr.length == 9) {
                        cellValue = "2017" + (arr[3]) + arr[0];
                    }
                    newCell.setCellValue(cellValue);
                    try{
                        Boolean flag = true;
                        if(title.contains("项目归集")){
                            for (String s:dutyList) {
                                if(cellValue.contains(s)){
                                    flag = false;
                                    break;
                                }
                            }
                        }
                        if(flag){
                            newCell.setCellStyle(cellStyle);
                        }else {
                            newCell.setCellStyle(cellStyleColor);
                        }
                    }catch (Exception e){
                        System.out.println("The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook");
                    }
                }
                rowIndex++;
            }
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                workbook.write(os);
                workbook.dispose();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return os;
        }
        
        //导出excel
        public static HttpServletResponse download(String path, HttpServletResponse response) {
            try {
                // path是指欲下载的文件的路径。
                File file = new File(path);
                // 取得文件名。
                String filename = file.getName();
                // 以流的形式下载文件。
                InputStream fis = new BufferedInputStream(new FileInputStream(path));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                // 清空response
                response.reset();
                // 设置response的Header
    //            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.addHeader("Content-Length", "" + file.length());
                response.setHeader("Content-Disposition", "attachment;filename="
                        + new String(filename.getBytes(), "iso-8859-1"));
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                toClient.write(buffer);
                toClient.flush();
                toClient.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            return response;
        }
        
    
        //表头样式
        public static CellStyle getCellStyle(SXSSFWorkbook workbook, CellStyle titleStyle) {
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 500);
            titleFont.setFontName("微软雅黑");
            titleStyle.setFont(titleFont);
            return titleStyle;
        }
    
        //单元格样式
        public static CellStyle getCellStyle1(SXSSFWorkbook workbook, CellStyle cellStyle) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setWrapText(true);//自动换行
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellFont.setFontName("微软雅黑");
            cellFont.setFontHeightInPoints((short) 11);
            cellStyle.setFont(cellFont);
            return cellStyle;
        }
    
        //列头样式
        public static CellStyle getCellStyle2(SXSSFWorkbook workbook, CellStyle headerStyle) {
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 11);
            headerFont.setFontName("微软雅黑");
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            //分类样式
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font typeFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 11);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerFont.setFontName("微软雅黑");
            headerStyle.setFont(typeFont);
            return headerStyle;
        }
    
    
        //内容 设置颜色
        public static CellStyle getCellStyle4(SXSSFWorkbook workbook, CellStyle style){
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setWrapText(true);//自动换行
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellFont.setFontName("微软雅黑");
            cellFont.setFontHeightInPoints((short) 11);
            cellFont.setColor(Font.COLOR_RED);
            style.setFont(cellFont);
            style.setWrapText(false);//自动换行
            return style;
        }

可直接复制使用,接下来是展示怎么调用以及使用工具类方法

![077a6ab597324303b819de5451516bd6.png][]

按照这样的方法使用 就能导出一个简单的excel

![9b9de408bf324c3c9c3b24ef2416a063.png][]

前端阔以直接调用接口路径 则直接提示用户下载excel

[077a6ab597324303b819de5451516bd6.png]: https://img-blog.csdnimg.cn/077a6ab597324303b819de5451516bd6.png
[9b9de408bf324c3c9c3b24ef2416a063.png]: https://img-blog.csdnimg.cn/9b9de408bf324c3c9c3b24ef2416a063.png