Java Excel数据导入数据库的方法

Java Excel数据导入数据库的方法

目录1、根据业务需求设计数据库表2、根据数据库表设计一个Excel模板3、环境准备4、通过插件生成表对应的实体类5、自定义编写工具类6、编写具体业务逻辑Service7、在dao层对应的xml文件中，编写批量上传的方法8、Controller实现业务的控制9、通过Swagger测试接口10、在数据和控制台中查看导入效果总结

1、根据业务需求设计数据库表

2、根据数据库表设计一个Excel模板

模板的每列属性必须与表字段一一对应

3、环境准备

我这里项目环境是基于SpringBoot单体式架构，持久层用的公司框架，内置了基于MyBatis-Plus的各种单表操作的方法。

导入依赖

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

4、通过插件生成表对应的实体类

/**

* TbZbzs: 值班值守表

* @author zs

* @date 2021-12-17 08:46:31

**/

@Data

@ApiModel(value="值班值守表,对应表tb_zbzs",description="适用于新增和修改页面传参")

public class TbZbzs extends ProBaseEntity {

private static final long serialVersionUID = 1L;

@ApiModelProperty(value="id")

private String id; // id

@ApiModelProperty(value="部门")

private String bm; // 部门

@ApiModelProperty(value="值班上报")

private String zbsb; // 值班上报

@ApiModelProperty(value="值班人员")

private String zbry; // 值班人员

@ApiModelProperty(value="上报时间")

@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

private java.util.Date sbsj; // 上报时间

@ApiModelProperty(value="结束时间")

@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

private java.util.Date jssj; // 结束时间

@ApiModelProperty(value="联系方式")

private String lxfs; // 联系方式

@ApiModelProperty(value="状态")

private String zt; // 状态

/**

* 逻辑删除

*/

@ApiModelProperty(value="逻辑删除")

private String delFlag;

/**

* 创建时间

*/

@ApiModelProperty(value="创建时间")

@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")

private Date createDate;

}

5、自定义编写工具类

这里提供的是一个基础模板，根据业务的需求可以增加转换条件

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

/**

* 新增值班排班表导入Excel表工具类

* zyw

*/

public class ImportExcelUtil {

private final static String excel2003L =".xls"; //2003- 版本的excel

private final static String excel2007U =".xlsx"; //2007+ 版本的excel

/**

* 描述：获取IO流中的数据，组装成List>对象

* @param in,fileName

* @return

* @throws Exception

*/

public static List> getListByExcel(InputStream in, String fileName) throws Exception {

List> list = null;

//创建Excel工作薄

Workbook work = ImportExcelUtil.getWorkbook(in,fileName);

if(null == work){

throw new Exception("创建Excel工作薄为空！");

}

Sheet sheet = null;

Row row = null;

Cell cell = null;

list = new ArrayList>();

//遍历Excel中所有的sheet

for (int i = 0; i < work.getNumberOfSheets(); i++) {

sheet = work.getSheetAt(i);

if(sheet==null){continue;}

//遍历当前sheet中的所有行

for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {

row = sheet.getRow(j);

if(row==null||row.getFirstCellNum()==j){continue;}

//遍历所有的列

List

for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {

cell = row.getCell(y);

li.add(ImportExcelUtil.getCellValue(cell));

}

list.add(li);

}

}

// work.close();

return list;

}

/**

* 描述：根据文件后缀，自适应上传文件的版本

* @param inStr,fileName

* @return

* @throws Exception

*/

public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{

Workbook wb = null;

String fileType = fileName.substring(fileName.lastIndexOf("."));

if(excel2003L.equals(fileType)){

wb = new HSSFWorkbook(inStr); //2003-

}else if(excel2007U.equals(fileType)){

wb = new XSSFWorkbook(inStr); //2007+

}else{

throw new Exception("解析的文件格式有误！");

}

return wb;

}

/**

* 描述：对表格中数值进行格式化

* @param cell

* @return

*/

public static Object getCellValue(Cell cell){

Object value = null;

DecimalFormat df = new DecimalFormat("0"); //格式化number String字符

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

// SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化

// DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字

if (cell!=null){

AoVyTCuRLr switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

value = cell.getRichStringCellValue().getString();

break;

case Cell.CELL_TYPE_NUMERIC:

if("General".equals(cell.getCellStyle().getDataFormatString())){

value = df.format(cell.getNumericCellValue());

}

else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){

value = sdf.format(cell.getDateCellValue());

}

else{

value = sdf.format(cell.getDateCellValue());

}

break;

case Cell.CELL_TYPE_BOOLEAN:

value = cell.getBooleanCellValue();

break;

case Cell.CELL_TYPE_BLANK:

value = "";

break;

default:

break;

}

}

return value;

}

}

6、编写具体业务逻辑Service

主要思想：通过工具类将Excel文件解析成Object泛型的集合，再将集合循环遍历，在遍历中，将每行数据一次填入对象中，再每次循环中，将赋值后的对象存入一个list集合，最后统一将集合执行批量上传的方法，存入数据库。

public Map

Map resultMap = new HashMap<>();

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

List tbZbzsList = new ArrayList<>();

try {

//获取数据

List> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());

resultMap.put("导入成功",200);

//封装数据

for (int i = 0; i < olist.size(); i++) {

List

if (list.get(0) == "" || ("序号").equals(list.get(0))) {

continue;

}

TbZbzs tbZbzs = new TbZbzs();

tbZbzs.setId(UUID.randomUUID().toString().replace("-", "").substring(0, 20));

//根据下标获取每一行的每一条数据

if (String.valueOf(list.get(0))==null) {

resultMap.put("state", "部门不能为空");

continue;

}

tbZbzs.setBm(String.valueOf(list.get(0)));

if (String.valueOf(list.get(1))==null) {

resultMap.put("state", "值班上报不能为空");

continue;

}

tbZbzs.setZbsb(String.valueOf(list.get(1)));

if (String.valueOf(list.get(2))==null) {

resultMap.put("state", "值班人员不能为空");

continue;

}

tbZbzs.setZbry(String.valueOf(list.get(2)));

if (String.valueOf(list.get(3))==null) {

resultMap.put("state", "导入失败,上报时间不能为空");

continue;

}

String dateStr3 = String.valueOf(list.get(3));

Date date3 = simpleDateFormat.parse(dateStr3);

tbZbzs.setSbsj(date3);

if (String.valueOf(list.get(4))==null) {

resultMap.put("state", "导入失败,结束时间不能为空");

continue;

}

String dateStr4 = String.valueOf(list.get(4));

Date date4 = simpleDateFormat.parse(dateStr4);

tbZbzs.setJssj(date4);

if (String.valueOf(list.get(5))==null) {

resultMap.put("state", "联系方式不能为空");

continue;

}

tbZbzs.setLxfs(String.valueOf(list.get(5)));

if (String.valueOf(list.get(6))==null) {

resultMap.put("state", "状态不能为空");

continue;

}

tbZbzs.setZt(String.valueOf(list.get(6)));

if (String.valueOf(list.get(7))==null) {

resultMap.put("state", "逻辑删除不能为空");

continue;

}

tbZbzs.setDelFlag(String.valueOf(list.get(7)));

if (String.valueOf(list.get(8))==null) {

resultMap.put("state", "导入失败,创建时间不能为空");

continue;

}

String dateStr8 = String.valueOf(list.get(8));

Date date8 = simpleDateFormat.parse(dateStr8);

tbZbzs.setCreateDate(date8);

tbZbzsList.add(tbZbzs);

}

int i = tbZbzsDao.insertTbZbzsList(tbZbzsList);

if (i != 0) {

resultMap.put("state", "导入成功");

}else {

resultMap.put("state", "导入失败");

}

} catch (Exception e) {

e.printStackTrace();

resultMap.put("state", "导入失败");

}

return resultMap;

}

7、在dao层对应的xml文件中，编写批量上传的方法

insert into tb_zbzs (

id,

bm,

zbsb,

zbry,

sbsj,

jssj,

lxfs,

zt,

del_flag,

create_date

) VALUES

(

#{item.id},

#{item.bm},

#{item.zbsb},

#{item.zbry},

#{item.sbsj},

#{item.jssj},

#{item.lxfs},

#{item.zt},

#{item.delFlag},

#{item.createDate}

)

8、Controller实现业务的控制

/**

* @方法名称: excelProTbZbzs

* @实现功能: 导入值班值守表Excel TODO: 方法入参根据页面对象设置

* @param file

* @return java.lang.String

* @create by zyw at 2022-03-17 16:49:31

**/

@ApiOperation(value="导入值班值守表Excel",notes="返回导入情况接口",response = TbZbzs.class)

@PostMapping(value = "/excelProTbZbzs")

public String excelProTbZbzs(@RequestParam("file") MultipartFile file){

try {

return buildResultStr(service.importTprkxx(file).get("state").equals("导入成功") ? buildSuccessResultData() : buildErrorResultData(service.importTprkxx(file).get("state").toString()));

}catch (RuntimeException e){

logError(log, e);

return buildResultStr(buildErrorResultData(e));

}

}

9、通过Swagger测试接口

10、在数据和控制台中查看导入效果

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注我们的更多内容!

Map resultMap = new HashMap<>();

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

List tbZbzsList = new ArrayList<>();

try {

//获取数据

List> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());

resultMap.put("导入成功",200);

//封装数据

for (int i = 0; i < olist.size(); i++) {

List

if (list.get(0) == "" || ("序号").equals(list.get(0))) {

continue;

}

TbZbzs tbZbzs = new TbZbzs();

tbZbzs.setId(UUID.randomUUID().toString().replace("-", "").substring(0, 20));

//根据下标获取每一行的每一条数据

if (String.valueOf(list.get(0))==null) {

resultMap.put("state", "部门不能为空");

continue;

}

tbZbzs.setBm(String.valueOf(list.get(0)));

if (String.valueOf(list.get(1))==null) {

resultMap.put("state", "值班上报不能为空");

continue;

}

tbZbzs.setZbsb(String.valueOf(list.get(1)));

if (String.valueOf(list.get(2))==null) {

resultMap.put("state", "值班人员不能为空");

continue;

}

tbZbzs.setZbry(String.valueOf(list.get(2)));

if (String.valueOf(list.get(3))==null) {

resultMap.put("state", "导入失败,上报时间不能为空");

continue;

}

String dateStr3 = String.valueOf(list.get(3));

Date date3 = simpleDateFormat.parse(dateStr3);

tbZbzs.setSbsj(date3);

if (String.valueOf(list.get(4))==null) {

resultMap.put("state", "导入失败,结束时间不能为空");

continue;

}

String dateStr4 = String.valueOf(list.get(4));

Date date4 = simpleDateFormat.parse(dateStr4);

tbZbzs.setJssj(date4);

if (String.valueOf(list.get(5))==null) {

resultMap.put("state", "联系方式不能为空");

continue;

}

tbZbzs.setLxfs(String.valueOf(list.get(5)));

if (String.valueOf(list.get(6))==null) {

resultMap.put("state", "状态不能为空");

continue;

}

tbZbzs.setZt(String.valueOf(list.get(6)));

if (String.valueOf(list.get(7))==null) {

resultMap.put("state", "逻辑删除不能为空");

continue;

}

tbZbzs.setDelFlag(String.valueOf(list.get(7)));

if (String.valueOf(list.get(8))==null) {

resultMap.put("state", "导入失败,创建时间不能为空");

continue;

}

String dateStr8 = String.valueOf(list.get(8));

Date date8 = simpleDateFormat.parse(dateStr8);

tbZbzs.setCreateDate(date8);

tbZbzsList.add(tbZbzs);

}

int i = tbZbzsDao.insertTbZbzsList(tbZbzsList);

if (i != 0) {

resultMap.put("state", "导入成功");

}else {

resultMap.put("state", "导入失败");

}

} catch (Exception e) {

e.printStackTrace();

resultMap.put("state", "导入失败");

}

return resultMap;

}

7、在dao层对应的xml文件中，编写批量上传的方法

insert into tb_zbzs (

id,

bm,

zbsb,

zbry,

sbsj,

jssj,

lxfs,

zt,

del_flag,

create_date

) VALUES

(

#{item.id},

#{item.bm},

#{item.zbsb},

#{item.zbry},

#{item.sbsj},

#{item.jssj},

#{item.lxfs},

#{item.zt},

#{item.delFlag},

#{item.createDate}

)

8、Controller实现业务的控制

/**

* @方法名称: excelProTbZbzs

* @实现功能: 导入值班值守表Excel TODO: 方法入参根据页面对象设置

* @param file

* @return java.lang.String

* @create by zyw at 2022-03-17 16:49:31

**/

@ApiOperation(value="导入值班值守表Excel",notes="返回导入情况接口",response = TbZbzs.class)

@PostMapping(value = "/excelProTbZbzs")

public String excelProTbZbzs(@RequestParam("file") MultipartFile file){

try {

return buildResultStr(service.importTprkxx(file).get("state").equals("导入成功") ? buildSuccessResultData() : buildErrorResultData(service.importTprkxx(file).get("state").toString()));

}catch (RuntimeException e){

logError(log, e);

return buildResultStr(buildErrorResultData(e));

}

}

9、通过Swagger测试接口

10、在数据和控制台中查看导入效果

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注我们的更多内容!

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