前段時(shí)間做一個(gè)小項(xiàng)目，為了同時(shí)存儲(chǔ)多條數(shù)據(jù)，其中有一個(gè)功能是解析Excel并把其中的數(shù)據(jù)存入對(duì)應(yīng)數(shù)據(jù)庫(kù)中。花了兩天時(shí)間，不過(guò)一天多是因?yàn)橛昧?#34;upload"關(guān)鍵字作為URL從而導(dǎo)致總報(bào)同一個(gè)錯(cuò)，最后在同學(xué)的幫助下順利解決，下面我把自己用"POI"解析的方法總結(jié)出來(lái)供大家參考(我用的是SpingMVC和hibernate框架)。

1.web.xml中的配置文件

web.xml中的配置文件就按照這種方式寫(xiě)，只需要把"application.xml"換成你的配置文件名即可

org.springframework.web.context.ContextLoaderListener

contextConfigLocation

classpath:application.xml

2.application.xml的配置文件(固定寫(xiě)發(fā))

在這個(gè)配置文件中你還可以規(guī)定上傳文件的格式以及大小等多種屬性限制

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

3.文件上傳的前端HTML

注意：

1.enctype="multipart/form-data" 必須寫(xiě)，封裝表單

2.method="post"，提交方式必須為"post"提交

3.action="${text}/uploadfile"， "uploadfile"切記不要寫(xiě)成"upload"，否則你找到世界末日也不會(huì)找到哪里有問(wèn)題(本人因?yàn)檫@個(gè)折騰了一天多時(shí)間)。

請(qǐng)選擇正確的excel文件上傳

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

4.驗(yàn)證上傳文件的格式

//用于驗(yàn)證文件擴(kuò)展名的正則表達(dá)式

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層的接口和實(shí)現(xiàn)類(lèi)

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層的接口和實(shí)現(xiàn)類(lèi)

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 {

//同時(shí)支持Excel 2003、2007

File excelFile = new File(path); //創(chuàng)建文件對(duì)象

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

Workbook workbook = WorkbookFactory.create(is); //這種方式 Excel 2003/2007/2010 都是可以處理的

int sheetCount = workbook.getNumberOfSheets(); //Sheet的數(shù)量

//存儲(chǔ)數(shù)據(jù)容器

list = new ArrayList();

//遍歷每個(gè)Sheet

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

Sheet sheet = workbook.getSheetAt(s);

int rowCount = sheet.getPhysicalNumberOfRows(); //獲取總行數(shù)

//遍歷每一行

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

Row row = sheet.getRow(r);

int cellCount = row.getPhysicalNumberOfCells(); //獲取總列數(shù)

//用來(lái)存儲(chǔ)每行數(shù)據(jù)的容器

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為標(biāo)志列，不解析

String cellValue = null;

switch(cellType) {

case Cell.CELL_TYPE_STRING: //文本

cellValue = cell.getStringCellValue();

//model[c-1] = cellValue;

break;

case Cell.CELL_TYPE_NUMERIC: //數(shù)字、日期

if(DateUtil.isCellDateFormatted(cell)) {

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

//model[c-1] = cellValue;

}

else {

cellValue = String.valueOf(cell.getNumericCellValue()); //數(shù)字

//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: //錯(cuò)誤

cellValue = "錯(cuò)誤";

break;

case Cell.CELL_TYPE_FORMULA: //公式

cellValue = "錯(cuò)誤";

break;

default:

cellValue = "錯(cuò)誤";

}

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層實(shí)現(xiàn)

//文件上傳方法

@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.randomUUID().toString()+originalFilename;

System.out.println("目標(biāo)文件名稱(chēng)："+originalFilename+",目標(biāo)文件類(lèi)型："+type);

File targetFile = new File(path,originalFilename );

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

targetFile.getParentFile().mkdirs();

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

targetFile.mkdirs();

}

// 獲得上傳文件的文件擴(kuò)展名

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

System.out.println("文件的擴(kuò)展名："+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("集合的長(zhǎng)度為："+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轉(zhuǎn)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";

}

以上這7個(gè)部分就是我實(shí)現(xiàn)解析excel文件并存入數(shù)據(jù)庫(kù)的全部代碼。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

總結(jié)

以上是生活随笔為你收集整理的读取ppt并存入数据库_Java解析Excel文件并把数据存入数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。