POI表格导出多个sheet工作表
2019-01-29 本文已影响3人
菜菜___
前面两篇文章已经介绍了POI导出表格的基本使用、常见excel的导出。
使用HSSFWorkbook导出、操作excel
使用HSSFWorkbook导出一份Excel版个人信息表
这一篇文章是介绍导出一个excel创建多个sheet工作表的示例。有时候我们需要对数据归纳分类处理，因此会需要多个工作表展示，让数据更加清晰、一目了然。
下面是效果图：
一：导出表格方法
/**
* 导出学校信息，有多个工作表sheet
* @param request
* @param response
*/
public void exportExcel(HttpServletRequest request,HttpServletResponse response) {
try {
String fileName = URLDecoder.decode(request.getParameter("fileName"),"UTF-8");
response.setContentType("application/msexcel");
response.setHeader("Content-disposition",
"attachment; filename=" + new String(fileName.getBytes("UTF-8"), "8859_1") + ".xls");
HSSFWorkbook workbook = new HSSFWorkbook();
OutputStream os = response.getOutputStream();
Map<String,List<School>>resultMap = School.getJsonMap();
if(null == resultMap){
workbook.write(os);
os.close();
return;
}
createExcelSheet(workbook,"文科",resultMap);
createExcelSheet(workbook,"理科",resultMap);
os = response.getOutputStream();
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
二：生成单个工作表方法
/**
* 生成单个工作表
* @param workbook
* @param sheetName
* @param dataMap
*/
public static void createExcelSheet(HSSFWorkbook workbook , String sheetName, Map<String,List<School>> dataMap){
try {
HSSFSheet sheet = workbook.createSheet(sheetName);
int columnSize = 6;
//设置列宽
for(int i = 0;i< columnSize;i++ ){
sheet.setColumnWidth(i, 4000);
}
sheet.setColumnWidth(2, 10000);
sheet.setColumnWidth(4, 15000);
// 设置字体
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);
headstyle.setBorderLeft((short) 1);
headstyle.setLeftBorderColor(HSSFColor.BLACK.index);
headstyle.setRightBorderColor(HSSFColor.BLACK.index);
headstyle.setBottomBorderColor(HSSFColor.BLACK.index);
headstyle.setBorderBottom((short) 1);
headstyle.setBorderRight((short) 1);
// 加粗字体样式
HSSFFont columnHeadFont = workbook.createFont();
columnHeadFont.setFontName("宋体");
columnHeadFont.setFontHeightInPoints((short) 12);
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.setTopBorderColor(HSSFColor.BLACK.index);
columnHeadStyle.setBorderTop((short) 1);
columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);
columnHeadStyle.setBorderLeft((short) 1);
columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);
columnHeadStyle.setBorderRight((short) 1);
columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index);
columnHeadStyle.setBorderBottom((short) 1);
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
/**内容居中单元格样式*/
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.setTopBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderTop((short) 1);
centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderRight((short) 1);
centerstyle.setBottomBorderColor(HSSFColor.BLACK.index);
centerstyle.setBorderBottom((short) 1);
centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**内容居左单元格样式*/
HSSFCellStyle leftstyle = workbook.createCellStyle();
leftstyle.setFont(font);
leftstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
leftstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
leftstyle.setWrapText(true);
leftstyle.setLeftBorderColor(HSSFColor.BLACK.index);
leftstyle.setBorderLeft((short) 1);
leftstyle.setTopBorderColor(HSSFColor.BLACK.index);
leftstyle.setBorderTop((short) 1);
leftstyle.setRightBorderColor(HSSFColor.BLACK.index);
leftstyle.setBorderRight((short) 1);
leftstyle.setBottomBorderColor(HSSFColor.BLACK.index);
leftstyle.setBorderBottom((short) 1);
leftstyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**跨行跨列无边框线样式*/
HSSFCellStyle rowColstyle = workbook.createCellStyle();
rowColstyle.setFont(font);
rowColstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rowColstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
rowColstyle.setWrapText(true);
rowColstyle.setLeftBorderColor(HSSFColor.WHITE.index);
rowColstyle.setBorderLeft((short) 1);
rowColstyle.setTopBorderColor(HSSFColor.WHITE.index);
rowColstyle.setBorderTop((short) 1);
rowColstyle.setRightBorderColor(HSSFColor.BLACK.index);
rowColstyle.setBorderRight((short) 1);
rowColstyle.setBottomBorderColor(HSSFColor.WHITE.index);
rowColstyle.setBorderBottom((short) 1);
rowColstyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**字体红色无边框居左*/
HSSFFont redFont = workbook.createFont();
redFont.setFontName("宋体");
redFont.setFontHeightInPoints((short) 12);
redFont.setColor(HSSFColor.RED.index);
HSSFCellStyle redStyle = workbook.createCellStyle();
redStyle.setFont(redFont);
redStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
redStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
redStyle.setWrapText(true);
redStyle.setLeftBorderColor(HSSFColor.WHITE.index);
redStyle.setBorderLeft((short) 1);
redStyle.setTopBorderColor(HSSFColor.WHITE.index);
redStyle.setBorderTop((short) 1);
redStyle.setRightBorderColor(HSSFColor.BLACK.index);
redStyle.setBorderRight((short) 1);
redStyle.setBottomBorderColor(HSSFColor.WHITE.index);
redStyle.setBorderBottom((short) 1);
redStyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**字体红色有边框居中*/
HSSFCellStyle redCenterStyle = workbook.createCellStyle();
redCenterStyle.setFont(redFont);
redCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
redCenterStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
redCenterStyle.setWrapText(true);
redCenterStyle.setLeftBorderColor(HSSFColor.BLACK.index);
redCenterStyle.setBorderLeft((short) 1);
redCenterStyle.setTopBorderColor(HSSFColor.BLACK.index);
redCenterStyle.setBorderTop((short) 1);
redCenterStyle.setRightBorderColor(HSSFColor.BLACK.index);
redCenterStyle.setBorderRight((short) 1);
redCenterStyle.setBottomBorderColor(HSSFColor.BLACK.index);
redCenterStyle.setBorderBottom((short) 1);
redCenterStyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**字体红色加粗有边框居中*/
HSSFFont redBoldFont = workbook.createFont();
redBoldFont.setFontName("宋体");
redBoldFont.setFontHeightInPoints((short) 12);
redBoldFont.setColor(HSSFColor.RED.index);
redBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle redBoldStyle = workbook.createCellStyle();
redBoldStyle.setFont(redBoldFont);
redBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
redBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
redBoldStyle.setWrapText(true);
redBoldStyle.setLeftBorderColor(HSSFColor.BLACK.index);
redBoldStyle.setBorderLeft((short) 1);
redBoldStyle.setTopBorderColor(HSSFColor.BLACK.index);
redBoldStyle.setBorderTop((short) 1);
redBoldStyle.setRightBorderColor(HSSFColor.BLACK.index);
redBoldStyle.setBorderRight((short) 1);
redBoldStyle.setBottomBorderColor(HSSFColor.BLACK.index);
redBoldStyle.setBorderBottom((short) 1);
redBoldStyle.setFillForegroundColor(HSSFColor.WHITE.index);
/**创建标题*/
HSSFRow row0 = sheet.createRow(0);
/**设置行高*/
row0.setHeight((short) 900);
HSSFCell cell = row0.createCell(0);
cell.setCellValue(new HSSFRichTextString("*******Excel标题*******"));
cell.setCellStyle(headstyle);
for(int i = 1;i< columnSize;i++ ){
cell = row0.createCell(i);
cell.setCellStyle(headstyle);
}
CellRangeAddress range = new CellRangeAddress(0, 0, 0,5);
sheet.addMergedRegion(range);
/**创建第二行*/
HSSFRow row1 = sheet.createRow(1);
cell = row1.createCell(0);
cell.setCellValue(new HSSFRichTextString("说明文字:"));
row1.setHeight((short) 400);
cell.setCellStyle(rowColstyle);
for(int i = 1;i< columnSize;i++ ){
cell = row1.createCell(i);
cell.setCellStyle(rowColstyle);
}
range = new CellRangeAddress(1, 1, 0,5);
sheet.addMergedRegion(range);
/**创建第三行*/
HSSFRow row2 = sheet.createRow(2);
cell = row2.createCell(0);
cell.setCellValue(new HSSFRichTextString("1、说明文字。"));
row2.setHeight((short) 400);
cell.setCellStyle(rowColstyle);
for(int i = 1;i< columnSize;i++ ){
cell = row2.createCell(i);
cell.setCellStyle(rowColstyle);
}
range = new CellRangeAddress(2, 2, 0,5);
sheet.addMergedRegion(range);
/**创建第四行*/
HSSFRow row3 = sheet.createRow(3);
cell = row3.createCell(0);
cell.setCellValue(new HSSFRichTextString("2、特殊提醒"));
row3.setHeight((short) 400);
cell.setCellStyle(redStyle);
for(int i = 1;i< columnSize;i++ ){
cell = row3.createCell(i);
cell.setCellStyle(redStyle);
}
range = new CellRangeAddress(3, 3, 0,5);
sheet.addMergedRegion(range);
/**创建第五行*/
HSSFRow row4 = sheet.createRow(4);
cell = row4.createCell(0);
cell.setCellValue(new HSSFRichTextString("3、用户协议"));
row4.setHeight((short) 400);
cell.setCellStyle(rowColstyle);
for(int i = 1;i< columnSize;i++ ){
cell = row4.createCell(i);
cell.setCellStyle(rowColstyle);
}
range = new CellRangeAddress(4, 4, 0,5);
sheet.addMergedRegion(range);
/**第六行开始创建批次数据*/
int rowNo = 5;
int rowFlag = 0;
for(Map.Entry<String,List<School>> map:dataMap.entrySet()){
String batName = map.getKey();
HSSFRow row5 = sheet.createRow(rowNo++);
cell = row5.createCell(0);
cell.setCellValue(new HSSFRichTextString(batName));
row5.setHeight((short) 700);
cell.setCellStyle(columnHeadStyle);
for(int i = 1;i< columnSize;i++ ){
cell = row5.createCell(i);
cell.setCellStyle(columnHeadStyle);
}
range = new CellRangeAddress(rowNo-1, rowNo-1, 0,5);
sheet.addMergedRegion(range);
/**志愿表信息列标题*/
HSSFRow row6 = sheet.createRow(rowNo++);
row6.setHeight((short) 700);
cell = row6.createCell(0);
cell.setCellValue(new HSSFRichTextString("序号"));
cell.setCellStyle(columnHeadStyle);
cell = row6.createCell(1);
cell.setCellValue(new HSSFRichTextString("学校代码"));
cell.setCellStyle(columnHeadStyle);
cell = row6.createCell(2);
cell.setCellValue(new HSSFRichTextString("学校名称"));
cell.setCellStyle(columnHeadStyle);
cell = row6.createCell(3);
cell.setCellValue(new HSSFRichTextString("年份"));
cell.setCellStyle(columnHeadStyle);
cell = row6.createCell(4);
cell.setCellValue(new HSSFRichTextString("录取分数"));
cell.setCellStyle(columnHeadStyle);
cell = row6.createCell(5);
cell.setCellValue(new HSSFRichTextString("是否确认"));
cell.setCellStyle(redBoldStyle);
Map<String,List<School>> scoreMap = new LinkedHashMap<>();
for(School detail:map.getValue()){
String schoolCode = detail.getCode();
List<School> scoreList = new ArrayList<>();
if(scoreMap.containsKey(schoolCode)){
scoreList = scoreMap.get(schoolCode);
}
scoreList.add(detail);
scoreMap.put(schoolCode,scoreList);
}
int r = 0;
for(Map.Entry<String,List<School>> s:scoreMap.entrySet()){
r++;
for(School detail:s.getValue()){
/**志愿表信息列*/
HSSFRow row7 = sheet.createRow(rowNo++);
row7.setHeight((short) 700);
cell = row7.createCell(0);
cell.setCellValue(r);
cell.setCellStyle(centerstyle);
cell = row7.createCell(1);
cell.setCellValue(detail.getCode());
cell.setCellStyle(centerstyle);
cell = row7.createCell(2);
cell.setCellValue(detail.getName());
cell.setCellStyle(centerstyle);
cell = row7.createCell(3);
cell.setCellValue(detail.getYear());
cell.setCellStyle(centerstyle);
cell = row7.createCell(4);
cell.setCellValue(detail.getScore());
cell.setCellStyle(leftstyle);
cell = row7.createCell(5);
cell.setCellValue(1 == detail.getIs_confirm()?"是":"否");
cell.setCellStyle(redCenterStyle);
}
/**合并单元格*/
range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 0, 0);
sheet.addMergedRegion(range);
range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 1, 1);
sheet.addMergedRegion(range);
range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 2, 2);
sheet.addMergedRegion(range);
range = new CellRangeAddress(rowNo-s.getValue().size(), rowNo-1, 5, 5);
sheet.addMergedRegion(range);
}
/**创建空行*/
if(dataMap.size() > ++rowFlag){
HSSFRow row8 = sheet.createRow(rowNo++);
cell = row8.createCell(0);
row8.setHeight((short) 700);
cell.setCellStyle(centerstyle);
for(int i = 1;i< columnSize;i++ ){
cell = row8.createCell(i);
cell.setCellStyle(centerstyle);
}
range = new CellRangeAddress(rowNo-1, rowNo-1, 0,5);
sheet.addMergedRegion(range);
}
}
}catch (Exception e){
e.printStackTrace();
}
}
三：导出的数据在实际开发中一般是数据库查询出来的数据，这个示例是学校分数线的测试数据，因此创建了一个枚举类来存放我们的测试数据，方便我们在导出数据的时候访问对象。
School枚举类如下：
package com.qiuxuebao.app.controller.expert.bds;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* Created by hs011 on 2019/1/26.
*/
public enum School {
data1("01","北京大学",1,"本科一批",2016,"最低分：666",1),
data2("01","北京大学",1,"本科一批",2017,"最低分：665",1),
data3("01","北京大学",1,"本科一批",2018,"最低分：664",1),
data4("02","清华大学",1,"本科一批",2016,"平均分：680",0),
data5("02","清华大学",1,"本科一批",2017,"平均分：682",0),
data6("02","清华大学",1,"本科一批",2018,"平均分：681",0),
data7("03","软件学院",2,"本科二批",2016,"最高分：506",1),
data8("03","软件学院",2,"本科二批",2017,"最高分：488",1),
data9("03","软件学院",2,"本科二批",2018,"最高分：489",1);
/**学校编码*/
private String code;
/**学校名称*/
private String name;
/**学校批次*/
private int bat;
/**批次名称*/
private String batName;
/**年份*/
private int year;
/**录取分数*/
private String score;
/**是否确认*/
private int is_confirm;
private School(String code,String name,int bat,String batName,int year,String score,int is_confirm) {
this.code = code;
this.name = name;
this.bat = bat;
this.batName = batName;
this.year = year;
this.score = score;
this.is_confirm = is_confirm;
}
/**
* 获取所有的学校
* @return
*/
public static HashMap<String, List<School>> getJsonMap() {
HashMap<String, List<School>> jsonMap = new HashMap<String, List<School>>();
for (School school : School.values()) {
if(jsonMap.containsKey(school.getBatName())){
jsonMap.get(school.getBatName()).add(school);
}else{
List<School> list = new ArrayList<>();
list.add(school);
jsonMap.put(school.getBatName(),list);
}
}
return jsonMap;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getBat() {
return bat;
}
public void setBat(int bat) {
this.bat = bat;
}
public int getYear() {
return year;
}
public void setYear(int year) {
this.year = year;
}
public String getScore() {
return score;
}
public void setScore(String score) {
this.score = score;
}
public int getIs_confirm() {
return is_confirm;
}
public void setIs_confirm(int is_confirm) {
this.is_confirm = is_confirm;
}
public String getBatName() {
return batName;
}
public void setBatName(String batName) {
this.batName = batName;
}
}
四：前端请求导出excel的方法：
function exportExcel(){
var fileName = "学校分数线";
window.location.href = encodeURI("exportExcel?fileName="+encodeURIComponent(fileName));
}
原文作者技术博客：https://www.jianshu.com/u/ac4daaeecdfe
95后前端妹子一枚，爱阅读，爱交友，将工作中遇到的问题记录在这里，希望给每一个看到的你能带来一点帮助。
欢迎留言交流。
