POI实现Excel导入到数据库
1.创建maven工程,工程结构如下
2.导入依赖和编写配置文件
<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-parent</artifactId><version>2.0.0.RELEASE</version></parent><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><dependency><groupId>mons</groupId><artifactId>commons-lang3</artifactId></dependency><dependency><groupId>io.springfox</groupId><artifactId>springfox-swagger2</artifactId><version>2.7.0</version></dependency><dependency><groupId>io.springfox</groupId><artifactId>springfox-swagger-ui</artifactId><version>2.7.0</version></dependency></dependencies>
server:port: 9001spring:datasource:url: jdbc:mysql://localhost:3306/admin_bd?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&noAccessToProcedureBodies=true&zeroDateTimeBehavior=convertToNullusername: rootpassword: roottype: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.jdbc.Driverhikari:connection-timeout: 30000idle-timeout: 600000max-lifetime: 1800000maximum-pool-size: 50minimum-idle: 10validation-timeout: 5000
3.编写启动类
import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;/*** @className: ImportExcelApplication* @description: 导入excel* @author: penghailan* @create: -06-25 17:11**/@SpringBootApplication@MapperScan("com.demo.importExcel.mapper")public class ImportExcelApplication {public static void main(String[] args) {SpringApplication.run(ImportExcelApplication.class,args);}}
4.编写excel注解
import java.lang.annotation.*;/*** @className: ExcelHeader* @description:* @author: penghailan* @create: -06-04 11:42**/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface ExcelHeader {/*** 表头* @return*/String value() default "";/*** 列索引* @return*/int columnIndex() default 0;}
5.编写响应类
import lombok.Data;/*** @className: Response* @description: 响应类* @author: penghailan* @create: -03-30 15:47**/@Datapublic class Response {private Integer code;private Object data;private String msg;public static Response success(Object data){Response response = new Response();response.setCode(200);response.setData(data);response.setMsg("ok");return response;}public static Response error(String mes){Response response = new Response();response.setCode(300);response.setMsg(mes);return response;}}
6.编写导入excel的对应实体类GoodsBaseBo
import com.demo.importExcel.anno.ExcelHeader;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*** @className: GoodsBase* @description: 导入商品* @author: penghailan* @create: -06-24 14:00**/@Data@NoArgsConstructor@AllArgsConstructorpublic class GoodsBaseBo {@ExcelHeader(value = "商品编码")private String goodsCode;//名称@ExcelHeader(value = "商品名称")private String goodsName;//别名@ExcelHeader(value = "别名")private String aliasName;//品牌@ExcelHeader(value = "品牌")private String brand;//批次号（包含生成日期）@ExcelHeader(value = "批次号")private String batchNumber;//商品说明@ExcelHeader(value ="商品说明" )private String goodsDesc;//香型，1酱香，2清香，3浓香@ExcelHeader(value = "香型")private Integer fragrance;//产地@ExcelHeader(value = "产地")private String origin;//商品类型，1成品酒，2原坛酒@ExcelHeader(value = "商品类型")private Integer goodsType;//酒精度@ExcelHeader(value = "酒精度")private Integer alcohol;//份额净含量（默认单位ml）@ExcelHeader(value = "份额净含量")private Integer netContent;//单位,1箱，2瓶，3坛@ExcelHeader(value = "单位")private Integer unit;//交易份额@ExcelHeader(value = "交易份额")private Integer transShare;//商品规格@ExcelHeader(value = "商品规格")private String goodsPackSpec;//封藏天数@ExcelHeader(value ="封藏天数" )private Integer days;}
6.编写导入excel工具类ExcelUtils
import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.math.BigDecimal;import java.time.LocalDateTime;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.ArrayList;import java.util.Date;import java.util.List;/*** @className: ExcelUtils* @description: 导入excel* @author: penghailan* @create: -06-25 17:34**/@Slf4jpublic class ExcelUtils {static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");/**** @param multipartFile* @param clz VO对象，对应Excel表头* @param <T>* @throws IOException* @throws NoSuchMethodException* @throws IllegalAccessException* @throws InvocationTargetException* @throws InstantiationException*/public static <T> List<T> importExcel(MultipartFile multipartFile, Class<T> clz) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {if(null == multipartFile) {throw new NullPointerException("请选择文件");}log.info(multipartFile.getName());log.info("文件类型:{}", multipartFile.getContentType());String fileName = multipartFile.getOriginalFilename();log.info("文件名:{}", fileName);if(!"application/vnd.ms-excel".equals(multipartFile.getContentType())) {throw new RuntimeException("请选择正确的文件类型与文件!");}// 返回数据List<T> list = new ArrayList<>();InputStream inputStream = multipartFile.getInputStream();Workbook wb = WorkbookFactory.create(inputStream);// 读取第一个sheetSheet sheet = wb.getSheetAt(0);// 获取最大行数(或者sheet.getLastRowNum())int rownum = sheet.getPhysicalNumberOfRows();// 反射获取字段Field[] fields = clz.getDeclaredFields();// 获取第一行(表头)Row row = sheet.getRow(0);// 获取最大列数int column = row.getPhysicalNumberOfCells();// 表头校验for (int j = 0; j < fields.length; j++){Field field = fields[j];if (field.isAnnotationPresent(ExcelHeader.class)) {ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);Cell cell = row.getCell(j);if (cell == null || !getCellValue(cell).equals(annotation.value())) {throw new RuntimeException("Excel格式错误");}}}// 处理行数据for (int i = 1; i<rownum; i++) {row = sheet.getRow(i);// 遇到空行则结束if (row == null) {break;}T rowData = clz.getDeclaredConstructor().newInstance();// 处理列数据for (int j = 0; j < fields.length; j++){Field field = fields[j];// 设置属性可访问field.setAccessible(true);if (field.isAnnotationPresent(ExcelHeader.class)) {ExcelHeader annotation = field.getAnnotation(ExcelHeader.class);// 这里默认按列顺序，也可以根据columnIndex设置列顺序int columnIndex = annotation.columnIndex();Cell cell = row.getCell(j);if (cell == null) {continue;}// 获取列值Object value = getCellValue(cell);// 设置属性setFieldValue(rowData, field, value);}}list.add(rowData);}log.info("上传数据={}", list.toString());return list;}private static <T> void setFieldValue(T rowData, Field field, Object value) throws IllegalAccessException {if (field.getType() == int.class || field.getType() == Integer.class) {field.set(rowData, value);} else if (field.getType() == long.class || field.getType() == Long.class) {field.set(rowData, value);} else if (field.getType() == double.class || field.getType() == Double.class) {field.set(rowData, value);} else if (field.getType() == String.class) {field.set(rowData, String.valueOf(value));} else if (field.getType() == LocalDateTime.class) {field.set(rowData, LocalDateTime.parse(String.valueOf(value), dateTimeFormatter));}}private static Object getCellValue(Cell cell) {CellType cellType = cell.getCellType();Object cellValue = null;if (cellType == CellType._NONE) {} else if (cellType == CellType.NUMERIC) {// 数值型if (DateUtil.isCellDateFormatted(cell)) {// 日期类型Date d = cell.getDateCellValue();cellValue = dateTimeFormatter.format(LocalDateTime.ofInstant(d.toInstant(), ZoneId.systemDefault()));} else {double numericCellValue = cell.getNumericCellValue();BigDecimal bdVal = new BigDecimal(numericCellValue);if ((bdVal + ".0").equals(Double.toString(numericCellValue))) {// 整型cellValue = new Integer(bdVal.toString());//cellValue = bdVal;} else if (String.valueOf(numericCellValue).contains("E10")) {// 科学记数法cellValue = new BigDecimal(numericCellValue).toPlainString();} else {// 浮点型cellValue = numericCellValue;}}} else if (cellType == CellType.STRING) {// 字符串型cellValue = cell.getStringCellValue();} else if (cellType == CellType.FORMULA) {// 公式型} else if (cellType == CellType.BLANK) {// 空值} else if (cellType == CellType.BOOLEAN) {// 布尔型cellValue = cell.getBooleanCellValue();} else if (cellType == CellType.ERROR) {// 错误cellValue = cell.getErrorCellValue();}log.info("cellType={}, cellValue={}", cellType.name(), cellValue);return cellValue;}}
6.GoodsBaseController
import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;import java.lang.reflect.InvocationTargetException;import java.util.List;/*** @className: GoodsBaseController* @description:* @author: penghailan* @create: -06-25 17:30**/@Slf4j@RestController@RequestMapping("goodsBase")public class GoodsBaseController {@Autowiredprivate GoodsBaseService goodsBaseService;/*** @Title: importExcel* @Description: 导入商品列表* @param: request* @return: Response*/@ResponseBody@RequestMapping(value = "import",method = RequestMethod.POST,produces = "application/json")public Response importExcel(MultipartFile file) throws IOException, InvocationTargetException, NoSuchMethodException, InstantiationException, IllegalAccessException {List<GoodsBaseBo> goodsBaseList = ExcelUtils.importExcel(file, GoodsBaseBo.class);log.info("goodsBaseList:{}",goodsBaseList);return goodsBaseService.importExcel(goodsBaseList);}}
7.GoodsBaseService
import java.util.List;public interface GoodsBaseService {/*** @Title: addGoodsBase* @Description: 导入商品信息* @param: goodsBaseQo* @return: Response*/Response importExcel(List<GoodsBaseBo> goodsBaseList);}
8.GoodsBaseServiceImpl
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;/*** @className: GoodsBaseServiceImpl* @description:* @author: penghailan* @create: -06-25 17:31**/@Servicepublic class GoodsBaseServiceImpl implements GoodsBaseService {@Autowiredprivate GoodsBaseMapper goodsBaseMapper;/*** @param goodsBaseList* @Title: addGoodsBase* @Description: 导入商品信息* @param: goodsBaseQo* @return: Response*/public Response importExcel(List<GoodsBaseBo> goodsBaseList) {//判断商品信息是否存在for (GoodsBaseBo goodsBase : goodsBaseList) {int existGoodsName = goodsBaseMapper.ExistGoodsName(goodsBase.getGoodsCode());if(existGoodsName>0){return Response.error(goodsBase.getGoodsCode()+"商品编号已存在,请重新输入!");}}//批量导入商品int i = goodsBaseMapper.mulAddGoodsBase(goodsBaseList);if (i==goodsBaseList.size()){return Response.success("导入商品成功");}return Response.error("导入商品失败");}}
9.GoodsBaseMapper
import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Select;import java.util.List;@Mapperpublic interface GoodsBaseMapper {/*** @Title: isExistGoodsName* @Description: 判断编号是否已存在* @param: goodsCode goodsBaseId* @return: int*/@Select(" SELECT COUNT(1) FROM goods_base_bt WHERE active_flag='y' AND goodsCode=#{goodsCode} ")int ExistGoodsName(String goodsCode);/*** @Title: addGoodsBase* @Description: 批量添加商品* @param: goodsBaseQo* @return: int*/@Insert("<script>" +" INSERT INTO goods_base_bt(goodsCode,goodsName,aliasName,brand,batchNumber,goodsDesc,fragrance," +" origin,goodsType,alcohol,netContent,unit,transShare,goodsPackSpec,days," +" create_by,create_time,active_flag)" +" VALUES" +" <foreach collection='list' item='item' index='index' separator=',' close= ';'> " +" (#{item.goodsCode},#{item.goodsName},#{item.aliasName},#{item.brand},#{item.batchNumber},#{item.goodsDesc},#{item.fragrance}," +" #{item.origin},#{item.goodsType},#{item.alcohol},#{Content},#{item.unit},#{item.transShare},#{item.goodsPackSpec},#{item.days},0,NOW(3),'y') " +" </foreach>" +"</script>")int mulAddGoodsBase(List<GoodsBaseBo> list);}
10.测试
数据库中执行下面语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/`admin_bd` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `admin_bd`;/*Table structure for table `goods_base_bt` */CREATE TABLE `goods_base_bt` (`goodsId` int(11) NOT NULL AUTO_INCREMENT,`goodsCode` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,`goodsName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,`aliasName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,`brand` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,`batchNumber` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,`goodsDesc` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,`fragrance` tinyint(2) NOT NULL,`origin` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,`goodsType` tinyint(2) NOT NULL,`alcohol` int(11) NOT NULL,`netContent` int(11) NOT NULL,`unit` tinyint(2) NOT NULL,`transShare` int(11) NOT NULL,`goodsPackSpec` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,`days` int(11) NOT NULL,`create_by` bigint(20) NOT NULL,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`update_by` bigint(20) DEFAULT NULL,`update_time` timestamp NULL DEFAULT NULL,`active_flag` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,PRIMARY KEY (`goodsId`),UNIQUE KEY `goods_base_bt_ux1` (`goodsCode`)) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
10.1测试前数据库数据
10.2导入商品数据
10.3postman工具请求
10.3检验是否导入成功
数据库数据
接口返回数据
证明导入成功
