java用POI导入导出Excel数据导数据库

Excel导入导出：

http://www.cnblogs.com/Damon-Luo/p/5919656.html

 

导入Excel数据：

1. 页面

<form name="fileupload" action="importExcel" method="POST" enctype="multipart/form-data">

<div class="form-group">

<label for="file">上传文件</label>

 <input type="file"  id="file" name="file">

</div>

<button  οnclick="checkSuffix()" class="btn btn-default">提交</button>

 

//用于验证文件扩展名的正则表达式

function checkSuffix() {

var name = document.getElementById("file").value;

var strRegex = "(.xls|.xlsx|.xlsb|.xlsm|.xlst)$";

var re = new RegExp(strRegex);

if (re.test(name.toLowerCase())) {

alert("上传成功");

document.fileupload.submit();

} else {

alert("文件名不合法");

}

}

1. </form>配置

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

 

</bean>

1. 后台接收

@RequestMapping(value="/importExcel")

    public ModelAndView importExcel(HttpServletRequest request, HttpServletResponse response)

    {

     MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

// 得到上传的文件

     MultipartFile deviceExcel = multipartRequest.getFile("file");

     ModelAndView mv=new ModelAndView();

     mv.setViewName("success");

     try {

     Workbook wookbook = new XSSFWorkbook(deviceExcel.getInputStream());//得到工作簿

     Sheet sheet = wookbook.getSheetAt(0); // 从工作区中取得页（Sheet）  

     //获得表头

         Row rowHead = sheet.getRow(0);

       //判断表头是否正确

         if(rowHead.getPhysicalNumberOfCells() != 2)

         {

           System.out.println("表头的数量不对!");

         }

       //获得数据的总行数

         int totalRowNum = sheet.getLastRowNum();

         //要获得属性

         String name = "";

         Double latitude = null;

         //获得所有数据

         for(int i = 1 ; i <= totalRowNum ; i++)

         {

           //获得第i行对象

           Row row = sheet.getRow(i);

           //获得获得第i行第0列的 String类型对象

           Cell cell = row.getCell((short)0);

          // name = cell.getStringCellValue().toString();

            name = (String) getRightTypeCell(cell);

           //获得一个数字类型的数据

           cell = row.getCell((short)1);

           //latitude = (int) cell.getNumericCellValue();

           latitude = (Double) getRightTypeCell(cell);

           System.out.println("名字："+name+",年龄："+latitude);

         }

} catch (Exception e) {

e.printStackTrace();

}   

     return mv;

    }

    /**

     *  

     * @param cell 一个单元格的对象

     * @return 返回该单元格相应的类型的值

     */

    public static Object getRightTypeCell(Cell cell){

      Object object = null;

      switch(cell.getCellType())

      {

        case Cell.CELL_TYPE_STRING :

        {

          object=cell.getStringCellValue();

          break;

        }

        case Cell.CELL_TYPE_NUMERIC :

        {

          cell.setCellType(Cell.CELL_TYPE_NUMERIC);

          object=cell.getNumericCellValue();

          break;

        }

        case Cell.CELL_TYPE_FORMULA :

        {

          cell.setCellType(Cell.CELL_TYPE_NUMERIC);

          object=cell.getNumericCellValue();

          break;

        }

        case Cell.CELL_TYPE_BLANK :

        {

          cell.setCellType(Cell.CELL_TYPE_BLANK);

          object=cell.getStringCellValue();

          break;

        }

      }

      return object;

}

导入Excel数据：

1. js代码

function exportEngExcel(){

$.ajaxFileUpload({

url : Utils.getRootPath()+'/engMonthlyDetail/exportEngExcel?engCode='+engCode,

secureuri : false,

type:"post",

dataType:'json',

success : function(data) {

},

error:function(d){

}

});

}