首先我们先来看效果图,我这个两个sheet是不同数据。
这就是最终下载出的excel。每个 sheet的数据有自己决定是否可以重复 。
废话不多说 看代码
public void expchakan(HttpServletRequest request, HttpServletResponse response, AwAuditPointMainDTO dto) throws EncryptedDocumentException {
try {
List<AuditDraftTempDTO> listData = getListData(dto);
ResultUtils.resultzbjg(listData);
List<SystemManageDTO> colss = new ArrayList<SystemManageDTO>();
List<SystemManageDTO> colss2 = new ArrayList<SystemManageDTO>();
// 审计项目基本信息
colss.add(new SystemManageDTO("num", "序号", 2));
colss.add(new SystemManageDTO("year", "计划年度", 5));
colss.add(new SystemManageDTO("projectname", "项目名称", 10));
colss.add(new SystemManageDTO("projectcode", "审计类型", 10));
// 底稿信息
colss2.add(new SystemManageDTO("num", "序号", 2));
colss2.add(new SystemManageDTO("attention", "模型", 5));
colss2.add(new SystemManageDTO("auditpoints", "模型111", 10));
colss2.add(new SystemManageDTO("auditmethods", "模型222", 10));
String datass = new ObjectMapper().writeValueAsString(listData);
//导出 name 第一个sheet的名称 name2 为第二个sheet的名称
exportERiskresult(request, response, "审计项目基本信息", "底稿信息", colss, colss2, datass);
} catch (Exception e) {
e.printStackTrace();
}
}
下面是调用代码 exportERiskresult();
public void exportERiskresult(HttpServletRequest request, HttpServletResponse response, String name,String name2,
List<SystemManageDTO> cols, List<SystemManageDTO> colsB,String datas) {
if (name == null || cols == null || datas == null) {
return;
}
try {
List<Map<String, Object>> dataList = new ObjectMapper().readValue(datas.replaceAll
(""", "\""), new TypeReference<List<Map<String, Object>>>() {
});
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 列头的样式
HSSFCellStyle titleStyle = workbook.createCellStyle(); //Sheet样式
titleStyle.setFillForegroundColor(HSSFColor.DARK_TEAL.index); // 前景色的设定
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 填充模式
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//表名单元格样式
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.WHITE.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
HSSFFont fontHead = workbook.createFont();
fontHead.setFontName("宋体");
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体样式(粗体显示)
fontHead.setFontHeightInPoints((short) 14);//设置字体大小
headStyle.setFont(fontHead);
// 另一个字体样式
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
HSSFCellStyle oddStyle = workbook.createCellStyle();
oddStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
oddStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
oddStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
oddStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
oddStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCellStyle evenStyle = workbook.createCellStyle();
evenStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
evenStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
evenStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
evenStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
evenStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
evenStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
evenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(name);
// 生成第一个sheet
//产生表格标题行,合并第一行
HSSFRow rowOne = sheet.createRow(0);
rowOne.setHeightInPoints(38.25f);
//参数 1:行号 参数 2:起始列号 参数 3:行号 参数 4:终止列号
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cols.size() - 1);
sheet.addMergedRegion(cellRangeAddress);
HSSFCell cell = rowOne.createCell(0);
cell.setCellStyle(headStyle);
cell.setCellValue(name);
//为合并单元格设置边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
SystemManageDTO col;
Map<String, Object> data;
HSSFRow row = sheet.createRow(1);//sheet创建第二行
HSSFRow rowTwo = sheet.createRow(2);//sheet创建第三行
for (int i = 0; i < cols.size(); i++) {
col = cols.get(i);
sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
cell = row.createCell(i);//创建第一个列单元格
cell.setCellStyle(titleStyle);
cell.setCellValue(col.getTitle());
//sheet.setColumnWidth(i, col.getWidth() * 400);
sheet.setColumnWidth(i, col.getWidth() * 300 * 2);
//sheet.setColumnWidth(i, Integer.parseInt(col.get("width").toString())*40);
}
for (int i = 0; i < cols.size(); i++) {
if (i == 1 || i == 2) {
continue;
}
cell = rowTwo.createCell(i);
cell.setCellStyle(titleStyle);
}
Object value = null;
for (int i = 0; i < dataList.size(); i++) {
data = dataList.get(i);
row = sheet.createRow(i + 3);
row.setHeight((short) 320);
for (int j = 0; j < cols.size(); j++) {
col = cols.get(j);
cell = row.createCell(j);
if (col.getField().equalsIgnoreCase("num")) {
value = i + 1;
cell.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
} else {
value = data.get(col.getField());
cell.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
}
if (value != null) {
if (value instanceof Double) {
cell.setCellValue(((Double) value).doubleValue());
} else if (value instanceof Integer) {
cell.setCellValue(((Integer) value).intValue());
} else {
cell.setCellValue(value.toString());
}
}
}
}
// 生成第二个sheet
HSSFSheet sheetB = workbook.createSheet(name2);
//产生表格标题行,合并第一行
HSSFRow rowOneB = sheetB.createRow(0);
rowOneB.setHeightInPoints(38.25f);
//参数 1:行号 参数 2:起始列号 参数 3:行号 参数 4:终止列号
CellRangeAddress cellRangeAddressB = new CellRangeAddress(0, 0, 0, colsB.size() - 1);
sheetB.addMergedRegion(cellRangeAddressB);
HSSFCell cellB = rowOneB.createCell(0);
cellB.setCellStyle(headStyle);
cellB.setCellValue(name2);
//为合并单元格设置边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddressB, sheetB, workbook);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddressB, sheetB, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddressB, sheetB, workbook);
SystemManageDTO colB;
Map<String, Object> dataB;
HSSFRow rowB = sheetB.createRow(1);//sheet创建第二行
HSSFRow rowTwoB = sheetB.createRow(2);//sheet创建第三行
for (int i = 0; i < colsB.size(); i++) {
colB = colsB.get(i);
sheetB.addMergedRegion(new CellRangeAddress(1, 2, i, i));
cellB = rowB.createCell(i);//创建第一个列单元格
cellB.setCellStyle(titleStyle);
cellB.setCellValue(colB.getTitle());
//sheet.setColumnWidth(i, col.getWidth() * 400);
sheetB.setColumnWidth(i, colB.getWidth() * 300 * 2);
//sheet.setColumnWidth(i, Integer.parseInt(col.get("width").toString())*40);
}
for (int i = 0; i < colsB.size(); i++) {
if (i == 1 || i == 2) {
continue;
}
cellB = rowTwoB.createCell(i);
cellB.setCellStyle(titleStyle);
}
Object value2 = null;
for (int i = 0; i < dataList.size(); i++) {
dataB = dataList.get(i);
rowB = sheetB.createRow(i + 3);
rowB.setHeight((short) 320);
for (int j = 0; j < colsB.size(); j++) {
colB = colsB.get(j);
cellB = rowB.createCell(j);
if (colB.getField().equalsIgnoreCase("num")) {
value2 = i + 1;
cellB.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
} else {
value2 = dataB.get(colB.getField());
cellB.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
}
if (value2 != null) {
if (value2 instanceof Double) {
cellB.setCellValue(((Double) value2).doubleValue());
} else if (value2 instanceof Integer) {
cellB.setCellValue(((Integer) value2).intValue());
} else {
cellB.setCellValue(value2.toString());
}
}
}
}
String fileName = name + ".xls";
fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
response.reset();
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);//指定下载的文件名
response.setContentType("application/vnd.ms-excel");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
如果需要创建更多的sheet 只需将下面代码进行复制按照上面相应的方法进行改动即可
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(name);
// 生成第一个sheet
//产生表格标题行,合并第一行
HSSFRow rowOne = sheet.createRow(0);
rowOne.setHeightInPoints(38.25f);
//参数 1:行号 参数 2:起始列号 参数 3:行号 参数 4:终止列号
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cols.size() - 1);
sheet.addMergedRegion(cellRangeAddress);
HSSFCell cell = rowOne.createCell(0);
cell.setCellStyle(headStyle);
cell.setCellValue(name);
//为合并单元格设置边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, workbook);
SystemManageDTO col;
Map<String, Object> data;
HSSFRow row = sheet.createRow(1);//sheet创建第二行
HSSFRow rowTwo = sheet.createRow(2);//sheet创建第三行
for (int i = 0; i < cols.size(); i++) {
col = cols.get(i);
sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
cell = row.createCell(i);//创建第一个列单元格
cell.setCellStyle(titleStyle);
cell.setCellValue(col.getTitle());
//sheet.setColumnWidth(i, col.getWidth() * 400);
sheet.setColumnWidth(i, col.getWidth() * 300 * 2);
//sheet.setColumnWidth(i, Integer.parseInt(col.get("width").toString())*40);
}
for (int i = 0; i < cols.size(); i++) {
if (i == 1 || i == 2) {
continue;
}
cell = rowTwo.createCell(i);
cell.setCellStyle(titleStyle);
}
Object value = null;
for (int i = 0; i < dataList.size(); i++) {
data = dataList.get(i);
row = sheet.createRow(i + 3);
row.setHeight((short) 320);
for (int j = 0; j < cols.size(); j++) {
col = cols.get(j);
cell = row.createCell(j);
if (col.getField().equalsIgnoreCase("num")) {
value = i + 1;
cell.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
} else {
value = data.get(col.getField());
cell.setCellStyle(i % 2 == 0 ? oddStyle : evenStyle);
}
if (value != null) {
if (value instanceof Double) {
cell.setCellValue(((Double) value).doubleValue());
} else if (value instanceof Integer) {
cell.setCellValue(((Integer) value).intValue());
} else {
cell.setCellValue(value.toString());
}
}
}
}