I see the source code in addImage ClientAnchor anchor = helper.createClientAnchor(); it can setDx1,setDy1,setDx2,setDy2;maybe we can develop this function for the image;

I have created same conditional formatting rules in the template sheet with a multisheet comment. It does not copy this rules when creating the new sheets. I attach examples files.

Version is 2.8.1

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

when Excel print preview, the picture moves downwards or upwards and cannot be displayed accurately in the table

Hi,

I am trying to use dependent queries with jxls2 as in http://jxls.sourceforge.net/1.x/samples/reportsample.html, Dependent Queries

The example uses jxls1 syntax so I translate it to jxls2 syntax.

So I translate the following:
(jxls1)
<jx:forEach items="${rm.exec('SELECT name, payment, bonus FROM employee e where e.depid = ' + dep.id)}" var="employee">

to jxls2:
jx:each(items="jdbc.query('SELECT name, payment, bonus FROM employee e where e.depid = ' + dep.id)" var="employee" lastCell="B3")

and everything works as expected. So dependent queries work with this syntax.

But if I change this:
jx:each(items="jdbc.query('SELECT name, payment, bonus FROM employee e where e.depid = ' + dep.id)" var="employee" lastCell="B3")

to this:
jx:each(items="jdbc.query('SELECT name, payment, bonus FROM employee e where e.depid = ?', dep.id)" var="employee" lastCell="B3")

no data is being printed. Are jxs2 dependent queries not supporting the "?" syntax for parameters ?

Regards,
Eduardo

I've used jxls version 2.6.0 to stream data to excel xlsx file. The problem is GC overhead limit exceeded as below:

nested exception is java.lang.OutOfMemoryError: GC overhead limit exceeded] with root cause
java.lang.OutOfMemoryError: GC overhead limit exceeded 
        at java.util.Arrays.copyOf(Arrays.java:3181) ~[?:1.8.0_265]
        at java.util.ArrayList.grow(ArrayList.java:267) ~[?:1.8.0_265]
        at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:241) ~[?:1.8.0_265]
        at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:233) ~[?:1.8.0_265]
        at java.util.ArrayList.add(ArrayList.java:464) ~[?:1.8.0_265]
        at org.apache.xmlbeans.impl.store.Xobj.find_all_element_users(Xobj.java:2098) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTColsImpl.getColArray(Unknown Source) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.getColumn1Based(ColumnHelper.java:187) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.getOrCreateColumn1Based(ColumnHelper.java:285) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.apache.poi.xssf.usermodel.helpers.ColumnHelper.setColWidth(ColumnHelper.java:271) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.apache.poi.xssf.usermodel.XSSFSheet.setColumnWidth(XSSFSheet.java:2559) ~[spring-batch-excel-0.5.4.jar!/:?]
        at org.jxls.transform.poi.PoiTransformer.transformCell(PoiTransformer.java:190) ~[jxls-poi-1.2.0.jar!/:?]
        at org.jxls.transform.poi.PoiTransformer.transform(PoiTransformer.java:172) ~[jxls-poi-1.2.0.jar!/:?]
        at org.jxls.area.XlsArea.transformTopStaticArea(XlsArea.java:393) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.area.XlsArea.applyAt(XlsArea.java:159) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.command.EachCommand.processCollection(EachCommand.java:309) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.command.EachCommand.applyAt(EachCommand.java:265) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.area.XlsArea.applyAt(XlsArea.java:171) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.command.EachCommand.processCollection(EachCommand.java:309) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.command.EachCommand.applyAt(EachCommand.java:265) ~[jxls-2.6.0.jar!/:?]
        at org.jxls.area.XlsArea.applyAt(XlsArea.java:171) ~[jxls-2.6.0.jar!/:?]

My question is the problem cause by jxls or not? Any one can help me to resolve it?
Thanks a lot.

Hi,

in this example:
https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java
, method: simpleSxssf()

I am trying to support grouping similar to the jxls1 grouping feature.
So I changed the example to print the age and set age to 30 or 40:

public static Employee generateOne(String nameSuffix, int counter){
     return new Employee("Employee " + nameSuffix, (counter % 2 == 0? 30: 40 ), 1000 + random.nextDouble()*5000, random.nextInt(100)/100.0d, new Date(current - (1000000 + random.nextInt(1000000))));
}

public static List<Employee> generate(int num){
     List<Employee> result = new ArrayList<Employee>();
     int counter = 0;
     for(int index = 0; index < num; index++){
         result.add( generateOne("" + index, counter) );
         counter++;
     }
     return result;
}

In my template (attached), you see that the total sums are being calculated with a workaround:
=SUMMEWENN(A:A;"Employee*";E:E)

In english:
=SUMIF(A:A,"Employee*",E:E)

But in the calculation of the group sums, this workaround doesn't work, since Excel does not accept this:

=SUMMEWENN(B:E;${_group.item.age};E:E)

In english:

=SUMIF(B:E,${_group.item.age},E:E)

The problem is in the ${_group.item.age} construct.

If I make this a text, just to check if jxls is replacing ${_group.item.age} with the correct text:

'SUMMEWENN(B:E;${_group.item.age};E:E)

in english:

'SUMIF(B:E,${_group.item.age},E:E)

I see in the result that the correct values are being replaced!

'SUMMEWENN(B:E;30;E:E)

and

'SUMMEWENN(B:E;40;E:E)

The only part missing is to make this text a formula:

=SUMMEWENN(B:E;30;E:E)

and

=SUMMEWENN(B:E;40;E:E)

Is this possible in jxls2 ? It is almost working, it is just this syntax that Excel does not understand. Is there any other syntax for ${_group.item.age} that would work ? Or is this a bug?

sxssf_template.xlsx

I have a question concerning the functionality of the CellDataUpdater. I have implemented an updater, but for any reason the CellData value isn't updated. Perhaps I have a misunderstanding and what I would like to achieve is not possible.

I would like to set/update the value of my cell to a new value, hence I was thinking the CellDataUpdater is the best choice. With this very simplified example I expected to see "HELLO" in the cells using that updater, but unfortunately not:

@Override
public void updateCellData(CellData cellData, CellRef targetCell, Context context) {
	System.out.println("Updater is working....");
	cellData.setEvaluationResult("HELLO");
}

The cells, using that updater, are part of a each command. I see the code is really executed.

If this is not the best approach, what would you suggest? I have a data collection and I do prefer for some reasons to not change that collection upfront before handing it over. Instead adjust the Excel-Processing.

Hey everyone, i'm using this library to generate a report and having some issues with a formula in a dynamic column;

My expected result:

My current result:

This is my template, my current result and my expected result

Anyone can help? Thank you

The PoiTransformer.clearCellValue workaround for a POI bug (see BitBucket issue 180) works for non-streaming. There's another call to setCellFormula where this clearCellValue() call is missing. With that call streaming would also work.

Background info: In POI 4.1.0 Vladislav Galas removed the unsetV() call after modifying a formula. This results sometimes in wrong sums (0).

Can't parse Jxls template $[=SUM(F:F)] ，Need to click Out $[=SUM(F:F)] Normal Show.

I've used jxls to stream data to excel xlsx file. The problem is GC overhead limit exceeded as below:
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded at java.util.Arrays.copyOf(Arrays.java:3181) ~[?:1.8.0_265] at java.util.ArrayList.grow(ArrayList.java:267) ~[?:1.8.0_265] at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:241) ~[?:1.8.0_265] at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:233) ~[?:1.8.0_265] at java.util.ArrayList.add(ArrayList.java:464) ~[?:1.8.0_265] at org.apache.xmlbeans.impl.store.Xobj.find_all_element_users(Xobj.java:2098)

My question is the problem cause by jxls or not? Any one can help me to resolve it?
Thanks alot.

Hi,

if I take the template of this example ( sxssf_template.xlsx ) : https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java
, method: simpleSxssf()

and copy the sheet containing the jxls into a new sheet "Copy", only the original template is being evaluated into the "Result" tab (please refer to my screenshot)

I tried by changing:

Area xlsArea = xlsAreaList.get(0);

to

for (Area xlsArea: xlsAreaList) {
   xlsArea.applyAt(new CellRef("Result!A1"), context);
}

but same result.

If I use the non-streaming variant it works:

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

Is this not supported in streaming?

Jxls and jxls-poi version:

• 2.8.1

Office vendor and version:

• OpenOffice 4.1.5 - OK
• LibreOffice 7.0.1.2 - OK
• MS Office 2007 and 2016 - Not OK - Textbox is present

Excel version:

• Textbox only appears with .xlsx files. There is no textbox when using .xls files

Java (11) code used:

public void export() {
    try (InputStream is = Exporter.class.getResourceAsStream("/shop-items-template.xlsx")) {
        Path path = Paths.get("shop-items.xlsx");
        if (!Files.exists(path)) {
            Files.createFile(path);
        }
        File file = path.toFile();
        try (OutputStream os = new FileOutputStream(file)) {
            Context context = configureContext();
            JxlsHelper.getInstance().processTemplate(is, os, context);
        }
    } catch (IOException e) {
        // Handle it
    }
}

private Context configureContext() {
    Context context = new Context();
    context.putVar("shop", shopRepository.findById(1).orElseThrow());
    context.putVar("items", itemRepository.findAll());
    return context;
}  

Minimal use case

jx:each inside a jx:area, but jx:each and jx:area do not originate from the same comment/cell.

Template

shop-items-template-minimal.xlsx

Result

shop-items-minimal.xlsx

LibreOffice Calc

This is what it looks like in LibreOffice and OpenOffice Calc and what I believe it should like like in other Excel programs

MS Excel 2007

There is a massive TextBox element covering data on the Sheet, but when you delete the textbox, results are fine.

In my real use case I have some other cells to process in jx:area and display a jx:each below said cells. Result is the same and TextBox is present only when viewing the generated .xlsx file in MS Excel.
I can provide more info if needed.

Dear jxls team,
I try rendering (static columns)(dynamic columns)(static columns). However, right static columns have not fill data exactly index, index has not been updated after appending dynamic columns.
For example: Result after rendering:
A | B | C | D
1 | 2 | 0 | 3
A: static column with value 1,
B,C: dynamic columns with values: 2, 3
D: static column with value 0.
Expected values filled: 1 | 2 | 3 | 0.

Can you show me any way to rendering correctly?
Thank you very much.

Hi,

here:
https://bitbucket.org/leonate/jxls-demo/src/master/ I read:

!!!Jxls-2 Examples - DEPRECATED!!!
DEPRECATION NOTE!
All the examples have been migrated to the main Jxls repo.

The link points to: https://bitbucket.org/leonate/jxls/src/master/jxls-examples/
where I can read:

Hmm... can't find that one That file or directory doesn’t exist. Go back to the previous page and try another one.

What is the correct link of the examples?

In addition to the Maven build, a gradle build is to be introduced. The release process and in particular the publication on Maven-central should continue to be carried out with the Maven build.

Gradle should solve my Eclipse problems (because of Spock), enable Jitpack snapshot releases and the code coverage should be based on it.

POI 4.0 requires Java 8 to be the minimum supported version.
We should implement similar requirement for Jxls and have Java 8 version set in Maven pom.

Given a template with multiple jx:area defined, one after the other, with a static top region for each area, and when using xlsArea.applyAt() to transform the areas, updateRowHeights does not correctly copy the height from the source cell. It seems to attempt to lookup the transformed location in the source area and will not find a hit if rows/cols have been added to a prior area. I suspect issue is srcRow, which is not looking at the real source.

XlsArea.java
    private void updateRowHeights(CellRef areaStartCellRef, int relativeStartRow, int relativeEndRow) {
        if (transformer == null) return;
        for (int relativeSrcRow = relativeStartRow; relativeSrcRow <= relativeEndRow; relativeSrcRow++) {
            if (!cellRange.containsCommandsInRow(relativeSrcRow)) {
                int relativeTargetRow = cellRange.findTargetRow(relativeSrcRow);
                int targetRow = areaStartCellRef.getRow() + relativeTargetRow;
//use this.startCellRef instead?
                int srcRow = areaStartCellRef.getRow() + relativeSrcRow;
                try {
                    transformer.updateRowHeight(startCellRef.getSheetName(), srcRow, areaStartCellRef.getSheetName(), targetRow);
                } catch (Exception e) {
                    logger.error("Failed to update row height for src row={} and target row={}", relativeSrcRow, targetRow, e);
                }
            }
        }
    }

Hello,

As I mentioned in another issue I was trying to generate a report with quite a lot of rows coming from an SQL database.

I tried to optimize the memory usage by creating an Iterable backed by the resultset (which does not fetch all the rows at once), but right after returning the iterable it is used to create a List with all its elements, this is the code:

And looking at the commit history it does not seem to be a very strong reason for this (just avoiding warnings).

I changed the code to this, ran all the tests and all of them passed, so I think it's safe to change it:

@SupressWarnings("unchecked")
Iterable<Object> ret = (Iterable<Object>) collectionObject;

return ret;

What do you think?

• We have only a POI implementation, so move the code to the jxls project. We will separate core from POI using different packages. A possible other implementation could be in a separate project.
• If you built in a core feature and wanted to test it with an Excel file, the test case always had to be in jxls-poi. I found that unfavorable.
• docs in same repo. It's important to use a github-capable solution (folder "/docs").

PROS and CONS

• PRO: If you edit a ticket it's easier to also update changes.md
• PRO: If somebody clones the repo he gets also the docs. Somebody who just need the JAR (with sources) would not clone and will use Maven-central.
• PRO: one JAR, one version
• CONTRA: users must remove jxls-poi

Hello

I would like to find the way I can have a row that is copied downwards, but that row has a dynamic amount of columns at the end of it. I think there is a way with double jx:each command, but I do not know the correct syntax. Can you help me?

Data-wise in JAVA, it would be a POJO with some fields, and a List containing a different POJO with only one field (OR a List containing a primitive type).

expected2.xlsx
template2.xlsx

Exceptions during the evaluation of commands are catched and silently return empty collections. Callers are not able to retrieve the actual cause but are only able to see the Error Message in Std.Err.

Exceptions like this will cause the Report to be invalid hence the Exception should propagate up to the caller.

In EachCommand.java

 @Override
    public Size applyAt(CellRef cellRef, Context context) {
        Iterable<?> itemsCollection = null;
        try {
            itemsCollection = util.transformToIterableObject(getTransformationConfig().getExpressionEvaluator(), items, context);
            orderCollection(itemsCollection);
        } catch (Exception e) {
            logger.warn("Failed to evaluate collection expression {}", items, e);
            itemsCollection = Collections.emptyList();
        }
        Size size;
        if (groupBy == null || groupBy.length() == 0) {
            size = processCollection(context, itemsCollection, cellRef, var);
        } else {
            Collection<GroupData> groupedData = util.groupIterable(itemsCollection, groupBy, groupOrder);
            String groupVar = var != null ? var : GROUP_DATA_KEY;
            size = processCollection(context, groupedData, cellRef, groupVar);
        }
        if (direction == Direction.DOWN) {
            getTransformer().adjustTableSize(cellRef, size);
        }
        return size;
    }

hi，jxls team
when i am using 'each' and 'mergeCells' at the same time，i found the problem below here。
my template：

what i want：

But what I actually got is：

am i wrong or it is a bug？
waiting for your reply...thank you.

java heap space OOM then

I have an excel template with a cell at D1 with the following comment
jx:each(items="context.numberslist" var="number" lastCell="D1" direction="RIGHT")

This is part of an area defined at A1 cell by the following comment:
jx:area(lastCell="D8")

There are also a few vertical foreach comments on the A column, like this:
jx:each(items="context.list" var="variable" lastCell="C4")

The relevant code in my project is:

InputStream is = MyClass.class.getResourceAsStream(templateResource + language.toUpperCase() + ".xlsx");

ByteArrayOutputStream os = new ByteArrayOutputStream();
Transformer transformer = TransformerFactory.createTransformer(is, os);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
Context context = transformer.createInitialContext();
context.putVar("context", sheetContext);
for (Area xlsArea : areaBuilder.build()) {
    xlsArea.applyAt(xlsArea.getStartCellRef(), context);
}
transformer.write();
is.close();
os.flush();

The vertical clones work as expected. However, the horizontal one does not. It instead clones vertically, even though the direction was specified. How do I make it work?

actual.xlsx
expected.xlsx
template.xlsx

If use the each command, the image in the Excel template will be lost in the new content

You need to update the following libraries to fix vulnerabilities:

1. Group - org.apache.commons, name - commons-compress, version - 1.18. Vulnerability: CVE-2019-10086. Description: "The file name encoding algorithm used internally in Apache Commons Compress 1.15 to 1.18 can get into an infinite loop when faced with specially crafted inputs. This can lead to a denial of service attack if an attacker can choose the file names inside of an archive created by Compress"
2. Group - commons-beanutils, name - commons-beanutils, version - 1.9.3. Vulnerability: CVE-2019-10086. Description: "In Apache Commons Beanutils 1.9.2, a special BeanIntrospector class was added which allows suppressing the ability for an attacker to access the classloader via the class property available on all Java objects. We, however were not using this by default characteristic of the PropertyUtilsBean"
3. Group - org.apache.poi, name - poi, version - 4.1.0. Vulnerability: CVE-2019-12415. Description: "In Apache POI up to 4.1.0, when using the tool XSSFExportToXml to convert user-provided Microsoft Excel documents, a specially crafted document can allow an attacker to read files from the local filesystem or from internal network resources via XML External Entity (XXE) Processing"

When I use the multisheet function,the image in the template will be lost in the output excel

Hi,

I am using the example here: https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java

method: simpleSxssf()

I am trying to use the SelectSheetsForStreamingPoiTransformer like this:

I copy the sheet "Template" into a second sheet "NoStreaming". I adapt the method simpleSxssf() to only stream the "Template" sheet:

Context context = new Context();
context.putVar("cellRefUpdater", new CellRefUpdater());
context.putVar("employees", employees);
context.getConfig().setIsFormulaProcessingRequired(false);
Workbook workbook = WorkbookFactory.create(is);
SelectSheetsForStreamingPoiTransformer transformer = new SelectSheetsForStreamingPoiTransformer(workbook);
Set<String> streamingSheets = new HashSet<>();
streamingSheets.add("Template");
transformer.setDataSheetsToUseStreaming(streamingSheets);
//transformer.setEvaluateFormulas(true); // used with newest JXLS version
processTemplate(context, transformer);
workbook.write(os);

private void processTemplate(Context context, Transformer transformer) {
        XlsCommentAreaBuilder areaBuilder = new XlsCommentAreaBuilder();
        areaBuilder.setTransformer(transformer);
        List<Area> xlsAreaList = areaBuilder.build();
        for (Area xlsArea : xlsAreaList) {
            xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
        }
    }

but I am getting the outputs in the screenshot. What is going wrong here?

Using jxls 2.8.1

I try to add a picture below each command like :
jx:image(lastCell="H18" src="signature" imageType="PNG" scaleX="1.7976931348623157E308" scaleY="1.7976931348623157E308")
I want the picture to be the original size,but it was stretched.What's the reason...

when using sql in template ,if colume type is clob, clob column can't be displayed properly.

AZ列后无法合并

Hi,

in this example:
https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java
, method: simpleSxssf()

I always get a new tab with name "Result". This is different from the non-streaming code:

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

where the original template sheet is being replaced with the results, and the sheet maintains its original name.

Is this possible in the streaming variant? Or at least is it possible to change the name of the resulting sheet ?

Hi,

we are trying to support jxls2 streaming in our application.

So we change this:

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

to

Transformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();
for (Area xlsArea: xlsAreaList) {
    xlsArea.applyAt(new CellRef("Result!A1"), context);
}

context.getConfig().setIsFormulaProcessingRequired(false); // with SXSSF you cannot use normal formula processing
workbook.setForceFormulaRecalculation(true);
workbook.setActiveSheet(1);

((PoiTransformer) transformer).getWorkbook().write(os);

This seems to work. But the Template sheet is not being deleted, in contrast to the processTemplate() approach.

So I try to add the following:

workbook.setActiveSheet(1);

List<String> templateSheetsName = new ArrayList<>();
for (Area xlsArea : xlsAreaList) {
    templateSheetsName.add(xlsArea.getAreaRef().getSheetName());
}

for (String sheetName : templateSheetsName) {
    transformer.deleteSheet(sheetName);
}

((PoiTransformer) transformer).getWorkbook().write(os);

This seems to remove the correct sheet.

My question is: is this the correct approach? If yes: wouldn’t it be easier for the user to have some method like:

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

which would create the exactly same result as with the existing

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

with the difference that the second supports streaming?

We should check if we use the Workbook in readOnly mode. Especially for our testcases.

Symptom: none modified Excel template files get changed even the testcase has not changed them

Can you help me?

when use multi sheet template with image ,it will lose image on every page

Hi there,

I was testing this library to generate a quite large report with over ~150k rows from an SQL database, one of the issues I found was that it was taking so much time (~3 minutes) and it was due to the rather low value of rows fetched per network call, ie: if no value is specified using the setFetchSize method of the statement, it fetches 10 rows at a time by default, which means over 15k network calls to the database.

Once I overwrote the query method from the JdbcHelper class and specified 1000 rows as the fetch size, the same report took only ~25s.

So I am proposing to create a new constructor for the JdbcHelper class which allows to specify the fetchSize to use. The implementation is pretty simple so I can create the PR, but I am not sure if this can be tested because the the in-memory database probably won't be affected by a network optimization.

Hi,

as mentioned in other issues, we are trying to support streaming. Our current approach replaces this:

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

by this:

Workbook workbook = WorkbookFactory.create(is);
Transformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();
for (Area xlsArea: xlsAreaList) {
   xlsArea.applyAt(new CellRef("Result!A1"), context);
}
            
            
context.getConfig().setIsFormulaProcessingRequired(false); // with SXSSF you cannot use normal formula processing
workbook.setForceFormulaRecalculation(true);
workbook.setActiveSheet(1);
            
List<String> templateSheetsName = new ArrayList<>();
for (Area xlsArea : xlsAreaList) {
   templateSheetsName.add(xlsArea.getAreaRef().getSheetName());
}
            
for (String sheetName : templateSheetsName) {
    transformer.deleteSheet(sheetName);
}
            
((PoiTransformer) transformer).getWorkbook().write(os);

It doesn't throw any errors, but the result is not the expected result (comparing it with the result of the .processTemplate approach.

In particular, "TEST BEFORE" disappears, and the sum is not correctly set. I understand that with streaming no formulas are being evaluated, but they should be evaluated correctly afterwards with workbook.setForceFormulaRecalculation(true);, or am I wrong ?

To compare both results: output_rsuser_1.xlsx with the .processTemplate approach, _2 with the streaming approach. in _1 you see: =SUMME(D2:D37), which is correct, but in _2 you see: =SUMME(D2) which is not correct.
Also, you see in _2 that "TEST BEFORE" disappears.

Am I doing something wrong or is this a bug ?

rsuser.xlsx
output_rsuser_1.xlsx
output_rsuser_2.xlsx

I tested with 2.8.1.

Regards,
Eduardo

Generate codecov output when building with Gradle. Publish code coverage report on codecov.io

At some point we will get a conflict between the old BitBucket ticket numbers and the new Github ticket numbers. Some testcases include the ticket number in the name. We will put a "B" in front of the BitBucket numbers (example: Issue133Test to IssueB113Test). The Github numbers will then not get a prefix. We decided this to make it easier for future testcase authors.

Hi team,

When creating a report from excel template using jx:each, data validation (drop down) control is not being processed at all. Fields generated that way don't have data validation associated with them.
Not sure if this is by design and if so, do you have any ideas how to achieve this (I have to use excel template).
I have attached a demo template and a demo output where you can see that only the first row has drop down control attached to it.

demo_template.xlsx
demo_output.xlsx

Kind: bug
Priority: blocker
Component: jxls
Version: 2.8.1

Best regards,
Josip

Hi,

this issue based on this: #38
I cannot reopen the issue so I am opening a new one.

I am using the example here: https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java

method: simpleSxssf()

I am trying to use the SelectSheetsForStreamingPoiTransformer.

Using Jxls 2.8.1.

For different data I become the same output: please refer to the attached screenshot. The no-streaming sheet shows data correctly, but the streaming-sheet only shows Employee 0.

I also attached the template and the result.

The code for this testcase is (based on SxssfDemo.simpleSxssf()):

Context context = new Context();
context.putVar("employees", employees);
context.putVar("departments", departments);
            	
// JxlsHelper.getInstance().processTemplate(is, os, context);
            	
context.putVar("cellRefUpdater", new CellRefUpdater());
context.getConfig().setIsFormulaProcessingRequired(false);
Workbook workbook = WorkbookFactory.create(is);
SelectSheetsForStreamingPoiTransformer transformer = new SelectSheetsForStreamingPoiTransformer(workbook);
Set<String> streamingSheets = new HashSet<>();
streamingSheets.add("Template");
transformer.setDataSheetsToUseStreaming(streamingSheets);
//transformer.setEvaluateFormulas(true); // used with newest JXLS version
processTemplate(context, transformer);
workbook.write(os);

private void processTemplate(Context context, Transformer transformer) {
   XlsCommentAreaBuilder areaBuilder = new XlsCommentAreaBuilder();
   areaBuilder.setTransformer(transformer);
   List<Area> xlsAreaList = areaBuilder.build();
   for (Area xlsArea : xlsAreaList) {
      xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
   }
}

If I use JxlsHelper.getInstance().processTemplate(is, os, context); , both sheets are created correctly.

simple_sxssf_output.xlsx
sxssf_template.xlsx

`

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

        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-reader</artifactId>
            <version>2.0.6</version>
        </dependency>

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


      try (InputStream templateXmlStream = ExcelUtils.class.getResourceAsStream(xmlFile);
         InputStream inputXlsStream = new BufferedInputStream(inputXls)) {
        XLSReader reader = ReaderBuilder.buildFromXML(templateXmlStream);
        ReaderConfig.getInstance().setSkipErrors( true );
        XLSReadStatus readStatus = reader.read(inputXlsStream, beanMap);
        if (!readStatus.isStatusOK()) {
            throw new BusinessException("读取excel发生错误");
        }
    } catch (Exception e) {
        logger.error("读取excel发生错误", e);
        throw new BusinessException("读取excel发生错误");
    }

java.io.IOException:java.lang.ClassNotFoundException:org.apache.poi.xssf.usermodel.XSSFWorkbookFactory

`

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

package com.example.excelutil;

public class A {
    public String get() {
        return "AAAAAAAAAAAAA";
    }
}

package com.example.excelutil;

public class B {
    public String get(){
        return "BBBBBBBBBBBBB";
    }
}

package com.example.excelutil;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;

import com.example.excelutil.util.ExcelUtil;
import com.example.excelutil.util.IndexUtil;

public class Test {
    public static void main(String[] args) throws FileNotFoundException {

        export(new HashMap<String, Object>(){{put("demo", new A());}}, "C:\\Users\\lwr\\Desktop\\22.xlsx");

        export(new HashMap<String, Object>(){{put("demo", new B());}}, "C:\\Users\\lwr\\Desktop\\33.xlsx");
    }

    public static void export(Map<String, Object> funcs, String outPath) {
        ArrayList<Integer> list = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            list.add(i);
        }
        try {
            FileInputStream inputStream = new FileInputStream("C:\\Users\\lwr\\Desktop\\11.xlsx");
            FileOutputStream fileOutputStream = new FileOutputStream(outPath);

            Context context = new Context();
            context.putVar("list", list);

            JxlsHelper jxlsHelper = JxlsHelper.getInstance();
            Transformer transformer = jxlsHelper.createTransformer(inputStream, fileOutputStream);
            JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();

            JexlEngine jexlEngine = new JexlBuilder().namespaces(funcs).create();
            evaluator.setJexlEngine(jexlEngine);
            jxlsHelper.processTemplate(context, transformer);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

excel template:
${demo:get()}

result:
33.xlsx is the same as 22.xlsx
why not different between them
funcs of 'Class B' dont work

Hi,

We are using Jxls to generate excel exports. It works fine and exports are generated for templates read from file system. However we have a need to keep them on central location and read the templates from URL. Does Jxls support templates from URL?

Exception details:
java.io.IOException: Your InputStream was neither an OLE2 stream, nor an OOXML stream
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234)
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:198)
org.jxls.transform.poi.PoiTransformer.createTransformer(PoiTransformer.java:135)
org.jxls.transform.poi.PoiTransformer.createTransformer(PoiTransformer.java:120)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:497)
org.jxls.util.TransformerFactory.createTransformer(TransformerFactory.java:43)
org.jxls.util.JxlsHelper.createTransformer(JxlsHelper.java:425)
org.jxls.util.JxlsHelper.processTemplate(JxlsHelper.java:206)

Sample Code:
//read template content
URL url = new URL("http://....//template.xlsx");

		final InputStream is = url.openStream();
		byte[] resultBuffer = org.apache.commons.io.IOUtils.toByteArray(is);
		
		//Update jxls model 			
		Context context = new Context();
		context.putVar(objectName, data);
		
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		//export
		JxlsHelper.getInstance().processTemplate(is, baos, context);

Hi,

We are trying to switch to SxssfTransformer to support JXLS2 in large files.

Currently, we use this:

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

So we changed to:

Transformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);  
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);  
List<Area> xlsAreaList = areaBuilder.build();  
for (Area xlsArea: xlsAreaList) {  
   xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);  
}  
context.getConfig().setIsFormulaProcessingRequired(false); // with SXSSF you cannot use normal formula processing  
workbook.setForceFormulaRecalculation(true);  
workbook.setActiveSheet(1);  
((PoiTransformer) transformer).getWorkbook().write(os);

But we are getting this error:
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
java.lang.IllegalArgumentException: Sheet index (1) is out of range (0..0)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetIndex(XSSFWorkbook.java:1470)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.setActiveSheet(XSSFWorkbook.java:1449)
at maventest.MavenTest.main(MavenTest.java:56)

If we change

for (Area xlsArea: xlsAreaList) {
    xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
}

to

for (Area xlsArea: xlsAreaList) {
   xlsArea.applyAt(new CellRef("Result!A1"), context);
}

it works. I don’t understand this. I want to use a generic way, so xlsArea.getStartCellRef().getCellName() should also work, or what am I doing wrong? Is this a bug?

The complete test case is this:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.jxls.area.Area;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.jdbc.JdbcHelper;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

public class MavenTest {

    public static void main(String[] args) {

        try {
            Connection conn = DriverManager.getConnection(
                    "jdbc:sqlserver://ip;databaseName=reportserver", "user",
                    "pwd");
            JdbcHelper jdbcHelper = new JdbcHelper(conn);

            InputStream is = null;
            OutputStream os = null;

            is = new FileInputStream(new File("rsuser.xlsx"));
            os = new FileOutputStream("output_rsuser.xlsx");

            Context context = new Context();

            context.putVar("conn", conn);
            context.putVar("jdbc", jdbcHelper);

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

            Workbook workbook = WorkbookFactory.create(is);
            Transformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);
            AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
            List<Area> xlsAreaList = areaBuilder.build();
            for (Area xlsArea: xlsAreaList) {
                xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
            }
            context.getConfig().setIsFormulaProcessingRequired(false); // with SXSSF you cannot use normal formula processing
            workbook.setForceFormulaRecalculation(true);
            workbook.setActiveSheet(1);
            ((PoiTransformer) transformer).getWorkbook().write(os);

            is.close();
            os.flush();
            os.close();
            System.out.println("output created.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The template attached.

We tried with jxls 2.7.2 and 2.8.1, both same behavior.

rsuser.xlsx

Hi,

what is the meaning of the 3 last parameters of createSxssfTransformer ?
In the javadoc: http://jxls.sourceforge.net/javadoc/jxls-poi/org/jxls/transform/poi/PoiTransformer.html#createSxssfTransformer-org.apache.poi.ss.usermodel.Workbook-int-boolean-boolean-

I only see:

createSxssfTransformer

public static PoiTransformer createSxssfTransformer(org.apache.poi.ss.usermodel.Workbook workbook,
int rowAccessWindowSize,
boolean compressTmpFiles,
boolean useSharedStringsTable)
Creates transformer for given workbook and streaming parameters. Streaming will be used.
Parameters:
workbook - Excel template. Format must be XLSX.
rowAccessWindowSize -
compressTmpFiles -
useSharedStringsTable -
Returns:
transformer instance with the given workbook as template

would it be possible to explain / document these 3 parameters?

Thanks