一、pom.xml引入jar
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-contrib</artifactId> <version>3.6</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.7</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.7</version> </dependency>
二、编写工具类
package com.zdnst.common.infra.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import com.ctc.wstx.sw.EncodingXmlWriter;
import com.zdnst.common.infra.constants.BaseCode;
import com.zdnst.common.infra.exception.ZdnstException;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;
import javax.servlet.http.HttpServletRequest;
/**
* Created by yongqin.zhong on 2017-11-14.
*/
public class ExcelReader {
private static final String EXTENSION_XLS = "xls";
private static final String EXTENSION_XLSX = "xlsx";
/**
* 通用读取excel
* @param request
* @return 返回LinkedHashMap 的list，第一行是key，其余是value
* @throws ZdnstException
*/
public static List<Map> readCommonExcel(HttpServletRequest request) throws ZdnstException {
List<Map> resultList = new ArrayList<Map>();
Workbook workbook=ExcelReader.getOneExcelWorkBook(request);
try {
for(int sheet=0;sheet<workbook.getNumberOfSheets();sheet++){
// 获取第sheet张Sheet表
Sheet rs = workbook.getSheetAt(sheet);
int rowCount = rs.getPhysicalNumberOfRows(); //获取总行数
if (rowCount == 0) {
// 导入数据表为空直接跳过
continue;
}
//获取标题，检查列数
int totalCellInt = rs.getRow(0).getPhysicalNumberOfCells();//获取总列数
for (int row = 1; row < rowCount; row++) {//跳过第一行标题
Map<String, Object> map = new LinkedHashMap<>();
for(int cellInt=0;cellInt<totalCellInt;cellInt++){
String noOneCellValue=getCellValue( rs.getRow(0).getCell(cellInt),true);//读取第一行标题为key
String curCellValue=getCellValue( rs.getRow(row).getCell(cellInt),true);//读取第一行标题为key
if(StringUtils.isNotEmpty(noOneCellValue))
map.put(noOneCellValue,curCellValue);
}
resultList.add(map);
}
}
} catch (ZdnstException e) {
throw new ZdnstException(e.getCode(),e);
} catch (Exception e) {
throw new ZdnstException(BaseCode.ERROR_CODE110,e);
}
return resultList;
}
/**
* 读取excel文件内容
* @param request
* @throws FileNotFoundException
* @throws FileFormatException
*/
public static Workbook getOneExcelWorkBook(HttpServletRequest request) throws ZdnstException{
Workbook workbook =null;
File tempFile=null;
try {
String fileType = SystemProperties.getProperties().getProperty(
"fileType");
// 获得磁盘文件条目工厂
DiskFileItemFactory factory = new DiskFileItemFactory();
// 获取文件需要上传到的路径
String path = request.getSession().getServletContext()
.getRealPath("/files");
// 如果没以下两行设置的话，上传大的 文件 会占用 很多内存，
// 设置暂时存放的 存储室 , 这个存储室，可以和 最终存储文件 的目录不同
factory.setRepository(new File(path));
// factory.s.setSizeMax(2*1024*1024);
// 设置 缓存的大小，当上传文件的容量超过该缓存时，直接放到 暂时存储室
factory.setSizeThreshold(1024 * 1024 * 10);// 10M缓存大小
ServletFileUpload upload = new ServletFileUpload(factory);
// 可以上传多个文件
List<FileItem> list = (List<FileItem>) upload.parseRequest(request);
if(list!=null&&list.size()>0) {
FileItem item = list.get(0);
String value = item.getName();
if (CommonUtils.isNotEmpty(value)) {
String filePath=path+"/"+value;
tempFile=new File(filePath);
item.write(tempFile);
// 检查
preReadCheck(filePath);
workbook = getWorkbook(filePath);
}
}
} catch (Exception e) {
e.printStackTrace();
throw new ZdnstException(BaseCode.ERROR_CODE200,"文件格式不正确");
}finally {
if(tempFile!=null){
try{
tempFile.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return workbook;
}
/***
* <pre>
* 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
* xls:HSSFWorkbook
* xlsx：XSSFWorkbook
* @param filePath
* @return
* @throws IOException
* </pre>
*/
private static Workbook getWorkbook(String filePath) throws IOException {
Workbook workbook = null;
InputStream is = new FileInputStream(filePath);
if (filePath.endsWith(EXTENSION_XLS)) {
workbook = new HSSFWorkbook(is);
} else if (filePath.endsWith(EXTENSION_XLSX)) {
workbook = new XSSFWorkbook(is);
}
return workbook;
}
/**
* 文件检查
* @param filePath
* @throws FileNotFoundException
* @throws FileFormatException
*/
private static void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException {
// 常规检查
File file = new File(filePath);
if (!file.exists()) {
throw new FileNotFoundException("传入的文件不存在：" + filePath);
}
if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) {
throw new FileFormatException("传入的文件不是excel");
}
}
/**
* 取单元格的值
* @param cell 单元格对象
* @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
* @return
*/
private static String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
if (treatAsStr) {
// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
// 加上下面这句，临时把它当做文本来读取
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}
三、编写control方法
/**
* 通用读取excel
* @param request
* @param response
* @return
*/
@RequestMapping(value = "/readCommonExcel", method = RequestMethod.POST)
public ModelAndView readCommonExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
try {
List<Map> dataMap = ExcelReader.readCommonExcel(request);
return JsonView.dataToJson(dataMap, response);
} catch (ZdnstException e) {
//打印异常消息
logger.error(Constants.EX_CONTROLLER_EXCEPTION + BaseCode.getDetailMsg(e));
//返回异常消息
return JsonView.addErrorToJson(e, response, null);
} catch (Exception e) {
//转换异常
ZdnstException ex = BaseCode.getZdnstException(e);
//打印详细信息
logger.error(Constants.EX_CONTROLLER_EXCEPTION + BaseCode.getDetailMsg(ex),e);
//返回异常消息
return JsonView.addErrorToJson(ex, response, null);
}
}
四、编写jsp，调试范例
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>上传附件</title>
</head>
<body>
<!-- 添加、编辑框开始 -->
<div>
<form id="myForm" method="post" action="/jsf/excel/readCommonExcel" enctype="multipart/form-data" >
<table>
<tr>
<td align="right"><span>上传封面：</span></td>
<td><input name="186bdc97-8426-11e5-a5bd-000c294debb1" type="file">
<input type="submit" name="submit" value="save"/>
</td>
</tr>
</table>
</form>
</div>
<!-- 添加框结束 -->
</body>
</html>
71
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
