使用net.sf.jxls下的jxls-core包进行复杂的Excel导出

此文章用于使用net.sf.jxls下jxls-core包的使用说明：

        jxis-core1.0.6是一款java语言的开源库，用于实现将Excel模板与数据进行绑定的功能。然而，jxls-core1.0.6并不支持poi4.1.2版本,poi4.1.2是一款Java语言的开源库，用于处理Microsoft Ofice格式的文件，包括Excel、Word、PowerPoint等。它是Apache POl项目的最新版本，提供了更多的功能和改进。由于poi4.1.2版本与jxs-core1.0.6库的兼容性不好，因此在使用时需要注意版本的匹配。如果要使用poi4.1.2版本的库，需要使用jxls-core的更高版本，或者考虑使用其他类似的Excel处理库。另外，如果需要使用jxls-core1.0.6，建议使用poi3.9或更低版本的库。这需要在项目的依赖管理中进行调整和配置，以确保各个库的版本兼容性。在实际开发中这种版本兼容性问题是比较普遍的，需要开发者谨慎处理，以免出现问题。

下篇文章将写明怎么去做到兼容poi4.1.2版本

我们可以看到JXLS Core这个官方老六已经10年没更新了，所以只能使用poi3.9去进行兼容。

然鹅呢，POI的版本一直是在升级维护的

一般来说版本不一致就会出现以下报错，或出现数据写入失败的情况

这个报错呢，一般就是你的POI版本太高了，通过阅读源码就可以知道

这是poi 3.x的版本下的Cell

这是poi 4.x的版本

很明显的区别吧，所以自己检查下版本

---

不扯这么多了，首先先上怎么去使用net.sf.jxls的代码：

核心的生成代码就是在JxlsUtils类里面的这个方法

public static void exportTemplate(String fileName, InputStream inputStream, OutputStream outputStream, Map<String, Object> contextMap) {
   try {
        //此处就是调用net.sf.jxls下的API进行插入数据了
      XLSTransformer transformer = new XLSTransformer();
      Workbook workbook = transformer.transformXLS(inputStream, contextMap);
      workbook.write(outputStream);
      outputStream.flush();
      outputStream.close();
   } catch (Exception var24) {
      var24.printStackTrace();
   } finally {
      try {
         if (inputStream != null) {
            inputStream.close();
         }

         if (outputStream != null) {
            outputStream.close();
         }
      } catch (IOException var23) {
      }

   }

}

excelDemo内容截图

这个的语法与原生的JXLS是有点区别的，注意区分！！

一：pom.xml文件内容

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls</artifactId>
			<version>2.4.6</version>
		</dependency>

		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-poi</artifactId>
			<version>1.0.15</version>
		</dependency>
		<dependency>
			<groupId>net.sf.jxls</groupId>
			<artifactId>jxls-core</artifactId>
			<version>1.0.5</version>
		</dependency>
	</dependencies>

二：JxlsUtils代码

package common.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import org.jxls.common.Context;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;

/**
 * 
 * @author klguang
 *
 */
public class JxlsUtils {

	public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
		Context context = new Context();
		if (model != null) {
			for (String key : model.keySet()) {
				context.putVar(key, model.get(key));
			}
		}
		JxlsHelper jxlsHelper = JxlsHelper.getInstance();
		Transformer transformer = jxlsHelper.createTransformer(is, os);
		jxlsHelper.processTemplate(context, transformer);
	}
	public static void exportTemplate(String fileName, InputStream inputStream, OutputStream outputStream, Map<String, Object> contextMap) {
		try {
			XLSTransformer transformer = new XLSTransformer();
			Workbook workbook = transformer.transformXLS(inputStream, contextMap);
			workbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (Exception var24) {
			var24.printStackTrace();
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}

				if (outputStream != null) {
					outputStream.close();
				}
			} catch (IOException var23) {
			}

		}

	}

	// 日期格式化
	public String dateFmt(Date date, String fmt) {
		if (date == null) {
			return "";
		}
		try {
			SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
			return dateFmt.format(date);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

	// if判断
	public Object ifelse(boolean b, Object o1, Object o2) {
		return b ? o1 : o2;
	}

}

三：ObjectCollectionDemoXlsx代码

package jxls.demo.guide;

import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import common.utils.JxlsUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * Object collection output demo
 *
 * @author jcl
 */
public class ObjectCollectionDemoXlsxNSheet {
    static Logger logger = LoggerFactory.getLogger(ObjectCollectionDemoXlsxNSheet.class);

    public static void main(String[] args) throws ParseException, IOException {
        logger.info("Running Object Collection demo");

        JSONObject entries = getJsonString();
        Map model = JSONUtil.toBean(entries.toString(), Map.class);
        OutputStream os = new FileOutputStream("target/object_collection_templateTestNSheet4.xlsx");
        InputStream inputStream = ObjectCollectionDemoXlsxNSheet.class.getClassLoader()
                .getResourceAsStream("jxls-template/object_collection_templateNSheet.xlsx");
        JxlsUtils.exportTemplate("来了老弟", inputStream, os, model);
//        JxlsUtils.exportExcel(inputStream, os, model);
        os.close();
    }

    public static JSONObject getJsonString() throws ParseException {
        String jsonData = "{\n" +
                "\t\"title\": \"来了？\",\n" +
                "\t\"reportDate\": \"2024-06-25\",\n" +
                "\t\"createDate\": \"2024-06-25\",\n" +
                "\t\"firstData\": {\n" +
                "\t\t\"countHeadList\": [\n" +
                "\t\t\t\"test11\",\n" +
                "\t\t\t\"test22\",\n" +
                "\t\t\t\"test33\"\n" +
                "\t\t],\n" +
                "\t\t\"groupTotalCount\": \"1\",\n" +
                "\t\t\"personTotalCount\": \"2\",\n" +
                "\t\t\"fac\": \"3\",\n" +
                "\t\t\"firstExcelData\": [\n" +
                "\t\t\t{\n" +
                "\t\t\t\t\"loginName\": \"张三\",\n" +
                "\t\t\t\t\"userName\": \"张三2\",\n" +
                "\t\t\t\t\"groupCount\": \"4\",\n" +
                "\t\t\t\t\"personCount\": \"5\",\n" +
                "\t\t\t\t\"amount\": \"6\",\n" +
                "\t\t\t\t\"addCountList\": [\n" +
                "\t\t\t\t\t10,\n" +
                "\t\t\t\t\t20,\n" +
                "\t\t\t\t\t30\n" +
                "\t\t\t\t]\n" +
                "\t\t\t},\n" +
                "\t\t\t{\n" +
                "\t\t\t\t\"loginName\": \"李四\",\n" +
                "\t\t\t\t\"userName\": \"李四2\",\n" +
                "\t\t\t\t\"groupCount\": \"7\",\n" +
                "\t\t\t\t\"personCount\": \"8\",\n" +
                "\t\t\t\t\"amount\": \"9\",\n" +
                "\t\t\t\t\"addCountList\": [\n" +
                "\t\t\t\t\t11,\n" +
                "\t\t\t\t\t21,\n" +
                "\t\t\t\t\t31\n" +
                "\t\t\t\t]\n" +
                "\t\t\t}\n" +
                "\t\t]\n" +
                "\t},\n" +
                "\t\"secondData\": {\n" +
                "\t\t\"countHeadList\": [\n" +
                "\t\t\t\"test44\",\n" +
                "\t\t\t\"test55\",\n" +
                "\t\t\t\"test66\"\n" +
                "\t\t],\n" +
                "\t\t\"groupTotalCount\": \"1\",\n" +
                "\t\t\"personTotalCount\": \"2\",\n" +
                "\t\t\"fac\": \"3\",\n" +
                "\t\t\"secondExcelData\": [\n" +
                "\t\t\t{\n" +
                "\t\t\t\t\"loginName\": \"张三66\",\n" +
                "\t\t\t\t\"userName\": \"张三266\",\n" +
                "\t\t\t\t\"groupCount\": \"4\",\n" +
                "\t\t\t\t\"personCount\": \"5\",\n" +
                "\t\t\t\t\"amount\": \"6\",\n" +
                "\t\t\t\t\"addCountList\": [\n" +
                "\t\t\t\t\t10,\n" +
                "\t\t\t\t\t20,\n" +
                "\t\t\t\t\t30\n" +
                "\t\t\t\t]\n" +
                "\t\t\t},\n" +
                "\t\t\t{\n" +
                "\t\t\t\t\"loginName\": \"李四666\",\n" +
                "\t\t\t\t\"userName\": \"李四266\",\n" +
                "\t\t\t\t\"groupCount\": \"7\",\n" +
                "\t\t\t\t\"personCount\": \"8\",\n" +
                "\t\t\t\t\"amount\": \"9\",\n" +
                "\t\t\t\t\"addCountList\": [\n" +
                "\t\t\t\t\t11,\n" +
                "\t\t\t\t\t21,\n" +
                "\t\t\t\t\t31\n" +
                "\t\t\t\t]\n" +
                "\t\t\t}\n" +
                "\t\t]\n" +
                "\t}\n" +
                "}";

        //jsonData转成jsonObject类型
        JSONObject entries = JSONUtil.parseObj(jsonData);
        return entries;
    }
}

四：Employee代码

package jxls.demo.guide;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @author jcl
 */
public class Employee {
    private String index;
    private Date birthDate;
    private BigDecimal payment;
    private BigDecimal bonus;
    private String passportNo;

    public Employee(String index, Date birthDate, BigDecimal payment, BigDecimal bonus) {
        this.index = index;
        this.birthDate = birthDate;
        this.payment = payment;
        this.bonus = bonus;
    }

    public Employee(String index, Date birthDate, double payment, double bonus) {
        this(index, birthDate, new BigDecimal(payment), new BigDecimal(bonus));
    }
    public Employee(String index, Date birthDate, BigDecimal payment, BigDecimal bonus, String passportNo) {
        this.index = index;
        this.birthDate = birthDate;
        this.payment = payment;
        this.bonus = bonus;
        this.passportNo = passportNo;
    }

    public Employee(String index, Date birthDate, double payment, double bonus,String passportNo) {
        this(index, birthDate, new BigDecimal(payment), new BigDecimal(bonus),passportNo);
    }

    public String getPassportNo() {
        return passportNo;
    }

    public void setPassportNo(String passportNo) {
        this.passportNo = passportNo;
    }

    public String getIndex() {
        return index;
    }

    public void setIndex(String index) {
        this.index = index;
    }

    public Date getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }

    public BigDecimal getPayment() {
        return payment;
    }

    public void setPayment(BigDecimal payment) {
        this.payment = payment;
    }

    public BigDecimal getBonus() {
        return bonus;
    }

    public void setBonus(BigDecimal bonus) {
        this.bonus = bonus;
    }
}