Excel导入读取数据和导出数据为指定Excel文件

1.需要先导入Pom.xml依赖

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.10.0</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>2.10.0</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>1.0.9</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-reader</artifactId>
    <version>2.0.6</version>
</dependency>
<dependency>
    <groupId>org.nutz</groupId>
    <artifactId>nutz</artifactId>
    <version>1.r.59</version>
    <classifier>json</classifier>
</dependency>

<!--工具包-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.8</version>
</dependency>

<dependency>
    <groupId>com.deepoove</groupId>
    <artifactId>poi-tl</artifactId>
    <version>1.8.2</version>
</dependency>

2.工具类

package org.jeecg.modules.util;

import cn.hutool.core.util.ReUtil;
import org.apache.commons.beanutils.ConvertUtilsBean;
import org.apache.logging.log4j.util.Strings;
import org.jxls.common.Context;
import org.jxls.reader.*;
import org.jxls.util.JxlsHelper;
import org.nutz.json.Json;
import org.nutz.lang.Files;
import org.nutz.lang.Lang;
import org.nutz.lang.Streams;
import org.nutz.log.Log;
import org.nutz.log.Logs;

import java.io.*;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ProjectName: equipment project
 * @Package: org.boyi.petrochina.util
 * @ClassName: ExcelUtil
 * @Description: java类作用描述
 * @UpdateUser: 更新者
 * @UpdateRemark: 更新说明
 * @Version: 1.0
 */
public class ExcelUtil {
    private static Log log = Logs.get();

    public static <T> void getDatasFromExcel(List<T> datas, String beanName, String configFile, File file) {
        try {
            InputStream inputXML = Streams.fileIn(configFile);
            ReaderConfig.getInstance().setSkipErrors(true);
            XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
            ConvertUtilsBean convertUtilsBean = mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean();
            convertUtilsBean.deregister(Date.class);
            convertUtilsBean.register(new AllDateConverter(), Date.class);

            InputStream inputXLS = new BufferedInputStream(new FileInputStream(file));
            Map<String, Object> beans = new HashMap<String, Object>();
            beans.put(beanName, datas);
            XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
            if (readStatus.isStatusOK()) {
                System.out.println("jxls读取Excel成功！");
            }
            List<XLSReadMessage> readMsgs = readStatus.getReadMessages();
            log.debug(Json.toJson(readMsgs));
            for (XLSReadMessage readMsg : readMsgs) {
                String msg = readMsg.getMessage();
                Exception ex = readMsg.getException();
                if ((ex != null) && (ex.getMessage().contains("No value specified"))) {
                    continue;
                }
                throw Lang.makeThrow(msg);
            }
            log.debug("共导入：" + datas.size());
        } catch (Exception e) {
            e.printStackTrace();
            throw Lang.makeThrow(translateErrMsg(e.getMessage()));
        }
    }

    private static String translateErrMsg(String msg) {
        String result = msg;
        String errTemplate = "Can't read cell (\\S+) on Sheet";
        String core = ReUtil.get(errTemplate, msg, 1);
        if (!Strings.isBlank(core)) {
            result = String.format("不能读取%s的值，格式可能错误！", core);
        }
        return result;
    }

    public static <T> void exportDatasToExcle(Object datas, String outFile, String tempFile) throws IOException, InterruptedException {
        InputStream is = Files.findFileAsStream(tempFile);
        OutputStream os = new FileOutputStream(outFile);
        Context context = new Context();
        context.putVar("datas", datas);
        JxlsHelper.getInstance().processTemplate(is, os, context);
        is.close();
        os.close();
        os.flush();
        Thread.sleep(5000);
    }

    public static <T> void exportDatasToExcle(Object header,Object datas, String outFile, String tempFile) throws IOException, InterruptedException {
        InputStream is = Files.findFileAsStream(tempFile);
        OutputStream os = new FileOutputStream(outFile);
        Context context = new Context();
        context.putVar("header", header);
        context.putVar("datas", datas);
        JxlsHelper.getInstance().setProcessFormulas(true).processTemplate(is, os, context);
        is.close();
        os.close();
        os.flush();
        Thread.sleep(5000);
    }
}

3.工具类

package org.jeecg.modules.util;

import cn.hutool.core.util.NumberUtil;
import org.apache.commons.beanutils.ConversionException;
import org.apache.commons.beanutils.Converter;
import org.apache.poi.ss.usermodel.DateUtil;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ProjectName: equipment project
 * @Package: org.boyi.petrochina.util
 * @ClassName: AllDateConverter
 * @Description: java类作用描述
 * @UpdateUser: 更新者
 * @UpdateRemark: 更新说明
 * @Version: 1.0
 */
public class AllDateConverter implements Converter {
    public AllDateConverter() {
    }

    @Override
    public Object convert(Class type, Object value) {
        if (value == null) {
            throw new ConversionException("No value specified");
        } else {
            double date;
            if (value instanceof Double) {
                date = (Double) value;
            } else if (value instanceof Number) {
                date = ((Number) value).doubleValue();
            } else {
                if (!(value instanceof String)) {
                    if (value instanceof Date) {
                        return value;
                    }

                    throw new ConversionException("No value specified");
                }

                String t = (String) value;
                if (NumberUtil.isInteger(t)) {
                    //如果是数字 小于0则 返回
                    BigDecimal bd = new BigDecimal(t);
                    //天数
                    int days = bd.intValue();
                    int mills = (int) Math.round(bd.subtract(new BigDecimal(days)).doubleValue() * 24 * 3600);

                    //获取时间
                    Calendar c = Calendar.getInstance();
                    c.set(1900, 0, 1);
                    c.add(Calendar.DATE, days - 2);
                    int hour = mills / 3600;
                    int minute = (mills - hour * 3600) / 60;
                    int second = mills - hour * 3600 - minute * 60;
                    c.set(Calendar.HOUR_OF_DAY, hour);
                    c.set(Calendar.MINUTE, minute);
                    c.set(Calendar.SECOND, second);

                    Date d = c.getTime();
                    return d;
                }
                t = t.replaceAll("\\.", "-").replaceAll("/", "-");
                String[] t0 = t.split("-");
                if (t0.length < 3) {
                    t = t + "-01";
                }
                Date d = cn.hutool.core.date.DateUtil.parseDate(t);
                System.out.println(d);
                return d;
            }

            return DateUtil.getJavaDate(date);
        }
    }

    private boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("[0-9]+\\.*[0-9]*");
        Matcher isNum = pattern.matcher(str);
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }
}

注意:

        1.导出时，需要在Excel中使用批注配合，注意批注作用的单元格的范围

        2.如果只有单个则不需要

        3.Administrator:
jx:area(lastCell="E14")
为最后一个单元格