在项目中，有很多对excel的操作，大都数时候我们都会使用poi工具类，本文将介绍poi的一些使用方法。
1.poi导入excel，并展示数据
使用poi导入excel，解析后返回List数据到前台展示。
/**
*
* (读入excel文件，解析后返回)
* @param file(文件类型)
* @throws IOException
* @return List<String[]>
*/
public static List<String[]> readExcel(MultipartFile file)
throws IOException {
// 检查文件
checkFile(file);
// 获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if (workbook != null) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++ ) {
// 获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
// 循环除了第一行的所有行
for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++ ) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++ ) {
Cell cell=row.getCell(cellNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
}
return list;
}
/**
*
* (判断文件)
* @param file
* @throws IOException
* @return void
*/
public static void checkFile(MultipartFile file)
throws IOException {
// 判断文件是否存在
if (null == file) {
throw new FileNotFoundException("文件不存在！");
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith(XLS_TYPE) && !fileName.endsWith(XLSX_TYPE)) {
throw new IOException(fileName + "不是excel文件");
}
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
// 判断数据的类型
switch (cell.getCellType()) {
// 数字
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
// 字符串
case Cell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
// Boolean
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 公式
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
// 空值
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
// 故障
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
*
* (getWorkBook:(创建WorkBook对象))
* @param file
* @return
* @return Workbook
*/
public static Workbook getWorkBook(MultipartFile file) {
// 获得文件名
String fileName = file.getOriginalFilename();
// 创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
// 获取excel文件的io流
InputStream is = file.getInputStream();
// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith(XLS_TYPE)) {
// 2003
workbook = new HSSFWorkbook(is);
}
else if (fileName.endsWith(XLSX_TYPE)) {
// 2007
workbook = new XSSFWorkbook(is);
}
}
catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
2.poi导出excel并以web下载方式保存excel
将数据库中的数据组装成excel并导出，并在web下载栏中直接下载。
/**
* 人员导出
*
* @param sysSysUserVO
* @throws IOException
*/
@PostMapping("/exportUser")
@ApiOperation(value = "导出人员excel", notes = "导出人员excel")
public void
exportUser(@ApiParam(name = "人员id", value = "人员id", required = false) @RequestBody List<SysUserVO> sysSysUserVO)
throws IOException {
List<SysUserVO> middleList = new ArrayList<>();
// 查询用户详细信息
for (SysUserVO sysUserVO : sysSysUserVO) {
List<SysUserVO> resultListSysUser = sysUserService.querySysUserAll(sysUserVO);
userInfoUtil.completionInformation(resultListSysUser.get(0));
middleList.add(resultListSysUser.get(0));
}
// excle格式
String[] headers = {"用户名", "姓名", "密码", "启动状态", "岗位", "角色", "所属部门", "手机", "邮箱", "身份证号"};
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
// 设置列宽
sheet.setDefaultColumnWidth((short)18);
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
// 创建单元格，每行多少数据就创建多少个单元格
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
// 给单元格设置内容
cell.setCellValue(text);
}
for (int j = 0; j < middleList.size(); j++) {
SysUserVO export = middleList.get(j);
// 从第二行开始填充数据
row = sheet.createRow(j + 1);
List<String> datas = new ArrayList<>();
String userName = export.getUsername();
String trueName = export.getTureName();
String password = export.getPassword();
String status = String.valueOf(export.getIsEnabled());
String postName = export.getPostName();
String roleName = export.getRoleName();
String organName = export.getOrganizationName();
String phone = export.getMobile();
String email = export.getEmail();
String identityCard = export.getIdentityCard();
datas.add(userName);
datas.add(trueName);
datas.add(password);
datas.add(status);
datas.add(postName);
datas.add(roleName);
datas.add(organName);
datas.add(phone);
datas.add(email);
datas.add(identityCard);
for (int k = 0; k < datas.size(); k++) {
String string = datas.get(k);
HSSFCell cell = row.createCell(k);
HSSFRichTextString richString = new HSSFRichTextString(string);
HSSFFont font3 = workbook.createFont();
// 定义Excel数据颜色，这里设置为蓝色
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
String fileName = "人员导出.xls";
// 导出
HttpServletResponse response =
((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
RequestAttributes requsetAttributes = RequestContextHolder.currentRequestAttributes();
HttpServletRequest request = ((ServletRequestAttributes)requsetAttributes).getRequest();
// 获得浏览器代理信息
final String userAgent = request.getHeader("USER-AGENT");
// 判断浏览器代理并分别设置响应给浏览器的编码格式
if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident")) {
// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {
// google,火狐浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else {
// 其他浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");// 其他浏览器
}
// 设置HTTP响应头
response.reset();
// 重置 如果不在页面上显示而是下载下来 则放开注释
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
OutputStream os = response.getOutputStream();
workbook.write(os);
os.close();
}
3.poi导出并以流方式保存excel
将数据库中的数据组装成excel并导出，并以数据流方式存在指定的路径。
public class BarcodeExportlFlow implements IBarcodeExport {
@Autowired
BarcodeManageBatchSerivce barcodeManageBatchSerivce;
@Override
public OutputStream exportData() throws IOException {
//查询批次码
BarcodeBatchManageBo input = new BarcodeBatchManageBo();
List<BarcodeBatchManageBo> middleList = barcodeManageBatchSerivce.selectBatch(input);
String[] headers = {"id", "条码批次码", "激活状态", "有效状态", "导入人", "导入时间", "激活人", "激活时间", "作废人", "作废时间", "本批次条数"};
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
//设置列宽
sheet.setDefaultColumnWidth((short) 18);
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
//创建单元格，每行多少数据就创建多少个单元格
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
//给单元格设置内容
cell.setCellValue(text);
}
for (int j = 0; j < middleList.size(); j++) {
BarcodeBatchManageBo export = middleList.get(j);
//从第二行开始填充数据
row = sheet.createRow(j + 1);
List<String> datas = new ArrayList<>();
String id = export.getId().toString();
String batchCode = export.getBatchCode();
String activationStatus = export.getActivationStatus();
String effectiveStatus = export.getEffectiveStatus();
datas.add(id);
datas.add(batchCode);
datas.add(activationStatus);
datas.add(effectiveStatus);
for (int k = 0; k < datas.size(); k++) {
String string = datas.get(k);
HSSFCell cell = row.createCell(k);
HSSFRichTextString richString = new HSSFRichTextString(string);
HSSFFont font3 = workbook.createFont();
//定义Excel数据颜色，这里设置为蓝色
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
FileOutputStream fos = new FileOutputStream("D:/wb.xls");
workbook.write(fos);
fos.close();
return null;
}
}
