SSM之poi导人

最新推荐文章于 2020-02-11 10:23:53 发布

没有五官的企鹅

最新推荐文章于 2020-02-11 10:23:53 发布

阅读量228

点赞数

分类专栏： SSM+poi导入导出文章标签： SSM之poi导入

本文链接：https://blog.csdn.net/qq_37000286/article/details/88864560

版权

SSM+poi导入导出专栏收录该内容

2 篇文章 0 订阅

订阅专栏

导入poi包
在这里插入图片描述
js

实体类
在这里插入图片描述

公共类ExcelUtilDr

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

public class ExcelUtilDr {
	private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
      
    /** 
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
     * @param in,fileName 
     * @return 
     * @throws IOException  
     */  
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
        List<List<Object>> list = null;  
          
        //创建Excel工作薄  
        Workbook work = this.getWorkbook(in,fileName);  
        if(null == work){  
            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中的所有行  
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                row = sheet.getRow(j);  
                if(row==null||row.getFirstCellNum()==j){continue;}  
                  
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                    cell = row.getCell(y);  
                    li.add(this.getValue(cell));  
                }  
                list.add(li);  
            }  
        }  
 
        return list;  
        
    }  
      
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
  
    /** 
     * 描述：对表格中数值进行格式化 
     * @param cell 
     * @return 
     */  
  //解决excel类型问题，获得数值  
    public  String getValue(Cell cell) {  
        String value = "";  
        if(null==cell){  
            return value;  
        }  
        switch (cell.getCellType()) {  
        //数值型  
        case Cell.CELL_TYPE_NUMERIC:  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                //如果是date类型则 ，获取该cell的date值  
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());  
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
                value = format.format(date);;  
            }else {// 纯数字  
                BigDecimal big=new BigDecimal(cell.getNumericCellValue());  
                value = big.toString();  
                //解决1234.0  去掉后面的.0  
                if(null!=value&&!"".equals(value.trim())){  
                     String[] item = value.split("[.]");  
                     if(1<item.length&&"0".equals(item[1])){  
                         value=item[0];  
                     }  
                }  
            }  
            break;  
            //字符串类型   
        case Cell.CELL_TYPE_STRING:  
            value = cell.getStringCellValue().toString();  
            break;  
        // 公式类型  
        case Cell.CELL_TYPE_FORMULA:  
            //读公式计算值  
            value = String.valueOf(cell.getNumericCellValue());  
            if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串  
                value = cell.getStringCellValue().toString();  
            }  
            break;  
        // 布尔类型  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = " "+ cell.getBooleanCellValue();  
            break;   
        default:  
            value = cell.getStringCellValue().toString();  
    }  
    if("null".endsWith(value.trim())){  
        value="";  
    }  
  return value;  
    }  
    
}

Dao层

//样本录入
	void insertYblr(Yblr yblr)throws Exception;
	void updateYblr(Yblr yblr)throws Exception;

.xml映射文件

<insert id="insertYblr">
		INSERT INTO zzzz_ybdj_info 
		(user_id,d1,d2,d3,d4,d5
		<if test="d10!=null">
		,d10
		</if>
		<if test="d11!=null">
		,d11
		</if>
		<if test="d12!=null">
		,d12
		</if>
		<if test="d13!=null">
		,d13
		</if>
		<if test="d14!=null">
		,d14
		</if>
		<if test="d15!=null">
		,d15
		</if>
		<if test="d16!=null">
		,d16
		</if>
		<if test="d17!=null">
		,d17
		</if>
		<if test="d18!=null">
		,d18
		</if>
		<if test="d19!=null">
		,d19
		</if>
		<if test="d20!=null">
		,d20
		</if>
		<if test="d21!=null">
		,d21
		</if>
		<if test="d22!=null">
		,d22
		</if>
		<if test="d23!=null">
		,d23
		</if>
		<if test="d24!=null">
		,d24
		</if>
		<if test="d25!=null">
		,d25
		</if>
		<if test="d26!=null">
		,d26
		</if>
		<if test="d27!=null">
		,d27
		</if>
		<if test="d28!=null">
		,d28
		</if>
		<if test="d29!=null">
		,d29
		</if>
		<if test="d30!=null">
		,d30
		</if>
		<if test="d75!=null">
		,d75
		</if>
		<if test="d76!=null">
		,d76
		</if>
		<if test="d77!=null">
		,d77
		</if>
		<if test="d78!=null">
		,d78
		</if>
		<if test="d79!=null">
		,d79
		</if>
		<if test="d80!=null">
		,d80
		</if>
		<if test="d81!=null">
		,d81
		</if>
		<if test="d82!=null">
		,d82
		</if>
		<if test="d83!=null">
		,d83
		</if>
		) 
		VALUE 
		(#{user_id},#{d1},#{d2},2,#{d4},#{d5}
		<if test="d10!=null">
		,#{d10}
		</if>
		<if test="d11!=null">
		,#{d11}
		</if>
		<if test="d12!=null">
		,#{d12}
		</if>
		<if test="d13!=null">
		,#{d13}
		</if>
		<if test="d14!=null">
		,#{d14}
		</if>
		<if test="d15!=null">
		,#{d15}
		</if>
		<if test="d16!=null">
		,#{d16}
		</if>
		<if test="d17!=null">
		,#{d17}
		</if>
		<if test="d18!=null">
		,#{d18}
		</if>
		<if test="d19!=null">
		,#{d19}
		</if>
		<if test="d20!=null">
		,#{d20}
		</if>
		<if test="d21!=null">
		,#{d21}
		</if>
		<if test="d22!=null">
		,#{d22}
		</if>
		<if test="d23!=null">
		,#{d23}
		</if>
		<if test="d24!=null">
		,#{d24}
		</if>
		<if test="d25!=null">
		,#{d25}
		</if>
		<if test="d26!=null">
		,#{d26}
		</if>
		<if test="d27!=null">
		,#{d27}
		</if>
		<if test="d28!=null">
		,#{d28}
		</if>
		<if test="d29!=null">
		,#{d29}
		</if>
		<if test="d30!=null">
		,#{d30}
		</if>
		<if test="d75!=null">
		,#{d75}
		</if>
		<if test="d76!=null">
		,#{d76}
		</if>
		<if test="d77!=null">
		,#{d77}
		</if>
		<if test="d78!=null">
		,#{d78}
		</if>
		<if test="d79!=null">
		,#{d79}
		</if>
		<if test="d80!=null">
		,#{d80}
		</if>
		<if test="d81!=null">
		,#{d81}
		</if>
		<if test="d82!=null">
		,#{d82}
		</if>
		<if test="d83!=null">
		,#{d83}
		</if>
		)
	</insert>
	
<update id="updateYblr">
	update zzzz_ybdj_info set d2=2
	<if test="d1!=null">
	,d1=#{d1}
	</if>
	<if test="d3!=null">
	,d3=2
	</if>
	<if test="d4!=null">
	,d4=#{d4}
	</if>
	<if test="d5!=null">
	,d5=#{d5}
	</if>
	<if test="d10!=null">
	,d10=#{d10}
	</if>
	<if test="d11!=null">
	,d11=#{d11}
	</if>
	<if test="d12!=null">
	,d12=#{d12}
	</if>
	<if test="d13!=null">
	,d13=#{d13}
	</if>
	<if test="d14!=null">
	,d14=#{d14}
	</if>
	<if test="d15!=null">
	,d15=#{d15}
	</if>
	<if test="d16!=null">
	,d16=#{d16}
	</if>
	<if test="d17!=null">
	,d17=#{d17}
	</if>
	<if test="d18!=null">
	,d18=#{d18}
	</if>
	<if test="d19!=null">
	,d19=#{d19}
	</if>
	<if test="d20!=null">
	,d20=#{d20}
	</if>
	<if test="d21!=null">
	,d21=#{d21}
	</if>
	<if test="d22!=null">
	,d22=#{d22}
	</if>
	<if test="d23!=null">
	,d23=#{d23}
	</if>
	<if test="d24!=null">
	,d24=#{d24}
	</if>
	<if test="d25!=null">
	,d25=#{d25}
	</if>
	<if test="d26!=null">
	,d26=#{d26}
	</if>
	<if test="d27!=null">
	,d27=#{d27}
	</if>
	<if test="d28!=null">
	,d28=#{d28}
	</if>
	<if test="d29!=null">
	,d29=#{d29}
	</if>
	<if test="d30!=null">
	,d30=#{d30}
	</if>
	<if test="d31!=null">
	,d31=#{d31}
	</if>
	<if test="d32!=null">
	,d32=#{d32}
	</if>
	<if test="d33!=null">
	,d33=#{d33}
	</if>
	<if test="d34!=null">
	,d34=#{d34} 
	</if>
	<if test="d75!=null">
	,d75=#{d75}
	</if>
	<if test="d76!=null">
	,d76=#{d76}
	</if>
	<if test="d77!=null">
	,d77=#{d77}
	</if>
	<if test="d78!=null">
	,d78=#{d78}
	</if>
	<if test="d79!=null">
	,d79=#{d79}
	</if>
	where id=#{id}
</update>

Biz层

//导入
String ajaxUploadExcel(HttpServletRequest request,HttpSession session,HttpServletResponse response) throws Exception;

Service层

/**
	 * 导入
	 */
	@Override
	public String ajaxUploadExcel(HttpServletRequest request,HttpSession session, HttpServletResponse response) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		Date currentTime = new Date(); 
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");  
	    String dateString = formatter.format(currentTime);  
		MultipartFile file = multipartRequest.getFile("upfile");
		if (file.isEmpty()) {
			try {
				throw new Exception("文件不存在！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	InputStream in = null;
	try {
		in = file.getInputStream();
	} catch (IOException e) {
		e.printStackTrace();
	}

	List<List<Object>> listob = null;
	try {
		listob = new ExcelUtilDr().getBankListByExcel(in, file.getOriginalFilename());
	} catch (Exception e) {
		e.printStackTrace();
	}

	// 该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
	for (int i = 0; i < listob.size(); i++) {
		List<Object> lo = listob.get(i);
		Yblr vo = new Yblr();
		Yblr j = null;

		try {
			j = yblrDao.selectYblrByBids(String.valueOf(lo.get(0)));
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			System.out.println("没有新增");
		}

		UserInfo users = (UserInfo) session.getAttribute("users");
		vo.setD1(String.valueOf(lo.get(0)));
		vo.setD2("2");
		vo.setD4(dateString);
		vo.setUser_id(String.valueOf(users.getUserId()));
		String d10=String.valueOf(lo.get(6));
		String d11=String.valueOf(lo.get(11));
		String d12=String.valueOf(lo.get(16));
		String d13=String.valueOf(lo.get(21));
		String d14=String.valueOf(lo.get(26));
		vo.setD10(d10);
		vo.setD11(d11);
		vo.setD12(d12);
		vo.setD13(d13);
		vo.setD14(d14);
		String d75=String.valueOf(lo.get(7));
		String d76=String.valueOf(lo.get(12));
		String d77=String.valueOf(lo.get(17));
		String d78=String.valueOf(lo.get(22));
		String d79=String.valueOf(lo.get(27));
		if(d75.equals("血清")){
			d75="1";//vo.setD75("1"); 
		}else if(d75.equals("血细胞")){
			d75="2";//vo.setD75("2");
		}else if(d75.equals("血浆")){
			d75="3";//vo.setD75("3");
		}else if(d75.equals("血凝块")){
			d75="4";//vo.setD75("4");
		}
		
		if(d76.equals("血清")){
			d76="1";//vo.setD76("1");
		}else if(d76.equals("血细胞")){
			d76="2";//vo.setD76("2");
		}else if(d76.equals("血浆")){
			d76="3";//vo.setD76("3");
		}else if(d76.equals("血凝块")){
			d76="4";//vo.setD76("4");
		}
		
		if(d77.equals("血清")){
			d77="1";//vo.setD77("1");
		}else if(d77.equals("血细胞")){
			d77="2";//vo.setD77("2");
		}else if(d77.equals("血浆")){
			d77="3";//vo.setD77("3");
		}else if(d77.equals("血凝块")){
			d77="4";//vo.setD77("4");
		}
		
		if(d78.equals("血清")){
			d78="1";//vo.setD75("1");
		}else if(d78.equals("血细胞")){
			d78="2";//vo.setD78("2");
		}else if(d78.equals("血浆")){
			d78="3";//vo.setD78("3");
		}else if(d78.equals("血凝块")){
			d78="4";//vo.setD78("4");
		}
		
		if(d79.equals("血清")){
			d79="1";//vo.setD79("1");
		}else if(d79.equals("血细胞")){
			d79="2";//vo.setD79("2");
		}else if(d79.equals("血浆")){
			d79="3";//vo.setD79("3");
		}else if(d79.equals("血凝块")){
			d79="4";//vo.setD79("4");
		}
		String lx=d75+","+d76+","+d77+","+d78+","+d79;
		String[] sourceStrArray = lx.split(",");
		int num1=0;
		int num2=0;
		int num3=0;
		int num4=0;
		for(int t = 0; t < sourceStrArray.length; t++){
			if(sourceStrArray[t]!=null && !sourceStrArray[t].equals("")){
				if(sourceStrArray[t].equals("1")){
					num1+=1;
					String num=String.valueOf(num1);
					vo.setD80(num);
				}
				if(sourceStrArray[t].equals("2")){
					num2+=1;
					String num=String.valueOf(num2);
					vo.setD81(num);
				}
				if(sourceStrArray[t].equals("3")){
					num3+=1;
					String num=String.valueOf(num3);
					vo.setD82(num);
				}
				if(sourceStrArray[t].equals("4")){
					num4+=1;
					String num=String.valueOf(num4);
					vo.setD83(num);
				}
			}
		}
		vo.setD75(d75);
		vo.setD76(d76); 
		vo.setD77(d77); 
		vo.setD78(d78); 
		vo.setD79(d79); 
		String d15=String.valueOf(lo.get(8));
		String d16=String.valueOf(lo.get(13));
		String d17=String.valueOf(lo.get(18));
		String d18=String.valueOf(lo.get(23));
		String d19=String.valueOf(lo.get(28));
		vo.setD15(d15);
		vo.setD16(d16);
		vo.setD17(d17);
		vo.setD18(d18);
		vo.setD19(d19);
		String d20=String.valueOf(lo.get(9));
		String d21=String.valueOf(lo.get(14));
		String d22=String.valueOf(lo.get(19));
		String d23=String.valueOf(lo.get(24));
		String d24=String.valueOf(lo.get(29));
		vo.setD20(d20);
		vo.setD21(d21);
		vo.setD22(d22);
		vo.setD23(d23);
		vo.setD24(d24);
		String d25=String.valueOf(lo.get(10));
		String d26=String.valueOf(lo.get(15));
		String d27=String.valueOf(lo.get(20));
		String d28=String.valueOf(lo.get(25));
		String d29=String.valueOf(lo.get(30));
		vo.setD25(d25);
		vo.setD26(d26);
		vo.setD27(d27);
		vo.setD28(d28);
		vo.setD29(d29);
		if(d10!="" && d11!="" ){
			vo.setD5("2");
		}else{
			vo.setD5("1");
			}
		/*vo.setPassword(String.valueOf(lo.get(2)));
		vo.setAge(Integer.valueOf(String.valueOf(lo.get(3))));
		vo.setDate(Date.valueOf(String.valueOf(lo.get(4))));*/
		if (j == null) {
			yblrDao.insertYblr(vo);
		} else {
			yblrDao.updateYblr(vo);
		}
		
	}
	return "文件导入成功！";
}

Controller层

 /** 
     * 描述：通过 jquery.form.js 插件提供的ajax方式上传文件 
     * @param request 
     * @param response 
     * @throws Exception 
     */  
    @ResponseBody  
    @RequestMapping("daoru")  
    public String ajaxUploadExcel(HttpServletRequest request,HttpSession session,HttpServletResponse response) throws Exception {  
    
    	return ybkrBiz.ajaxUploadExcel(request, session,response);
    }

jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>

<script type="text/javascript" src="statics/js/jquery.js"></script>
<script type="text/javascript" src="statics/js/jquery-form.js"></script>
 <script type="text/javascript">  
            //ajax 方式上传文件操作  
             $(document).ready(function(){  
                $('#btn').click(function(){  
                    if(checkData()){  
                        $('#form1').ajaxSubmit({    
                            url:'daoru',  
                            dataType: 'text',  
                            success: resutlMsg,  
                            error: errorMsg  
                        });   
                        function resutlMsg(msg){  

                            alert("导入成功");
                            //alert(msg);     
                            $("#upfile").val(""); 
                        }  
                        function errorMsg(){   
                            alert("导入excel出错！");      
                        }  
                    }  
                });  
             });  
               
             //JS校验form表单信息  
             function checkData(){  
                var fileDir = $("#upfile").val();  
                var suffix = fileDir.substr(fileDir.lastIndexOf("."));  
                if("" == fileDir){  
                    alert("选择需要导入的Excel文件！");  
                    return false;  
                }  
                if(".xls" != suffix && ".xlsx" != suffix ){  
                    alert("选择Excel格式的文件导入！");  
                    return false;  
                }  
                return true;  
             }  
    </script>   
  </head>  
    
  <body>  
  <div><!-- 1.通过简单的form表单提交方式，进行文件的上</br>  --><!-- 2.通过jquery.form.js插件提供的form表单一步提交功能  --></div></br>  
    <form method="POST"  enctype="multipart/form-data" id="form1" action="daoru">  
        <table>  
         <tr>  
            <td>导入excle文件: </td>  
              
         </tr> 
         <tr><td>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp<input id="upfile" type="file" name="upfile"></td></tr> 
        <tr>  
           <!--  <td><input type="submit" value="提交" "checkData()"></td>   -->
            <td>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp<input type="button" value="导入" id="btn" name="btn" ></td>  
         </tr>  
        </table>    
    </form>  
      
  </body> 
  </html>

poi包和js

链接：https://pan.baidu.com/s/1PAYJtAB3t98QPeuwENl22g
提取码：jn8n