赞
踩
我带领的是xlsx表导入,如果是后者去下面这个大神家找灵感!
java读取excel中的数据(包括.xlsx和.xls)_java 读取 xls和xlxs内容-CSDN博客
这三张表里面我只要所有绿色标注的数据
导入依赖:
- <!-- excel工具 -->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>4.1.2</version>
- </dependency>
- @PostMapping("/importData")
- @ResponseBody
- public AjaxResult importData(MultipartFile file) throws Exception {
-
- // 将MultipartFile转换为File对象
- File excelFile = File.createTempFile(file.getOriginalFilename(), ".xlsx");
- file.transferTo(excelFile);
- XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(excelFile));
-
- int sheetNum = xssfWorkbook.getNumberOfSheets();
- List<Object> shuju = new ArrayList<>();
- // 使用计数器变量记录行号
- int counter = 1;
- for (int i = 0; i < sheetNum; i++) {
- System.out.println("读取第" + (i+1) + "个sheet");
- int sysid=i+2;
- System.out.println("这是id为"+(sysid)+"的数据");
- counter = 1; // 重置计数器为1
- XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
- //获取几行数据
- int maxRow = sheet.getLastRowNum();
-
- for (int row = 3; row <= maxRow; row += 2) {
- XSSFRow xssfRow = sheet.getRow(row);
- if (xssfRow == null) {
- continue;
- }
-
- System.out.println("--------第" + (row + 1) + "行的数据如下"+"--------");
- //获取几列数据
- int maxCol = xssfRow.getLastCellNum();
- System.out.println(counter+"月:");
- for (int col = 1; col < maxCol; col++) {
- XSSFCell cell = xssfRow.getCell(col);
- //如果cell数据为null则跳过
- if (cell == null) {
- continue;
- }
- DataFormatter dataFormatter = new DataFormatter();
- String cellValue = dataFormatter.formatCellValue(cell);
- System.out.print(cellValue + " ");
- //添加数据
- if(!StrUtil.isEmpty(cellValue)){
- shuju.add(cellValue);
- }
- }
- klDeviceYearService.daoru(sysid , counter ,shuju);
- System.out.println();
- counter++;
- shuju.clear();
- }
-
- }
-
- return AjaxResult.success(null);
- }
- public int daoru(Integer sysid,Integer counter,List<Object> shuju) {
- //获取年
- int year = Calendar.getInstance().get(Calendar.YEAR);
-
- //日期初始值
- int dayOfMonth = 1;
- for (Object data : shuju) {
- String zhi = data.toString();
- //获取当月最大天数
- int maxDayOfMonth=getDayOfMonth(year,counter);
- int daoru = klDeviceYearMapper.daoru(sysid, counter,dayOfMonth, zhi); // 使用获取到的日期号
- dayOfMonth++;
- //判断日期是否到达最后一天
- if (dayOfMonth>maxDayOfMonth){
- dayOfMonth=1;
- }
-
- }
- //去除小数点
- return klDeviceYearMapper.qingchudian();
-
- }
-
- /**
- * 根据月来确定当月的总天数
- * @param year
- * @param month
- * @return
- */
- public int getDayOfMonth(int year, int month) {
- YearMonth yearMonth = YearMonth.of(year, month);
- return yearMonth.lengthOfMonth();
- }
- /**
- * 数据库修改
- * @param sysid
- * @param counter
- * @param dayOfMonth
- * @param zhi
- * @return
- */
- @Insert("UPDATE kl_device_year SET value=#{zhi} WHERE sys_id=#{sysid} and month=#{counter} and day=#{dayOfMonth}")
- Integer daoru(@Param(value = "sysid") Integer sysid, @Param(value = "counter") Integer counter,
- @Param(value = "dayOfMonth")Integer dayOfMonth, @Param(value = "zhi") String zhi);
-
- /**
- * 清除小数点
- * @return
- */
- @Update("UPDATE kl_device_year SET value =REPLACE(value,'.','');")
- Integer qingchudian();
注意:建议从后往前看,因为我的命名不太规范,如果看懵;去除小数点工作可以不做
counter 代表我的月份显示,根据Excel表格看出一行正好是一个月的
dayOfMonth 代表我每天的数据,根据Excel表格可以知道每个数据时一天的
所有参数都是在“控制层”做好配置然后往后传
直接在一个文件里面完成
- package com.ruoyi.web.controller.Excel;
-
- import cn.hutool.core.util.StrUtil;
-
- import org.apache.poi.ss.usermodel.DataFormatter;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
-
-
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- import java.time.YearMonth;
- import java.util.ArrayList;
- import java.util.Calendar;
- import java.util.List;
-
- public class excle{
-
-
- public static void main(String[] args) {
-
- try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\Administrator\\Desktop\\河北.xlsx"))) {
-
- int sheetNum = xssfWorkbook.getNumberOfSheets();
- List<Object> list = new ArrayList<>();
- // 使用计数器变量记录行号
- int mounth = 1;
- for (int i = 0; i < sheetNum; i++) {
- System.out.println("读取第" + (i + 1) + "个sheet");
- mounth = 1; // 重置计数器为1
- XSSFSheet sheet = xssfWorkbook.getSheetAt(i);
- //获取几行数据
- int maxRow = sheet.getLastRowNum();
-
- for (int row = 3; row <= maxRow; row += 2) {
- XSSFRow xssfRow = sheet.getRow(row);
- if (xssfRow == null) {
- continue;
- }
-
- System.out.println("--------第" + (row + 1) + "行的数据如下"+"--------");
- //获取几列数据
- int maxCol = xssfRow.getLastCellNum();
- System.out.println(mounth+"月:");
- for (int col = 1; col < maxCol; col++) {
- XSSFCell cell = xssfRow.getCell(col);
- //如果cell数据为null则跳过
- if (cell == null) {
- continue;
- }
-
- DataFormatter dataFormatter = new DataFormatter();
- String cellValue = dataFormatter.formatCellValue(cell);
- System.out.print(cellValue + " ");
- //排除cellValue里面的空值
- if (!StrUtil.isEmpty(cellValue)) {
- list.add(cellValue);
- }
- }
- //添加方法
- jia(mounth, list);
-
- System.out.println();
- mounth++;
- list.clear();
- }
- System.out.println("添加完成!请去数据库表中核对信息");
- }
-
- } catch (IOException e) {
- e.printStackTrace();
- }
-
- }
-
- /**
- * 添加
- */
- public static void jia(Integer mounth ,List<Object> shuju){
- //获取年
- int year = Calendar.getInstance().get(Calendar.YEAR);
- //天数初始化
- int day=1;
- Connection conn = null;
- PreparedStatement statement=null;
- try {
- //建立数据库连接
- conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/zzy_moni_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&&useSSL=false&serverTimezone=GMT%2B8","Li","123456");
- //设置自动提交为false(事务手动控制)
- conn.setAutoCommit(false);
- //sql
- String sql="UPDATE kl_device_year SET value=? WHERE sys_id=? and month=? and day=?";
- //创建对象,设置参数
- statement=conn.prepareStatement(sql);
- for (Object data:shuju){
- String zhi = data.toString();
- //获取最大天数
- int maxDay = getDayOfMonth(year, mounth);
- //设置sql参数
- statement.setString(1,zhi);
- statement.setInt(2,2);
- statement.setInt(3,mounth);
- statement.setInt(4,day);
-
- //执行sql
- statement.executeUpdate();
- day++;
- //判断日期是否最大值最后一天
- if (day>maxDay){
- day=1;
- }
- }
- //提交事物
- conn.commit();
- } catch (SQLException e) {
- e.printStackTrace();
- //出现异常回滚
- try {
- if (conn!=null){
- conn.rollback();
- }
- }catch (SQLException ex){
- ex.printStackTrace();
- }
- }finally {
- //关闭数据库
- try {
- if (statement!=null||conn!=null){
- statement.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
-
- }
-
-
- /**
- * 日期换算
- */
- public static int getDayOfMonth(int year, int month) {
- YearMonth yearMonth = YearMonth.of(year, month);
- return yearMonth.lengthOfMonth();
- }
-
-
-
- }
注意:面对不同的项目直接修改数据库配置,然后修改sql条件,main方法修改文件名称运行即可。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。