java解析excel文件入数据库

第一步依赖jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>

第二步实体类

/**
 * @description: 经费表
 * @author:
 * @date: 2021-04-19 15:50
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Price {
    private Integer id;

    /**
     * 姓名
     */
    private String username;

    private String account;
    /**
     * 交通
     */
    private String traffic;

    /**
     * 创建时间
     */
    private  String createTime;
    /**
     * 修改时间
     */
    private String updateTime;


}

第三步映射文件SQL

<insert id="savePrices"  parameterType="com.cn.entity.Price">
    INSERT INTO  no10_price
    (route_id,username,account,traffic,create_time)
    VALUES
    <foreach collection="list" item="item" index="index" separator="," close="">
        (
        #{item.routeId},
        #{item.username},
        #{item.account},
        #{item.traffic},
        #{item.createTime}
        )
    </foreach>
</insert>

 

第四步接口

@Mapper
public interface PriceMapper {
   

    /**
     * 批量写入数据库
     * @param list
     * @return
     */
    int savePrices(List<Price> list);

}

public interface PriceService {

    /**
     * 上传excel 批量写入数据库
     * @param file
     * @param routeId
     * @param account
     * @return
     */

    File  ParsingExcels (MultipartFile file, Integer routeId,String account) throws IOException, InvalidFormatException;

}

第五步数据处理类

public class ExcelUtils {

    /**
     * excel值处理
     * @param cell
     * @return
     */

    public static Object getXSSFValue(XSSFCell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case _NONE:
                    break;
                case NUMERIC:
                    //cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                   //cellValue = String.valueOf(cell.getNumericCellValue());
                    cell.getNumericCellValue();//数字
                    DecimalFormat d = new DecimalFormat("#.00");
                    cellValue = d.format(cell.getNumericCellValue());
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();//字符串
                    break;
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";//布尔
                    break;
                case FORMULA://计算
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue evaluate = evaluator.evaluate(cell);
                    cellValue = evaluate.formatAsString();
                    break;

                case BLANK://空值
                    cellValue = "";
                    break;

                case ERROR://故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }

        }
        return cellValue;
    }

第六步service数据处理

@Service
public class PriceServiceImpl  implements PriceService {

    @Autowired
    private PriceMapper priceMapper;
  

    /**
     * 批量写入数据库
     * @param file
     * @param routeId
     * @param account
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    @Override
    public File ParsingExcels(MultipartFile file, Integer routeId, String account) throws IOException, InvalidFormatException {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String extension = "."+ FilenameUtils.getExtension(file.getOriginalFilename());
        File file1 = UploadUtils.uploadExcel(file, account);
        List<Price> list = null;
        if(".xlsx".equals(extension)) {
            //excel2007及以上版本
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file1);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); //获取excel的sheet
            list = new ArrayList<>();
            //循环获取excel每一行
            for(int rowNum = 2; rowNum < xssfSheet.getLastRowNum()+1; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if(xssfRow == null) {
                    continue;
                }
                Price price = new Price();
                //循环获取excel每一行的每一列
                for(int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                    XSSFCell xssCell = xssfRow.getCell(cellNum);
                    if(xssCell == null) {
                        continue;
                    }
                    if(cellNum == 1) {
                        price.setAccount(ExcelUtils.getXSSFValue(xssCell).toString());
                    }else if(cellNum == 2) {
                        price.setUsername(ExcelUtils.getXSSFValue(xssCell).toString());
                    }else if(cellNum == 3) {
                        price.setTraffic(ExcelUtils.getXSSFValue(xssCell).toString());
                    }
                    price.setCreateTime(df.format(new Date()));
                    price.setRouteId(routeId);
                }
                list.add(price);  //将excel每一行的数据封装到user对象,并将user对象添加到list

            }
        }
        else if("xls".equals(extension)) {
             //excel2003版本
            Workbook workbook = new HSSFWorkbook(POIFSFileSystem.create(file1));
            Sheet sheet = workbook.getSheetAt(0); //获取excel的sheet

            list = new ArrayList<>();
            //循环获取excel每一行
            for(int rowNum=2;rowNum<sheet.getLastRowNum()+1;rowNum++) {
                Row row=sheet.getRow(rowNum);
                if(row==null) {
                    continue;
                }
                Price price = new Price();
                //循环获取excel每一行的每一列
                for(int cellNum=0;cellNum<row.getLastCellNum();cellNum++) {
                    Cell cell=row.getCell(cellNum);
                    if(cell==null) {
                        continue;
                    }
                    if(cellNum==0) {
                        price.setAccount((String) ExcelUtils.getValue(cell));
                    }else if(cellNum==1) {
                        price.setUsername((String)ExcelUtils.getValue(cell));
                    }else if(cellNum==2) {
                        price.setTraffic((String)ExcelUtils.getValue(cell));
                    }
                    price.setCreateTime(df.format(new Date()));
                    price.setRouteId(routeId);
                }
                list.add(price);    //将excel每一行的数据封装到user对象,并将user对象添加到list
            }
        }

        //将list批量添加到数据库
        int prices = priceMapper.savePrices(list);
        System.out.println(prices);
        return null;
    }

}

第七步接收文件工具类

public class UploadUtils {
    
    public static File uploadExcel(MultipartFile file,String account) {
        String s = file.getOriginalFilename();
        String extension = "."+FilenameUtils.getExtension(file.getOriginalFilename());
        String uuid = UUID.randomUUID().toString().replace("_", "");
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String newFilename= df + uuid + extension;
/*虚拟地址*/
        String path = IMAGEROOT + HEADIMAGEPATH + "/SysImage/vel/" + account +"/price";
        try {
            File fileExcel= new File(path);
            if (!fileExcel.exists() && !fileExcel.isDirectory()) {
                System.out.println("目录不存在");
                fileExcel.mkdirs();
            }
            File file1 = new File(path, newFilename);
            file.transferTo(file1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  new File(path, newFilename);
    }



}

第八步controller层接收上传文件

@Controller
@ResponseBody
@RequestMapping("sn")
public class ExcelController {

    @Autowired
   private PriceService priceService;

    /**
     * 上传excel
     * @param file
     * @param routeId
     * @param session
     */
    @RequestMapping("get")
    @ResponseBody
    public void get(@RequestParam("file")MultipartFile file, Integer routeId,HttpSession session) {
        System.out.println(file);
        User user = (User) session.getAttribute("user");
        try {
            priceService.ParsingExcels(file, routeId, user.getAccount());
        }catch (Exception e){
            e.printStackTrace();
        }

    }

}

第九步前端

<li>
 <span class="input">
     <input type="file" id="upfile" name="upfile" placeholder="" />
 </span>
    <button @click="importExp" class="butExcel">上&nbsp;&nbsp;&nbsp;传</button>
    <span>格式：.xlsx</span>
</li>

js需要引入vue +jQuery +Ajax

importExp: function () {
    var  that =this;
    var formData = new FormData();
    var name = $("#upfile").val();
    var routeId = $("#routeId").val();
    formData.append("file",$("#upfile")[0].files[0]);
    formData.append("name",name);
    formData.append("routeId", routeId);
    var fileExtension = name.split('.').pop().toLowerCase();
    if (fileExtension !="xlsx"){
        top.layer.msg("未选择文件或文件类型不匹配，请使用xlsx类型的excel导入！", {icon: 2});

    }else {
        $.ajax({
            url : ctx +'/sn/get',
            type : 'POST',
            async : false,
            data : formData,
            processData : false,
            contentType : false,
            beforeSend:function(){//上传千
            },
            success : function(res) {
                top.layer.msg("上传成功,请刷新表格！", {icon: 1});
                that.createTable();
            }
            , error: function () {
                top.layer.msg("读取文件失败，请使用模板文件上传！", {icon: 2});
            }
        });
    }
},

效果图