导入依赖
<!--poi 03版本exel 后缀。xls xml-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<!--07版本 后缀 xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.9.9</version>
</dependency>
<!--mybaitsPlus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.4.1</version>
</dependency>
<!--数据库链接-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
yml,mapper pojo跳过
Controller
@RequestMapping("/test2")
@ResponseBody
public String test2() throws IOException {
/**创建一个工作簿*/
XSSFWorkbook workbook = new XSSFWorkbook();
/**sheet工作簿名称*/
XSSFSheet sheet = workbook.createSheet("宣城博物馆统计表");
String [] ts={"id","姓名","年龄","身份证","时间"};
List<ActivityDetails> activityDetails = activityDetailsMapper.getlist();
/**创建一个单元格 */
XSSFCell cell =null;
/**创建行*/
XSSFRow row=null;
/**设置单元格样式*/
XSSFCellStyle cellStyle = workbook.createCellStyle();
// cellStyle.setAlignment(XSSFCellSty);
// cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
/**这个for循环为标题*/
for (int i = 0; i < ts.length; i++) {
row = sheet.createRow(i);
/**创建行单元格*/
cell= row.createCell(i);
/**创建行单元格内容*/
cell.setCellValue(ts[i]);
cell.setCellStyle(cellStyle);
}
/**遍历元素,按索引取值*/
for (int i = 0; i < activityDetails.size(); i++) {
XSSFCell cell1;
/*标题单元格第0行 标题下内容第一行 */
row = sheet.createRow(i + 1);
for (int j=0;j<ts.length;j++){
/**根据 类标题 循环取List中的值 到 每个标题下的内容*/
switch (j){
case 0:
/*创建单格*/
cell1= row.createCell(j);
cell1.setCellValue(activityDetails.get(j).getId());
cell1.setCellStyle(cellStyle);
continue;
case 1:
/*创建单格*/
cell1= row.createCell(j);
/*往单元格填充 值*/
cell1.setCellValue(activityDetails.get(j).getName());
/*设置单元格格式*/
cell1.setCellStyle(cellStyle);
continue;
case 2:
/*创建单格*/
cell1= row.createCell(j);
cell1.setCellValue(activityDetails.get(j).getAge());
cell1.setCellStyle(cellStyle);
continue;
case 3:
/*创建单格*/
cell1= row.createCell(j);
cell1.setCellValue(activityDetails.get(j).getIdCard());
cell1.setCellStyle(cellStyle);
continue;
case 4:
/*创建单格*/
cell1= row.createCell(j);
Date activitySignUpTime = activityDetails.get(j).getActivitySignUpTime();
SimpleDateFormat format = new SimpleDateFormat("yyy MM dd HH:mm:ss ");
/**时间转化*/
String format1 = format.format(activitySignUpTime);
cell1.setCellValue(format1);
cell1.setCellStyle(cellStyle);
continue;
default:
}
}
}
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "宣称博物馆.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
return "string";
}