foxlog
4/10/2018 - 9:41 AM

用jxls template的方式导出数据 ( POIFSFileSystem.hasPOIFSHeader no method 错误)(Can't load XLS transformer) (Helen math 试卷)(resources, resourcesAsStre

用jxls template的方式导出数据 ( POIFSFileSystem.hasPOIFSHeader no method 错误)(Can't load XLS transformer) (Helen math exam 试卷)(resources, resourcesAsStream classpath) 计算题 math

<project>
    <dependencies>
              <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>

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

        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>1.0.14</version>
        </dependency>

    </dependencies>
</project>
package me.alexcoding.personal;

import me.alexcoding.utils.JxlsUtil;
import me.alexcoding.utils.TimeUtil;
import me.alexcoding.valuevos.Exam;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.util.*;

/**
 * Created by alex on 2018-04-09.
 *
 * 数学试题测试生成, 包含答案, 包含规则.
 *
 */
public class MathTestGenerate {

    public static void main(String[] args) throws Exception{
        MathTestGenerate instance = new MathTestGenerate();

        Map<String, Object> map = new HashMap<>();
        map.put("exam1", instance.test001());
        map.put("exam1uid", TimeUtil.getMDHMSForFile()+"-" + generateRandom(111, 999));
        map.put("exam2", instance.test001());
        map.put("exam2uid", TimeUtil.getMDHMSForFile()+"-" + generateRandom(111, 999));
        map.put("exam3", instance.test001());
        map.put("exam3uid", TimeUtil.getMDHMSForFile()+"-" + generateRandom(111, 999));
        map.put("exam4", instance.test001());
        map.put("exam4uid", TimeUtil.getMDHMSForFile()+"-" + generateRandom(111, 999));
        map.put("exam5", instance.test001());
        map.put("exam5uid", TimeUtil.getMDHMSForFile()+"-" + generateRandom(111, 999));

        JxlsUtil.generateExcelFromResource("/helenExcelTemplate.xls", map, "/tmp/HelenExam4.xls");

    }

    private static String safeParseScript(String script){
        String result = script.replace("×", "*");
        result = result.replace("÷" , "/");
        return result;
    }






    //试卷1 2位数 乘
    //50道题
    private List<Exam>  test001(){

        List<Exam> rtnResult = new ArrayList<>();
        List<String> list1 = generateRule_xx_xx(6);
        List<String> list11 = generateRule_xx_00(4);
        List<String> list2 = generateRule_xx_xx_minus_xx(10);
        List<String> list3 = generateRule_xx_xx_plus_xx(6);
        List<String> list31 = generateRule_xx_x(4);
        List<String> list4 = generateRule_xx_divide_x(6);
        List<String> list41 = generateRule_xxx_minus_xx(4);

        List<String> list5 = generateRule_xxx_divide_x(10);

        List<String> allList = new ArrayList<String>(50);
        allList.addAll(list1);
        allList.addAll(list11);
        allList.addAll(list2);
        allList.addAll(list3);
        allList.addAll(list31);
        allList.addAll(list4);
        allList.addAll(list41);
        allList.addAll(list5);
        Collections.shuffle(allList);
        for(int i =0;i< allList.size();i++){
            Exam exam = new Exam();
            exam.setQuestionone(allList.get(i));
            exam.setQuestiononeAnswer(evalString(safeParseScript(allList.get(i))));

            i++;
            exam.setQuestiontwo(allList.get(i));
            exam.setQuestiontwoAnswer(evalString(safeParseScript(allList.get(i))));

            i++;
            exam.setQuestionthree(allList.get(i));
            exam.setQuestionthreeAnswer(evalString(safeParseScript(allList.get(i))));

            i++;
            exam.setQuestionfour(allList.get(i));
            exam.setQuestionfourAnswer(evalString(safeParseScript(allList.get(i))));

            i++;
            exam.setQuestionfive(allList.get(i));
            exam.setQuestionfiveAnswer(evalString(safeParseScript(allList.get(i))));

            rtnResult.add(exam);


        }


        return rtnResult;

    }

    private static String evalString(String question){
        ScriptEngineManager mgr = new ScriptEngineManager();
        ScriptEngine engine = mgr.getEngineByName("JavaScript");

        try {
            String tmp = String.valueOf(engine.eval(question));
            if(tmp.contains(".")){
                //说明有余数
                return parseMod(question, tmp);
            }else{
                return tmp;
            }
        } catch (ScriptException e) {

            e.printStackTrace();
            System.out.println("错误! " + question);
        }

        return "";
    }

    //解析余数
    // 假设
    public static String parseMod(String question, String strMath){
        String shangResult = strMath.substring(0, strMath.indexOf("."));
        String _question = question.replace("(", "");

        _question = _question.replace(")", "");
        _question = _question.replace(" ", "");
        String firstNumber = _question.split("/")[0];
        String secondNumber = _question.split("/")[1];
        int modResult = Integer.valueOf(firstNumber) % Integer.valueOf(secondNumber);

        return shangResult + ".." + modResult;

    }

    //2位数 * 1位数
    private static List<String> generateRule_xx_x(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(2, 9);

            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //两位数 * 两位数
    private static List<String>  generateRule_xx_xx(int numbers){
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);

            int secondNumber = generateRandom(11, 99);

            //第一个数和第二个数不重复
            if(firstNumber == secondNumber){
                continue;
            }


            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);
    }

    //两位数 * 整数
    private static List<String> generateRule_xx_00(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);

            //第一个树和第二个数不重复
            if(Arrays.asList(_intnumbers).contains(firstNumber)){
                continue;
            }

            int secondNumber = _intnumbers[new Random().nextInt(_intnumbers.length)];

            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //3位数 * 整数
    private static List<String> generateRule_xxx_00(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(111, 999);


            int secondNumber = _intnumbers[new Random().nextInt(_intnumbers.length)];

            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //3位数 * 两位数
    private static List<String> generateRule_xxx_xx(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(111, 999);

            //第一个树和第二个数不重复
//            if(Arrays.asList(_intnumbers).contains(firstNumber)){
//                continue;
//            }

            int secondNumber = generateRandom(11, 99);

            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //3位数 * 1位数
    private static List<String> generateRule_xxx_x(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(111, 999);

            //第一个树和第二个数不重复
//            if(Arrays.asList(_intnumbers).contains(firstNumber)){
//                continue;
//            }

            int secondNumber = generateRandom(2, 9);

            String outResult = String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber);

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //2位数 * 2位数 - 两位数
    private static List<String> generateRule_xx_xx_minus_xx(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(11, 99);

            if(firstNumber == secondNumber ){
                continue;
            }

            //第三个数要求小于前面的两数相乘
            int thirdNumber = generateRandom(11, firstNumber * secondNumber);


            String outResult = "(" +  String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber) + ") - " + Math.min(thirdNumber, generateRandom(222,999));

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }


    //2位数 * 2位数 + 两位数
    private static List<String> generateRule_xx_xx_plus_xx(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(11, 99);

            if(firstNumber == secondNumber ){
                continue;
            }

            //第三个数要求小于前面的两数相乘
            int thirdNumber = generateRandom(11, 99);

            String outResult = "(" +  String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber) + ") + " + thirdNumber;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    // 两位数 + 2位数 * 2位数
    private static List<String> generateRule_xx_plus_xx_xx(int numbers){

        int[] _intnumbers = {10,20,30,40,50,60,70,80,90};
        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(11, 99);

            if(firstNumber == secondNumber ){
                continue;
            }

            //第三个数要求小于前面的两数相乘
            int thirdNumber = generateRandom(11, 99);

            String outResult = thirdNumber + " + (" +  String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber) + ") " ;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    // 3位数 - 2位数 * 2位数
    private static List<String> generateRule_xxx_minus_xx_xx(int numbers){

        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(11, 99);

            if(firstNumber == secondNumber ){
                continue;
            }


            if(firstNumber * secondNumber > 999){
                continue;
            }

            //第三个数要求大于前面的两数相乘
            int thirdNumber = generateRandom(firstNumber * secondNumber, 999);

            String outResult = thirdNumber +   " - (" +  String.format("%-3d", firstNumber) +" × " + String.format("%3d", secondNumber) + ")" ;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    //三位数 - 两位数
    private static List<String> generateRule_xxx_minus_xx(int numbers){

        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(111, 999);


            int secondNumber = generateRandom(22, 99);




            String outResult = String.format("%-3d", firstNumber) +" - " + String.format("%3d", secondNumber) ;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    // 两位数 / 1位数
    private static List<String> generateRule_xx_divide_x(int numbers){

        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(11, 99);


            int secondNumber = generateRandom(2, 9);

            String outResult =    "(" +  String.format("%-3d", firstNumber) +" ÷ " + String.format("%3d", secondNumber) + ")" ;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }

    // 3位数 / 1位数
    private static List<String> generateRule_xxx_divide_x(int numbers){

        HashSet<String> rtnResult=new HashSet<String>();
        while(rtnResult.size() < numbers){
            int firstNumber = generateRandom(111, 999);


            int secondNumber = generateRandom(2, 9);



            String outResult = "(" +  String.format("%-3d", firstNumber) +" ÷ " + String.format("%3d", secondNumber) + ")" ;

            rtnResult.add(outResult);
        }

        return new ArrayList<String>(rtnResult);

    }



    public static int generateRandom(int minimum, int maximum) {
        return minimum + (int) (Math.random() * (maximum-minimum + 1));
    }

}
package me.alexcoding.utils;


import me.alexcoding.valuevos.Employee;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by alex on 2018-04-10.
 */
public class JxlsUtil {
    public static void generateExcel(String templateFile, List<?> data, String destFile) throws Exception{
        try(InputStream is = new FileInputStream(templateFile)) {
            try (OutputStream os = new FileOutputStream(destFile)) {
                Context context = new Context();
                context.putVar("list", data);
                JxlsHelper.getInstance().processTemplate(is, os, context);
            }
        }
    }
    public static void generateExcel(String templateFile, Map<String, ?> data, String destFile) throws Exception{
        try(InputStream is = new FileInputStream(templateFile)) {
            try (OutputStream os = new FileOutputStream(destFile)) {
                Context context = new Context();
                context.putVar("map", data);
                JxlsHelper.getInstance().processTemplate(is, os, context);
            }
        }
    }

    public static void generateExcelFromResource(String templateFile, Map<String, ?> data, String destFile) throws Exception{
        try(InputStream is = JxlsUtil.class.getResourceAsStream(templateFile)) {
            try (OutputStream os = new FileOutputStream(destFile)) {
                Context context = new Context();
                context.putVar("map", data);
                JxlsHelper.getInstance().processTemplate(is, os, context);
            }
        }
    }

    public static void generateExcelFromResource(String templateFile, List<?> data, String destFile) throws Exception{
        try(InputStream is = JxlsUtil.class.getResourceAsStream(templateFile)) {
            try (OutputStream os = new FileOutputStream(destFile)) {
                Context context = new Context();
                context.putVar("map", data);
                JxlsHelper.getInstance().processTemplate(is, os, context);
            }
        }
    }



    public static void main(String[] args) throws Exception {
        List<Employee> list = new ArrayList<Employee>(5);
        list.add(new Employee("Jack", "23"));
        list.add(new Employee("Kate", "33"));
        list.add(new Employee("Grace", "35"));


        generateExcelFromResource("/helenExcelTemplate.xls", list, "target/destfile2.xls");
    }
}

package me.alexcoding.valuevos;

/**
 * Created by alex on 2018-04-10.
 */
public class Exam {
    private String questionone;
    private String questiontwo;
    private String questionthree;
    private String questionfour;
    private String questionfive;

    private String questiononeAnswer;
    private String questiontwoAnswer;
    private String questionthreeAnswer;
    private String questionfourAnswer;
    private String questionfiveAnswer;

    ... get..set...
}

官方文档

see: http://jxls.sourceforge.net/getting_started.html

Getting Started Guide

Let’s assume we have a Java collection of employee objects that we want to output into Excel. The Employee class may look like this

public class Employee {
    private String name;
    private Date birthDate;
    private BigDecimal payment;
    private BigDecimal bonus;
    // ... constructors
    // ... getters/setters
}

To use Jxls to output this object collection into an Excel we need to do the following

  1. Add required Jxls libraries to your project
  2. Create an Excel template using a special markup
  3. Use Jxls API to process the prepared template and fill it with the employee data

Let’s look at each of these steps in detail.

Adding Jxls libraries to the project

The easiest way to add Jxls libraries to your project is to use Maven and specify the required libraries in your project build configuration file.

Jxls jars are available in the Central Maven repository.

We need to add the following dependency to core Jxls module

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

Alternatively you can download Jxls distribution from the Sourceforge site and use the jars from the distribution.

Besides the dependency to core Jxls module we need to add a dependency to an implementation of Jxls transformer engine which will execute all the underlying Java to Excel manipulations.

As it is explained in Transformers section (see Main Concepts)) Jxls core module does not depend on any specific Java-Excel library and works with Excel exclusively through a predefined interface. Currently Jxls supplies two implementations of this interface in separate modules based on the well-known Apache POI and Java Excel API libraries.

To use Apache POI API based transformer implementation add the following dependency

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>1.0.14</version>
</dependency>

To use Java Excel API based transformer implementation add the following dependency

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>1.0.6</version>
</dependency>

Creating Excel template

A template is an excel file which uses a special markup to specify how Jxls should output the data.

Jxls provides some built-in markup processors which can be used to parse an excel template and extract control commands.

A custom markup processor can be created if needed. So one define his own markup for an excel template and parse in a proper way to create Jxls Commands structure.

Let’s look at the built-in Jxls markup processors.

By default Jxls supports Apache JEXL as an expression language that can be used in an excel template to refer to java object properties and methods. The object must be available in Jxls context under a certain key. To output the employee name in a cell we can put the following text in the cell ${employee.name}. Basically we just surrounded Jexl expression with ${ and }. We assume that in the context there is an Employee object under the employee key.

The property notation is configurable so you may decide to use for example [[employee.name]] as a property notation. See Expression Language for more details on how to do it.

The final template for the example to output a list of Employee objects can be downloaded here and looks like this

In the template cells in row 4 we refer to the employee object properties using JEXL expressions as described above.

Cell A1 contains an excel comment with the following text jx:area(lastCell="D4"). It defines the root area of our template to be A1:D4.

A comment to A4 cell defines Jxls Each-Command with the following comment text jx:each(items="employees" var="employee" lastCell="D4"). The Each-Commandwill iterate the collection of objects under employees key in Jxls context and place each individual collection item into the context under employee key (defined by varattribute). The body area of the Each-Command is A4:D4 (defined by the lastCell attribute) and it will be cloned and processed with each new Employee object in the context.

This example assumes usage of XlsCommentAreaBuilder class to construct Jxls areas from the template. By using this class you can define Jxls commands in Excel cell comments. If you prefer to define the commands in Java code then the template will be the same except you have to remove the comments from the cells.

Use Jxls API to process the template

Here you can see how to use Jxls API to process the excel template

...
    logger.info("Running Object Collection demo");
    List<Employee> employees = generateSampleEmployeeData();
    try(InputStream is = ObjectCollectionDemo.class.getResourceAsStream("object_collection_template.xls")) {
        try (OutputStream os = new FileOutputStream("target/object_collection_output.xls")) {
            Context context = new Context();
            context.putVar("employees", employees);
            JxlsHelper.getInstance().processTemplate(is, os, context);
        }
    }
...

In this example we are loading the template from the classpath resource object_collection_template.xls. And the target excel file will be written to target/object_collection_output.xls.

All the main processing is performed in a single line

    JxlsHelper.getInstance().processTemplate(is, os, context);

By default JxlsHelper assumes that you want to override the template sheet with the data.

But you may also choose to generate the data at another sheet by using the following method

JxlsHelper.getInstance().processTemplateAtCell(is, os, context, "Result!A1");

Here the area will be processed at cell A1 of Result sheet.

The final report can be downloaded here and looks like this