SpringMVC+POI上传Excel2003/2007/2010文件并导入数据库

最新推荐文章于 2024-09-30 20:09:33 发布

weixin_30344995

最新推荐文章于 2024-09-30 20:09:33 发布

阅读量68

点赞数

文章标签：数据库 java 运维

原文链接：http://www.cnblogs.com/qianxun-2017/p/7873501.html

版权

POI需要的jar包：

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>2.3.0</version>
    </dependency>

index.jsp

WenwuController.java

package com.lxtech.ssh.controller;

import java.io.File;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.lxtech.ssh.entity.Wenwu;
import com.lxtech.ssh.service.WenwuService;

@Controller
@RequestMapping("/wenwu")
public class WenwuController {

@Autowired
private WenwuService wenwuService;
    /*
     * 提交文件路径
     */
    @RequestMapping(value = "/path")
    public String path(HttpServletRequest request) throws Exception{
    //程序开始时间
    long startTime = System.currentTimeMillis();
    //上传的文件夹路径
    String path = request.getParameter("upload");
    System.out.println(path);

        File file = new File(path);//File类型可以是文件也可以是文件夹
    File[] fileList = file.listFiles();//将该目录下的所有文件放置在一个File类型的数组中
    List<File> fileListNew = new ArrayList<File>();//新建一个文件集合
    for (int i = 0; i < fileList.length; i++) {
        if (fileList[i].isFile()) {//判断是否为文件
         fileListNew.add(fileList[i]);
        }
    }

    //遍历文件
    for(int i=0;i<fileListNew.size();i++){
      wenwuService.importExcel(fileListNew.get(i).getAbsolutePath());


     }

    long endTime = System.currentTimeMillis();    //获取结束时间

    System.out.println("程序运行时间：" + (endTime - startTime) + "ms");    //输出程序运行时间

  return "success";

    }


}

WenwuServiceImpl.java

package com.lxtech.ssh.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.lxtech.ssh.dao.WenwuDao;
import com.lxtech.ssh.entity.Wenwu;
import com.lxtech.ssh.service.WenwuService;
import com.lxtech.ssh.util.ExcelUtil;

@Service("wenwuService")
public class WenwuServiceImpl extends BaseServiceImpl<Integer, Wenwu> implements WenwuService {

@Autowired
private WenwuDao wenwuDao;
/*
* 同时支持Excel 2003、2007
*/
@Override
public void importExcel(String path) {
  //定义集合批量处理数据
  List<Wenwu> list=new ArrayList<Wenwu>();

        try {
         FileInputStream fis=new FileInputStream(new File(path));//文件流
             Workbook book=(Workbook) WorkbookFactory.create(fis);//这种方式 Excel 2003/2007/2010 都是可以处理的

            //遍历每个Sheet
   for(int s=0;s<book.getNumberOfSheets();s++){
    Sheet sheet=book.getSheetAt(s);

    //遍历每一行
    for(int r=1;r<=sheet.getLastRowNum();r++){
     Row row=sheet.getRow(r);

     //获取第一行第一列的单元格的值
        Row row1=sheet.getRow(0);
        Cell cell1=row1.getCell(0);

        //实体属性初始化
              String type=book.getSheetAt(s).getSheetName();
              String name = null;
        String property = null;
        String data = null;

        if(row!=null){
        if("名称".equals(ExcelUtil.getValue(cell1))){
       name = ExcelUtil.getValue(row.getCell(0));
             property = ExcelUtil.getValue(row.getCell(1));
             data = ExcelUtil.getValue(row.getCell(2));

      }else{
       name = ExcelUtil.getValue(row.getCell(1));
             property = ExcelUtil.getValue(row.getCell(2));
             data = ExcelUtil.getValue(row.getCell(3));
      }
        }

        Wenwu wenwu =new Wenwu();
        wenwu.setType(type);
    wenwu.setName(name);
    wenwu.setProperty(property);
    wenwu.setData(data);

    if(property!=null && property!=""){
              list.add(wenwu);
        }
    }
   }

        }catch (Exception e) {
   e.printStackTrace();
  }
        wenwuDao.saveAll(list);

}
}

ExcelUtil.java

package com.lxtech.ssh.util;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

public class ExcelUtil {
public static String getValue(Cell cell) {
  String value = "";
        if(cell==null){
        value = "";
        }else{

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING://文本
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC://数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {//日期型
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    } else {
                        value = "";
                    }
                } else {//数字型
                    value =new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_FORMULA: //导入时如果为公式生成的数据则无值
                if (!cell.getStringCellValue().equals("")) {
                    value = cell.getStringCellValue();
                } else {
                    value = cell.getNumericCellValue() + "";
                }
                break;
            case Cell.CELL_TYPE_BLANK://空白
            value ="";
                break;
            case Cell.CELL_TYPE_ERROR://错误
                value = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN://布尔型
                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                break;
            default:
                value = "";
            }
        }
        return value;
    }