Java解析Excel文件并把数据存入数据库

Java解析Excel文件并把数据存入数据库

前段时间做一个小项目，为了同时存储多条数据，其中有一个功能是解析Excel并把其中的数据存入对应数据库中。花了两天时间，不过一天多是因为用了"upload"关键字作为URL从而导致总报同一个错，最后在同学的帮助下顺利解决，下面我把自己用"POI"解析的方法总结出来供大家参考（我用的是SpingMVC和hibernate框架）。

1.web.xml中的配置文件

web.xml中的配置文件就按照这种方式写，只需要把"application.xml"换成你的配置文件名即可

org.springframework.web.context.ContextLoaderListener

contextConfigLocation

classpath:application.xml

2.application.xml的配置文件（固定写发）

在这个配置文件中你还可以规定上传文件的格式以及大小等多种属性限制

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

3.文件上传的前端HTML

注意：

1.enctype="multipart/form-data" 必须写，封装表单

2.method="post"，提交方式必须为"post"提交

3.action="${text}/uploadfile"， "uploadfile"切记不要写成"upload"，否则你找到世界末日也不会找到哪里有问题（本人因为这个折腾了一天多时间）。

请选择正确的excel文件上传

支持的excel格式为：xls、xlsx、xlsb、xlsm、xlst！

4.验证上传文件的格式

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

function checkSuffix(){

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

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

var re=new RegExp(strRegex);

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

alert("上传成功");

document.fileupload.submit();

} else{

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

}

}

5.dao层的接口和实现类

package com.gxxy.team1.yyd.dao;

public interface IFileUploadDao {

public void save(Object o);

}

package com.gxxy.team1.yyd.dao.impl;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

import com.gxxy.team1.yyd.dao.IFileUploadDao;

@Repository

public class FileUploadDaoImpl implements IFileUploadDao {

@Autowired

private SessionFactory sessionFactory;

private Session getSession() {

Session session = sessionFactory.getCurrentSession();

return session;

}

@Override

public void save(Object o) {

getSession().save(o);

}

}

6.service层的接口和实现类

package com.gxxy.team1.yyd.service;

import java.util.List;

public interface IFileUploadService {

public List readExcel(String path);

public void save(Object o);

}

package com.gxxy.team1.yyd.service.impl;

import java.io.File;

import java.io.FileInputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

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

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

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.gxxy.team1.yyd.dao.IFileUploadDao;

import com.gxxy.team1.yyd.service.IFileUploadService;

@Service

public class FileUploadServiceImpl implements IFileUploadService {

@Autowired

private IFileUploadDao fileDao;

@Override

public List readExcel(String path) {

SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

List list = null;

try {

//同时支持Excel 2003、2007

File excelFile = new File(path); //创建文件对象

FileInputStream is = new FileInputStream(excelFile); //文件流

Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的

int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量

//存储数据容器

list = new ArrayList();

//遍历每个Sheet

for (int s = 0; s < sheetCount; s++) {

Sheet sheet = workbook.getSheetAt(s);

int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数

//遍历每一行

for (int r = 0; r < rowCount; r++) {

Row row = sheet.getRow(r);

int cellCount = row.getPhysicalNumberOfCells(); //获取总列数

//用来存储每行数据的容器

String[] model = new String[cellCount-1];

//遍历每一列

for (int c = 0; c < cellCount; c++) {

Cell cell = row.getCell(c);

int cellType = cell.getCellType();

if(c == 0) continue;//第一列ID为标志列，不解析

String cellValue = null;

switch(cellType) {

case Cell.CELL_TYPE_STRING: //文本

cellValue = cell.getStringCellValue();

//model[c-1] = cellValue;

break;

case Cell.CELL_TYPE_NUMERIC: //数字、日期

if(DateUtil.isCellDateFormatted(cell)) {

cellValue = fmt.format(cell.getDateCellValue()); //日期型

//model[c-1] = cellValue;

}

else {

cellValue = String.valueOf(cell.getNumericCellValue()); //数字

//model[c-1] = cellValue;

}

break;

case Cell.CELL_TYPE_BOOLEAN: //布尔型

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_BLANK: //空白

cellValue = cell.getStringCellValue();

break;

case Cell.CELL_TYPE_ERROR: //错误

cellValue = "错误";

break;

case Cell.CELL_TYPE_FORMULA: //公式

cellValue = "错误";

break;

default:

cellValue = "错误";

}

System.out.print(cellValue + " ");

model[c-1] = cellValue;

}

//model放入list容器中

list.add(model);

System.out.println();

}

}

is.close();

}

catch (Exception e) {

e.printStackTrace();

}

return list;

}

@Override

public void save(Object o) {

fileDao.save(o);

}

}

7.controller层实现

//文件上传方法

@RequestMapping("/uploadfile")

public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model,Model mod) throws Exception {

String path = request.getSession().getServletContext().getRealPath("upload");

System.out.println("文件路径："+path);

String originalFilename = file.getOriginalFilename();

String type = file.getContentType();

//originalFilename = UUID.rahttp://ndomUUID().toString()+originalFilename;

System.out.println("目标文件名称："+originalFilename+",目标文件类型："+type);

File targetFile = new File(path,originalFilename );

if (!targetFile.getParentFile().exists()) {

targetFile.getParentFile().mkdirs();

}else if (!targetFile.exists()) {

targetFile.mkdirs();

}

// 获得上传文件的文件扩展名

String subname = originalFilename.substring(originalFilename.lastIndexOf(".")+1);

System.out.println("文件的扩展名："+subname);

try {

file.transferTo(targetFile);

} catch (Exception e) {

e.printStackTrace();

}

FileUploadServiceImpl fileUp = new FileUploadServiceImpl();

String rootpath = path + File.separator + originalFilename;

List excellist = fileUp.readExcel(rootpath);

int len = excellist.size();

System.out.println("集合的长度为："+len);

for (int i = 0; i < len; i++) {

String[] fields = excellist.get(i);

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

String sampleNo = fields[0];

Double valueOf = Double.valueOf(fields[1]);

int sampleType = valueOf.intValue(); //double转int

String createTime = fields[2];

Date createTime1 = format.parse(createTime);

String name = fields[3];

String pId = fields[4];

String hospitalName = fields[5];

String cellPhone = fields[6];

Sample sample = new Sample(sampleNo, sampleType, createTime1, name, pId);

Patient patient = new Patient(hospitalName, cellPhone);

fileService.save(sample);

fileService.save(patient);

}

//model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+originalFilename);

String username = (String) request.getSession().getAttribute("username");

List> power = powerService.power(username);

mod.addAttribute("list", power);

return "redirect:/ yyd";

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。