java中利用poi工具生成复杂的人员信息表并excel导出
概要
话不多说，直接上效果图
poi包
引用需要用到的包，可自行搜索依赖
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
主体代码
OutputStream outputStream = response.getOutputStream();
try {
String values = request.getParameter("values");
if(StringUtil.isNotNull(values)){
//业务数据对象
HrStaffPersonInfo info = (HrStaffPersonInfo) getServiceImp(request).findByPrimaryKey(values);
// 自定义创建excel
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("信息表1");
HSSFSheet sheet2 = wb.createSheet("信息表2");
//设置宽度（多少列）
int[] width = {13, 13, 13, 13, 13, 13, 13, 13, 13, 13};
for (int columnIndex = 0; columnIndex < 10; columnIndex++) {
sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);
sheet2.setColumnWidth(columnIndex, width[columnIndex] * 256);
}
//设置样式集合
Map<String, HSSFCellStyle> styles = addStyle(wb);
setMess(sheet,styles,info);
setMess2(sheet2,styles,info);
String fileName = info.getFdName()+"_员工信息表.xls";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
wb.write(outputStream);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
outputStream.flush();
outputStream.close();
}
下面是开始画页签数据
private void setMess(HSSFSheet sheet, Map<String, HSSFCellStyle> styles, HrStaffPersonInfo info) throws Exception {
//添加标题行
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) (50 * 20));
HSSFCell cell0 = row0.createCell(0);
cell0.setCellStyle(styles.get("title"));
String cellValues0 = "人员信息采集表";
cell0.setCellValue(cellValues0);
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 9);
sheet.addMergedRegion(cra);
//添加1-4行数据
for(int i=1;i<5;i++){
HSSFRow row = sheet.createRow(i);
setMess1Row(row,String.valueOf(i),styles,info);
}
int startH=5;
//这里是合并单元格，参数分别是（起始行，终止行，起始列，终止列）
CellRangeAddress cra1 = new CellRangeAddress(1, 4, 8, 9);
sheet.addMergedRegion(cra1);
CellRangeAddress cra2 = new CellRangeAddress(4, 4, 5, 7);
sheet.addMergedRegion(cra2);
//职业资格信息
startH = setMess1Zyzg(sheet,styles,info,startH);
//学历信息
startH = setMess1Xlxx(sheet,styles,info,startH);
//工作简历
startH = setMess1Gzjl(sheet,styles,info,startH);
}
绘制表单前四行
private void setMess1Row(HSSFRow row, String num, Map<String, HSSFCellStyle> styles, HrStaffPersonInfo info) throws Exception {
row.setHeight((short) (40 * 20));
for (int colIndex = 0; colIndex < 10; colIndex++) {
row.createCell(colIndex);
row.getCell(colIndex).setCellStyle(styles.get("header_center"));
}
//拼接第一行数据
if("1".equals(num)){
row.getCell(0).setCellValue("姓名");
row.getCell(1).setCellValue(info.getFdName()!=null?info.getFdName():"");
row.getCell(2).setCellValue("性别");
row.getCell(3).setCellValue(info.getFdSex()!=null?info.getFdSex().equals("M")?"男":"女":"");
row.getCell(4).setCellValue("民族");
row.getCell(5).setCellValue(info.getFdNation()!=null?info.getFdNation():"");
row.getCell(6).setCellValue("出生年月");
row.getCell(7).setCellValue(info.getFdDateOfBirth()!=null?DateUtil.convertDateToString(info.getFdDateOfBirth(), "yyyy-MM-dd"):"");
}else if("2".equals(num)){
row.getCell(0).setCellValue("籍贯");
row.getCell(1).setCellValue(info.getFdNativePlace()!=null?info.getFdNativePlace():"");
row.getCell(2).setCellValue("政治面貌");
row.getCell(3).setCellValue(info.getFdPoliticalLandscape()!=null?info.getFdPoliticalLandscape():"");
row.getCell(4).setCellValue("入党（团）时间");
row.getCell(5).setCellValue(info.getFdDateOfParty()!=null?DateUtil.convertDateToString(info.getFdDateOfParty(), "yyyy-MM-dd"):"");
row.getCell(6).setCellValue("出生地");
row.getCell(7).setCellValue(info.getFdHomeplace()!=null?info.getFdHomeplace():"");
}else if("3".equals(num)){
row.getCell(0).setCellValue("专业技术职务");
row.getCell(1).setCellValue(info.getFdTechnicalPosition()!=null?info.getFdTechnicalPosition():"");
row.getCell(2).setCellValue("取得时间");
row.getCell(3).setCellValue(info.getFdInspectionTime()!=null?DateUtil.convertDateToString(info.getFdInspectionTime(), "yyyy-MM-dd"):"");
row.getCell(4).setCellValue("参加工作时间");
row.getCell(5).setCellValue(info.getFdWorkTime()!=null?DateUtil.convertDateToString(info.getFdWorkTime(), "yyyy-MM-dd"):"");
row.getCell(6).setCellValue("到岗日期");
row.getCell(7).setCellValue(info.getFdEntryTime()!=null?DateUtil.convertDateToString(info.getFdEntryTime(), "yyyy-MM-dd"):"");
}else if("4".equals(num)){
row.getCell(0).setCellValue("现任职务");
row.getCell(1).setCellValue(info.getFdStaffingLevel()!=null?info.getFdStaffingLevel().getFdName():"");
row.getCell(2).setCellValue("任职时间");
row.getCell(3).setCellValue("");
row.getCell(4).setCellValue("身份证号");
row.getCell(5).setCellValue(info.getFdIdCard()!=null?info.getFdIdCard():"");
}
}
后面同样的规律放入业务数据，这里只贴一个列表
private int setMess1Gzjl(HSSFSheet sheet, Map<String, HSSFCellStyle> styles, HrStaffPersonInfo info, int startH) throws Exception {
List<HrStaffPersonExperienceBase> quaList=(List<HrStaffPersonExperienceBase>)getHrStaffPersonExperienceWorkService().getHrStaffPersonExperiences(info.getFdId());
//默认展示五行
int hang = 5;
if(quaList.size()>5){
hang=quaList.size();
}
CellRangeAddress cra = new CellRangeAddress(startH, startH+hang, 0, 0);
sheet.addMergedRegion(cra);
boolean setV = false;
if(!ArrayUtil.isEmpty(quaList)){setV=true;}
for (int hIndex = 0; hIndex < hang+1; hIndex++) {
HSSFRow row = sheet.createRow(startH);
row.setHeight((short) (40 * 20));
//列
for (int colIndex = 0; colIndex < 10; colIndex++) {
row.createCell(colIndex);
row.getCell(colIndex).setCellStyle(styles.get("header_center"));
}
if(hIndex == 0){
row.getCell(0).setCellValue("工作简历");
row.getCell(1).setCellValue("起始时间");
row.getCell(2).setCellValue("终止时间");
row.getCell(3).setCellValue("工作单位");
row.getCell(7).setCellValue("所在部门");
row.getCell(8).setCellValue("担任职务");
}
//放入值
if(setV){
if(hIndex!=0&&hIndex<=quaList.size()){
HrStaffPersonExperienceWork work = (HrStaffPersonExperienceWork)quaList.get(hIndex-1);
row.getCell(1).setCellValue(work.getFdBeginDate()!=null?DateUtil.convertDateToString(work.getFdBeginDate(), "yyyy-MM-dd"):"");
row.getCell(2).setCellValue(work.getFdEndDate()!=null?DateUtil.convertDateToString(work.getFdEndDate(), "yyyy-MM-dd"):"");
row.getCell(3).setCellValue(work.getFdCompany()!=null?work.getFdCompany():"");
row.getCell(8).setCellValue(work.getFdPosition()!=null?work.getFdPosition():"");
}
}
CellRangeAddress cra1 = new CellRangeAddress(startH, startH, 3, 6);
sheet.addMergedRegion(cra1);
CellRangeAddress cra2 = new CellRangeAddress(startH, startH, 8, 9);
sheet.addMergedRegion(cra2);
startH++;
}
return startH;
}
样式集合（只写了几个这个功能需要用到的，更多的样式可网上查阅）
private Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {
Map<String, HSSFCellStyle> styles = new HashMap();
//设置字体
HSSFFont headFont = wb.createFont();
headFont.setFontName("微软雅黑");
headFont.setFontHeightInPoints((short) 16);
headFont.setBold(true);
HSSFFont bodyFont = wb.createFont();
bodyFont.setFontName("微软雅黑");
bodyFont.setFontHeightInPoints((short) 10);
//标题行样式
HSSFCellStyle style = wb.createCellStyle();
style.setFont(headFont);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
styles.put("title", style);
//数据头居中样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
//设置字体水平和垂直居中，jar包版本比较低，新版本jar包放入的类型可自行上网搜索
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor((short) 27);
//设置边框，jar包版本比较低，新版本jar包放入的类型可自行上网搜索
style.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.index);
style.setBottomBorderColor(IndexedColors.BLACK.index);
style.setLeftBorderColor(IndexedColors.BLACK.index);
style.setRightBorderColor(IndexedColors.BLACK.index);
styles.put("header_center", style);
//数据头居右样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.RIGHT);
style.setFillForegroundColor((short) 27);
style.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.index);
style.setBottomBorderColor(IndexedColors.BLACK.index);
style.setLeftBorderColor(IndexedColors.BLACK.index);
style.setRightBorderColor(IndexedColors.BLACK.index);
styles.put("header_right", style);
return styles;
}
小结
因为这个需求给到的excel表，字段太多，且都是不同的字段，没有太多相同业务能封装代码，也可能是时间太紧迫，只用了一天半把这个需求从0实现完，导致代码多半都是业务代码拼装出来的，业务代码就不贴上来了，没太大意义
总结的话：
只需要了解poi包里的几个核心方法就行
1.设置单元格样式，包括高度，宽度，字体样式，边框样式等等
2.合并单元格，（开始行，结束行，开始列，结束列）这样的规则去合并，就可以合并成自己需要的样子了
1393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
