osglworks / java-excel Goto Github PK

View Code? Open in Web Editor NEW

18.0 18.0 9.0 157 KB

An easy to use and flexible Excel file reader

CSS 11.54% Java 88.46%

java-excel's People

Contributors

Stargazers

Watchers

Forkers

niloay lamtsui0620 apple006 chasun-fhd rapune ddelgador fentonmartin realboutouil buianhtai

java-excel's Issues

Provide styling support

Provide a mechanism to allow developer define output sheet style, basically:

turn on/off display grid lines
bg/fg color for top row
bg/fg color for data rows
optionally bg/fg color for alternative data row (for even/odd row distinguish)

ExcelWriter - set auto filter

Enable auto filter when export list of data into excel spreadsheet

Rename to osgl-excel

We want to add writing support to this library so rename the artifact from excel-reader to osgl-excel

Plugin Excel reading into `IO.read` framework

With the introduction of osglworks/java-tool#154, now we can plugin Excel file reading into IO read framework

Support specify cell data format by header name

ExcelWriter - when passing data of Iterable type, it renders an empty spreadsheet

Is there an option to disable this warning [main] WARN org.osgl.xls.ExcelReader - column index not fully built on sheet

Hi, I understand that this warning is thrown if number of columns in excel don't match with fields in pojo.
I have a pojo that has fields more than the columns in excel sheet. I have a situation where in after converting excel sheet to pojo I set few extra fields. As I have many sheets in a work book, this warning is thrown huge number of times.

I was looking for an option if I can disable this warning?

[main] WARN org.osgl.xls.ExcelReader - column index not fully built on sheet

ExcelWriter - `IllegalArgumentException` when write a single object or empty list

Error stack:

java.lang.IllegalArgumentException: Invalid cell range, having lastRow < firstRow || lastCol < firstCol, had rows 0 >= 0 or cells -2 >= -1
	at org.apache.poi.ss.util.CellRangeAddress.<init>(CellRangeAddress.java:49)
	at org.osgl.xls.ExcelWriter.writeSheet(ExcelWriter.java:196)
	at org.osgl.xls.ExcelWriter.writeSheet(ExcelWriter.java:156)
	at org.osgl.xls.ExcelWriter.writeSheet(ExcelWriter.java:144)
	at org.osgl.xls.ExcelWriter.write(ExcelWriter.java:124)
	at act.view.excel.ExcelDirectRender.render(ExcelDirectRender.java:61)
	at act.view.DirectRenderResult.apply(DirectRenderResult.java:38)
	at act.handler.builtin.controller.RequestHandlerProxy.onResult(RequestHandlerProxy.java:333)
	at act.handler.builtin.controller.RequestHandlerProxy.handle(RequestHandlerProxy.java:230)
	at act.app.ActionContext.proceedWithHandler(ActionContext.java:1452)
	at act.route.Router$ContextualHandler.handle(Router.java:1782)
	at act.xio.NetworkHandler$3.run(NetworkHandler.java:191)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

support terminator

When terminator string is specified and any cell value equals to that string then stop reading the sheet

Support large excel file

The current implementation costs a lot of memory and time reading large excel file

This request is to implement reader using POI SAX style API so we can address large excel file

`ExcelReader`: read sheets into Map indexed by sheet name

Currently it reads all sheets into a single data structure:

List<Map<String, Object>> read();

This issue is to create an new API to read sheets into multiple data structure indexed by sheet name

Map<String, List<Map<String, Object>> readSheets();

It shall provides corresponding static API:

Map<String, List<Map<String, Object>> readSheets(File);
Map<String, List<Map<String, Object>> readSheets(InputStream);
Map<String, List<Map<String, Object>> readSheets(ISObject);

A different set of API for reading specific sheet

List<Map<String, Object>> readSheet(String sheetName);
List<Map<String, Object>> readSheet(int sheetId);

and the corresponding static APIs:

List<Map<String, Object>> readSheet(String sheetName, File file);
List<Map<String, Object>> readSheet(int sheetId, File file);
List<Map<String, Object>> readSheet(String sheetName, InputStream is);
List<Map<String, Object>> readSheet(int sheetId, InputStream is);
List<Map<String, Object>> readSheet(String sheetName, ISObject sobj);
List<Map<String, Object>> readSheet(int sheetId, ISObject sobj);

Support write big data to Excel file

Allow app to choose write using SXSSFWorkbook (via configuration) to stream Excel writing

failed to set cell value warning when cell value is json string

I am using <version>1.4.0</version>

One of the cell has json string for which I get such warning

[main] WARN org.osgl.xls.ExcelReader - failed to set cell value[{ "userId": 1, "id": 1, "title": "delectus aut autem", "completed": false }] to POJO[class com.example.excel.Model]: K2@[mySheet] org.osgl.exception.UnexpectedException: java.lang.NumberFormatException: For input string: "{ "userId": 1, "id": 1, "title": "delectus aut autem", "completed": false }" at org.osgl.util.E.unexpected(E.java:189) at org.osgl.util.ReflectionPropertySetter.setProperty(ReflectionPropertySetter.java:58) at org.osgl.util.ReflectionPropertySetter.set(ReflectionPropertySetter.java:47) at org.osgl.xls.ExcelReader.read(ExcelReader.java:157) at org.osgl.xls.ExcelReader.read(ExcelReader.java:122) at org.osgl.xls.ExcelReader.read(ExcelReader.java:82)

My reader code as below

ExcelReader.builder() .sheets("mySheet") .file(new File("myExcel.xlsx")) .build().read(Model.class)

Is there something I am missing?

java.lang.NullPointerException: null
	at org.osgl.xls.ExcelReader$TolerantLevel.onReadCellException(ExcelReader.java:307)
	at org.osgl.xls.ExcelReader.read(ExcelReader.java:160)
	at org.osgl.xls.ExcelReader.read(ExcelReader.java:122)
	at org.osgl.xls.ExcelReader.read(ExcelReader.java:82)
	at org.osgl.xls.ExcelReader.read(ExcelReader.java:68)
	at com.pixolut.colgate.bsbf.DataCleaner.mergeRachelData(DataCleaner.java:97)
	at com.pixolut.colgate.bsbf.DataCleanerMethodAccess.invoke(Unknown Source)
	at act.cli.bytecode.ReflectedCommandExecutor.invoke(ReflectedCommandExecutor.java:196)
	at act.cli.bytecode.ReflectedCommandExecutor.access$000(ReflectedCommandExecutor.java:51)
	at act.cli.bytecode.ReflectedCommandExecutor$1.run(ReflectedCommandExecutor.java:132)
	at act.job.TrackableWorker.apply(TrackableWorker.java:31)
	at act.job.TrackableWorker.apply(TrackableWorker.java:28)
	at org.osgl.Osgl$F1$2.apply(Osgl.java:499)
	at act.job.Job.doJob(Job.java:345)
	at act.job.Job.run(Job.java:282)
	at java.util.concurrent.Executors$RunnableAdapter.call$$$capture(Executors.java:511)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java)
	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
	at java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

ignoreEmptyRows does not work as expected

It won't ignore empty rows

Error rendering xls file: The maximum number of cell styles was exceeded

java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1263)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:131)
	at org.osgl.xls.ExcelWriter.createSpecialFormatStyle(ExcelWriter.java:417)
	at org.osgl.xls.ExcelWriter.writeCell(ExcelWriter.java:243)
	at org.osgl.xls.ExcelWriter.createDataRow(ExcelWriter.java:232)
	at org.osgl.xls.ExcelWriter.writeSheet(ExcelWriter.java:164)
	at org.osgl.xls.ExcelWriter.writeSheet(ExcelWriter.java:141)
	at org.osgl.xls.ExcelWriter.writeSheets(ExcelWriter.java:100)
	at org.osgl.xls.ExcelWriter.write(ExcelWriter.java:107)
	at act.view.excel.ExcelDirectRender.render(ExcelDirectRender.java:58)
	at act.view.DirectRenderResult.apply(DirectRenderResult.java:38)
	at act.handler.builtin.controller.RequestHandlerProxy.onResult(RequestHandlerProxy.java:322)
	at act.handler.builtin.controller.RequestHandlerProxy.handle(RequestHandlerProxy.java:229)
	at act.app.ActionContext.proceedWithHandler(ActionContext.java:1358)
	at act.route.Router$ContextualHandler.handle(Router.java:1771)
	at act.xio.NetworkHandler$3.run(NetworkHandler.java:169)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)