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"
               
               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 ></i>
        <div >
          將檔案拖到此處，或
          <em>點擊上傳</em>
        </div>
        <div 
             style="color:red"
             slot="tip">提示：僅允許匯入“xls”或“xlsx”格式檔案！</div>
      </el-upload>
      <div slot="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 = https://www.cnblogs.com/52mqq/p/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();
    }
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/547996.html

標籤：Java

上一篇：執行緒池

下一篇：PLG SaaS 產品 Figma 商業模式拆解