Java解析Excel⽂件并把数据存⼊数据库
前段时间做⼀个⼩项⽬，为了同时存储多条数据，其中有⼀个功能是解析Excel并把其中的数据存⼊对应数据库中。花了两天时间，不过
⼀天多是因为⽤了"upload"关键字作为URL从⽽导致总报同⼀个错，最后在同学的帮助下顺利解决，下⾯我把⾃⼰⽤"POI"解析的⽅法总结出来供⼤家参考（我⽤的是SpingMVC和hibernate框架）。
l中的配置⽂件
<!--⽂件上传对应的配置⽂件-->
<listener>
<listener-class>org.t.ContextLoaderListener</listener-class>
</listener>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>l</param-value>
</context-param>
l的配置⽂件（固定写发）
在这个配置⽂件中你还可以规定上传⽂件的格式以及⼤⼩等多种属性限制
<!-- 定义⽂件上传解析器 -->
<bean id="multipartResolver"
class="org.springframework.web.multipartmons.CommonsMultipartResolver">
</bean>
3.⽂件上传的前端HTML
注意：
3.action="${text}/uploadfile"， "uploadfile"切记不要写成"upload"，否则你到世界末⽇也不会到哪⾥有问题（本⼈因为这个折腾了⼀天多时间）。
<form name="fileupload" enctype="multipart/form-data" action="${text}/uploadfile" method="post">
<p >请选择正确的excel⽂件上传</p>
<input id="txt" class="input" type="text" disabled="disabled" value="⽂件域" name="txt">
<input class="liulan" type="button" onclick="file.click()" size="30" value="上传⽂件" onmousemove="file.style.pixelLeft=event.x-60;file.style.pixelTop=this.offsetTop;"> <input id="file1" class="files" type="file" hidefocus="" size="1" name="file" onchange="txt.value=this.value">
<br/><input type="button" onclick="checkSuffix();" value="提交上传" >
<p >⽀持的excel格式为：xls、xlsx、xlsb、xlsm、xlst！</p>
</form>
4.验证上传⽂件的格式
//⽤于验证⽂件扩展名的正则表达式
function checkSuffix(){
var name = ElementById("txt").value;
var strRegex = "(.xls|.xlsx|.xlsb|.xlsm|.xlst)$";
var re=new RegExp(strRegex);
if (re.LowerCase())){
alert("上传成功");
document.fileupload.submit();
springboot aop
} else{
alert("⽂件名不合法");
}
}
5.dao层的接⼝和实现类
d.dao;
public interface IFileUploadDao {
public void save(Object o);
}
d.dao.impl;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
d.dao.IFileUploadDao;
@Repository
public class FileUploadDaoImpl implements IFileUploadDao {
@Autowired
private SessionFactory sessionFactory;
private Session getSession() {
Session session = CurrentSession();
return session;
}
@Override
public void save(Object o) {
getSession().save(o);
}
}
6.service层的接⼝和实现类
d.service;
import java.util.List;
public interface IFileUploadService {
public List<String[]> readExcel(String path);
public void save(Object o);
}
d.service.impl;
import java.io.File;
import java.io.FileInputStream;
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;
d.dao.IFileUploadDao;
d.service.IFileUploadService;
@Service
public class FileUploadServiceImpl implements IFileUploadService {
@Autowired
private IFileUploadDao fileDao;
@Override
public List<String[]> readExcel(String path) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
List<String[]> list = null;
try {
//同时⽀持Excel 2003、2007
File excelFile = new File(path); //创建⽂件对象
FileInputStream is = new FileInputStream(excelFile); //⽂件流
Workbook workbook = ate(is); //这种⽅式 Excel 2003/2007/2010 都是可以处理的 int sheetCount = NumberOfSheets(); //Sheet的数量
//存储数据容器
list = new ArrayList<String[]>();
//遍历每个Sheet
for (int s = 0; s < sheetCount; s++) {
Sheet sheet = SheetAt(s);
int rowCount = PhysicalNumberOfRows(); //获取总⾏数
//遍历每⼀⾏
for (int r = 0; r < rowCount; r++) {
Row row = Row(r);
int cellCount = PhysicalNumberOfCells(); //获取总列数
//⽤来存储每⾏数据的容器
String[] model = new String[cellCount-1];
//遍历每⼀列
for (int c = 0; c < cellCount; c++) {
Cell cell = Cell(c);
int cellType = CellType();
if(c == 0) continue;//第⼀列ID为标志列，不解析
String cellValue = null;
switch(cellType) {
case Cell.CELL_TYPE_STRING: //⽂本
cellValue = StringCellValue();
//model[c-1] = cellValue;
break;
case Cell.CELL_TYPE_NUMERIC: //数字、⽇期
if(DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.DateCellValue()); //⽇期型
//model[c-1] = cellValue;
}
else {
cellValue = String.NumericCellValue()); //数字
//model[c-1] = cellValue;
}
break;
case Cell.CELL_TYPE_BOOLEAN: //布尔型
cellValue = String.BooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: //空⽩
cellValue = StringCellValue();
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);
}
}
//⽂件上传⽅法
@RequestMapping("/uploadfile")
public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model,Model mod) throws Exception { String path = Session().getServletContext().getRealPath("upload");
System.out.println("⽂件路径："+path);
String originalFilename = OriginalFilename();
String type = ContentType();
//originalFilename = UUID.randomUUID().toString()+originalFilename;
System.out.println("⽬标⽂件名称："+originalFilename+",⽬标⽂件类型："+type);
File targetFile = new File(path,originalFilename );
if (!ParentFile().exists()) {
}else if (!ists()) {
targetFile.mkdirs();
}
// 获得上传⽂件的⽂件扩展名
String subname = originalFilename.substring(originalFilename.lastIndexOf(".")+1);
System.out.println("⽂件的扩展名："+subname);
try {
} catch (Exception e) {
e.printStackTrace();
}
FileUploadServiceImpl fileUp = new FileUploadServiceImpl();
String rootpath = path + File.separator + originalFilename;
List<String[]> excellist = adExcel(rootpath);
int len = excellist.size();
System.out.println("集合的长度为："+len);
for (int i = 0; i < len; i++) {
String[] fields = (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", ContextPath()+"/upload/"+originalFilename);
String username = (String) Session().getAttribute("username");
List<List<Menu>> power = powerService.power(username);
mod.addAttribute("list", power);
return "redirect:/ yyd";
}
以上这7个部分就是我实现解析excel⽂件并存⼊数据库的全部代码。希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论