需要的依赖
<!--导出到Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.17</version>
</dependency>
需要在web-INF中配置上传下载
<!--上传下载-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 可以添加一个属性配置,用来限制上传文件的大小,当上传文件超出指定值时,无法上传 -->
<!-- 单位B -->
<property name="maxUploadSize" value="10285760"></property>
</bean>
controller层代码
@RequestMapping("/archives-add")
public String archivesAdd(MultipartFile files) throws Exception {
System.out.println(files);
if(files != null){
//创建一个集合,保存表中的数据
List<Archives> archs = new ArrayList<>();
//上传的文件对应的对象
XSSFWorkbook workbook = null;
String originalFilename = files.getOriginalFilename();
File file = new File("C:\\Users\\Administrator\\Desktop\\项目",originalFilename);
files.transferTo(file);
//读取Excel表格文件
InputStream inputStream = new FileInputStream(file);
workbook = new XSSFWorkbook(inputStream);
inputStream.close();
//循环文件中的每个sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
XSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
//循环读取Excel表格中每一行数据
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
XSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 将单元格中的内容存入集合
Archives arch = new Archives();
XSSFCell cell = hssfRow.getCell(0);
if (cell == null) {
continue;
}
arch.setDnum(cell.getStringCellValue());
cell = hssfRow.getCell(1);
if (cell == null) {
continue;
}
arch.setLandline(((int)cell.getNumericCellValue()) + "");
cell = hssfRow.getCell(2);
if (cell == null) {
continue;
}
arch.setSchool(cell.getStringCellValue());
cell = hssfRow.getCell(3);
if (cell == null) {
continue;
}
arch.setZhuanye(cell.getStringCellValue());
cell = hssfRow.getCell(4);
if (cell == null) {
continue;
}
arch.setSosperson(cell.getStringCellValue());
cell = hssfRow.getCell(5);
if (cell == null) {
continue;
}
String str1 = cell.getStringCellValue();
Date parse = sdf.parse(str1);
arch.setBiyedate(parse);
cell = hssfRow.getCell(6);
if (cell == null) {
continue;
}
arch.setZzmm(cell.getStringCellValue());
cell = hssfRow.getCell(7);
if (cell == null) {
continue;
}
arch.setMinzu(cell.getStringCellValue());
cell = hssfRow.getCell(8);
if (cell == null) {
continue;
}
arch.setXueli(cell.getStringCellValue());
cell = hssfRow.getCell(9);
if (cell == null) {
continue;
}
arch.setEmail(cell.getStringCellValue());
cell = hssfRow.getCell(10);
if (cell == null) {
continue;
}
String empStr = "";
int cellType = cell.getCellType();
if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
empStr = (int)cell.getNumericCellValue() + "";
}else {
empStr = cell.getStringCellValue();
}
Employee employee = employeeService.queryEmployeeByUsername(empStr);
arch.setEmpFk(employee.getEid());
cell = hssfRow.getCell(11);
if (cell == null) {
continue;
}
arch.setRemark(cell.getStringCellValue());
cell = hssfRow.getCell(12);
if (cell == null) {
continue;
}
String str2 = cell.getStringCellValue();
Date parse1 = sdf.parse(str2);
arch.setHirdate(parse1);
//读取出来的一个数据,封装到了一个对象,对象再放入集合
archs.add(arch);
}
}
//保存集合数据
System.out.println(1);
System.out.println(archs);
archService.save(archs);
}
return "/archives.jsp";
}