读取ppt并存入数据库_Java解析Excel文件并把数据存入数据库
前段時(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)題。
- 上一篇: 高调宣布加息后,该国货币和股市崩盘,央行
- 下一篇: 通货膨胀正在发生,如果出现严重通货膨胀,