网上有很多java整合excel的文章,但好多使用的过期方法(比如格式转换,我这用的是poi的枚举类进行switch,不是过期方法,用cell.getCellType()获取类型是过期方法了),自己重新将前端后端所需要的方法都重写了一遍并且加了很多注解。
1、导包
<!--上传文件-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!--EXCEL操作-->
<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.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
2、前端模板(文件名:excel.jsp)
<a href="${pageContext.request.contextPath}/download">导出用户信息</a>
<form id="upload_form" action="${pageContext.request.contextPath}/upload" enctype="multipart/form-data" method="post">
<div >
<label class="col-sm-2 control-label">表格上传</label>${msg}
<input type="file" id="file" name="file" multiple="multiple" />
</div>
<button type="submit" class="btn btn-success btn-block bgc-color">导入用户表</button>
</form>
3、控制层(UserController.java)
(导出方法)
/**
* excel文件导出
*
* @param response 响应对象
*/
@RequestMapping(value = "/download", method = RequestMethod.GET)
public void download(HttpServletResponse response) {
final Office office = new Office();
// 转换二进制数据,由于没有键值,所以,一次只能下载一个文件
response.setContentType("application/binary;charset=UTF-8"); //更改响应头,相当于Content-Type:application/octet-stream,
try {
List<Map<String, Object>> mapList = BeanChange.objectsToMaps(userService.selAllUser()); //Bean转换成List<Map<String, Object>>集合
//设置文件头:最后一个参数是设置下载文件名(这里是由年月日时分秒组成)
response.setHeader("Content-Disposition", "attachment;fileName="
+ URLEncoder.encode(new DateTime().toString("yyyyMMddHHmmss") + ".xls", "UTF-8"));
office.writeExcel(mapList, response,"用户表",0);
} catch (Exception e) {
LOGGER.error("download fail:{}",e);
e.printStackTrace();
}
}
(导入方法)
/**
* excel文件导入
*
* @param file 文件
* @return 返回excel页面
*/
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public String upload(MultipartFile file, Model model) {
if (file.getSize() <= 0) {
model.addAttribute("msg", "请添加文件");
} else {
Boolean b = userService.uploadExcel(file);
if (b) {
model.addAttribute("msg", "上传成功");
} else {
model.addAttribute("msg", "上传失败");
}
}
return "excel";
}
(导入方法的UserService.java)
public Boolean uploadExcel(MultipartFile file) {
final Office office = new Office();
try {
final List<String[]> list = office.readExcel(file);
final Boolean b = office.uploadExcel(file, list.get(0)[0]); //将文件保存在服务器
if (b) {
for (int i = 2; i < list.size(); i++) { //根据用户表添加用户
User user = new User();
user.setAccount(list.get(i)[0]);
user.setUsername(list.get(i)[1]);
user.setPassword(Md5Utils.encodePasswordUseMd5(list.get(i)[2], list.get(i)[0], 2));
user.setReam(list.get(i)[3]);
userDao.addDao(user);
}
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
4、工具类(office.java)
(导入方法)
private final static String XLS = "xls"; //后缀名 03版本
private final static String XLSX = "xlsx"; //后缀名 07版本
/**
* 上传excel文档
* 将文档复制到服务器上
*
* @param file 文件
* @param header excel的标题头
* @return Boolean 是否复制成功
*/
public Boolean uploadExcel(MultipartFile file, String header) {
final File file2 = new File("D:/fileupload/" + header + new DateTime().toString("yyyyMMddHHmmss") + ".xlsx");
//复制路径以及文件名
try {
//复制到本地
FileUtils.copyInputStreamToFile(file.getInputStream(), file2);
} catch (IOException e) {
e.printStackTrace();
LOGGER.error("copy file on local fail:{}",e);
return false;
}
return true;
}
/**
* 读入excel文件,解析后返回
*
* @param file 文件
* @return List<String> 数据集合
* @throws IOException IO流异常
*/
public List<String[]> readExcel(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
final Workbook workbook = getWorkBook(file);
//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
List<String[]> list = Lists.newArrayList();
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
//获得当前sheet工作表
final Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null || sheet.getLastRowNum() == 0) continue;
//bug:当一个excel文件有几张表格都有数据时候,会被覆盖掉,只返回最后一张有数据的表格
list = sortRow(sheet);
}
workbook.close();
return list;
}
/**
* 检查文件是不是excel文件
*
* @param file 文件
* @throws IOException IO异常
*/
private void checkFile(MultipartFile file) throws IOException {
//获得文件名
final String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
LOGGER.error(fileName + "不是excel文件");
throw new IOException(fileName + "不是excel文件");
}
}
/**
* 创建workbook
* 根据后缀名的区分来获取不同的Workbook实现类对象
*
* @param file 文件
* @return workbook实现类对象
*/
private Workbook getWorkBook(MultipartFile file) {
//获得文件名
final String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
final InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith(XLS)) {
workbook = new HSSFWorkbook(is); //2003
} else if (fileName.endsWith(XLSX)) {
workbook = new XSSFWorkbook(is); //2007
}
} catch (IOException e) {
LOGGER.error("get workbook fail:{}",e);
}
return workbook;
}
/**
* 解析整张表格数据
*
* @param sheet 表格
* @return List<String> 数据集合
*/
private List<String[]> sortRow(Sheet sheet) {
//行集合
final List<String[]> list2 = Lists.newArrayList();
//获得当前sheet的开始行
final int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
final int lastRowNum = sheet.getLastRowNum();
//循环所有行
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
//获得当前行
final Row row = sheet.getRow(rowNum);
if (row == null) continue;
String[] strings = sortCell(row);
list2.add(strings);
}
return list2;
}
/**
* 处理单行数据
*
* @param row 行对象
* @return String[] 数据数组
*/
private String[] sortCell(Row row) {
//获得当前行的开始列
final int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
final int lastCellNum = row.getPhysicalNumberOfCells();
//创建数据数组
final String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
return cells;
}
/**
* 格式转换
*
* @param cell 单元格对象
* @return String 单元格值
*/
private String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
//判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: //空值
cellValue = "";
break;
case ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
(导出方法)
private Workbook wk = null; // XSSFWorkbook 是07年之后的版本,后续.xlsx, HSSWorkbook 是03版本后缀.xls
/**
* 输出excel文档
*
* @param mapList bean对象转换过来的集合
* @param response 响应对象
* @param header 标题头
* @param rowNum 起始行
*/
public void writeExcel(List<Map<String, Object>> mapList, HttpServletResponse response, String header, int rowNum) {
//创建工作簿
wk = new HSSFWorkbook();
//创建表名
final Sheet sheet = wk.createSheet("用户表");
//插入数据
sortListMap(mapList, sheet, rowNum + 1);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, sheet.getRow(rowNum + 1).getLastCellNum() - 1));
//设置表头值
creatCell(sheet, rowNum, 0, header);
//设置列的自适应宽度
for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
sheet.autoSizeColumn(i, true);
}
try (ServletOutputStream out = response.getOutputStream()) { //输出流
//workbook输出
wk.write(out);
} catch (Exception e) {
LOGGER.error("out workbook fail:{}",e);
e.printStackTrace();
}
}
/**
* 处理表格数据
*
* @param mapList 数据
* @param sheet 表格
* @param firstRowNum 除表头的起始行
*/
private void sortListMap(List<Map<String, Object>> mapList, Sheet sheet, int firstRowNum) {
List<String> list = null;
int rowNum = firstRowNum;
for (Map<String, Object> aMapList : mapList) {
list = Lists.newArrayList(aMapList.keySet());
//map键排序
list.sort(String::compareTo);
//按map键的顺序插入数据
for (Map.Entry<String, Object> entry : aMapList.entrySet()) {
creatCell(sheet, rowNum + 1, list.indexOf(entry.getKey()), entry.getValue().toString());
}
rowNum++;
}
if (list != null) {
for (int i = 0; i < list.size(); i++) { //map键添加到次行
creatCell(sheet, firstRowNum, i, list.get(i));
}
}
}
/**
* 创建单元格
*
* @param sheet 表对象
* @param rowNum 行号
* @param cellNum 列号
* @param value 值
*/
private void creatCell(Sheet sheet, int rowNum, int cellNum, String value) {
Row row;
if (sheet.getRow(rowNum) != null) {
row = sheet.getRow(rowNum);
} else {
row = sheet.createRow(rowNum);
}
Cell cell = row.createCell(cellNum);
cell.setCellValue(value);
cell.setCellStyle(changeCellStyle()); //单元格设置单元格属性
}
/**
* 设置单元格格式
*
* @return CellStyle 单元格格式对象
*/
private CellStyle changeCellStyle() {
final CellStyle cellStyle = wk.createCellStyle(); //设置单元格属性
cellStyle.setAlignment(HorizontalAlignment.CENTER); //文本水平居中
return cellStyle;
}