Java解析Excel
Java解析Excel可以说在日常工作中必不可少的一个需求。解析Excel有很多方式,主流有poi,easyexcel等技术,本文使用springboot+poi技术来实现解析excel文件。其中包含解析本地文件和web端导入导出文件,且支持2003和2007及以上版本。
1.依赖导入
依赖版本不同可能会出现报错,整篇文章使用以下依赖版本进行编写。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.10-FINAL</version>
</dependency>
2.本地导入导出
定义要读取和存放Excel文件的路径
// 要读取的本地文件
String localExcelPath = "/Users/jeckwu/Desktop/test/test.xlsx";
// 把数据写入excel放到本地路径
String writeDateToExcelPath = "/Users/jeckwu/Desktop/test/";
解析本地excel:解析指定目录中的excel数据
// 读取本地excel数据
@Test
public void getLocalExcelData() throws IOException {
// 获取文件流
FileInputStream fileInputStream = new FileInputStream(localExcelPath);
// 解析数据
List<LinkedHashMap<String, Object>> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);
assert maps != null;
log.info("size:{}", maps.size());
log.info("data:{}", JSON.toJSONString(maps));
}
这里简单的封装了一下,操作本地文件只针对xlsx格式文件,web端的处理包括xls类型。
/**
* excel2007及以上版本
*
* @param file
* @return
* @throws IOException
*/
static List<LinkedHashMap<String, Object>> xlsxImportExcel(FileInputStream file, Integer titleNo, Integer dataNo, Integer sheetNo) throws IOException {
log.info("excel2007及以上版本");
// 读取流中的excel数据 这里用到的是poi中的XSSFWorkbook 用来处理xlsx(版本在2007以后的excel文件) HSSFWorkbook对象用来处理xls类型 版本为2003的文件
XSSFWorkbook xwb = new XSSFWorkbook(file); //获取excel工作簿
XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet
if (xssfSheet == null) {
return null;
}
// 防止数据列错乱
List<LinkedHashMap<String, Object>> mapList = new ArrayList<>();
Row rowTitle = xssfSheet.getRow(titleNo);
//循环获取excel每一行
for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
LinkedHashMap<String, Object> map = new LinkedHashMap<>();
//循环获取excel每一行的每一列
for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) {
XSSFCell xssCell = xssfRow.getCell(cellNum);
if (xssCell == null) {
// 防止单元格为空
map.put(rowTitle.getCell(cellNum).toString(), "");
continue;
}
try {
map.put(rowTitle.getCell(cellNum).toString(), getXSSFValue(xssCell));
} catch (Exception e) {
log.error("");
}
}
mapList.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list
}
return mapList;
}
处理excel各种类型值
/**
* excel值处理
*
* @param hssfCell
* @return
*/
public static Object getXSSFValue(XSSFCell hssfCell) {
Object result = null;
int cellType = hssfCell.getCellType();
switch (hssfCell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: //数字
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
// 需要对日期这一列进行设置样式,否则无法识别是日期类型还是数值类型
// 默认不支持中文日期类型,需要设置成纯英语日期类型,不要包含年月日等汉字
// 最好是使用这种格式 2019/10/10 0:00
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
break;
}
result = hssfCell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
result = hssfCell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR: //故障
result = hssfCell.getErrorCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
result = hssfCell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
break;
default: //字符串
result = hssfCell.getStringCellValue();
}
return result;
}
Excel下载到本地:根据指定数据集生成excel到指定目录中
这里用到了上面的excel数据作为测试 实际根据自己项目封装即可。
// 把数据集写入到excel中 放到本地服务器
@Test
public void writeDataToLocalExcel() throws IOException {
// 获取文件流-就拿本地数据源
FileInputStream fileInputStream = new FileInputStream(localExcelPath);
List<LinkedHashMap<String, Object>> maps = xlsxImportExcel(fileInputStream, 0, 1, 0);
assert maps != null;
// 取10条写入到其他文件
List<LinkedHashMap<String, Object>> maps1 = maps.subList(0, 10);
// 新建工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 新建Sheet
XSSFSheet xssfSheet = wb.createSheet("testSheet");
// 获取标题 前面用的是linkedhashmap 直接获取map中的标题 且和读取的excel列相同
List<String> titleList = maps1.get(0).keySet().stream().map(s -> s).collect(Collectors.toList());
//标题行
XSSFRow titleRow = xssfSheet.createRow(0);
for (int i = 0; i < maps1.get(0).keySet().size(); i++) {
XSSFCell titleCell = titleRow.createCell(i);
titleCell.setCellValue(titleList.get(i));
}
// 数据行
for (int i = 0; i < maps1.size(); i++) {
XSSFRow xssfRow = xssfSheet.createRow(i + 1);
Map<String, Object> stringObjectMap = maps1.get(i);
for (int i1 = 0; i1 < titleList.size(); i1++) {
XSSFCell xssfCell = xssfRow.createCell(i1);
xssfCell.setCellValue(stringObjectMap.get(titleList.get(i1)).toString());
}
}
log.info("size:{}", maps1.size());
log.info("data:{}", JSON.toJSONString(maps1));
// 写入本地文件中 将文件保存到指定的位置
try {
FileOutputStream fos = new FileOutputStream(writeDateToExcelPath+"写入本地excel.xlsx");
wb.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
3.Web端导入导出
导入Excel:在网页上导入Excel,然后处理业务逻辑
下面我们编写测试代码,导入数据Excel之后做一下基本的判断,就开始业务处理
// 根据参数选择解析文件格式数据
public ApiResult analysisPcsData(PcsDataIMportParamModel pcsDataIMportParamModel){
if (pcsDataIMportParamModel.getMultipartFile() == null) return new ApiResult(true,"请上传文件");
log.info("{}-导入PCS数据,参数:{}",DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss"),pcsDataIMportParamModel.toString());
// 根据参数选择解析
String originalFilename = pcsDataIMportParamModel.getMultipartFile().getOriginalFilename();
assert originalFilename != null;
// 解析的数据
List<Map<String, Object>> = new ArrayList<>();
// 入库的数据
List<TJPcsDataModel> pcsDataModelList = new ArrayList<>();
try {
String expandName = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
if ((expandName.equals("xls") || expandName.equals("xlsx") ) && "EP".equals(pcsDataIMportParamModel.getCustomer_factory())){
mapList = ImportExcel.importExcel(pcsDataIMportParamModel.getMultipartFile(),1,2,0);
pcsDataModelList.addAll(analysisExcel(mapList,pcsDataIMportParamModel));
}else {
return new ApiResult(false,"上传的文件格式不正确,只支持xls,xlsx,csv!或者参数选择错误!");
}
if (CollectionUtils.isEmpty(mapList)) return new ApiResult(false,"传入的文件数据为空,请检查后再上传!!");
log.info("导入PCS解析数据数量:{}",mapList.size());
log.info("PCS入库数据数量:{}",pcsDataModelList.size());
return new ApiResult(true,"导入数据成功!!文件名:"+originalFilename+",成功条数:"+i);
}catch (Exception e){
log.error("导入PCS数据解析失败!!!原因:{}",e.getMessage(),e);
return new ApiResult(false,"导入PCS数据解析失败!!!");
}
这里区分了Excel版本
// 判断excel版本
public static List<Map<String,Object>> importExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
String fileName = file.getOriginalFilename(); //获得上传的excel文件名
assert fileName != null;
String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1); //获取上传的excel文件名后缀
List<Map<String,Object>> mapList = null;
if ("xlsx".equals(fileSuffix)) {
mapList = xlsxImportExcel(file,titleNo,dataNo,sheetNo);
} else if ("xls".equals(fileSuffix)) {
mapList = xlsImportExcel(file,titleNo,dataNo,sheetNo);
}
return mapList;
}
Excel2007版本处理
/**
* excel2007及以上版本
*
* @param file
* @return
* @throws IOException
*/
static List<Map<String,Object>> xlsxImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
log.info("excel2007及以上版本");
XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream()); //获取excel工作簿
XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet
if (xssfSheet == null) {
return null;
}
List<Map<String,Object>> mapList = new ArrayList<>();
Row rowTitle = xssfSheet.getRow(titleNo);
//循环获取excel每一行
for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
Map<String,Object> map = new HashMap<>();
//循环获取excel每一行的每一列
for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) {
XSSFCell xssCell = xssfRow.getCell(cellNum);
if (xssCell == null) {
continue;
}
try {
map.put(rowTitle.getCell(cellNum).toString(),getXSSFValue(xssCell));
}catch (Exception e){
log.error("");
}
}
mapList.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list
}
return mapList;
}
Excel2003版本处理
/**
* @param file
* @return excel2003版本
* @throws IOException
*/
static List<Map<String,Object>> xlsImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException {
log.info("excel2003版本");
Workbook wb = new HSSFWorkbook(file.getInputStream()); //获取excel工作簿
Sheet sheet = wb.getSheetAt(sheetNo); //获取excel的sheet
if (sheet == null) {
return null;
}
List<Map<String,Object>> list = new ArrayList<>();
Row rowTitle = sheet.getRow(titleNo);
//循环获取excel每一行
for (int rowNum = dataNo; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
Map<String,Object> map = new HashMap<>();
//循环获取excel每一行的每一列
for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
map.put(rowTitle.getCell(cellNum).toString(),getValue(cell));
}
list.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list
}
return list;
}
Excel中各种类型值处理,包括2003和2007及以上的版本的
/**
* excel值处理 2007
*
* @param hssfCell
* @return
*/
public static Object getXSSFValue(XSSFCell hssfCell) {
Object result = null;
int cellType = hssfCell.getCellType();
switch (hssfCell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: //数字
if (HSSFDateUtil.isCellDateFormatted(hssfCell))
{
// 需要对日期这一列进行设置样式,否则无法识别是日期类型还是数值类型
// 默认不支持中文日期类型,需要设置成纯英语日期类型,不要包含年月日等汉字
// 最好是使用这种格式 2019/10/10 0:00
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
break;
}
result = hssfCell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
result = hssfCell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR: //故障
result = hssfCell.getErrorCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
result = hssfCell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
break;
default: //字符串
result = hssfCell.getStringCellValue();
}
return result;
}
/**
* excel值处理 2003
*
* @param cell
* @return
*/
public static Object getValue(Cell cell){
//判断是否为null或空串
if (cell== null || cell.toString().trim().equals( "" )) {
return null ;
}
String cellValue;
int cellType=cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
cellValue= cell.getStringCellValue().trim();
cellValue= StringUtils.isEmpty(cellValue) ? "" : cellValue;
break ;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break ;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
} else {
cellValue = new DecimalFormat( "#.######" ).format(cell.getNumericCellValue());
}
break ;
default :
cellValue = null ;
break ;
}
return cellValue == null ? null : cellValue.replaceAll("\\xa0", "");
}
导出Excel:在网页上导出Excel
// 导出excel
public void invoicePartHisExcelExport(QueryParamModel queryParamModel, HttpServletResponse response){
long startTime = System.currentTimeMillis();
log.info("----开始导出Excel:"+startTime);
//导出全部数据
queryParamModel.setPageSize(-1);
queryParamModel.setPageSize(-1);
//根据条件查询业务数据
List<TRPAInvoicePartHisVO> invoicePartHis = findInvoicePartHis(queryParamModel);
String excelName = "test数据.xlsx";
String sheetName = "testSheet";
List<String> titleList = new ArrayList<>();
titleList.add("单据号");
titleList.add("行号");
titleList.add("订单状态");
titleList.add("客户【描述】");
titleList.add("订单类型【描述】");
// 整理数据data
List<List<String>> datasList = new ArrayList<>();
if (invoicePartHis.size()>0){
for (TRPAInvoicePartHisVO partHisModel : invoicePartHis) {
List<String> list = new ArrayList<>();
list.add(Strings.isNotBlank(partHisModel.getInvoice_no())?partHisModel.getInvoice_no():"");
list.add(Strings.isNotBlank(partHisModel.getLine_no())?partHisModel.getLine_no():"");
list.add(Strings.isNotBlank(partHisModel.getOrder_status_desc())?partHisModel.getOrder_status_desc():"");
list.add(Strings.isNotBlank(partHisModel.getCustomer_name())?partHisModel.getCustomer_name():"");
list.add(Strings.isNotBlank(partHisModel.getOrder_type_desc())?partHisModel.getOrder_type_desc():"");
datasList.add(list);
}
}
ExportExcel.createSheetExcel(excelName,sheetName,titleList,datasList,response);
log.info("----导出Excel结束,所用时间:"+(System.currentTimeMillis()-startTime));
}
这里可以设置Excel的样式
/**
* excel下载单个sheet
* @param excelName --Excel名称
* @param sheetName --sheet名称--sheet为null或者空字符串表示默认sheet名
* @param title --表头
* @param datas --数据 list,一行一个list
* @param response --对服务器的响应
*/
public static void createSheetExcel(String excelName,String sheetName,List<String> title, List<List<String>> datas, HttpServletResponse response) {
try{
int rowNum = datas.size() + 1;
int colNum = title.size();
//创建工作簿
XSSFWorkbook wb = new XSSFWorkbook();
//创建一个sheet
XSSFSheet sheet = wb.createSheet();
if(sheetName!=null||!"".equals(sheetName)){
wb.setSheetName(0, sheetName);
}
sheet.setDefaultColumnWidth(16);
// 创建单元格样式
XSSFCellStyle style1 = wb.createCellStyle();
style1.setFillForegroundColor((short) 1); //设置要添加表背景颜色
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //solid 填充
style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); //文字水平居中
style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//文字垂直居中
style1.setBorderBottom(BorderStyle.THIN); //底边框加黑
style1.setBorderLeft(BorderStyle.THIN); //左边框加黑
style1.setBorderRight(BorderStyle.THIN); // 有边框加黑
style1.setBorderTop(BorderStyle.THIN); //上边框加黑
//为单元格添加背景样式
sheet=setTitleStyle(sheet,rowNum,colNum,style1,null);
//tian入数据
sheet=setSheetData(sheet,rowNum,colNum,title, datas);
//将数据写入文件
writeData(excelName,response,wb);
}catch (Exception e){
log.error("excel下载单个sheet报错:"+e);
throw GlobalException.serverErrException(500,"下载出错");
}
}
最后把流放进响应中,浏览器自动解析流,并且下载Excel文章来源:https://www.toymoban.com/news/detail-610146.html
/**
*生成excel
* */
public static void writeData(String excelName, HttpServletResponse response, XSSFWorkbook wb){
OutputStream outputStream = null;
try{
excelName = new String(excelName.getBytes(), StandardCharsets.UTF_8);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取
response.setHeader("Content-Disposition", "attachment;filename="+ excelName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}catch (Exception e){
log.error("输出excel报错:"+e);
throw GlobalException.serverErrException(500,"下载出错");
}finally {
if(outputStream!=null){
try{
outputStream.close();
}catch (Exception e){
log.error("流关闭报错:"+e);
throw GlobalException.serverErrException(500,"下载出错");
}
}
}
}
最后实际对Excel操作,都可以根据自己的业务来封装。文章来源地址https://www.toymoban.com/news/detail-610146.html
到了这里,关于Java解析Excel文件的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!