正文
excel文件导出和导入
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
pom.xml添加依赖
@RestController
@RequestMapping(value = "/excel")
public class ExpImpExcelController { // 导出user表
@GetMapping(value = "/export/user")
public String getUser(HttpServletResponse response) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 给sheet起个名字
HSSFSheet sheet = workbook.createSheet("用户表"); // 创建表头
createTitle(workbook, sheet); // 准备数据，写入sheet
List<User> userList = new ArrayList<User>();
User user1 = new User("", "zhangsan1", "张三1", new Date());
User user2 = new User("", "zhangsan2", "张三1", new Date());
User user3 = new User("", "zhangsan3", "张三1", new Date());
userList.add(user1);
userList.add(user2);
userList.add(user3);
List<User> rows = userList;
// 设置日期格式
HSSFCellStyle style = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("yyyy年MM月dd日"));
// 新增数据行，并且设置单元格数据
int rowNum = ;
for (User user : rows) {
HSSFRow row = sheet.createRow(rowNum);
row.createCell().setCellValue(user.getId());
row.createCell().setCellValue(user.getUserName());
row.createCell().setCellValue(user.getNickName());
HSSFCell cell = row.createCell();
cell.setCellValue(user.getCreateTime());
cell.setCellStyle(style);
rowNum++;
} // 生成excel文件
String fileName = "用户表.xls";
buildExcelFile(fileName, workbook); // 浏览器下载excel
downloadExcelFile(fileName, workbook, response); return "";
} // 创建表头
private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
HSSFRow row = sheet.createRow();
// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(, * );
sheet.setColumnWidth(, * ); // 设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font); HSSFCell cell;
cell = row.createCell();
cell.setCellValue("ID");
cell.setCellStyle(style); cell = row.createCell();
cell.setCellValue("用户名");
cell.setCellStyle(style); cell = row.createCell();
cell.setCellValue("昵称");
cell.setCellStyle(style); cell = row.createCell();
cell.setCellValue("创建时间");
cell.setCellStyle(style);
} // 生成excel文件
protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
FileOutputStream fos = new FileOutputStream(filename);
workbook.write(fos);
fos.flush();
fos.close();
} // 浏览器下载excel
protected void downloadExcelFile(String filename, HSSFWorkbook workbook, HttpServletResponse response)
throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} //导入excel
@PostMapping("/import/user")
public boolean addUser(@RequestParam("file") MultipartFile file) {
boolean a = false;
String fileName = file.getOriginalFilename();
try {
a = batchImport(fileName, file);
} catch (Exception e) {
e.printStackTrace();
}
return a;
} public boolean batchImport(String fileName, MultipartFile file) throws Exception {
Workbook wb = null;
try {
List<User> userList = new ArrayList<User>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new RuntimeException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream(); if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt();
if (sheet == null) {
throw new RuntimeException("sheet页内容为空");
}
User user;
// 从第二行开始解析单元格
for (int r = ; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
user = new User();
// excel 单元格类型
// CELL_TYPE_NUMERIC 数值型 0
// CELL_TYPE_STRING 字符串型 1
// CELL_TYPE_FORMULA 公式型 2
// CELL_TYPE_BLANK 空值 3
// CELL_TYPE_BOOLEAN 布尔型 4
// CELL_TYPE_ERROR 错误 5 if (row.getCell().getCellType() != Cell.CELL_TYPE_STRING) {
throw new RuntimeException("导入失败(第" + (r + ) + "行,ID请设为文本格式)");
}
String id = row.getCell().getStringCellValue();
if (id == null || id.isEmpty()) {
throw new RuntimeException("导入失败(第" + (r + ) + "行,ID未填写)");
} String userName = row.getCell().getStringCellValue();
if (userName == null || userName.isEmpty()) {
throw new RuntimeException("导入失败(第" + (r + ) + "行,用户名未填写)");
} String nickName = row.getCell().getStringCellValue();
if (nickName == null || nickName.isEmpty()) {
throw new RuntimeException("导入失败(第" + (r + ) + "行,昵称未填写)");
} Date createTime;
if (row.getCell().getCellType() != ) {
throw new RuntimeException("导入失败(第" + (r + ) + "行,创建日期格式不正确或未填写)");
} else {
createTime = row.getCell().getDateCellValue();
}
user.setId(id);
user.setUserName(userName);
user.setNickName(nickName);
user.setCreateTime(createTime);
userList.add(user);
}
} finally {
wb.close();
} return true;
}
}
public class User {
private String id;
private String userName;
private String nickName;
private Date createTime;
}
