POI上传，导入excel文件到服务器1
首先说一下所使用的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; } } } } }
