springBoot，springCode项目 java 读取Excel 文件操作
导入的文件https://appdocs.wpscdn.cn/api/v3/office/copy/cG5LSHhBQmlEU1dWUU5xZHlwQkQ2TXd3amJwNk9BNXlCYTdpSjZXRVU4eFIzNUZvRVh4eCtWZWlNWm1PWElmcWtXaG9XeXMzK01xWm5Wei9ZQWlVV2tkTSs4eVhvVDloQWpkMk82RW9wSCtxNHRMRHR5Mjh3TlR4VXh0WGwyRlJ3bFpDUGJFSDJkWW1PY2ZIRDAxMzlSL2NLZjBGcXAwUmlzcWRvbHgvV3pOUXNuVnZQR293TGE1T0xWU3k5bmpRaElWak80dmRPK0x2Z0NZZ0YyTWI4bmRVVUovTEJXK2F3U0VVSDBHL0U3bkVXNGlEZjVhM3ZRR1VweTNiVmorc0FQS0xjcHU1NUFMTmlqd3NtWVRvTTNtMHRWeFlOdHVMblFCTzZSUjUxR2U1WENqSkRieTI4b1pFMDdNMw==/attach/object/ZGOPGAYAHE?
前端点击上传得到文件（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>https://appdocs.wpscdn.cn/api/v3/office/copy/cG5LSHhBQmlEU1dWUU5xZHlwQkQ2TXd3amJwNk9BNXlCYTdpSjZXRVU4eFIzNUZvRVh4eCtWZWlNWm1PWElmcWtXaG9XeXMzK01xWm5Wei9ZQWlVV2tkTSs4eVhvVDloQWpkMk82RW9wSCtxNHRMRHR5Mjh3TlR4VXh0WGwyRlJ3bFpDUGJFSDJkWW1PY2ZIRDAxMzlSL2NLZjBGcXAwUmlzcWRvbHgvV3pOUXNuVnZQR293TGE1T0xWU3k5bmpRaElWak80dmRPK0x2Z0NZZ0YyTWI4bmRVVUovTEJXK2F3U0VVSDBHL0U3bkVXNGlEZjVhM3ZRR1VweTNiVmorc0FQS0xjcHU1NUFMTmlqd3NtWVRvTTNtMHRWeFlOdHVMblFCTzZSUjUxR2U1WENqSkRieTI4b1pFMDdNMw==/attach/object/P2G7GAYAPI?
[*]弹出框代码
<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 ></i>
将文件拖到此处，或
<em>点击上传</em>
提示：仅允许导入“xls”或“xlsx”格式文件！
</el-upload>
<el-button type="primary"
@click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</el-dialog>https://appdocs.wpscdn.cn/api/v3/office/copy/cG5LSHhBQmlEU1dWUU5xZHlwQkQ2TXd3amJwNk9BNXlCYTdpSjZXRVU4eFIzNUZvRVh4eCtWZWlNWm1PWElmcWtXaG9XeXMzK01xWm5Wei9ZQWlVV2tkTSs4eVhvVDloQWpkMk82RW9wSCtxNHRMRHR5Mjh3TlR4VXh0WGwyRlJ3bFpDUGJFSDJkWW1PY2ZIRDAxMzlSL2NLZjBGcXAwUmlzcWRvbHgvV3pOUXNuVnZQR293TGE1T0xWU3k5bmpRaElWak80dmRPK0x2Z0NZZ0YyTWI4bmRVVUovTEJXK2F3U0VVSDBHL0U3bkVXNGlEZjVhM3ZRR1VweTNiVmorc0FQS0xjcHU1NUFMTmlqd3NtWVRvTTNtMHRWeFlOdHVMblFCTzZSUjUxR2U1WENqSkRieTI4b1pFMDdNMw==/attach/object/XGG7GAYADA?
[*]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("" + response.msg + "", "导入结果", { 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 层接口 本人接口{根据个人需求定义自己的接口}（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 importWeldingMonthlyProductionPlan(MultipartFile file) throws IOException { System.out.println("MultipartFile::"+file); List 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 dateList = getDayByMonth();//自定义方法得到这个月的所有年月日 //得到导入的数据集 for (int i = 0; i
页:
[1]