首先说一下所使用的POI版本3.8，需要用的的Jar包：
dom4j-1.6.1.jar
poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
poi-scratchpad-3.8-20120326.jar
xmlbeans-2.3.0.jar
web前端页面：
action:填写请求地址；
enctype="multipart/form-data"：一定要带上，要不然后台获取不到文件的字节流；
<form id="AddForm" action="excel/fileInfo.do" method="post" enctype="multipart/form-data">
<input id="excel" type="file" name="excel" /> <br />
<input onclick="submitFile()" type="button" value="提交" />
</form>
struts2的请求配置：
<package name="drugs" extends="struts-default" namespace="/drugs">
<action name="fileInfo" class="com.daat.manager.drugs.web.actions.DrugsAction" method="fileInfo">
<result name="success" type="jsonResult"></result>
</action>
</packge>
java处理struts2请求：
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.ServletActionContext; public class DrugsAction extends ActionSupport{
private File excelFile;
private String excelFileFileName; public File getExcelFile() {
return excelFile;
} public void setExcelFile(File excelFile) {
this.excelFile = excelFile;
} public String getExcelFileFileName() {
return excelFileFileName;
} public void setExcelFileFileName(String excelFileFileName) {
this.excelFileFileName = excelFileFileName;
}
public String fileInfo(){
try {
//创建新文件名
String excelName = "xinwenjian";
//获取文件上传路径
String filePath = getFilePath(excelFileName1);
//文件复制后保存路径
String path="WEB-INF/upload/";
//根据相对部署路径计算完整路径
path=ServletActionContext.getServletContext().getRealPath(path);
System.out.println(path);
String sufix = FileUtil.getFileSufix(excelFileFileName).toLowerCase();
excelFileName1 = excelFileName1 + "." + sufix;
//判断上传文件的保存目录是否存在
if (!file.exists() && !file.isDirectory()) {
//创建目录
file.mkdir();
}
//复制文件到服务器
FileUtils.copyFile(excelFile,new File(path,excelFileName1));
//解析excel文件
allInfo(path,excelFileName1);
} catch (IOException e) {
System.out.println("error");
e.printStackTrace();
}
//this.outJsonString("success");
return getResult();
}
/**
* 获取文件上传路径
* @param dirpath
* @return
*/
public static String getFilePath(String fileName) {
System.out.print(fileName);
String filePath = fileName.substring(0, 2)+File.separator+fileName.substring(2, 4)+File.separator
+fileName.substring(4, 6)+File.separator;
return filePath;
}
}
解析Excel类
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.ss.usermodel.WorkbookFactory; public class UserInfo { public static void main(String[] args){
String path2003 = "D:\\Users\\star\\Workspaces\\MyEclipse 10\\.metadata\\.me_tcat\\webapps\\dms\\WEB-INF";
String FileName="xinwenjian.xls";
//解析文件
excelInfo(path2003,FileName);
//删除文件
deleteFile(path2003,FileName);
} public static List excelInfo(String path,String FileName){
long start2003 = System.currentTimeMillis();
List excellist=new ArrayList();
String path2003 = "";//Excel2003文件路径 //解析Excel
excellist = parseExcel(path+File.separator+FileName);
System.out.println(excellist.size());
System.out.println("excellist"+excellist.toString());
System.out.println("size:"+excellist.size());
long end2003 = System.currentTimeMillis();
System.out.println("解析Excel2003完毕！共用时"+(end2003-start2003)+"毫秒！");
deleteFile(path,FileName);
return excellist;
} /**
* 根据路径加载解析Excel
* @param path
* @return
*/
public static List parseExcel(String path){
List excellist=new ArrayList();
System.out.println(path);
Map map = new HashMap();
File file = null;
InputStream input = null;
Workbook workBook = null;
Sheet sheet = null;
if(path!=null&&path.length()>7){
//判断文件是否是Excel(2003、2007)
String suffix = path.substring(path.lastIndexOf("."),path.length());
if (".xls".equals(suffix) || ".xlsx".equals(suffix)) {// 2003后缀或2007后缀
file = new File(path);
try {
input = new FileInputStream(file);
} catch (FileNotFoundException e) {
System.out.println("未找到指定的文件！");
e.printStackTrace();
} catch (Exception e) {
System.out.println("读取Excel文件发生异常！");
e.printStackTrace();
}
if(!input.markSupported()){
input = new PushbackInputStream(input,8);
}
try{
if(POIFSFileSystem.hasPOIFSHeader(input) || POIXMLDocument.hasOOXMLHeader(input)){
workBook = WorkbookFactory.create(input);
}else{
System.out.println("非法的输入流：当前输入流非OLE2流或OOXML流！");
}
} catch(IOException e){
System.out.println("创建表格工作簿对象发生IO异常！原因："+e.getMessage());
e.printStackTrace();
} catch(InvalidFormatException e){
//Your InputStream was neither an OLE2 stream, nor an OOXML stream.
System.out.println("非法的输入流：当前输入流非OLE2流或OOXML流！");
e.printStackTrace();
}
try{
if(workBook!=null){
int numberSheet = workBook.getNumberOfSheets();
if(numberSheet>0){
sheet = workBook.getSheetAt(0);//获取第一个工作簿(Sheet)的内容【注意根据实际需要进行修改】
excellist = getExcelContent(sheet);
}else{
System.out.println("目标表格工作簿(Sheet)数目为0！");
}
}
input.close();
}catch(IOException e){
System.out.println("关闭输入流异常！"+e.getMessage());
e.printStackTrace();
}
}else{
System.out.println("非法的Excel文件后缀！");
}
}else{
System.out.println("非法的文件路径!");
}
return excellist;
} /**
* 解析(读取)Excel内容
* @param sheet
* @return
*/
@SuppressWarnings("static-access")
public static List getExcelContent(Sheet sheet){
List excellist=new ArrayList(); Date data=new Date();
int rowCount = sheet.getPhysicalNumberOfRows();//总行数
if(rowCount>1){
Row titleRow = sheet.getRow(2);//标题行
int columnNum=titleRow.getPhysicalNumberOfCells();//总列数
for(int i=3;i<rowCount-1;i++){//遍历行，略过标题行，从第二行开始
Row row = sheet.getRow(i);//第i行
Map map=new HashMap();
for(int j=1;j<columnNum;j++){
Cell cell = row.getCell(j);//列
String title=titleRow.getCell(j).getStringCellValue();//标题
Object conten=isNUMERICAndSTRING(cell);
//当读取到null时不会报错，继续执行
if("NaN".equals(conten)){
conten=" ";
}
//string类型
if(title.indexOf("架位号")>=0){
map.put("shelfNum",conten);
}
//数字类型
else if(title.indexOf("商品类型")>=0){
map.put("drugType", (int)cell.getNumericCellValue());
}
//判断日期类型
else if(titleRow.getCell(j).getStringCellValue().indexOf("入库日期")>=0){
if(HSSFDateUtil.isCellDateFormatted(cell)){
data=cell.getDateCellValue();
map.put("storageDate", data);
}
}
}
excellist.add(map);
System.out.println("第"+i+"行"+excellist.toString());
}
}
return excellist;
}
//判断行内值的类型
public static Object isNUMERICAndSTRING(Cell cell){
if(cell==null ){
return "NaN";
}else if(cell.getCellType()==cell.CELL_TYPE_STRING){
return cell.getStringCellValue().trim();
}else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
return cell.getNumericCellValue();
}
return "error";
}
//删除文件
private static void deleteFile(String path,String FileName) {
File file = new File(path);
File Array[] = file.listFiles();
for (File f : Array) {
if (f.isFile()) {// 如果是文件
if (f.getName().equals(FileName)) {
f.delete();
return;
}
}
}
} }