记录-java（jxl） Excel导入数据库

 

本内容主要包括（文件上传、excel2003数据导入数据库）excel导入数据库功能需要jxl  jar包支持

 

下面是文件上传的前端测试代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>
</head>
<body>
    <form action="getAllByExcel" method="post" enctype="multipart/form-data">
        <input type="file" name="file" >
        <input type="submit" value="上传">
    </form>
</body>
</html>

 

下面控制类用于excel文件上传到服务器

@SuppressWarnings("unchecked")
    @RequestMapping(value="getAllByExcel" ,method = RequestMethod.POST)
    public Object getAllByExcel(HttpServletRequest request,HttpServletResponse response, ModelMap model){
        MultipartHttpServletRequest mulltipartRequest=(MultipartHttpServletRequest)request;
        
        MultipartFile files=mulltipartRequest.getFile("file");
        //得到上传服务器路径
        String path=request.getSession().getServletContext().getRealPath("/WEB-INF/res/upload");
        String fileName=files.getOriginalFilename();
        
        try {
            
          InputStream inputStream=files.getInputStream();
            
            byte[] b = new byte[1048576];
            int length = inputStream.read(b);
            path += "\\" + fileName;
            // 文件流写到服务器端
            FileOutputStream outputStream = new FileOutputStream(path);
            outputStream.write(b, 0, length);
            inputStream.close();
            outputStream.close();
          //解析excel
            List<Map<String, Object>> lists=CommonUtil.getAllExcel(path);

 

此功能需要导入jxl相关jar包，此方法提供excel路径返回List<Map<String, Object>>

//获取excel数据
    public static List<Map<String, Object>> getAllExcel(String url){
        List<Map<String, Object>> models=new ArrayList<Map<String, Object>>();
        try {

            File filse=new File(url);
            Workbook rwb=Workbook.getWorkbook(filse);
            Sheet sheet=rwb.getSheet(0);
            int clos=sheet.getColumns(); //获取列数
            int rows=sheet.getRows();//获取行数
            
            String[] heads =new String[clos];//保存所有的key值
            
            for (int i = 0; i < rows; i++) {
                Map<String, Object> mp =new HashMap<String,Object>();
                for(int j=0;j<clos;j++){
                    if(i == 0 ){
                        heads[j] =     sheet.getCell(j, i).getContents();//获取所有key值
                    }
                    else{
                        mp.put(heads[j], sheet.getCell(j, i).getContents());//获取每行value值
                    }
                }
                //如果map不为空则添加到list集合
                if (!mp.isEmpty()) {
                    models.add(mp);
                }
                
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        return models;
    }

 

 

附加poi excel导入 （存在点问题 和上面思路类似 此处只是做个记录）

public static List<Map<String, Object>> readExcel(String url) throws IOException{
        InputStream is=new FileInputStream(url);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        
        List<Map<String, Object>> models=new ArrayList<Map<String, Object>>();
        //循环Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet sheet=hssfWorkbook.getSheetAt(numSheet);
            if(sheet==null){
                continue;
            }
        HSSFSheet sheet=hssfWorkbook.getSheetAt(0);
            int celNums=sheet.getRow(0).getPhysicalNumberOfCells();
            String[] heads =new String[celNums];//保存所有的key值
            
            for (int rowNum = 0; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                HSSFRow Row = sheet.getRow(rowNum);
                Map<String, Object> mp =new HashMap<String,Object>();
                for (int celNum = 0; celNum < celNums; celNum++) {
                    
                    
                    if(rowNum == 0 ){
                        heads[celNum] = Row.getCell(celNum).getStringCellValue();//获取所有key值
                    }
                    else{
                        mp.put(heads[celNum], Row.getCell(celNum).getStringCellValue());//获取每行value值
                    }
                }
                if (!mp.isEmpty()) {
                    models.add(mp);
                }
            }
        }
        
        return models;
    }
View Code

 

posted @ 2016-03-08 15:30  大叔陈说  阅读(332)  评论(0)    收藏  举报