excel数据导入ssm

java web项目导入excel数据,是实用频率非常高的功能,通过做了几个这样的功能之后,现将此功能总结出了，对于Excel表的结构，简单理解我觉得大体可以把它分成三部分（Sheet，Cell，Row），可以把这三部分理解为页，列，行,为了以后自己方便使用,也为大家实现此功能做一个参考.

   1.导入对应的jar包

   2.Excel读取工具类

package org.springmvc.util;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;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;/** * Excel读取工具类 */public class ExcelUtil {    private final static String excel2003L = ".xls";    private final static String excel2007U = ".xlsx";    //读取文件数据    public static List<List<Object>> getExcelList(InputStream is, String fileName) throws Exception{        List<List<Object>> list = new ArrayList<List<Object>>();        Workbook workbook = null;        //验证文件格式        String suffix = fileName.substring(fileName.lastIndexOf("."));        if(suffix.equals(excel2003L)){            workbook = new HSSFWorkbook(is);        }else if(suffix.equals(excel2007U)){            workbook = new XSSFWorkbook(is);        }        Sheet sheet = null;        Row row = null;        Cell cell = null;        for(int i = 0 ; i < workbook.getNumberOfSheets();i++){            sheet = workbook.getSheetAt(i);            if(sheet == null) continue;            //遍历当前sheet中全部行            for(int j = sheet.getFirstRowNum();j < sheet.getLastRowNum();j++){                row = sheet.getRow(j);                if(row == null) continue;                //循环当前row中全部列                List<Object> li = new ArrayList<Object>();                for (int k = row.getFirstCellNum();k < row.getLastCellNum();k++){                    cell = row.getCell(k);                    if(cell!=null){                        li.add(getCellValue(cell));                    }                }                list.add(li);            }        }        return list;    }    //单元格数据类型格式化    public static Object getCellValue(Cell cell){        Object value = null;        DecimalFormat decimalFormat1 = new DecimalFormat("0");        DecimalFormat decimalFormat2 = new DecimalFormat("0.00");        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");        switch (cell.getCellType()){            case Cell.CELL_TYPE_NUMERIC:                if("General".equals(cell.getCellStyle().getDataFormatString())){                    value = decimalFormat1.format(cell.getNumericCellValue());                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){                    value = dateFormat.format(cell.getDateCellValue());                }else{                    value = decimalFormat2.format(cell.getNumericCellValue());                }                break;            case Cell.CELL_TYPE_BOOLEAN:                value = cell.getBooleanCellValue();                break;            case Cell.CELL_TYPE_BLANK:                value = "";                break;            case Cell.CELL_TYPE_STRING:                value = cell.getStringCellValue();                break;        }        return value;    }}

  3.Controller页面

//通过模态框显示批量添加页面@RequestMapping(value="/batchAdd",method=RequestMethod.GET)public String batchAdd(){return "blog/batchAdd";}//批量导入Excel数据到数据库@RequestMapping(value="/batchAdd",method=RequestMethod.POST)@ResponseBodypublic String batchAdd(MultipartHttpServletRequest multipartHttpServletRequest) throws IOException, Exception{int count = 0;//导入总条数MultipartFile file = multipartHttpServletRequest.getFile("uploadXls");List<List<Object>> list = ExcelUtil.getExcelList(file.getInputStream(), file.getOriginalFilename());count = list.size();//title,content,author,createtime,category_id,picfor (int i = 0; i < list.size(); i++) {List<Object> lo = list.get(i);Blog b = new Blog();b.setTitle(lo.get(0).toString());b.setContent(lo.get(1).toString());b.setAuthor(lo.get(2).toString());b.setIssueDate(new SimpleDateFormat("yyyy/MM/dd").parse(lo.get(3).toString()));Category c = new Category();c.setId(Integer.parseInt(lo.get(4).toString()));b.setCategory(c);b.setMypic(lo.get(5).toString());blogDao.insert(b);for(int j = 0;j<lo.size();j++){Object o = list.get(i).get(j);System.out.print(o+"\t\t");}System.out.println();}return String.format("数据导入成功!共计%s条", count);}

4.jsp页面

1.<button class="btn btn-warning" onclick="showDialog('#myModal','批量添加博客','blog/batchAdd')"><i class="glyphicon glyphicon-add"></i>批量导入</button><!--在form标签下添加一个属性,enctype="multipart/form-data"-->2.<form class="form" action="blog/batchAdd" method="post" enctype="multipart/form-data">        <input class="form-control" type="file" name="uploadXls" id="uploadXls"></form>function submitForm(){var $form = $modal.find('form');var action = $form.attr('action');//文件上传提交表单需要如下代码//FormData html5新添加的属性 ,可以支持文件上传//FormData 在ie8中不支持,  jquery.form.js//new FormData(dom)//jquery-->dom  $form.get(0);  $form[0]//dom-->jquery  $(dom)var form = new FormData($form.get(0));$.ajax({url:action,type:'post',data:form,async: false,        cache: false,        contentType: false,        processData: false,success:function(data){$modal.modal('hide');bootstrapTable.bootstrapTable('refresh');}});}//批量添加function batchAdd(modal,url){}