poi实现Excle导出功能SSM
poi实现Excle导出功能SSM
实现界面如下
添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
Controller层
@RequestMapping("/employeeExcel")
public class ExcelEmployeeController {
@Autowired
private EmployeesService employeesService;
@Autowired
private SysExcelInfoService sysExcelInfoService;
@RequestMapping("/export")
public void export( HttpServletRequest request, HttpServletResponse response) throws Exception {
try {
//获取数据源
List<EmployeesBean> employeesBeanList = employeesService.getListEmployee();
//导出excel
response.setHeader("Content-Disposition","attachment;filename="+new String("员工花名册表.xlsx".getBytes(),"ISO-8859-1"));
response.setContentType("application/x-excel;charset=UTF-8");
OutputStream outputStream = response.getOutputStream();
//导出
sysExcelInfoService.exportExcel(employeesBeanList,outputStream);
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Service层
public interface SysExcelInfoService {
void exportExcel(List<EmployeesBean> employeesBeanList, OutputStream outputStream) throws IOException;
}
ServiceImpl实现层
@Override
public void exportExcel(List<EmployeesBean> list, OutputStream outputStream) throws IOException {
//1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//2.创建工作表
XSSFSheet sheet = workbook.createSheet("员工花名册表");
XSSFCellStyle style = workbook.createCellStyle();
XSSFCellStyle style1 = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
XSSFFont font1 = workbook.createFont();
//边框
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
font1.setFontName("宋体");
font1.setFontHeightInPoints((short) 13);//设置字体大小
font1.setColor(HSSFColor.BLACK.index); //颜色
style1.setFont(font1);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//水平居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//3.1创建第一行及单元格
XSSFRow row1 = sheet.createRow(0);
XSSFCell cell1 = row1.createCell(0);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);//设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font.setColor(HSSFColor.BLACK.index); //颜色
style.setFont(font);
//3.单元格应用样式
cell1.setCellStyle(style);
cell1.setCellValue("员工花名册表");
//第一行居中
sheet.addMergedRegion(new CellRangeAddress(0,0,0,50));
//3.2创建第二行及单元格
XSSFRow row2 = sheet.createRow(1);
String[] row2Cell = {"部门","姓名","分类","类型","一级部门","二级部门","员工编号","所属团队","职级","用工形式",
"性别","身份证","联系电话","出身日期","年龄（岁）","入职日期","司龄","邮箱","文化程度","毕业院校",
"专业","毕业时间","工作年限","婚姻状况","政治面貌","籍贯","户籍地址","现住址","联络人（紧急）","联络电话（紧急）",
"与本人关系（紧急）","职称","专业（职称）","取得时间（职称）","执业注册","专业（执业注册）","证书编号（执业注册）","印章号","印章有效期","参保时间（社保）", "参保单位（社保）","缴费基数（元）（社保）","参保状态（社保）","参保时间（公积金）","参保单位（公积金）","缴费基数（元）（公积金）","参保状态（公积金）","档案存放地","起止时间","到期时间",
"离职日期"};
for (int j =0 ; j < row2Cell.length ; j++ ){
XSSFCell a = row2.createCell(j);
a.setCellStyle(style);
a.setCellValue(row2Cell[j]);
}
//设置列宽自适应
for (int i = 0; i < row2Cell.length; i++) {
sheet.autoSizeColumn(i);
}
//3.3创建第三行及单元格
if(list!= null && list.size()>0){
for(int i=0 ; i<list.size() ;i++){
//将下拉菜单的数字转变成对应的文字
list.get(i).setSexman(ExcelString.sexman(list.get(i).getSex()));
if (list.get(i).getClassification() != null){
list.get(i).setClassificationStr(ExcelString.classification(list.get(i).getClassification()));
}
if (list.get(i).getGenre() != null){
list.get(i).setGenreStr(ExcelString.genre(list.get(i).getGenre()));
}
if (list.get(i).getPrimarySector() != null){
list.get(i).setPrimarySectorStr(ExcelString.primarySector(list.get(i).getPrimarySector()));
}
if (list.get(i).getSecondarySector() != null){
list.get(i).setSecondarySectorStr(ExcelString.secondarySector(list.get(i).getSecondarySector()));
}
if (list.get(i).getEducation() != null){
list.get(i).setEducationStr(ExcelString.education(list.get(i).getEducation()));
}
if (list.get(i).getMarriage() != null){
list.get(i).setMarriageStr(ExcelString.marriage(list.get(i).getMarriage()));
}
if (list.get(i).getJobTitle() != null){
list.get(i).setJobTitleStr(ExcelString.jobTitle(list.get(i).getJobTitle()));
}
if (list.get(i).getPolitical() != null){
list.get(i).setPoliticalStr(ExcelString.political(list.get(i).getPolitical()));
}
if (list.get(i).getArchivesAddress() != null){
list.get(i).setArchivesAddressStr(ExcelString.archivesAddress(list.get(i).getArchivesAddress()));
}
if (list.get(i).getInsuredStatus() != null){
list.get(i).setInsuredStatusStr(ExcelString.insuredStatus(list.get(i).getInsuredStatus()));
}
if (list.get(i).getInsuredUnit() != null){
list.get(i).setInsuredUnitStr(ExcelString.insuredUnit(list.get(i).getInsuredUnit()));
}
if (list.get(i).getInsuredStatus1() != null){
list.get(i).setInsuredStatus1Str(ExcelString.insuredStatus(list.get(i).getInsuredStatus1()));
}
if (list.get(i).getInsuredUnit1() != null){
list.get(i).setInsuredUnit1Str(ExcelString.insuredUnit(list.get(i).getInsuredUnit1()));
}
if (list.get(i).getRank() != null){
list.get(i).setRankStr(ExcelString.rank(list.get(i).getRank()));
}
if (list.get(i).getForm() != null){
list.get(i).setFormStr(ExcelString.form(list.get(i).getForm()));
}
//第二行标题自适应
sheet.autoSizeColumn((short)i);
XSSFRow rowUser = sheet.createRow(i+2);
XSSFCell cell00 = rowUser.createCell(0);
cell00.setCellStyle(style1);
cell00.setCellValue(list.get(i).getTertiarySector());
XSSFCell cell01 = rowUser.createCell(1);
cell01.setCellStyle(style1);
cell01.setCellValue(list.get(i).getName());
XSSFCell cell02 = rowUser.createCell(2);
cell02.setCellStyle(style1);
cell02.setCellValue(list.get(i).getClassificationStr());
XSSFCell cell03 = rowUser.createCell(3);
cell03.setCellStyle(style1);
cell03.setCellValue(list.get(i).getGenreStr());
XSSFCell cell04 = rowUser.createCell(4);
cell04.setCellStyle(style1);
cell04.setCellValue(list.get(i).getPrimarySectorStr());
XSSFCell cell05 = rowUser.createCell(5);
cell05.setCellStyle(style1);
cell05.setCellValue(list.get(i).getSecondarySectorStr());
XSSFCell cell06 = rowUser.createCell(6);
cell06.setCellStyle(style1);
cell06.setCellValue(list.get(i).getEmployeeCode());
XSSFCell cell07 = rowUser.createCell(7);
cell07.setCellStyle(style1);
cell07.setCellValue(list.get(i).getTeamName());
XSSFCell cell08 = rowUser.createCell(8);
cell08.setCellStyle(style1);
cell08.setCellValue(list.get(i).getRankStr());
XSSFCell cell09 = rowUser.createCell(9);
cell09.setCellStyle(style1);
cell09.setCellValue(list.get(i).getFormStr());
XSSFCell cell10 = rowUser.createCell(10);
cell10.setCellStyle(style1);
cell10.setCellValue(list.get(i).getSexman());
XSSFCell cell11 = rowUser.createCell(11);
cell11.setCellStyle(style1);
cell11.setCellValue(list.get(i).getIdNumber());
XSSFCell cell12 = rowUser.createCell(12);
cell12.setCellStyle(style1);
cell12.setCellValue(list.get(i).getTel());
if (list.get(i).getBirthday() != null){
XSSFCell cell13 = rowUser.createCell(13);
cell13.setCellStyle(style1);
cell13.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getBirthday()));
}else {
XSSFCell cell13 = rowUser.createCell(13);
cell13.setCellStyle(style1);
cell13.setCellValue("");
}
if (list.get(i).getEmployeeAge() != null){
XSSFCell cell14 = rowUser.createCell(14);
cell14.setCellStyle(style1);
cell14.setCellValue(list.get(i).getEmployeeAge());
}else {
XSSFCell cell14 = rowUser.createCell(14);
cell14.setCellStyle(style1);
cell14.setCellValue("");
}
if (list.get(i).getHireDate() != null){
XSSFCell cell15 = rowUser.createCell(15);
cell15.setCellStyle(style1);
cell15.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getHireDate()));
}else {
XSSFCell cell15 = rowUser.createCell(15);
cell15.setCellStyle(style1);
cell15.setCellValue("");
}
XSSFCell cell16 = rowUser.createCell(16);
cell16.setCellStyle(style1);
cell16.setCellValue(list.get(i).getAge());
XSSFCell cell17 = rowUser.createCell(17);
cell17.setCellStyle(style1);
cell17.setCellValue(list.get(i).getEmail());
XSSFCell cell18 = rowUser.createCell(18);
cell18.setCellStyle(style1);
cell18.setCellValue(list.get(i).getEducationStr());
XSSFCell cell19 = rowUser.createCell(19);
cell19.setCellStyle(style1);
cell19.setCellValue(list.get(i).getGraduationSchool());
XSSFCell cell20 = rowUser.createCell(20);
cell20.setCellStyle(style1);
cell20.setCellValue(list.get(i).getProfessional());
if (list.get(i).getGraduationTime() != null){
XSSFCell cell21 = rowUser.createCell(21);
cell21.setCellStyle(style1);
cell21.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getGraduationTime()));
}else {
XSSFCell cell21 = rowUser.createCell(21);
cell21.setCellStyle(style1);
cell21.setCellValue("");
}
XSSFCell cell22 = rowUser.createCell(22);
cell22.setCellStyle(style1);
cell22.setCellValue(list.get(i).getWorkYear());
XSSFCell cell23 = rowUser.createCell(23);
cell23.setCellStyle(style1);
cell23.setCellValue(list.get(i).getWorkYear());
XSSFCell cell24 = rowUser.createCell(24);
cell24.setCellStyle(style1);
cell24.setCellValue(list.get(i).getPoliticalStr());
XSSFCell cell25 = rowUser.createCell(25);
cell25.setCellStyle(style1);
cell25.setCellValue(list.get(i).getBirthPlace());
XSSFCell cell26 = rowUser.createCell(26);
cell26.setCellStyle(style1);
cell26.setCellValue(list.get(i).getResidenceAddress());
XSSFCell cell27 = rowUser.createCell(27);
cell27.setCellStyle(style1);
cell27.setCellValue(list.get(i).getCurrentAddress());
XSSFCell cell28 = rowUser.createCell(28);
cell28.setCellStyle(style1);
cell28.setCellValue(list.get(i).getUrgentPeople());
XSSFCell cell29 = rowUser.createCell(29);
cell29.setCellStyle(style1);
cell29.setCellValue(list.get(i).getHomePhone());
XSSFCell cell30 = rowUser.createCell(30);
cell30.setCellStyle(style1);
cell30.setCellValue(list.get(i).getRelation());
XSSFCell cell31 = rowUser.createCell(31);
cell31.setCellStyle(style1);
cell31.setCellValue(list.get(i).getJobTitleStr());
XSSFCell cell32 = rowUser.createCell(32);
cell32.setCellStyle(style1);
cell32.setCellValue(list.get(i).getJobTitleProfession());
if (list.get(i).getJobTitleTime() != null){
XSSFCell cell33 = rowUser.createCell(33);
cell33.setCellStyle(style1);
cell33.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getJobTitleTime()));
}else {
XSSFCell cell33 = rowUser.createCell(33);
cell33.setCellStyle(style1);
cell33.setCellValue("");
}
XSSFCell cell34 = rowUser.createCell(34);
cell34.setCellStyle(style1);
cell34.setCellValue(list.get(i).getProfessionalRegistered());
XSSFCell cell35 = rowUser.createCell(35);
cell35.setCellStyle(style1);
cell35.setCellValue(list.get(i).getOccupation());
XSSFCell cell36 = rowUser.createCell(36);
cell36.setCellStyle(style1);
cell36.setCellValue(list.get(i).getProfessionalCertificate());
XSSFCell cell37 = rowUser.createCell(37);
cell37.setCellStyle(style1);
cell37.setCellValue(list.get(i).getStampNum());
if (list.get(i).getStampTime() != null){
XSSFCell cell38 = rowUser.createCell(38);
cell38.setCellStyle(style1);
cell38.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getStampTime()));
}else {
XSSFCell cell38 = rowUser.createCell(38);
cell38.setCellStyle(style1);
cell38.setCellValue("");
}
if (list.get(i).getStartTime() != null){
XSSFCell cell39 = rowUser.createCell(39);
cell39.setCellStyle(style1);
cell39.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getStartTime()));
}else {
XSSFCell cell39 = rowUser.createCell(39);
cell39.setCellStyle(style1);
cell39.setCellValue("");
}
XSSFCell cell40 = rowUser.createCell(40);
cell40.setCellStyle(style1);
cell40.setCellValue(list.get(i).getInsuredUnitStr());
if (list.get(i).getInsuredBase() != null){
XSSFCell cell41 = rowUser.createCell(41);
cell41.setCellStyle(style1);
cell41.setCellValue(list.get(i).getInsuredBase());
}else {
XSSFCell cell41 = rowUser.createCell(41);
cell41.setCellStyle(style1);
cell41.setCellValue("");
}
XSSFCell cell42 = rowUser.createCell(42);
cell42.setCellStyle(style1);
cell42.setCellValue(list.get(i).getInsuredStatusStr());
if (list.get(i).getStartTime1() != null){
XSSFCell cell43 = rowUser.createCell(43);
cell43.setCellStyle(style1);
cell43.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getStartTime1()));
}else {
XSSFCell cell43 = rowUser.createCell(43);
cell43.setCellStyle(style1);
cell43.setCellValue("");
}
XSSFCell cell44 = rowUser.createCell(44);
cell44.setCellStyle(style1);
cell44.setCellValue(list.get(i).getInsuredUnit1Str());
if (list.get(i).getInsuredBase1() != null){
XSSFCell cell45 = rowUser.createCell(45);
cell45.setCellStyle(style1);
cell45.setCellValue(list.get(i).getInsuredBase1());
}else {
XSSFCell cell45 = rowUser.createCell(45);
cell45.setCellStyle(style1);
cell45.setCellValue("");
}
XSSFCell cell46 = rowUser.createCell(46);
cell46.setCellStyle(style1);
cell46.setCellValue(list.get(i).getInsuredStatus1Str());
XSSFCell cell47 = rowUser.createCell(47);
cell47.setCellStyle(style1);
cell47.setCellValue(list.get(i).getArchivesAddressStr());
if (list.get(i).getContractStartTime() != null){
XSSFCell cell48 = rowUser.createCell(48);
cell48.setCellStyle(style1);
cell48.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getContractStartTime()));
}else {
XSSFCell cell48 = rowUser.createCell(48);
cell48.setCellStyle(style1);
cell48.setCellValue("");
}
if (list.get(i).getContractExpired() != null){
XSSFCell cell49 = rowUser.createCell(49);
cell49.setCellStyle(style1);
cell49.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getContractExpired()));
}else {
XSSFCell cell49 = rowUser.createCell(49);
cell49.setCellStyle(style1);
cell49.setCellValue("");
}
if (list.get(i).getLeaveDate() != null){
XSSFCell cell50 = rowUser.createCell(50);
cell50.setCellStyle(style1);
cell50.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getLeaveDate()));
}else {
XSSFCell cell50 = rowUser.createCell(50);
cell50.setCellStyle(style1);
cell50.setCellValue("");
//rowUser.createCell(50).setCellValue("");
}
}
}
sheet.setColumnWidth(0, 4000); //第一个参数代表列id(从0开始),第2个参数代表宽度值
//5.输出
workbook.write(outputStream);
}