使用HSSFWorkBooK导入Excel表

本文介绍了一个使用Java实现的Excel数据导入示例,包括文件上传、数据读取等关键步骤,并提供了完整的代码示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


实现步骤:

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());
	}

}



Excel表:





测试结果:


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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值