ssm项目实现excel与数据库双向导入

ssm项目实现excel与数据库双向导入

• 环境介绍

环境：SSM+Maven+Poi+jsp+mysql+jdk1.8

• pom.xml

   <!-- 文件上传 -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency> 
 
 <!--poi-->
 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        
        

• 工具类1直接可用ExcelBean.class

package com.jinrui.utils;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

public class ExcelBean implements java.io.Serializable {
    private String headTextName;//列头（标题）名
    private String propertyName;//对应字段名
    private Integer cols;//合并单元格数
    private XSSFCellStyle cellStyle;

    public ExcelBean(){

    }
    public ExcelBean(String headTextName, String propertyName){
        this.headTextName = headTextName;
        this.propertyName = propertyName;
    }

    public ExcelBean(String headTextName, String propertyName, Integer cols) {
        super();
        this.headTextName = headTextName;
        this.propertyName = propertyName;
        this.cols = cols;
    }

    public String getHeadTextName() {
        return headTextName;
    }

    public void setHeadTextName(String headTextName) {
        this.headTextName = headTextName;
    }

    public String getPropertyName() {
        return propertyName;
    }

    public void setPropertyName(String propertyName) {
        this.propertyName = propertyName;
    }

    public Integer getCols() {
        return cols;
    }

    public void setCols(Integer cols) {
        this.cols = cols;
    }

    public XSSFCellStyle getCellStyle() {
        return cellStyle;
    }

    public void setCellStyle(XSSFCellStyle cellStyle) {
        this.cellStyle = cellStyle;
    }
}

• 工具类2直接可用ExcelUtil.class

package com.jinrui.utils;

import java.io.IOException;
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 ExcelUtil {

    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
        // 将最大的列数记录下来
        int lastCellNum = 0;
        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>();
                // 比较当前行的列数跟表的最大的列数
                if (j == sheet.getFirstRowNum()) {
                    // 将第一行的列数设为最大
                    lastCellNum = row.getLastCellNum();
                }else {
                    lastCellNum = lastCellNum > row.getLastCellNum() ? lastCellNum : row.getLastCellNum();
                }
                for (int y = row.getFirstCellNum(); y < lastCellNum; 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());
                    // 根据自己的实际情况，excel表中的时间格式是yyyy-MM-dd HH:mm:ss还是yyyy-MM-dd，或者其他类型
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    // 由于方法的返回值类型为String，这里将Date类型转为String，便于统一返回数据
                    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;
    }
}

Excel--->数据库

1. controller层

    @RequestMapping("/fileUpload")
       public String fileUpload(@RequestParam(value = "uploadfile") MultipartFile uploadfile, Model model) {
           MultipartFile file = uploadfile;
           String originalFilename=uploadfile.getOriginalFilename();	//获取原文件名带有后缀----记录.xlsx
           System.out.println(originalFilename);
   
   
   
           InputStream in =null;
           try {
               in = file.getInputStream();
           } catch (IOException e) {
               e.printStackTrace();
           }
           List<List<Object>> listob = null;
   
           try {
               //在这里解析上传的excel中的每行每列的数据存到一个list中
               listob = new ExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
           } catch (Exception e) {
               e.printStackTrace();
           }
   
           //对每个元素进行设置到自己实体中
    for (int i = 0; i < listob.size(); i++) {
               List<Object> lo = listob.get(i);
               SaleDetail saleDetail = new SaleDetail();
               saleDetail.setSaleNo( new BigInteger(String.valueOf(lo.get(0))) );     // 表格的第一列   注意数据格式需要对应实体类属性
               saleDetail.setGoodsNo(Integer.valueOf(String.valueOf(lo.get(1))));   // 表格的第二列
               saleDetail.setNumber(Integer.valueOf(String.valueOf(lo.get(2))));   // 表格的第二列
               saleDetail.setSubTotal(new BigDecimal(String.valueOf(lo.get(3))));   // 表格的第二列
      
               System.out.println("从excel中读取的实体类对象："+ saleDetail);
               saleService.insert(saleDetail);
   	
           model.addAttribute("msg", "上传成功");
           return "info";
       }
   

数据库--->excel

1. controller层

 @RequestMapping("/outPutExcel")
    @ResponseBody
    public String outPutExcel(HttpServletRequest request, HttpServletResponse response){
        try {
        List<SaleDetail> saleDetailList = saleService.finAllSaleDetail();

        //导出excel
        response.setHeader("Content-Disposition","attachment;filename="+new String("测试表.xls".getBytes(),"ISO-8859-1"));
        response.setContentType("application/x-excel;charset=UTF-8");
        OutputStream outputStream = response.getOutputStream();
            //导出
            saleService.exportExcel(saleDetailList,outputStream);



            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return "ok";
    }

2. service层

   //service层
//导出
    @Override
    public void exportExcel(List<SaleDetail> saleDetailList, OutputStream outputStream) throws IOException {
        //1.创建工作簿
        HSSFWorkbook hwb =new HSSFWorkbook();
        //1.1创建合并单元格
        //CellRangeAddress cellRangeAddress =new CellRangeAddress(0,0,0,4);
        //2.创建工作表
        HSSFSheet sheet = hwb.createSheet("用户信息表");
        //2.1添加合并单元格
        //sheet.addMergedRegion(cellRangeAddress);
        //3.1创建第一行及单元格
//        HSSFRow row1 = sheet.createRow(0);
//        HSSFCell cell1 = row1.createCell(0);
//        cell1.setCellValue("用户信息");
        //3.2创建第二行及单元格
        HSSFRow row2 = sheet.createRow(1);
        String[] row2Cell = {"销售单号","商品类型","数量","小计"};
        for (int i =0 ; i < row2Cell.length ; i++ ){
            row2.createCell(i).setCellValue(row2Cell[i]);
        }
        //3.3创建第三行及单元格
        if(saleDetailList!= null && saleDetailList.size()>0){
            for(int j=0 ; j<saleDetailList.size() ;j++){
                HSSFRow rowUser = sheet.createRow(j+2);

                rowUser.createCell(0).setCellValue(String.valueOf(saleDetailList.get(j).getSaleNo()));
                rowUser.createCell(1).setCellValue(String.valueOf(saleDetailList.get(j).getGoodsNo()));
                rowUser.createCell(2).setCellValue(String.valueOf(saleDetailList.get(j).getNumber()));
                rowUser.createCell(3).setCellValue(String.valueOf(saleDetailList.get(j).getSubTotal()));

                //rowUser.createCell(4).setCellValue(userList.get(j).getId());
            }
        }
        //5.输出
        hwb.write(outputStream);
    }

前端

jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ taglib prefix="missingMessage" uri="http://www.springframework.org/tags/form" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8" isELIgnored="false" %>
<html>
<head>
    <title>上传测试</title>
</head>
<body>
<form action="SaleDetail/fileUpload" method="post" enctype="multipart/form-data">
    <input  id="upfile" type="file" required name="uploadfile" accept=".xls,.xlsx"/>
    <input type="submit" onclick="return checkData()" value="提交"/>
</form>
<a href="SaleDetail/outPutExcel" >导出</a>

<script type="text/javascript">
    var msg="${msg}";
    if(msg!=""){
        alert(msg);
    }
</script>
<script type="text/javascript">
    //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>

</body>
</html>