java导出多个sheet的,2019-09-20 java导出多个sheet的Excel表

直接看代码：

@SuppressWarnings("unchecked")

@RequestMapping(value = "/compRoomExport")

@ResponseBody

public void export( HttpSession session, HttpServletResponse response,

@RequestParam(value = "compRoomNames", required = true) String compRoomNames,

@RequestParam(value = "examCentreIds", required = true) String examCentreIds,

@RequestParam(value = "compRoomIds", required = true) String compRoomIds) {

EmSessionInfo sessionInfo = (EmSessionInfo) session.getAttribute(ConfigUtil.getSessionInfoName());

String [] compRoomNamesArr = compRoomNames.split(",");

String [] examCentreIdsArr = examCentreIds.split(",");

String [] compRoomIdsArr = compRoomIds.split(",");

String fileName = "机房考机信息表";

OutputStream out = null;

// 产生工作簿对象

HSSFWorkbook workbook = new HSSFWorkbook();

int a = compRoomNamesArr.length;

for(int i = 0 ; i

String compRoomName = compRoomNamesArr[i];

Long examCentreId = Long.parseLong(examCentreIdsArr[i]);

Long compRoomId = Long.parseLong(compRoomIdsArr[i]);

try {

// 进行转码，使其支持中文文件名

// fileName = java.net.URLEncoder.encode( fileName, );

response.setCharacterEncoding("UTF-8");

response.setContentType("application/msexcel");

response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xls");

// 产生工作表对象

HSSFSheet sheet = workbook.createSheet(compRoomName);

// 设置字体

HSSFFont headfont = workbook.createFont();

headfont.setFontName("黑体");

headfont.setFontHeightInPoints((short) 22);// 字体大小

headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

// 另一个样式

HSSFCellStyle headstyle = workbook.createCellStyle();

headstyle.setFont(headfont);

headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

headstyle.setLocked(true);

headstyle.setWrapText(true);// 自动换行

// 另一个字体样式

HSSFFont columnHeadFont = workbook.createFont();

columnHeadFont.setFontName("宋体");

columnHeadFont.setFontHeightInPoints((short) 10);

columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 列头的样式

HSSFCellStyle columnHeadStyle = workbook.createCellStyle();

columnHeadStyle.setFont(columnHeadFont);

columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

columnHeadStyle.setLocked(true);

columnHeadStyle.setWrapText(true);

columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色

columnHeadStyle.setBorderLeft((short) 1);// 边框的大小

columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色

columnHeadStyle.setBorderRight((short) 1);// 边框的大小

columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体

columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色

// 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式)

// 前景色的设定

columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);

// 背景色的设定

columnHeadStyle.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);

// 填充模式

columnHeadStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);

HSSFFont font = workbook.createFont();

font.setFontName("宋体");

font.setFontHeightInPoints((short) 10);

// 普通单元格样式

HSSFCellStyle style = workbook.createCellStyle();

style.setFont(font);

style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中

style.setWrapText(true);

style.setLeftBorderColor(HSSFColor.BLACK.index);

style.setBorderLeft((short) 1);

style.setRightBorderColor(HSSFColor.BLACK.index);

style.setBorderRight((short) 1);

style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体

style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．

style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．

// 另一个样式

HSSFCellStyle centerstyle = workbook.createCellStyle();

centerstyle.setFont(font);

centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

centerstyle.setWrapText(true);

centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);

centerstyle.setBorderLeft((short) 1);

centerstyle.setRightBorderColor(HSSFColor.BLACK.index);

centerstyle.setBorderRight((short) 1);

centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体

centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．

centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色

// 创建第一行

HSSFRow row0 = sheet.createRow(0);

// 设置行高

row0.setHeight((short) 500);

// 创建第一列

HSSFCell cell0 = row0.createCell(0);

cell0.setCellValue(new HSSFRichTextString(fileName));

cell0.setCellStyle(headstyle);

// 设置sheet页列宽 *

/* *

* 合并单元格 第一个参数：第一个单元格的行数(从0开始) 第二个参数：第二个单元格的行数(从0开始)

* 第三个参数：第一个单元格的列数(从0开始) 第四个参数：第二个单元格的列数(从0开始)*/

CellRangeAddress range = new CellRangeAddress(0, 0, 0, 4);

sheet.addMergedRegion(range);

// 创建第二行

HSSFRow row1 = sheet.createRow(1);

HSSFCell cell10 = row1.createCell(0);

cell10.setCellValue(new HSSFRichTextString("机房名称："));

cell10.setCellStyle(centerstyle);

HSSFCell cell11 = row1.createCell(1);

cell11.setCellValue(new HSSFRichTextString(compRoomName));

cell11.setCellStyle(centerstyle);

HSSFCell cell12 = row1.createCell(2);

cell12.setCellStyle(centerstyle);

HSSFCell cell13 = row1.createCell(3);

cell13.setCellStyle(centerstyle);

// 创建列头

// 这是列宽

sheet.setColumnWidth(0, 5000);

sheet.setColumnWidth(1, 5000);

sheet.setColumnWidth(2, 5000);

sheet.setColumnWidth(3, 5000);

String[] title = {"IP地址", "MAC地址", "座位号", "用途"};

HSSFRow row = sheet.createRow(2);// 创建一行

for (int j = 0; j < title.length; j++) {

HSSFCell cell = row.createCell(j);// 创建一列

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellStyle(columnHeadStyle);

cell.setCellValue(title[j]);

}

Map params = new HashMap();

params.put("examCentreId", examCentreId);

params.put("compRoomId", compRoomId);

List list = compInfoService.exportExamRoom(params);

if (CollectionUtils.isNotEmpty(list)) {

for (int k = 1; k <= list.size(); k++) {

CompInfo listVo = list.get(k - 1);

HSSFRow rows = sheet.createRow(k + 2);// 创建一行

// IP地址

HSSFCell cell03 = rows.createCell(0);// 创建一列

cell03.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell03.setCellValue(listVo.getIp());

cell03.setCellStyle(centerstyle);

// MAC地址

HSSFCell cell1 = rows.createCell(1);// 创建一列

cell1.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell1.setCellValue(listVo.getMac());

cell1.setCellStyle(centerstyle);

// 座位号

HSSFCell cell2 = rows.createCell(2);// 创建一列

cell2.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell2.setCellValue(listVo.getSeatNo());

cell2.setCellStyle(centerstyle);

// 用途

HSSFCell cell3 = rows.createCell(3);// 创建一列

cell3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cell3.setCellValue(listVo.getType() == 1?"考试机":"备用机");

cell3.setCellStyle(centerstyle);

}

}

} catch (Exception e) {

e.printStackTrace();

}

}

try {

out = response.getOutputStream();

response.setHeader("content-disposition","attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));

workbook.write(out);

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

导出结果：

image.png