给excel中添加列和值

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

提示：以下是本篇文章正文内容，下面案例可供参考

package com.yl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class easyExcel {
    public static void main(String[] args) {
        String path = "D:\\test.xlsx";
        //加载数据，从表头开始加载
        List<Object> loadData = loadData(path);
        //解析数据，处理表头，增加三列
        List<List<String>> tableHead = dealHeadData(loadData);
        //处理增加的三列的对应的值
        List<List<String>> tableData = dealTableData(loadData);
        //写入一个新excel表，完整路径: D:\\test-xx.xlsx
        writeData(tableData, tableHead);

    }


    private static void writeData(List<List<String>> loadData, List<List<String>> headData) {
        EasyExcelFactory.write("D:\\test-1.xlsx")
                .excelType(ExcelTypeEnum.XLSX)
                .head(headData)
                .sheet("测试")
                .doWrite(loadData);
    }


    private static List<List<String>> dealTableData(List<Object> loadData) {
        List<List<String>> tableData = new LinkedList<>();

        //从1开始获取数据，拿到真正的表数据
        for (int i = 1; i < loadData.size(); i++) {
            Map<Integer, String> o = (Map<Integer, String>) loadData.get(i);
            List<String> data = new LinkedList<>();
            for (Integer key : o.keySet()) {
                data.add(o.get(key));
            }
            tableData.add(data);
        }

        //先给平均公式列计算值
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd hh:mm");

        for (int i = 0; i < tableData.size(); i++) {
            List<String> strings = tableData.get(i);
            try {
                String name = strings.get(0);

                //拼接字符串，转换成时间正则表达式
                String s1 = strings.get(1);
                String s2 = strings.get(2);
                String s3 = strings.get(3);
                String ss2 = s1 + " " + s2;
                String ss3 = s1 + " " + s3;

                //拿到上班和下班时间的毫秒值，然后相减
                long time2 = dateFormat.parse(ss2).getTime();
                long time3 = dateFormat.parse(ss3).getTime();
                Double f = new Double(time3 - time2);

                // 毫秒值除以1000，再除以3600，拿到小时
                BigDecimal bd = new BigDecimal(f / 3600 / 1000);

                //四舍五入
                double value = bd.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                Double lastValue = value - 1.5;

                System.out.println(name + ":" + lastValue);

                //转换成String类型，添加
                strings.add(String.valueOf(lastValue));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        //再给X组赋值
        try {
            InputStream is = new BufferedInputStream(new FileInputStream("src/main/resources/interactive.properties"));
            BufferedReader bf = new BufferedReader(new InputStreamReader(is, "UTF-8"));

            Properties properties = new Properties();
            properties.load(bf);
            Set<String> set = properties.stringPropertyNames();
            for (String key : set) {
                for (int i = 0; i < tableData.size(); i++) {
                    List<String> strings = tableData.get(i);
                    if (key.equals(strings.get(0))) {
                        strings.add(0, (String) properties.get(key));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.println(tableData);
        System.out.println("处理完毕，请查看我的电脑D:\\test-xx.xlsx");
        return tableData;
    }


    private static List<List<String>> dealHeadData(List<Object> loadData) {
        Map<Integer, String> o = (Map<Integer, String>) loadData.get(0);
        List<List<String>> tableHead = new LinkedList<>();

        for (Integer key : o.keySet()) {
            for (int i = key; i <= key; i++) {
                List<String> head = new LinkedList<>();
                head.add(o.get(key));
                tableHead.add(head);
            }
        }

        try {
            tableHead.add(0, new LinkedList<>(Collections.singleton("XX1")));
            tableHead.add(new LinkedList<>(Collections.singleton("XX2")));
            System.out.println("增加了两列，分别为：XX1，XX2");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return tableHead;

    }


    private static List<Object> loadData(String path) {
        List<Object> list = new LinkedList<>();
        EasyExcel.read(path)
                .sheet(0)
                .headRowNumber(0)
                .registerReadListener(new AnalysisEventListener() {
                    @Override
                    public void invoke(Object o, AnalysisContext analysisContext) {
                        list.add(o);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("数据读取完毕，下一步：处理表头，增加三列");
                    }
                }).doRead();

        return list;
    }
}


---

# 总结
遇到的问题：
1、POI无法处理时间类型
2、easyExcel的方法不会用
3、Excel存放的数据类型是List<List<String>>，按照这个格式去处理数据就没问题
4、计算时间差，String类型转换成yyyy/MM/dd hh:mm，再转换毫秒值计算
5、数据量比较大的情况下，通过拿取properties文件的value值，再去赋值处理

依赖导入：（阿里easyExcel处理excel的神器）
<dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
 </dependencies>