页面表格导出EXCEL以及TXT

最新推荐文章于 2024-08-06 14:48:00 发布

一个暖心的IT男

最新推荐文章于 2024-08-06 14:48:00 发布

阅读量732

点赞数

本文链接：https://blog.csdn.net/weixin_37005773/article/details/86625246

版权

页面是一个table，我之前写了关于如何拿table中多条数据的文章,这里就不过多的去写了。

直接写后台处理：

controller:

EXCEL:

 public void exportExcel (HttpServletResponse response,Map totaltablestr,JSONArray storyboardstr) {
        String fileName = "串联单";
        try {
            String name = "串联单";
            String[] columnName = { "序号", "类别", "标题", "状态/内容","文字长度","总字数"};

//            List<Map<String,Object>> excelList = queryScoreCount(unitId,columnId,userId,from,to,logonUser);
//            List<Map<String,Object>> total = totalCount(storyboardstr);
//            excelList.add(total.get(0));
            int columnNumber = 6;
            // 第一步，创建一个webbook，对应一个Excel文件
            XSSFWorkbook wb = new XSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            XSSFSheet sheet = wb.createSheet(name);
            sheet.setDefaultColumnWidth(15); //统一设置列宽
            // 创建第0行 也就是标题
            XSSFRow rowTitle = sheet.createRow(0);
            rowTitle.setHeightInPoints(50);// 设备标题的高度
            // 第三步创建标题的单元格样式titleStyle以及字体样式titleFont
            XSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            titleStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            XSSFFont titleFont = wb.createFont(); // 创建字体样式
            titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            titleFont.setFontName("黑体"); // 设置字体类型
            titleFont.setFontHeightInPoints((short) 15); // 设置字体大小
            titleStyle.setFont(titleFont); // 为标题样式设置字体样式

            XSSFCell cell1 = rowTitle.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,columnNumber - 1)); // 合并列标题

            cell1.setCellValue(name); // 设置值标题
            cell1.setCellStyle(titleStyle); // 设置标题样式
            // 创建第1行 也就是表头
            XSSFRow row = sheet.createRow(1);
            row.setHeightInPoints(37);// 设置表头高度

            // 第四步，创建表头单元格样式 以及表头的字体样式
            XSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 设置自动换行
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式

            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            XSSFFont headerFont = wb.createFont(); // 创建字体样式
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
            style.setFont(headerFont); // 为标题样式设置字体样式

            // 第四.一步，创建表头的列
            for (int i = 0; i < columnNumber; i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(style);
            }

            // 第五步，创建单元格，并设置值
            for (int i = 0; i < storyboardstr.size(); i++) {
                Map map = (Map) storyboardstr.get(i);
                row = sheet.createRow(i + 2);
                // 为数据内容设置特点新单元格样式1 自动换行 上下居中
                XSSFCellStyle xssfCellStyle = wb.createCellStyle();
                xssfCellStyle.setWrapText(true);// 设置自动换行
                xssfCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式

                // 设置边框
                xssfCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
                xssfCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                xssfCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                xssfCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
                xssfCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

                XSSFCell datacell;

                for (int j = 0; j < columnNumber; j++) {
                    datacell = row.createCell(j);
                    switch (j) {
                        case 0:
                            datacell.setCellValue(map.get("sequence").toString());
                            break;
                        case 1:
                            datacell.setCellValue(map.get("sort").toString());
                            break;
                        case 2:
                            datacell.setCellValue(map.get("headline").toString());
                            break;
                        case 3:
                            datacell.setCellValue(map.get("matter").toString());
                            break;
                        case 4:
                            datacell.setCellValue(map.get("articlelength").toString());
                            break;
                        case 5:
                            datacell.setCellValue(map.get("scontext").toString());
                            break;
                    }
                    datacell.setCellStyle(xssfCellStyle);
                }
            }
            XSSFCellStyle xssfCellStyle = wb.createCellStyle();
            xssfCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            xssfCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            xssfCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            xssfCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            xssfCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            row = sheet.createRow(storyboardstr.size()+2);
            XSSFCell datacell = row.createCell(0);
            datacell.setCellValue(totaltablestr.get("name").toString());
            datacell.setCellStyle(xssfCellStyle);
            datacell = row.createCell(1);
            datacell.setCellStyle(xssfCellStyle);
            datacell = row.createCell(2);
            datacell.setCellStyle(xssfCellStyle);
            datacell = row.createCell(3);
            datacell.setCellStyle(xssfCellStyle);
            datacell = row.createCell(4);
            datacell.setCellValue(totaltablestr.get("times").toString());
            datacell.setCellStyle(xssfCellStyle);
            datacell = row.createCell(5);
            datacell.setCellValue(totaltablestr.get("totals").toString());
            datacell.setCellStyle(xssfCellStyle);

            // 第六步，将文件存到浏览器设置的下载位置
            String filename = fileName + ".xlsx";
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
            OutputStream out = response.getOutputStream();
            wb.write(out);// 将数据写出去
            out.close();
            String str = "导出" + fileName + "成功！";
            System.out.println(str);
        } catch (Exception e){
            e.printStackTrace();
            String str1 = "导出" + fileName + "失败！";
            System.out.println(str1);
        }
    }

TXT:

public void exportTxt (HttpServletResponse response,Map totaltablestr,JSONArray storyboardstr) {
    String fileName = "文稿及稿件统计.txt";
    String tab = "\t\t";
    String enter = "\r\n";
    try {
        response.setContentType("text/plain");// 一下两行关键的设置
        response.setHeader("Content-Disposition", "attachment;filename="
                .concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));// filename指定默认的名字

        OutputStreamWriter osw = new OutputStreamWriter(response.getOutputStream(), StandardCharsets.UTF_8);
        BufferedWriter bw = new BufferedWriter(osw);
        StringBuilder newStr = new StringBuilder();
        newStr.append("【序号】"+"\t").append(tab);
        newStr.append("【类别】"+"\t\t").append(tab);
        newStr.append("【标题】"+"\t\t").append(tab);
        newStr.append("【状态/内容】"+"\t\t").append(tab);
        newStr.append("【文字长度】"+"\t").append(tab);
        newStr.append("【总字数】").append(enter);
        int length = 40;

        for (int i = 0; i < storyboardstr.size(); i++) {
            Map map = (Map) storyboardstr.get(i);
            newStr.append(appendStr4Length(map.get("sequence").toString(), length));
            newStr.append(appendStr4Length(map.get("sort").toString(),length)).append(tab);
            newStr.append(appendStr4Length(map.get("headline").toString(),length)).append(tab);
            newStr.append(appendStr4Length(map.get("matter").toString(),length)).append(tab);
            newStr.append(appendStr4Length(map.get("articlelength").toString(),length)).append(tab);
            newStr.append(appendStr4Length(map.get("scontext").toString(),length)).append(enter);
        }
        newStr.append(appendStr4Length("【合计】", length)).append(tab);
        newStr.append(appendStr4Length("",length)).append(tab);
        newStr.append(appendStr4Length("",length)).append(tab);
        newStr.append(appendStr4Length("",length)).append(tab);
        newStr.append(appendStr4Length(totaltablestr.get("times").toString(),length)).append(tab);
        newStr.append(appendStr4Length(totaltablestr.get("totals").toString(),length)).append(enter);
        bw.write(newStr.toString()); //向新生的文件写入行数据
        bw.newLine();//换行

        bw.flush();
        bw.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static String appendStr4Length(String str, int length){
    if(str == null){
        str = "";
    }
    try {
        int strLen = 0;//计算原字符串所占长度,规定中文占两个,其他占一个
        for(int i = 0 ; i<str.length(); i++){
            if(isChinese(str.charAt(i))){
                strLen = strLen + 2;
            }else{
                strLen = strLen + 1;
            }
        }
        if(strLen>=length){
            return str;
        }
        int remain = length - strLen;//计算所需补充空格长度
        StringBuilder strBuilder = new StringBuilder(str);
        for(int i = 0; i< remain ; i++){
            strBuilder.append(" ");
        }
        str = strBuilder.toString();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return str;
}

private static boolean isChinese(char c) {
    Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
    return ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
            || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
            || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
            || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B
            || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
            || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS
            || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION;
}

这里excel的格式可以根据自己的需求进行更改，我这里在表格下加了单独一行的合计。不需要的可以去掉。

txt的样式也可以更改。