实现步骤:
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" })
@Override
public 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;
//循环列Cell
resultMap = 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);
else
return String.valueOf(hssfCell.getNumericCellValue()).trim();
}
}
//处理返回字符串类型的值
return String.valueOf(hssfCell.getRichStringCellValue());
}
}
测试结果:
-------------
序号:1
名称:芒果
产地:海南
合格:是
含水分:0.6
运输:海运
统计时间:10月31日
统计人员:张三
备注:无
---------------