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")
为最后一个单元格