POI导出Excel
今天是POI对excel导入导出时，出现了一些问题，因为excel的文件格式众多：
项目中对.xls格式是支持的，但是现在有了.xlsx格式，项目就会报出异常：
NoClassDefFoundError: org/apache/poi/ss/usermodel/AutoFilt搞了很久最后怀疑poi-ooxml和poi的兼容性问题,最后在pom文件中对poi依赖进行了修改，由：
<dependency> <groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
改成了：
<dependency> <groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
没错，只是把两个依赖的version改为一样，问题解决。
附上Java项目如何导入excel项目级实现：
首先controller层：
@RequestMapping(value = "/importExcel", method = RequestMethod.POST) public Object importExcel(@RequestParam("excel") MultipartFile file, HttpServletRequest request, HttpServletResponse response){
return svc.importExcel(file);
}
Service层对file进行处理：
/** * 文件导入
* @param file
* @return
*/
@SuppressWarnings("unchecked")
@Transactional
public Map<String, Object> importExcel(MultipartFile file){
// 判断文件是否为空
if (file.isEmpty()){
return comnSvc.invalidMessage("importNullExcel");
}
// 判断文件类型是否为excel
String type = file.getContentType();
if (!("application/vnd.ms-excel".equals(type) || "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(type))){
return comnSvc.invalidMessage("fileTyleError");
}
//excel的头部
String[] header = {"a", "b", "c","d","e","f","g","h"};
//解析excel
Map<String, Object> excelResult = excelImportSvc.getInfoFromFile(file, header, 3,null);
int status = Integer.parseInt(String.valueOf(excelResult.get("status")));
//为0则出现错误
if (status == 0) return excelResult;
List<List<Map<String, Object>>> resultList = (List<List<Map<String, Object>>>)excelResult.get("data");
//得到sheet为0页的数据
List<Map<String, Object>> rjzList = resultList.get(0);
return null;
}
这里用到了导入工具类ExcelImportService：
package com.inspur.ykj.service;import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
@Service
public class ExcelImportService {
@Autowired
CommonService commonServ;
/**
*
* 读取excel
*
* @param file
* @param header 对应excel的表头,存放到map中的key
* @param startReadRow 开始读取的行号
* @param from 预留字段,添加个性化需求
* @return
*/
public Map<String,Object> getInfoFromFile(MultipartFile file,String[] header,int startReadRow,String from) {
Map<String,Object> result = new HashMap<>();
//解析文件
InputStream inputStream = null;
Workbook excel = null;
try {
inputStream = file.getInputStream();
excel = WorkbookFactory.create(inputStream);
Sheet sheet = null;
sheet = excel.getSheetAt(0);
if (sheet.getLastRowNum() < startReadRow-1){
return commonServ.invalidMessage("emptyTemplet");
}
Cell cell = null;
List<List<Map<String,String>>> sheetList = new ArrayList<>();
List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
for (int rowIndex = startReadRow-1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null){
break;
}
Map<String, String> rowMap = new HashMap<String, String>();
for (int columnIndex = 0; columnIndex < header.length; columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:// 1-字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:// 0-数值
// 如果格式为数值,自定义格式输出
if(DateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyyMMdd");
value = formater.format(date);
}else {
DecimalFormat df = new DecimalFormat("#.##");
value = df.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 4-布尔
// 导入时如果为BOOLEAN型 自定义格式输出
value = (cell.getBooleanCellValue() ? "Y" : "N");
break;
case Cell.CELL_TYPE_FORMULA:// 2-公式
try {
value = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
value = String.valueOf(cell.getNumericCellValue());
}
break;
// 导入时如果为公式生成的数据则无值
case Cell.CELL_TYPE_BLANK: // 3-空格
case Cell.CELL_TYPE_ERROR: // 5-错误
value = String.valueOf(cell.getStringCellValue());
break;
default:
break;
}
}
if(!header[columnIndex].equals(""))
rowMap.put(header[columnIndex], value);
}
//循环放入的这一行的map是否全是空，如果全是空则不放
for(String str:rowMap.values()){
if (!(str==null||str.length()==0)) {
resultList.add(rowMap);
break;
}
}
}
sheetList.add(resultList);
result.put("status", 1);
result.put("data", sheetList);
}catch (Exception e){
e.printStackTrace();
return commonServ.invalidMessage("tempeleteError");
}finally {
try {
inputStream.close();
}catch (IOException e){
e.printStackTrace();
}
}
return result;
}
}
最后附上office对应的Content-Type：
.doc application/msword.dot application/msword
.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx application/vnd.openxmlformats-officedocument.wordprocessingml.template
.docm application/vnd.ms-word.document.macroEnabled.12
.dotm application/vnd.ms-word.template.macroEnabled.12
.xls application/vnd.ms-excel
.xlt application/vnd.ms-excel
.xla application/vnd.ms-excel
.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx application/vnd.openxmlformats-officedocument.spreadsheetml.template
.xlsm application/vnd.ms-excel.sheet.macroEnabled.12
.xltm application/vnd.ms-excel.template.macroEnabled.12
.xlam application/vnd.ms-excel.addin.macroEnabled.12
.xlsb application/vnd.ms-excel.sheet.binary.macroEnabled.12
.ppt application/vnd.ms-powerpoint
.pot application/vnd.ms-powerpoint
.pps application/vnd.ms-powerpoint
.ppa application/vnd.ms-powerpoint
.pptx application/vnd.openxmlformats-officedocument.presentationml.presentation
.potx application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppam application/vnd.ms-powerpoint.addin.macroEnabled.12
.pptm application/vnd.ms-powerpoint.presentation.macroEnabled.12
.potm application/vnd.ms-powerpoint.presentation.macroEnabled.12
.ppsm application/vnd.ms-powerpoint.slideshow.macroEnabled.12
扫码关注黑白色调
以上是 POI导出Excel 的全部内容， 来源链接： utcz.com/z/517455.html
