Improve cell data format

[bdzzaid]

Is your feature request related to a problem? Please describe. I need to display DATE in format dd.MM.yyyy instead of yyyy-MM-dd I need to display BOOLEAN as EMPTY if null, 0, if false, 1 if true

Describe the solution you'd like Add a customizable formatter for SQL -> JAVA mapping

Additional context I'll be happy to contribute to resolve the feature request.

[firegloves]

Hello @bdzzaid thx for your report.

Let me check if we can achieve this behavior in a simple way by leveraging a pre-existent API. I'll let you know.

In the case we will need a change (quite probable) I'll be happy to receive your contribution

[firegloves]

Date format

To address the date format requirement please look at this test

You should be able to achieve your goal by changing that line in your date cell style to something like: datetimeCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("dd.MM.yyyy"));

[LuccaPrado]

@firegloves, There are some place that we can put this example? It would be nice to have an wiki page like "Quick tips" or something like that

[bdzzaid]

Thanks for your quick answers !

Unfortunelly I tried the snippet code to change the date format, and the date format changed to a double number (using Excel for mac in version 16.+

I tried these date formats :

datetimeCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("dd.MM.yyyy hh:mm:ss"));
// and 
datetimeCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("dd.mm.yyyy hh:mm:ss"));
// and
datetimeCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("dd.MM.yyyy"));

And I always obtain the same result :

I cloned the MemPOI project, let me debug this week and give you a feedback.

[firegloves]

@bdzzaid look at this I tried quickly and it seems to work, but I didn't test a lot

I tried to find a solution to the BOOLEAN request, currently it's not possible to achieve that behavior because MemPOI maps the SQL boolean to a java boolean (not Boolean) => null is not currently supported.

But your request sounds interesting, so I'm thinking about a solution (probably it will consist of a transformation function to apply to the MempoiColumn). I'll let you know

[firegloves]

@LuccaPrado it's a good idea. Unfortunately GitHub doesn't support PR for the wiki and completely opening it doesn't sound so good. We may use GitHub discussions (not still active on MemPOI) to propose changes, could be a solution?

[bdzzaid]

Thanks for answers.

For the boolean request, let me know if I can help, we are testing MemPOI to replace JasperReport.

For the date/time format, I tried this solution and it's still not working, let me take the time to go deep dive into the code (and try with another version of Excel in Windows) to understand where the issue is coming from.

String excelFormatPattern = DateFormatConverter.convert(Locale.FRENCH, "dd.MM.yyyy hh:mm:ss");

CellStyle cellStyle = workbook.createCellStyle();

DataFormat poiFormat = workbook.createDataFormat(); cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));

MemPOI memPOI = MempoiBuilder .aMemPOI() .withFile(excel) .withAdjustColumnWidth(true) .withDatetimeCellStyle(cellStyle) .addMempoiSheet(createQuestionSheet(conn)) .addMempoiSheet(createAnswerSheet(selectExportId, conn)) .addMempoiSheet(createValidationSheet(selectExportId, conn)) .build();

Le lun. 21 déc. 2020 à 12:00, Luca Corsetti notifications@github.com a écrit :

@bdzzaid https://github.com/bdzzaid look at this https://poi.apache.org/apidocs/4.1/org/apache/poi/ss/util/DateFormatConverter.html I tried quickly and it seems to work, but I didn't test a lot

I tried to find a solution to the BOOLEAN request, currently it's not possible to achieve that behavior because MemPOI maps the SQL boolean to a java boolean (not Boolean) => null is not currently supported.

But your request sounds interesting, so I'm thinking about a solution (probably it will consist of a transformation function to apply to the MempoiColumn). I'll let you know

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/firegloves/MemPOI/issues/8#issuecomment-748912807, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVWKO4EO6U4K75UPUIADKLSV4TFZANCNFSM4U72ZRTA .

[firegloves]

@bdzzaid ok I wait for your news. However I'm on a Mac too, I'm using LibreOffice and using this code:

String excelFormatPattern = DateFormatConverter.convert(Locale.ITALIAN, "dd/MM/yyy");
DataFormat poiFormat = workbook.createDataFormat();
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));

results into this value: 04/01/19y if I add focus on the cell to edit it results into 04/01/2019

[firegloves]

@bdzzaid Excel uses data formats that differ from the java ones, a good way may be to inspect the content of a manually created document to look which format it does expect

[LuccaPrado]

@LuccaPrado it's a good idea. Unfortunately GitHub doesn't support PR for the wiki and completely opening it doesn't sound so good. We may use GitHub discussions (not still active on MemPOI) to propose changes, could be a solution?

Sorry for interrupting the thread again. This looks like a nice solution for me.

[firegloves]

In the meantime, I added some code documentation in the last commit and I generated 2 diagrams in the wiki that could help if you are analyzing the flow.

[bdzzaid]

Thank you.

I've seen them, the UML sequence diagram is really usefull and helps to understand when the framework interact with the data model and the ui. I'm trying today to give you my feedback about Excel 16 data rendering on mac.

Le dim. 27 déc. 2020 à 16:24, Luca Corsetti notifications@github.com a écrit :

In the meantime, I added some code documentation in the last commit and I generated 2 diagrams in the wiki that could help if you are analyzing the flow.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/firegloves/MemPOI/issues/8#issuecomment-751480241, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVWKO6PWBZRIYBFBDKPNH3SW5GT3ANCNFSM4U72ZRTA .

[firegloves]

Hi @bdzzaid,

I analyzed the situation and this is the result: your request is interesting and achievable but it requires changes to the MemPOI core. Your request shares some problems and required changes with another functionality that is already in the pipeline and that I was postponing: the possibility to supply styles per column. So this is probably the time to make a deep change aiming to increase MemPOI's power and flexibility, keeping the way easy for future implementations.

My idea is to add a MempoiColumnConfig (with the relative builder) where columns desired (and user-customizable) behaviors are centralized. Each MempoiSheet receives a list of MempoiColumnConfig and then the MempoiColumnStrategos will take care of merging user preferences and ResultSet's metadata to compose the final column configuration. This MempoiColumnConfig will contain a List of data transformation functions to apply to the ResultSet's record values ​​to achieve your desired behavior.

It's hard to split the development into various independent functionalities, so I think a possible and easy solution to contribute could be:

• fix the data format bug (maybe you will need to update some code, switching to a newer API)
• create one (or a set of) built-in data transformation function

Let me know if it sounds suitable for you, or if you have other ideas

[bdzzaid]

Hi @firegloves

Thank you for your answer, and your idea sounds very good.

OK, I will contribute for this update, we're evaluating the option to generalize MemPOI to our excel reports in production and remove Jasper, we really need the possibility to supply styles per column.

As you said, prior first to bug fixing, and when the date is correctly formatted on my excel sheet, I'll attack the development of MempoiColumnConfig and Strategies.

One another need is to protected the excel with a password, it's a nice to have, we have a work around for this, but if I can include this feature (if not present yet), I will check the possibility to add this feature into the MemPOIBuilder.

[firegloves]

Ok @bdzzaid, I'm glad to hear that. Let me set up the main system and then I'll ping you when we are ready to move ahead

[bdzzaid]

Hi,

I finally resolved the issue, it was inside my generation method :

• Fix 1 : I used the Locale.ENGLISH to format the date (I tried to open the Excel file with LibreOffice, an Excel, both are working well),
• Fix 2 : I set workbook to MempoiBuilder
• Fix 3 : I have to set the border style to the new CellStyle (as you can see in the uploaded image, its needed)

 Workbook workbook = new SXSSFWorkbook();
        // Fix 1 : Use English date format instead of french date format
        String excelFormatPattern = DateFormatConverter.convert(Locale.ENGLISH, "dd.mm.yyyy hh:mm");
        DataFormat poiFormat = workbook.createDataFormat();

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        // Fix 3 : Include border style format
        BorderStyle style = BorderStyle.THIN;
        cellStyle.setBorderBottom(style);
        cellStyle.setBorderLeft(style);
        cellStyle.setBorderRight(style);
        cellStyle.setBorderTop(style);

        MemPOI memPOI = MempoiBuilder
                .aMemPOI()
                .withWorkbook(workbook) // Fix 2 : set the workbook
                .withFile(excel)
                .withAdjustColumnWidth(true)
                .withDatetimeCellStyle(cellStyle)

        MemPOI memPOI = MempoiBuilder
                .aMemPOI()
                .withWorkbook(workbook) // Fix 3 : set the workbook
                .withFile(excel)
                .withAdjustColumnWidth(true)
                .withDatetimeCellStyle(cellStyle)
                .addMempoiSheet(createQuestionSheet(conn))
                .build();
        CompletableFuture<String> fut = memPOI.prepareMempoiReportToFile().orTimeout(3, TimeUnit.MINUTES);

[firegloves]

Good catch! I have to update the docs! In the meantime, I started working on the MempoiColumnConfig, I hope to have good news in a short.

If you want to add this snippet to the doc, during these days I'm going to create a section in the wiki

[firegloves]

As suggested by @LuccaPrado I created a Quick Tips section in the wiki. Do you want to describe your solution there? I already created a file in the wiki project for your solution, if you feel comfortable with it.

In the meantime, I added a contributing.md file in the docs folder.

[bdzzaid]

Thanks for the wikipage, I will add my solution in the wiki project into the section quicktips/Custom_date_format.md My apologise, my english is not very good looking, then please take the time to review my description when it's done.

I'll also read the contributing page. Maybe we can start a github discussion for all these aspects. The ticket can be closed for the date format. For the boolean format, we can also discuss this feature away from this issue.

Le jeu. 7 janv. 2021 à 20:01, Luca Corsetti notifications@github.com a écrit :

As suggested by @LuccaPrado https://github.com/LuccaPrado I created a Quick Tips section in the wiki. Do you want to describe your solution there? I already created a file in the wiki project for your solution, if you feel comfortable with it.

In the meantime, I added a contributing.md file in the docs folder.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/firegloves/MemPOI/issues/8#issuecomment-756314897, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVWKO34CZ26EN2K62UQVL3SYYAILANCNFSM4U72ZRTA .

[bdzzaid]

Because "remote: Permission to firegloves/MemPOI.wiki.git denied to bdzzaid." I'll send you my updates by email. If we need to discuss and exchange data, we should find a better way to do that.

Le ven. 8 janv. 2021 à 06:47, Zaïd Boudamouz bdzzaid@gmail.com a écrit :

Thanks for the wikipage, I will add my solution in the wiki project into the section quicktips/Custom_date_format.md My apologise, my english is not very good looking, then please take the time to review my description when it's done.

I'll also read the contributing page. Maybe we can start a github discussion for all these aspects. The ticket can be closed for the date format. For the boolean format, we can also discuss this feature away from this issue.

Le jeu. 7 janv. 2021 à 20:01, Luca Corsetti notifications@github.com a écrit :

As suggested by @LuccaPrado https://github.com/LuccaPrado I created a Quick Tips section in the wiki. Do you want to describe your solution there? I already created a file in the wiki project for your solution, if you feel comfortable with it.

In the meantime, I added a contributing.md file in the docs folder.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/firegloves/MemPOI/issues/8#issuecomment-756314897, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVWKO34CZ26EN2K62UQVL3SYYAILANCNFSM4U72ZRTA .

[LuccaPrado]

Because "remote: Permission to firegloves/MemPOI.wiki.git denied to bdzzaid." I'll send you my updates by email. If we need to discuss and exchange data, we should find a better way to do that.

@bdzzaid, I think that the way is create a fork, edit the doc wiki, then open a Pull/Merge request to the original repo. I guess that this is the way, right @firegloves?

[bdzzaid]

Sounds good to me. I'll try to do that. Thanks

Le ven. 8 janv. 2021 à 19:54, Lucca Prado notifications@github.com a écrit :

Because "remote: Permission to firegloves/MemPOI.wiki.git denied to bdzzaid." I'll send you my updates by email. If we need to discuss and exchange data, we should find a better way to do that.

@bdzzaid https://github.com/bdzzaid, I think that the way is create a fork, edit the doc wiki, then open a Pull/Merge request to the original repo. I guess that this is the way, right @firegloves https://github.com/firegloves?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/firegloves/MemPOI/issues/8#issuecomment-756935716, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVWKO6WGXCOU5YUPUUCEDDSY5IGRANCNFSM4U72ZRTA .

[firegloves]

Yes guys, I think LuccaPrado is right, but I'm not sure. Let me know if you can't find a way to make it work, please

[firegloves]

@bdzzaid I'm trying to add password protection. It's working for xlsx, but I can't get it working with xls documents. Do you have an example?

[bdzzaid]

@firegloves My sincere apologies, I checked our process, and in fact, we don't lock the excel file with a password, but for files like Excel/Word/Pictures we create a protected zip file. For PDF files, we are using this code.

            final String protectedFilename = FilenameUtils.removeExtension(source.getName()).concat(" (PROT).pdf");
            pdfReader = new PdfReader(source.getPath());
            final File encryptedFile = new File(source.getParent(), protectedFilename);
            final boolean renamedFile = source.renameTo(encryptedFile);
            stamper = new PdfStamper(pdfReader, new FileOutputStream(encryptedFile));
            stamper.setEncryption(password.getBytes(), password.getBytes(), PdfExporterConfiguration.ALL_PERMISSIONS.intValue(), PdfWriter.ENCRYPTION_AES_128);

You can ignore this feature then.

Note that PdfReader and PdfStamper classes are imported from com.lowagie.text that we are currently leaving. So I'll rewite the encryption process for PDF also.

Otherwise, I'll check in my side if there is a better way to protect an excel file by password and share with you the solution if found.

[firegloves]

@bdzzaid thanks for the info. No problem about the misunderstanding: the password protection is a nice feature, thx for the suggestion :)

However keep in mind that if you want to search for a solution, I have already a working example (already implemented in the MemPOI flow) for the xlsx. What is not working is only the xls

[firegloves]

I discovered that my problem was coming from LibreOffice: binary files were encrypted in the right way but LibreOffice is not able to open them. Using Excel solved the problem!

[apoorva-sriv]

Hi,

I finally resolved the issue, it was inside my generation method :

• Fix 1 : I used the Locale.ENGLISH to format the date (I tried to open the Excel file with LibreOffice, an Excel, both are working well),
• Fix 2 : I set workbook to MempoiBuilder
• Fix 3 : I have to set the border style to the new CellStyle (as you can see in the uploaded image, its needed)

 Workbook workbook = new SXSSFWorkbook();
        // Fix 1 : Use English date format instead of french date format
        String excelFormatPattern = DateFormatConverter.convert(Locale.ENGLISH, "dd.mm.yyyy hh:mm");
        DataFormat poiFormat = workbook.createDataFormat();

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        // Fix 3 : Include border style format
        BorderStyle style = BorderStyle.THIN;
        cellStyle.setBorderBottom(style);
        cellStyle.setBorderLeft(style);
        cellStyle.setBorderRight(style);
        cellStyle.setBorderTop(style);

        MemPOI memPOI = MempoiBuilder
                .aMemPOI()
                .withWorkbook(workbook) // Fix 2 : set the workbook
                .withFile(excel)
                .withAdjustColumnWidth(true)
                .withDatetimeCellStyle(cellStyle)

        MemPOI memPOI = MempoiBuilder
                .aMemPOI()
                .withWorkbook(workbook) // Fix 3 : set the workbook
                .withFile(excel)
                .withAdjustColumnWidth(true)
                .withDatetimeCellStyle(cellStyle)
                .addMempoiSheet(createQuestionSheet(conn))
                .build();
        CompletableFuture<String> fut = memPOI.prepareMempoiReportToFile().orTimeout(3, TimeUnit.MINUTES);

@firegloves, any update on why borders are missing and need to be manually added for Date cells (even with SXSSFWorkbook)?

[firegloves]

@apoorva-sriv if you are using the approach reported by bdzzaid, you need to manually add borders because you are overriding the cell style provided by MemPOI with a custom one

[apoorva-sriv]

@apoorva-sriv if you are using the approach reported by bdzzaid, you need to manually add borders because you are overriding the cell style provided by MemPOI with a custom one

Oh, my bad, I thought it was adding the date format to the existing style. Thanks for clarifying!