JAVA读取Excel文档
获取Excel文档
/**
* 获取文档
*/
public void importCountryExcelFile(){
InputStream input = null;
MFacesContext context = MFacesContext.getCurrentInstance();
HttpServletRequest request = (HttpServletRequest) context.getExternalContext().getRequest();
HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
if (request instanceof ServletRequest) {
ServletRequest multipartRequest = (ServletRequest) request;
while (multipartRequest != null && !(multipartRequest instanceof MultipartRequestWrapper)) {
if (multipartRequest instanceof HttpServletRequestWrapper) {
multipartRequest = ((HttpServletRequestWrapper) multipartRequest).getRequest();
} else {
multipartRequest = null;
}
}
if (multipartRequest != null) {
MultipartRequestWrapper mpReq = (MultipartRequestWrapper) multipartRequest;
if (context.getMessages() != null && context.getMessages().hasNext()) {
return;
}
String msg = "";
FileItem fileItem = mpReq.getFileItem("fileUpload001");
if (fileItem != null) {
try {
input = fileItem.getInputStream();
msg = readTaskExcel(input); // 返回上传结果
try {
toolMgr.outputData(msg);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} // 获得输入流
}else{
try {
toolMgr.outputData("请选择文件");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
读取xls文件
/*
* 读取xls文件
*/
private String readTaskExcel(InputStream input) {
HSSFWorkbook hssfWorkbook = null;
HSSFSheet hssfSheet = null;
StringBuffer msg = new StringBuffer("");
try {
hssfWorkbook = new HSSFWorkbook(input);
// 循环行Row
if (hssfWorkbook != null && hssfWorkbook.getNumberOfSheets() > 0) {
hssfSheet = hssfWorkbook.getSheetAt(0);
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
//第rowNum 行的第0列表格
HSSFCell codeCell = hssfRow.getCell(0);
HSSFCell countryNameCell = hssfRow.getCell(1);
//第rowNum 行的第0列数据
String code = parseExcel(codeCell);
String countryName = parseExcel(countryNameCell);
}
}
}
return msg.toString();
} catch (Exception e) {
e.printStackTrace();
return e.getMessage(); // excel的保存类型不正确
} finally {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
将HSSFCell解析出数据
/**
* 判断数据的类型
* @param cell
* @return
*/
private String parseExcel(HSSFCell cell) {
String cellValue = null;
if(cell==null){
return cellValue;
}
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = "错误";
break;
default:
cellValue = "错误";
}
return cellValue;
}
导入时会出现excel2003和excel2007版本的问题,文件后缀名是xls或者xlsx,用错类的话会报下面的错:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:131)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:138)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:322)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:303)
at com.kangxin.basic.service.ImportCountry.readTaskExcel(ImportCountry.java:98)
at com.kangxin.basic.service.ImportCountry.importCountryExcelFile(ImportCountry.java:65)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.matrix.form.engine.FormActionExecutor.execute(FormActionExecutor.java:176)
at com.matrix.form.engine.FormActionExecutor$$FastClassByCGLIB$$bec4c6cf.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
at com.matrix.msg.ErrorHandlerInterceptor.handleError(ErrorHandlerInterceptor.java:54)
at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.matrix.form.engine.FormActionExecutor$$EnhancerByCGLIB$$998e0b94.execute(<generated>)
at com.matrix.servlet.FormRunTimeServlet.exeFormEvent(FormRunTimeServlet.java:1209)
at com.matrix.servlet.FormRunTimeServlet.service(FormRunTimeServlet.java:648)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.matrix.client.foundation.identifyvalidator.filter.UserLogonFilter.doFilterInternal(UserLogonFilter.java:97)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.elk.filter.LoggerMDCFilter.doFilterInternal(LoggerMDCFilter.java:66)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.matrix.form.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:114)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.matrix.form.admin.common.webfilter.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:40)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
解决方法也很简单,如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook,下面例子导入的是xls文件,所以用的是HSSFWorkbook