pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
导出Excel
public void export(List<Demo> query,String sheetName,HttpServletResponse response){
//导出数据为空,直接返回
if (query.size() == 0) {
return;
}
//获取导出数据的总条数
int countColumnNum = query.size();
//创建XSSFWorkbook文件对象
XSSFWorkbook book = null;
book = new XSSFWorkbook();
//创建一个Name的新
XSSFSheet sheet = book.createSheet(sheetName);
// 获取表的第一行
XSSFRow firstRow = sheet.createRow(0);
//创建表的第一行的每列的说明
String[] options = {"编号","姓名"};
XSSFCell[] firstCells = new XSSFCell[options.length];
//给表的第一行的每一列赋值
for (int j = 0; j < options.length; j++) {
firstCells[j] = firstRow.createCell(j);
firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
}
//把表的第一列写好后,接下来从表的第二列开始把对应的值写入到文件里
for (int i = 0; i < countColumnNum; i++) {
//给execl创建一行
XSSFRow row = sheet.createRow(i + 1);
//获取集合对象
Demo result = query.get(i);
String depart = "";
//循环给列赋值
for (int column = 0; column < options.length; column++) {
//确认每一列对应的表的列
XSSFCell userId = row.createCell(0);
XSSFCell zhName = row.createCell(1);
//给对应列赋值
userId.setCellValue(result.getUserId());
zhName.setCellValue(result.getZhName());
}
}
//写一个try catch捕捉异常(response获取输出流)
OutputStream os = null;
try {
//处理下载文件名乱码
String filename= new String("人员信息".getBytes("utf-8"), "ISO_8859_1");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xlsx");
response.setContentType("application");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
os = response.getOutputStream();
book.write(os);
} catch (IOException e) {
log.info("IO流异常");
} finally {
try {
os.close();
} catch (IOException e) {
log.info("关闭IO流异常");
}
}
}
poi导入
@PostMapping("/excel/read")
public ResultVO<List<ExcelVO>> readExcel(MultipartFile file) {
List<ExcelVO> list = new ArrayList<ExcelVO>();
try {
//开始读取excel
InputStream is = file.getInputStream();
Workbook hssfWorkbook = null;
if (file.getOriginalFilename().endsWith("xlsx")) {
hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
} else if (file.getOriginalFilename().endsWith("xls")) {
hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
}
ExcelVO patch = null;
PatchExcelResultVO patchExcelResult = null;
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
Row hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
Cell userId = hssfRow.getCell(0);
Cell zhName = hssfRow.getCell(1);
String staffIdNoValue = formatStaffIdCell(staffIdNo);
swipeDate = formatCell(swipeDate,"yyyy-MM-dd");
list.add(new ExcelVO(userId,zhName));
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return new ResultVO(GlobalReturnCode.SUCCESS_CODE,"SUCCESS",list);
}