springBoot，springCode项目 java 读取Excel 文件操作

导入的文件

前端点击上传得到文件（MultipartFile file 【这里是存放的临时文件】）

本人前端用的vue3，elementui,
导入按钮代码

<!--导入文件 -->
      <el-col :span="1.5">
        <el-button type="info"
                   plain
                   icon="el-icon-upload"
                   size="mini"
                   @click="handleImport"
                   v-hasPermi="['production:monthly_production_plan:import']"
        >导入</el-button>
      </el-col>

弹出框代码

<!-- 月度焊接计划导入对话框 -->
    <el-dialog :title="upload.title"
               :visible.sync="upload.open"
               width="400px"
               append-to-body
               :close-on-click-modal="false">
      <el-upload ref="upload"
                 :limit="1"
                 accept=".xlsx, .xls"
                 :headers="upload.headers"
                 :action="upload.url + '?updateSupport=' + upload.updateSupport"
                 :disabled="upload.isUploading"
                 :on-progress="handleFileUploadProgress"
                 :on-success="handleFileSuccess"
                 :auto-upload="false"
                 drag>
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">
          将文件拖到此处，或
          <em>点击上传</em>
        </div>
        <div class="el-upload__tip"
             style="color:red"
             slot="tip">提示：仅允许导入“xls”或“xlsx”格式文件！</div>
      </el-upload>
      <div slot="footer"
           class="dialog-footer">
        <el-button type="primary"
                   @click="submitFileForm">确 定</el-button>
        <el-button @click="upload.open = false">取 消</el-button>
      </div>
    </el-dialog>

js代码 return{}层（upload参数）前端不清楚的请先看一下vue 框架官方文档

return {
      //导入
      upload: {
        // 是否显示弹出层（导入）
        open: false,
        // 弹出层标题（导入）
        title: "",
        // 是否禁用上传
        isUploading: false,
        // 是否更新已经存在的数据
        updateSupport: 0,
        // 设置上传的请求头部
        headers: { Authorization: "Bearer " + getToken() },
        // 上传的地址（后台接口）
        url: process.env.VUE_APP_BASE_API + "/production/monthly_production_plan/importData"
      },
    };

js代码 methods: {}中使用方法

methods: {
    /** 导入按钮操作 */
    handleImport() {
      console.log(this)
      this.upload.title = "焊接月度生产计划导入";
      this.upload.open = true;
    },
    /** 下载模板操作 */
    importTemplate() {
      this.download('production/monthly_production_plan/importTemplate', {
      }, `焊接月度生产计划_${new Date().getTime()}.xlsx`)
    },
     // 文件上传中处理
    handleFileUploadProgress(event, file, fileList) {
      this.upload.isUploading = true;
    },
    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
      this.upload.open = false;
      this.upload.isUploading = false;
      this.$refs.upload.clearFiles();
      this.$alert("<div style='overflow: auto;overflow-x: hidden;max-height: 70vh;padding: 10px 20px 0;'>" + response.msg + "</div>", "导入结果", { dangerouslyUseHTMLString: true });
      this.getList();
    },
    // 提交上传文件
    submitFileForm() {
      this.$refs.upload.submit();
    }
}

mysql数据表

drop table if exists iot_dos_welding_monthly_production_plan;
CREATE TABLE `iot_dos_welding_monthly_production_plan` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `planned_time` date DEFAULT NULL COMMENT '计划时间',
  `planned_output` Double DEFAULT NULL COMMENT '计划产量',
    `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '更新者',
  `creation_time` datetime DEFAULT NULL COMMENT '创建时间',
    `index_id` BIGINT DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='焊接月度生产计划';
-- DELETE FROM iot_dos_welding_monthly_production_plan;
create unique index idx_planned_time_index_id on iot_dos_welding_monthly_production_plan(planned_time, index_id);

• IotDosWeldingMonthlyProductionPlan 实体类domain

package com.sunkun.iot.production.domain;
import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.models.auth.In;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.ruoyi.common.core.annotation.Excel;
import com.ruoyi.common.core.web.domain.BaseEntity;
/**
 * 焊接月度生产计划对象 iot_dos_welding_monthly_production_plan
 * 
 * @author xiaolv
 * @date 2023-02-03
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class IotDosWeldingMonthlyProductionPlan extends BaseEntity
{
    private static final long serialVersionUID = 1L;
    /** 主键id */
    @Excel(name = "ID")
    private Integer id;
    /** 计划时间 */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "创建时间", width = 30, dateFormat = "yyyy-MM-dd")
    private Date plannedTime;
    /** 计划产量 */
    @Excel(name = "计划产量")
    private Double plannedOutput;
    /** 更新者 */
    @Excel(name = "更新者")
    private String updatedBy;
    /** 创建时间 */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @Excel(name = "创建时间", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss")
    private Date creationTime;
    /** 索引值 */
    private Integer indexId;
}

后台接收（MultipartFile file 【这里是存放的临时文件】）

controller 层接口本人接口[/production/monthly_production_plan/importData]{根据个人需求定义自己的接口}（MultipartFile file 【这里是存放的临时文件】）

/**
     * 焊接月度生产计划导入数据
     */
    @Log(title = "焊接月度生产计划导入数据", businessType = BusinessType.IMPORT)
    @PostMapping("/importData")
    public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
    {
        String message = "";
        try {
            List<IotDosWeldingMonthlyProductionPlan> list = WeldingMonthlyProductionPlanExcelUtil.importWeldingMonthlyProductionPlan(file);
            if(list.size() > 0) {
                message = iotDosWeldingMonthlyProductionPlanService.batchProcessingDataWelding(list);
            }else {
                message="该次导入的数据集为空，请检查导入的Excel文件！！！";
            }
        }catch (Exception e){
            e.printStackTrace();
            message="数据导入失败。";
        }
        return AjaxResult.success(message);
    }

自定义 WeldingMonthlyProductionPlanExcelUtil 用于读取文件信息（MultipartFile file 【这里是存放的临时文件】）

package com.sunkun.iot.production.utils;

import com.sunkun.iot.production.domain.IotDosWeldingMonthlyProductionPlan;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import static com.sunkun.iot.baseinfo.utils.BomExcelUnit.getSheetByWorkbook;
import static com.sunkun.iot.baseinfo.utils.BomExcelUnit.getWorkbookByInputStream;

public class WeldingMonthlyProductionPlanExcelUtil {
    /*读取月度焊接计划文件信息*/
    public static List<IotDosWeldingMonthlyProductionPlan> importWeldingMonthlyProductionPlan(MultipartFile file) throws IOException {
        System.out.println("MultipartFile::"+file);
        List<IotDosWeldingMonthlyProductionPlan> list = new ArrayList<>();
        Workbook workBook = null;
        String planNum = "";
        //得到工作空间
        workBook = getWorkbookByInputStream(file.getInputStream(), file.getOriginalFilename());
        //得到工作表
        Sheet sheet = getSheetByWorkbook(workBook, 0);
        if (sheet.getRow(2000) != null){
            throw new RuntimeException("系统已限制单批次导入必须小于或等于2000笔！");
        }
        //获取有几个sheet 遍历
        int numberOfSheets = workBook.getNumberOfSheets();
        System.out.println(numberOfSheets);
        //获取第几张表
        System.out.println("工作表名称：" + sheet);
        int rowsOfSheet = sheet.getPhysicalNumberOfRows();
        System.out.println("当前表格的总行数:" + rowsOfSheet);
        //获取当月天数
        Calendar cal = Calendar.getInstance();
        cal.setTime(new Date());
        cal.set(Calendar.DAY_OF_MONTH, 1);
        cal.roll(Calendar.DAY_OF_MONTH, -1);
        String format = new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime());
        int day = Integer.parseInt(format.substring((format.lastIndexOf("-") + 1)));//得到本月天数
        //得到当月日期集
        List<String> dateList = getDayByMonth();//自定义方法得到这个月的所有年月日
        //得到导入的数据集
        for (int i = 0; i<=day;i++){
            IotDosWeldingMonthlyProductionPlan welding = new IotDosWeldingMonthlyProductionPlan();
            if(i==0){
                continue;
            }
            welding.setPlannedTime(StringToDate(dateList.get(i-1).trim()));//通过自定义方法得到时间
            welding.setCreationTime(new Date());
            if(sheet.getRow(1).getCell(i).toString().trim().equals("")){
                welding.setPlannedOutput(0.0);
            }else {
                planNum = sheet.getRow(1).getCell(i).toString().trim();
                welding.setPlannedOutput(Double.valueOf(planNum));
            }
            list.add(welding);
        }
        return list;
    }

    /**
     * 通过函数获取当月天数
     * @return
     */
    public static List<String> getDayByMonth(){
        List<String> data = new ArrayList<>();
        try {
            Calendar c = Calendar.getInstance();
            // 获取当前的年份
            int year = c.get(Calendar.YEAR);
            // 获取当前的月份（需要加1才是现在的月份）
            int month = c.get(Calendar.MONTH) + 1;
            // 获取本月的总天数
            int dayCount = c.getActualMaximum(Calendar.DAY_OF_MONTH);
            // 定义时间格式
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            // 开始日期为当前年月拼接1号
            Date startDate = sdf.parse(year + "-" + month + "-01");
            // 结束日期为当前年月拼接该月最大天数
            Date endDate = sdf.parse(year + "-" + month + "-" + dayCount);
            // 设置calendar的开始日期
            c.setTime(startDate);
            // 当前时间小于等于设定的结束时间
            while(c.getTime().compareTo(endDate) <= 0){
                String time = sdf.format(c.getTime());
                data.add(time);
                // 当前日期加1
                c.add(Calendar.DATE, 1);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     *  String 转 date
     * @param datetime
     * @return
     */
    public static Date StringToDate(String datetime){
        SimpleDateFormat sdFormat=new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        try {
            date = sdFormat.parse(datetime);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return date;
    }
}

上面的WeldingMonthlyProductionPlanExcelUtil 是链接数据库的操作看不懂可以参考以下（以下是test类直接测试读取文件的操作）可以点此链接查看
https://blog.csdn.net/tanqingfu1/article/details/105369858
mapper层

    /**
     * 月度焊接计划批量新增
     * @param list
     * @return
     */
    int insertIotDosWeldingMonthlyProductionPlanList(List<IotDosWeldingMonthlyProductionPlan> list);

mapper.xml

<insert id="insertIotDosWeldingMonthlyProductionPlanList" parameterType="IotDosWeldingMonthlyProductionPlan">
        insert into iot_dos_welding_monthly_production_plan
        (planned_time, planned_output, updated_by, creation_time, index_id)
        VALUES
        <foreach collection ="list" item="welding" separator =",">
            (#{welding.plannedTime},#{welding.plannedOutput},#{welding.updatedBy},#{welding.creationTime},#{welding.indexId})
        </foreach >
        ON DUPLICATE KEY UPDATE 
        planned_output = VALUES(planned_output),
        updated_by = VALUES(updated_by),
        creation_time = VALUES(creation_time)
    </insert>

service 层和 serviceImpl层

package com.sunkun.iot.production.service;

import java.util.List;

import com.sunkun.iot.production.domain.IotDosShopCalendar;
import com.sunkun.iot.production.domain.IotDosWeldingMonthlyProductionPlan;

/**
 * 焊接月度生产计划Service接口
 * 
 * @author xiaolv
 * @date 2023-02-03
 */
public interface IIotDosWeldingMonthlyProductionPlanService 
{
    /**
     * 批量新增
     * @param list
     * @return
     */
    int insertIotDosWeldingMonthlyProductionPlanList(List<IotDosWeldingMonthlyProductionPlan> list);

    /**
     * 批量处理数据
     * @param list
     * @return
     */
    String batchProcessingDataWelding(List<IotDosWeldingMonthlyProductionPlan> list);
}
package com.sunkun.iot.production.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ruoyi.common.core.utils.ServletUtils;
import com.ruoyi.common.security.service.TokenService;
import com.ruoyi.system.api.model.LoginUser;
import com.sunkun.iot.production.mapper.IotDosShopCalendarMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.sunkun.iot.production.mapper.IotDosWeldingMonthlyProductionPlanMapper;
import com.sunkun.iot.production.domain.IotDosWeldingMonthlyProductionPlan;
import com.sunkun.iot.production.service.IIotDosWeldingMonthlyProductionPlanService;

import javax.validation.Validator;

/**
 * 焊接月度生产计划Service业务层处理
 * 
 * @author xiaolv
 * @date 2023-02-03
 */
@Service
public class IotDosWeldingMonthlyProductionPlanServiceImpl implements IIotDosWeldingMonthlyProductionPlanService 
{

    private static final Logger log = LoggerFactory.getLogger(IotDosShopCalendarMapper.class);
    @Autowired
    private IotDosWeldingMonthlyProductionPlanMapper iotDosWeldingMonthlyProductionPlanMapper;
    @Autowired
    protected Validator validator;
    @Autowired
    private TokenService tokenService;
  

    /**
     * 月度焊接计划批量新增
     *
     * @param list
     * @return
     */
    @Override
    public int insertIotDosWeldingMonthlyProductionPlanList(List<IotDosWeldingMonthlyProductionPlan> list) {
        List<IotDosWeldingMonthlyProductionPlan> weldingList = new ArrayList<>();
        LoginUser loginUser = tokenService.getLoginUser(ServletUtils.getRequest());
        String user = loginUser.getUsername();
        for (IotDosWeldingMonthlyProductionPlan plan : list) {
            IotDosWeldingMonthlyProductionPlan welding = new IotDosWeldingMonthlyProductionPlan();
            welding.setId(plan.getId());
            welding.setPlannedTime(plan.getPlannedTime());
            welding.setPlannedOutput(plan.getPlannedOutput());
            welding.setUpdatedBy(user);
            welding.setCreationTime(plan.getCreationTime());
            welding.setIndexId(1);
            weldingList.add(welding);
        }
        int count = iotDosWeldingMonthlyProductionPlanMapper.insertIotDosWeldingMonthlyProductionPlanList(weldingList);
        return count;
    }

    @Override
    public String batchProcessingDataWelding(List<IotDosWeldingMonthlyProductionPlan> list) {
        int num = insertIotDosWeldingMonthlyProductionPlanList(list);
        return "导入焊接月度计划总数据条数:"+list.size();
    }
}

posted on 2023-03-24 16:07 猫小吕阅读(301) 评论(0) 编辑收藏举报