使用HSSFWorkBooK导入Excel表

实现步骤：

1 使用commons-fileupload上传文件

2 使用HSSFWorkBooK读取数据

所需jar包：

文件上传的jar下载 ：点击这里

HSSFWorkbook所用jar包下载：点击这里

servlet.java 

package cn.imports.com;import java.io.IOException;import java.util.Iterator;import java.util.List;import java.util.Map;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.excel.util.ImportUtil;import cn.upload.util.FileUploadUtil;/** * 导入Excel数据表  * @author  */@SuppressWarnings("serial")public class ImportServlet extends HttpServlet{@SuppressWarnings({ "deprecation", "unchecked" })@Overridepublic void doPost(HttpServletRequest request, HttpServletResponse resp)throws ServletException, IOException { try {String path = request.getRealPath("/upload");String filename = FileUploadUtil.filesUpload(request, path);System.out.println("upload success! reading...");List list = ImportUtil.read(path + "/" + filename, 5);//从表的第5行开始读取for(Iterator iterator = list.iterator();iterator.hasNext();){Map perRecord = (Map) iterator.next();String xuhao = (String)perRecord.get("1") == null ? "" : (String)perRecord.get("1");String mingchen = (String)perRecord.get("2") == null ? "" : (String)perRecord.get("2");String chandi = (String)perRecord.get("3") == null ? "" : (String)perRecord.get("3");String hege = (String)perRecord.get("4") == null ? "" : (String)perRecord.get("4");String shuifen = (String)perRecord.get("5") == null ? "" : (String)perRecord.get("5");String yunshu  = (String)perRecord.get("6") == null ? "" : (String)perRecord.get("6");String tongjishijian  = (String)perRecord.get("7") == null ? "" : (String)perRecord.get("7");String renyuan = (String)perRecord.get("8") == null ? "" : (String)perRecord.get("8");String beizhu  = (String)perRecord.get("9") == null ? "" : (String)perRecord.get("9");System.out.println("-------------");System.out.println("序号:" + xuhao);System.out.println("名称:" + mingchen);System.out.println("产地:" + chandi);System.out.println("合格:" + hege);System.out.println("含水分:" + shuifen);System.out.println("运输:" + yunshu);System.out.println("统计时间:" + tongjishijian);System.out.println("统计人员:" + renyuan);System.out.println("备注:" + beizhu);System.out.println("---------------");}System.out.println("over!");} catch (Exception e) {e.printStackTrace();}}}

util.java

package cn.upload.util;import java.io.File;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.util.List;import javax.servlet.http.HttpServletRequest;import org.apache.commons.fileupload.FileItem;import org.apache.commons.fileupload.disk.DiskFileItemFactory;import org.apache.commons.fileupload.servlet.ServletFileUpload;/** * 文件上传Util * @author  */public class FileUploadUtil {@SuppressWarnings("unchecked")public static String filesUpload(HttpServletRequest request, String path) throws Exception{request.setCharacterEncoding("utf-8");  //设置编码      String fileName = null;    //获得磁盘文件条目factory    DiskFileItemFactory dfiFactory = new DiskFileItemFactory();      dfiFactory.setRepository(new File(path));      dfiFactory.setSizeThreshold(1024*1024) ;      ServletFileUpload upload = new ServletFileUpload(dfiFactory);                //多个文件 上传处理        List<FileItem> list = (List<FileItem>)upload.parseRequest(request);          for(FileItem item : list){              if(!item.isFormField()){                                    //获取路径名                  String value = item.getName() ;                  int start = value.lastIndexOf("\\");                  //获取文件名                fileName = value.substring(start+1);                  //文件写到磁盘上                  //item.write( new File(path,filename) );                OutputStream os = new FileOutputStream(new File(path,fileName));                  InputStream is = item.getInputStream() ;                  int length = 0 ;                  byte [] buf = new byte[1024] ;                  while( (length = is.read(buf) ) != -1){                      os.write(buf, 0, length);                  }                  is.close();                  os.close();              }          }  return fileName;}}

package cn.excel.util;import java.io.FileInputStream;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.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/** * 读取Excel Util * @author  */public class ImportUtil {@SuppressWarnings("unchecked")public static List read(String path, int starRow) throws Exception{List resList = null;if(!"".equals(path) && path != null){//确认文件类型是否正确String fileType = path.substring(path.lastIndexOf("."), path.length());if(fileType.equals(".xls")){//开始读取数据 resList = readExcel(path,starRow);}}return resList;}/**遍历表格 * @param path:    Excel表物理路径 * @param starRow：   指定遍历表格时的起始行号，表格中第一行的行号为‘0’ * @return List:   List中的元素为Map, Map的键值为Excel表中列的索引号，从‘1’开始，类型为String  */@SuppressWarnings({ "unchecked"})private static List readExcel(String path, int starRow) throws Exception{List resultList = new ArrayList();FileInputStream is;Map resultMap;is = new FileInputStream(path);HSSFWorkbook hssfWorkBook = new HSSFWorkbook(is);//读取第一张工作表HSSFSheet hssfSheet = hssfWorkBook.getSheetAt(0);//循环行Row （注：hssfSheet.getLastRowNum()的值等于表格的总行数-1）for(int rowNum = starRow; rowNum <= hssfSheet.getLastRowNum(); rowNum++){HSSFRow  hssfRow = hssfSheet.getRow(rowNum);if(hssfRow == null)continue;//循环列CellresultMap = new HashMap();for(int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++){HSSFCell hssfCell = hssfRow.getCell(cellNum);if(hssfCell == null){resultMap.put((cellNum + 1)+"", null);continue;}//如果当前列不为空，放入Map (放入格式为： Map<'列号','该列的值'>)resultMap.put((cellNum + 1)+"", getValue(hssfCell));}resultList.add(resultMap);}return resultList;}/** * 取得每一列的值 * @param hssfCell ： 表格的列Cell *  列的值有几种情况： * 1>:布尔类型的值 * 2>:时间类型的值 * 3>:数值类型的值 * 4>:字符串类型的值 * @return: 返回取得的值 */private static String getValue(HSSFCell hssfCell){//处理返回布尔类型的值if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){return String.valueOf(hssfCell.getBooleanCellValue());}else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//处理返回时间类型的值if(HSSFDateUtil.isCellDateFormatted(hssfCell)){SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");Date cellDate = hssfCell.getDateCellValue();if(cellDate == null)return null;return dateFormat.format(cellDate);}//处理返回数值类型的值(包括处理大数值数据，避免返回带对数的值)else{long cellData = (long) hssfCell.getNumericCellValue();if(cellData == hssfCell.getNumericCellValue())return String.valueOf(cellData);elsereturn String.valueOf(hssfCell.getNumericCellValue()).trim();}}//处理返回字符串类型的值return String.valueOf(hssfCell.getRichStringCellValue());}}

Excel表：

测试结果：

-------------
序号:1
名称:芒果
产地:海南
合格:是
含水分:0.6
运输:海运
统计时间:10月31日
统计人员:张三
备注:无
---------------