POM文件
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>net.sourceforge.nekohtml</groupId>
<artifactId>nekohtml</artifactId>
<version>1.9.15</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
实体bean
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TRole {
private Integer id;
private String name;
private Integer age;
}
/**
*Excel工具类
*/
public class ExcelUtil {
public static final String FILE_NAME = "用户表";
public static final String EXCEL_XLS = "xls";
public static final String EXCEL_XLSX = "xlsx";
public static boolean isExcelXls(String filePath) {
return filePath.matches("^.+\.(?i)(xls)$");
}
public static boolean isExcelXlsx(String filePath) {
return filePath.matches("^.+\.(?i)(xlsx)$");
}
/**
* 有些单元格为Numeric格式，带有指数E。因此，若想获取其String类型时，需要进行格式转换。
*
* @param cell
* @return
*/
public static String getStringFromNumericCell(Cell cell) {
return new DecimalFormat("#").format(cell.getNumericCellValue());
}
/**
* 通过WorkBook对象和sheet对象生成title
*
* @param wb WorkBook对象
* @param sheet sheet对象
*/
public static void createTitle(Workbook wb, Sheet sheet) {
//生成表的标题行
Row row = sheet.createRow(0);
//设置列宽
sheet.setColumnWidth(1, 12 * 256);
sheet.setColumnWidth(3, 17 * 256);
//设置风格：字体加粗，居中
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = wb.createFont();
font.setBold(true);
cellStyle.setFont(font);
//根据对象属性设置单元格标题
Cell cell0 = row.createCell(0);
cell0.setCellValue("ID");
cell0.setCellStyle(cellStyle);
Cell cell1 = row.createCell(1);
cell1.setCellValue("name");
cell1.setCellStyle(cellStyle);
Cell cell2 = row.createCell(2);
cell2.setCellValue("age");
cell2.setCellStyle(cellStyle);
}
/**
* 生成文件在本地
* @param fileName
* @param wb
*/
public static void buildExcelFile(String fileName, Workbook wb) {
try {
FileOutputStream fos = new FileOutputStream(fileName);
wb.write(fos);
fos.flush();
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 返回给浏览器
* @param fileName
* @param wb
* @param response
*/
public static void buildExcelFile(String fileName, Workbook wb, HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
ServletOutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
*Controller层操作，省略service和dao
*/
@Controller
@RequestMapping("/excel")
public class ExcelController {
@Autowired
ExcelService excelService;
@RequestMapping("/")
public String toIndex() {
return "excel";
}
@ResponseBody
@RequestMapping("/download/{way}")
public String excelFileDownload(@PathVariable String way, HttpServletResponse response) throws IOException {
Workbook wb = null;
String filename = ExcelUtil.FILE_NAME;
if (ExcelUtil.EXCEL_XLS.equals(way)) {
wb = new HSSFWorkbook();
filename += ".xls";
} else if ((ExcelUtil.EXCEL_XLSX.equals(way))) {
wb = new XSSFWorkbook();
filename += ".xlsx";
}
Sheet sheet = wb.createSheet("角色表");
ExcelUtil.createTitle(wb, sheet);
//从数据库查出数据
List<TRole> tRoles = excelService.getAllRoles();
//记录新增行数据
int rowNum = 1;
for (TRole tRole : tRoles) {
Row row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(tRole.getId());
row.createCell(1).setCellValue(tRole.getName());
row.createCell(2).setCellValue(tRole.getAge());
rowNum++;
}
//生成excel文件(在当前文件夹下)
//ExcelUtil.buildExcelFile(filename, wb);
//浏览器下载excel
ExcelUtil.buildExcelFile(filename, wb, response);
wb.close();
return "File downloaded successfully";
}
@ResponseBody
@RequestMapping("/upload")
public String excelFileUpload(MultipartFile file) throws IOException {
Workbook wb = null;
List<TRole> tRoleList = new ArrayList<>();
if (!ExcelUtil.isExcelXlsx(file.getOriginalFilename()) && !ExcelUtil.isExcelXls(file.getOriginalFilename())) {
return "The file must be of excel type";
}
if (file.getOriginalFilename() == null || file.getOriginalFilename().equals("") || file.getSize() == 0) {
return "The file cannot be empty";
}
try {
if (ExcelUtil.isExcelXlsx(file.getOriginalFilename())) {
wb = new XSSFWorkbook(file.getInputStream());
} else {
wb = new HSSFWorkbook(file.getInputStream());
}
//获取第一张表(默认就设置成1张表吧)
Sheet sheet = wb.getSheetAt(0);
//获取行数
int rowsNum = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rowsNum; i++) {
//标题行省略
if (i == 0) {
continue;
}
Row row = sheet.getRow(i);
//遍历每行的单元格
// for (int j=0;j<row.getPhysicalNumberOfCells();j++){
// Cell cell = row.getCell(j);
// }
//这里我们直接操作具体的单元格
Integer id = Integer.valueOf(Double.valueOf(row.getCell(0).getNumericCellValue()).intValue());
String name = row.getCell(1).getStringCellValue();
Integer age = Integer.valueOf(Double.valueOf(row.getCell(2).getNumericCellValue()).intValue());
TRole tRole = new TRole(id, name, age);
tRoleList.add(tRole);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
wb.close();
}
return JSON.toJSONString(tRoleList);
}
}