java导出Excel表格（原始方式）

大奔蛋

已于 2023-04-13 16:20:24 修改

阅读量206

点赞数

分类专栏： java 文章标签： java

于 2023-04-13 16:18:07 首次发布

本文链接：https://blog.csdn.net/weixin_45002612/article/details/130132670

版权

java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

该代码示例展示了如何使用ApachePOI库在Java中创建Excel工作簿，设置不同样式的单元格（居中、标题、右对齐、左对齐），创建表头，合并单元格，并调整列宽，最后将数据写入表格并导出为Excel文件。

摘要由CSDN通过智能技术生成

从表头开始的中间的数据不用管。

            XSSFWorkbook workbook = new XSSFWorkbook();
            //设置sheet页的名字setSheetName(第几页（起始位置是0）, 名称)
            //workbook.setSheetName(0, name);
            //设置居中样式
            XSSFSheet sheet = workbook.createSheet();
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            //设置标题的样式
            XSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setBorderBottom(BorderStyle.THIN);
            cellStyle1.setBorderLeft(BorderStyle.THIN);
            cellStyle1.setBorderRight(BorderStyle.THIN);
            cellStyle1.setBorderTop(BorderStyle.THIN);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
            XSSFFont font = workbook.createFont();
            font.setCharSet(HSSFFont.DEFAULT_CHARSET);
            font.setFontName("宋体");
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            cellStyle1.setFont(font);
            cellStyle1.setWrapText(true);
            //设置右对齐样式
            XSSFCellStyle moneyCellStyle = workbook.createCellStyle();
            moneyCellStyle.setBorderBottom(BorderStyle.THIN);
            moneyCellStyle.setBorderLeft(BorderStyle.THIN);
            moneyCellStyle.setBorderRight(BorderStyle.THIN);
            moneyCellStyle.setBorderTop(BorderStyle.THIN);
            moneyCellStyle.setAlignment(HorizontalAlignment.RIGHT);
            moneyCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置左对齐样式
            XSSFCellStyle cellStyleLeft = workbook.createCellStyle();
            cellStyleLeft.setBorderBottom(BorderStyle.THIN);
            cellStyleLeft.setBorderLeft(BorderStyle.THIN);
            cellStyleLeft.setBorderRight(BorderStyle.THIN);
            cellStyleLeft.setBorderTop(BorderStyle.THIN);
            cellStyleLeft.setAlignment(HorizontalAlignment.LEFT);
            cellStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);

            //表头
            String result = "凭证汇总";
            //标题
            String[] headNames = {"日期", "凭证字号", "摘要", "科目", "借方金额"
                    , "贷方金额","附件", "所需地区", "核算单位", "制单人", "凭证状态","期末结账"};
            //合并表头单元格
            //CellRangeAddress(起始行, 结束行, 起始列, 结束列)
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headNames.length - 1));
            h(cellStyle1, sheet, result, 0, 0, headNames.length);
            //创建第第二行（每一行只有一个XSSFROW对象，如果重复创建，后面的会覆盖前面的）
            XSSFRow head1 = sheet.createRow(1);
            for (int i = 0; i < headNames.length; i++) {
                if (i == 0 || i == 1) {
                    sheet.setColumnWidth(i, 15 * 256);
                } else if (i == 2) {
                    sheet.setColumnWidth(i, 30 * 256);
                } else if (i == 3) {
                    sheet.setColumnWidth(i, 55 * 256);
                } else if (i == 4 || i == 5) {
                    sheet.setColumnWidth(i, 15 * 256);
                } else if (i == 8) {
                    sheet.setColumnWidth(i, 25 * 256);
                } else if (i == 7) {
                    sheet.setColumnWidth(i, 30 * 256);
                } else if (i == 6) {
                    sheet.setColumnWidth(i, 5 * 256);
                }  else if (i == 9) {
                    sheet.setColumnWidth(i, 15 * 256);
                } else if (i == 10) {
                    sheet.setColumnWidth(i, 15 * 256);
                } else {
                    sheet.setColumnWidth(i, 5 * 256);
                }

                XSSFCell cell1 = head1.createCell(i);
                cell1.setCellValue(headNames[i]);
                cell1.setCellStyle(cellStyle);
            }
            int sum = 2;
            for (int j = 0; j < pingzList.size(); j++) {
                int size = pingzList.get(j).getPsList().size();
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 0, 0));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 1, 1));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 6, 6));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 7, 7));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 8, 8));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 9, 9));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 10, 10));
                sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 11, 11));
                sum += size;
            }
            for (int i = 0; i < list.size(); i++) {
                XSSFRow body = sheet.createRow(i + 2);
                Map item = list.get(i);
                Integer jzState = (Integer) item.get("jzState");
                String status = (String) item.get("status");
                String jzStateStr = "";
                String statusStr = "";
                if (!Utils.n(jzState)) {
                    switch (jzState) {
                        case 1:
                            jzStateStr = "是";
                            break;
                        case 0:
                            jzStateStr = "否";
                            break;

                    }
                }
                if (!Utils.n(status)) {
                    switch (status) {
                        case "0":
                            statusStr = "待审核";
                            break;
                        case "1":
                            statusStr = "已审核/待记账";
                            break;
                        case "2":
                            statusStr = "已记账";
                            break;
                        case "4":
                            statusStr = "驳回（审核不通过）";
                            break;
                    }
                }
                String[] bodyArr = {
                        item.get("pzDate").toString(),
                        item.get("pzh").toString(),
                        item.get("zhaiy").toString(),
                        item.get("kmName").toString(),
                        item.get("money_j").toString(),
                        item.get("money_d").toString(),
                        item.get("files").toString(),
                        item.get("areaName").toString(),
                        item.get("deptName").toString(),
                        item.get("userName").toString(),
                        statusStr,
                        jzStateStr
                };
                for (int j = 0; j < bodyArr.length; j++) {
                    XSSFCell cell = body.createCell(j);
                    cell.setCellValue(bodyArr[j]);
                    if (j == 4 || j == 5) {
                        cell.setCellStyle(moneyCellStyle);
                    } else if (j==2||j==3){
                        cell.setCellStyle(cellStyleLeft);
                    }else {
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
            //设置导出后表格的名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("凭证明细导出.xlsx", "UTF-8"));
            response.setContentType("application/vnd.openmosix-officiated.spreadsheet.sheet");
            response.setCharacterEncoding("utf-8");
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();

//设置表头的方法
public void h(XSSFCellStyle cellStyle,XSSFSheet sheet,String message ,int row ,int colum,int columLength){
        String result= message;
        XSSFRow head = sheet.createRow(row);
        for (int i = 0; i < columLength; i++) {
            XSSFCell cell = head.createCell(i);
            //sheet.setColumnWidth(0, 12 * 256);
            if (i==colum){
                cell.setCellValue(result);
                cell.setCellStyle(cellStyle);
                continue;
            }
            cell.setCellStyle(cellStyle);
        }
    }