poi批量导入导出Excel(二、不需要建数据库表一键实现导入导出功能)

本项目基于SSM框架,简单封装了Excel批量导入导出功能,不用建数据库表一键导入导出Excel,不过这样只适用于对导入的Excel表进行转换。上一篇介绍了建表导入导出Excel(点击跳转
一、下载poi jar包:
    点这里下载:poi 3.8/3.9/3.10三个版本下载
                        poi 3.17最新版本下载
二、基本操作步骤:   
首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)(HSSFSheet)组成,一个sheet是由多个row(行)(HSSFRow)组成,一个row是由多个cell(单元格)(HSSFCell)组成。
1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象,用行对象得到Cell对象
4、对Cell对象读写。

三、封装Excel工具类ImportExcelUtil

public class ImportExcelUtil {
	
	private final static String Excel_2003 = ".xls"; //2003 版本的excel
	private final static String Excel_2007 = ".xlsx"; //2007 版本的excel
	
	/**
	 * @param in
	 * @param fileName
	 * @param columNum 自定义列数
	 * @return
	 * */
	public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
		List<List<Object>> list = null;
		
		//创建Excel工作簿
		Workbook work = this.getWorkbook(in, fileName);
		if(work == null) {
			throw new Exception("创建Excel工作簿为空!");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		list = new ArrayList<List<Object>>();
		//遍历Excel中的所有sheet
		for(int i = 0; i<work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if(sheet == null) {continue;}
			//遍历当前sheet中的所有行
			//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式,这种方式取值不准确
			int totalRow = sheet.getPhysicalNumberOfRows();
			for(int j = sheet.getFirstRowNum(); j<totalRow; j++) {
				row = sheet.getRow(j);
				if(!isRowEmpty(row)) {
					//if(row != null && !"".equals(row)) {
					//获取第一个单元格的数据是否存在
					Cell fristCell=row.getCell(0);
					if(fristCell!=null){
						//遍历所有的列
						List<Object> li = new ArrayList<Object>();
						//int totalColum = row.getLastCellNum();
						for(int y = row.getFirstCellNum(); y<row.getLastCellNum(); y++) {
							cell = row.getCell(y);
							String callCal = this.getCellValue(cell)+"";
							li.add(callCal);
						}
						list.add(li);
					}

				}else if(isRowEmpty(row)){
					continue;
				}
				
			}
		}
		in.close();
		return list;
	}
	/**
	 * 判断行是否为空
	 * @param row
	 * @return
	 */
	public static boolean isRowEmpty(Row row) {
		   for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
		       Cell cell = row.getCell(c);
		       if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
		           return false;
		   }
		   return true;
		}
	/**
	 * 描述:根据文件后缀,自动适应上传文件的版本
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 * */
	public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception {
		Workbook work = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(Excel_2003.equals(fileType)){
			work=new HSSFWorkbook(inStr);//2003 版本的excel
		}else if(Excel_2007.equals(fileType)) {
			work=new XSSFWorkbook(inStr);//2007 版本的excel
		}else {
			throw new Exception("解析文件格式有误!");
		}
		return work;
	}
	
	/**
	 * 描述:对表格中数值进行格式化
	 * @param cell
	 * @return
	 * */
	public Object getCellValue(Cell cell) {
		/*Object value = null;
		DecimalFormat df1 = new DecimalFormat("0.00");//格式化number,string字符
		SimpleDateFormat sdf = new  SimpleDateFormat("yyy-MM-dd");//日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
		if(cell !=null && !"".equals(cell)) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df1.format(cell.getNumericCellValue());
				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				}else if(HSSFDateUtil.isCellDateFormatted(cell)){
					Date date = cell.getDateCellValue();
					value = sdf.format(date);				
				}
				else {
					value = df2.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				value = "";
				break;
			default:
				break;
			}
		}		
		return value;*/
		String result = new String();  
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_FORMULA:  //Excel公式
            try {  
            	result = String.valueOf(cell.getNumericCellValue());  
            } catch (IllegalStateException e) {  
            	result = String.valueOf(cell.getRichStringCellValue());
            }  
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
                SimpleDateFormat sdf;  
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
                        .getBuiltinFormat("h:mm")) {  
                    sdf = new SimpleDateFormat("HH:mm");  
                } else {// 日期  
                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                }  
                Date date = cell.getDateCellValue();  
                result = sdf.format(date);  
            } else if (cell.getCellStyle().getDataFormat() == 58) {  
                // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)  
                SimpleDateFormat sdf = new SimpleDateFormat("M月d日");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                result = sdf.format(date);  
            } else {  
                double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();  
                DecimalFormat format = new DecimalFormat();  
                String temp = style.getDataFormatString();  
                // 单元格设置成常规  
                if (temp.equals("General")) {  
                    format.applyPattern("#.##");  
                }  
                result = format.format(value);  
            }  
            break;  
        case HSSFCell.CELL_TYPE_STRING:// String类型  
            result = cell.getRichStringCellValue().toString();  
            break;  
        case HSSFCell.CELL_TYPE_BLANK:  
            result = "";  
        default:  
            result = "";  
            break;  
        }  
        return result;  
	}
	
	public String getFormat(String str) {
		if(str.equals("null")) {
			str="";
			return str;
		}else{
			return str;
		}	
	}
	public Integer getFormats(Integer str) {
		if(str==null) {
			str=0;
			return str;
		}else{
			return str;
		}	
	}
	
	/**
	 * 获取字符串中的数字订单号、数字金额等,如从"USD 374.69"中获取到374.69、从“交易单号:66666666666”获取到66666666666
	 * @param receiptAmountString
	 * @return
	 */
	public static String getFormatNumber(String str){
		str = str.trim();
		 Pattern p = Pattern.compile("[0-9]");
		 int indexNum = 0;
		 int lenght = str.length();
		 String num = "";
		 for(int i=0;i<lenght;i++){
			num += str.charAt(i);
		 	Matcher m = p.matcher(num);
		 	if(m.find()){
		 		indexNum = i;
		 		break;
		 	}
		  }
		 String formatNumber = str.substring(indexNum,lenght);
		 return formatNumber;
	}
}

四、控制层Controller

解决:MultipartFile文件或者图片上传一直为null问题

     /**
     * 导出销售订单数据
     * @param myFile
     * @param respon
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "export", method=RequestMethod.POST)
    public ModelAndView exportFile(@RequestParam(value="file",required=false)MultipartFile myFile,HttpServletResponse response,HttpServletRequest request)throws IOException {
    	ModelAndView mv = this.getModelAndView();
    	try {
    		ImportExcelUtil util = new ImportExcelUtil();
    		InputStream input = null;
    		List<List<Object>> lists = null;
    		if (myFile.isEmpty()) {
    			logBefore(logger, "导入文件为空,请先添加Excel文件!");
    		} else {
    			String fileName = myFile.getOriginalFilename();
    			input = myFile.getInputStream();
    			lists = util.getBankListByExcel(input, fileName);
    			input.close();
    			//导出用SXSSFWorkbook
    			SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
    			CellStyle style = workbook.createCellStyle();
    			style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//SXSSFWorkbook方式的居中
    			// 创建一个sheet页
    			SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("销售订单");
    			// 创建标题
    			SXSSFRow headRow = (SXSSFRow) sheet.createRow(0);
    			headRow.createCell(0).setCellValue("单据日期");
    			headRow.createCell(1).setCellValue("单据编号");
    			headRow.createCell(2).setCellValue("客户编码");
    			headRow.createCell(3).setCellValue("客户");
    			headRow.createCell(4).setCellValue("结算客户编码");
    			headRow.createCell(5).setCellValue("结算客户");
    			headRow.createCell(6).setCellValue("部门编码");
    			headRow.createCell(7).setCellValue("部门");
    			headRow.createCell(8).setCellValue("业务员编码");
    			headRow.createCell(9).setCellValue("业务员");
    			headRow.createCell(10).setCellValue("币种代码");
    			headRow.createCell(11).setCellValue("币种");
    			headRow.createCell(12).setCellValue("汇率");
    			headRow.createCell(13).setCellValue("运输方式");
    			headRow.createCell(14).setCellValue("送货地址");
    			headRow.createCell(15).setCellValue("联系人");
    			headRow.createCell(16).setCellValue("联系电话");
    			headRow.createCell(17).setCellValue("客户手机号");
    			headRow.createCell(18).setCellValue("合同号");
    			headRow.createCell(19).setCellValue("收款方式");
    			headRow.createCell(20).setCellValue("外部单据号");
    			headRow.createCell(21).setCellValue("按仓库拆单出库");	
    			headRow.createCell(22).setCellValue("国际单号");
    			headRow.createCell(23).setCellValue("内部单号");
    			headRow.createCell(24).setCellValue("备注");
    			headRow.createCell(25).setCellValue("仓库编码");
    			headRow.createCell(26).setCellValue("仓库");
    			headRow.createCell(27).setCellValue("项目编码");
    			headRow.createCell(28).setCellValue("项目");
    			headRow.createCell(29).setCellValue("存货编码");
    			headRow.createCell(30).setCellValue("存货名称");
    			headRow.createCell(31).setCellValue("销售单位");
    			headRow.createCell(32).setCellValue("数量");
    			headRow.createCell(33).setCellValue("报价");
    			headRow.createCell(34).setCellValue("折扣%");
    			headRow.createCell(35).setCellValue("税率%");
    			headRow.createCell(36).setCellValue("含税单价");
    			headRow.createCell(37).setCellValue("含税金额");
    			headRow.createCell(38).setCellValue("预计交货日期");
    			headRow.createCell(39).setCellValue("赠品");
    			headRow.createCell(40).setCellValue("备注");
    			headRow.createCell(41).setCellValue("国家");
    			headRow.createCell(42).setCellValue("货代");
    			headRow.createCell(43).setCellValue("客户单号");
    			// 循环将excel中的数据存入库
    			for (int i = 1; i < lists.size(); i++) {
    				List<Object> list = lists.get(i);
    				SXSSFRow dataRow = (SXSSFRow) sheet.createRow(sheet.getLastRowNum() + 1);
    				dataRow.createCell(0).setCellValue(util.getFormat(String.valueOf(list.get(0))));//单据日期
    				dataRow.createCell(1).setCellValue(util.getFormat(String.valueOf(list.get(1))));//单据编号(内部单号)
    				dataRow.createCell(2).setCellValue(util.getFormat(String.valueOf(list.get(21))));//往来单位编码(客户编码)(结算客户编码)
    				dataRow.createCell(3).setCellValue("");//客户
    				dataRow.createCell(4).setCellValue(util.getFormat(String.valueOf(list.get(21))));//结算客户编码
    				dataRow.createCell(5).setCellValue("");//结算客户
    				dataRow.createCell(6).setCellValue(util.getFormat(String.valueOf(list.get(23))));//部门编码
    				dataRow.createCell(7).setCellValue("");//部门
    				dataRow.createCell(8).setCellValue(util.getFormat(String.valueOf(list.get(19))));//业务员编码
    				dataRow.createCell(9).setCellValue(util.getFormat(String.valueOf(list.get(20))));//业务员
    				dataRow.createCell(10).setCellValue(util.getFormat(String.valueOf(list.get(12))));//币种代码
    				dataRow.createCell(11).setCellValue("");//币种
    				dataRow.createCell(12).setCellValue(util.getFormat(String.valueOf(list.get(13))));//汇率
    				dataRow.createCell(13).setCellValue("");//运输方式
    				dataRow.createCell(14).setCellValue("");//送货地址
    				dataRow.createCell(15).setCellValue("");//联系人
    				dataRow.createCell(16).setCellValue("");//联系电话
    				dataRow.createCell(17).setCellValue("");//客户手机号
    				dataRow.createCell(18).setCellValue("");//合同号
    				dataRow.createCell(19).setCellValue("其它");//收款方式
    				dataRow.createCell(20).setCellValue("");//外部单据号
    				dataRow.createCell(21).setCellValue("");//按仓库拆单出库
    				dataRow.createCell(22).setCellValue("");//国际单号
    				dataRow.createCell(23).setCellValue(util.getFormat(String.valueOf(list.get(4))));//内部单号
    				dataRow.createCell(24).setCellValue(util.getFormat(String.valueOf(list.get(6))));//备注
    				dataRow.createCell(25).setCellValue(util.getFormat(String.valueOf(list.get(24))));//仓库编码
    				dataRow.createCell(26).setCellValue("");//仓库
    				dataRow.createCell(27).setCellValue("");//项目编码
    				dataRow.createCell(28).setCellValue("");//项目
    				dataRow.createCell(29).setCellValue(util.getFormat(String.valueOf(list.get(8))));//存货编码
    				dataRow.createCell(30).setCellValue("");//存货名称
    				dataRow.createCell(31).setCellValue("个");//销售单位
    				dataRow.createCell(32).setCellValue(util.getFormat(String.valueOf(list.get(9))));//数量
    				dataRow.createCell(33).setCellValue("");//报价
    				dataRow.createCell(34).setCellValue("");//折扣%
    				dataRow.createCell(35).setCellValue("0");//税率%
    				dataRow.createCell(36).setCellValue(util.getFormat(String.valueOf(list.get(10))));//含税单价
    				dataRow.createCell(37).setCellValue(util.getFormat(String.valueOf(list.get(11))));//含税金额
    				dataRow.createCell(38).setCellValue("");//预计交货日期
    				dataRow.createCell(39).setCellValue("");//赠品
    				dataRow.createCell(40).setCellValue("");//备注
    				dataRow.createCell(41).setCellValue(util.getFormat(String.valueOf(list.get(15))));//国家
    				dataRow.createCell(42).setCellValue(util.getFormat(String.valueOf(list.get(17))));//货代?国际
    				dataRow.createCell(43).setCellValue(util.getFormat(String.valueOf(list.get(5))));//客户单号
    			}
    			
    			// 设置Excel文件名,并以中文进行编码
    			String codedFileName = new String("销售订单".getBytes("gbk"), "iso-8859-1");
    			response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + DateUtil.getDays() +".xlsx");
    			// 响应类型,编码
    			response.setContentType("application/octet-stream;charset=UTF-8");
    			// 形成输出流
    			OutputStream osOut = response.getOutputStream();
    			// 将指定的字节写入此输出流
    			workbook.write(osOut);
    			// 刷新此输出流并强制将所有缓冲的输出字节被写出
    			osOut.flush();
    			// 关闭流
    			osOut.close();
    			/*
    			 * dispose of temporary files backing this workbook on disk
    			 * 处理在磁盘上备份此工作簿的临时文件 SXSSF分配临时文件,您必须始终清除显式,通过调用dispose方法
    			 */
    			workbook.dispose();
    		}
    		
    	} catch (Exception e) {
    		e.printStackTrace();
    		System.out.println("异常信息: " + e.getMessage() );
    		//发送错误到前台页面
    		mv.addObject("msg","error");
    		mv.setViewName("purchasing/orderExcel/orderExcelList");
    		return mv;
    	}
    	mv.addObject("msg","success");
		return mv;
    }

 

五、jsp页面

 

<form action="orderExcel/export.do" name="Form" id="Form" method="post" enctype="multipart/form-data">
		<table id="table_report" class="table table-striped table-bordered table-hover">
			<tr>
				<td style="width:70px;text-align: right;padding-top: 13px;">选择Excel:</td>
				<td><input id="uploadFile" name="file" type="file" style="width:330px" onchange="fileType(this)"/><br/><br/>  
				</td>
			</tr>
			 <tr>
				<td style="text-align: center;" colspan="10">
					<input id="btnImportSubmit" class="btn btn-small btn-success" type="submit" value="Excel转换"/>
				</td>
			</tr>
		</table>
</form>
<script type="text/javascript">
		//接收错误信息判断!
		var msg = '${msg}';
		if(msg == 'error'){
			alert("导出文件失败!请检查Excel文件!")
		}
		$(top.hangge());
		
		//检索
		function search(){
			top.jzts();
			$("#Form").submit();
		}
		//过滤类型
		function fileType(obj){
			var fileType=obj.value.substr(obj.value.lastIndexOf(".")).toLowerCase();//获得文件后缀名
		    if(fileType != '.xls' && fileType != '.xlsx'){
		    	$("#uploadFile").tips({
					side:3,
		            msg:'请选择正确的Excel文件',
		            bg:'#AE81FF',
		            time:3
		        });
		    	$("#uploadFile").val('');
		    }
		}
</script>

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值