Basic formatting of only the first line (header) of XLSX export: Bold, Center, Filter, Freeze Top Row

[t603]

Hello, SQuirreL team,

may I ask You for improvement of XLSX export? I would like to propose basic formatting of only the first line (header) of XLSX export. If it is possible.

I propose to apply these four "actions" to only the first line (header) of XLSX export: Bold, Center, Filter, Freeze Top Row. I always do it manually for users, which is time consuming, and they appreciate it very much comparing no formatting XLSX output.

One can see desired output in attached screenshot.

Thank You in advance, Stepan

[gerdwagner]

Committed to our GIT repositories. Will be available in future snapshots and versions.

Excerpt from change log:

#46
SQL result and table export to MS-Excel files: New options to make exported sheets
auto filtered and to choose if header rows are bold, centered and/or frozen.
Note: Export to MS-Excel files and other formats is available through the right mouse menu of tables.
The SQL editor allows to export SQL results through the right mouse menu and tools popup (ctrl+t).

[t603]

Hello, Gerd,

thank You for a patch. I tested "SQuirreL SQL 20241117_2043" running on Adoptium Open JDK 17. Formatted export (all new formatting options checked) and:

Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 32-bit :

1. Freezed OK.
2. Autofiltered: OK, but also one void column next to to the rightmost.
3. Centered: NOK.
4. Bold: NOK.

But see attached Excel screenshot. Although original columns are not centered nor bold, when I manually add "manual A" and "manual B" column headers next to the last filtered column, it appears both bold and centered. So Apache POI made some attempts to center and bold header, but not correctly.

LibreOffice 24.8.0.3:

1. Freezed OK.
2. Autofiltered: OK, but also one void column next to to the rightmost.
3. Centered: OK.
4. Bold: OK.

Screenshot of Excel:

I am sorry, I forgot how tricky is Excel format. Unfortunately maybe it could be harder as I thought. I will try to look info XML code of resulted XLSX tomorrow, but maybe this RFE could be ceased due version incompability. Because I believe, that You tested it in some version of Excel and it works there and it does not work in my Excel version,

Regards, Stepan

[gerdwagner]

Fixed in our Git repository, will be available in future snapshots and versions. Excerpt from change log:

Follow up to https://github.com/squirrel-sql-client/squirrel-sql-code/issues/46
SQL result and table export to MS-Excel files: The options to make header rows bold and centered did not 
  work for MS-Excel but for Libre Office only.
  Details of the fix:
  - Apache-Poi was upgraded to version 5.3.0.
  - SQuirreL deliberately dropped dependency on Log4J in 2022. As Apache-Poi 5.3.0 directly depends 
    on Log4J, SQuirreL now has a Log4J mock to make Apache-Poi 5.3.0 work. 

[fml2]

I've tried the Excel export with SQuirreL SQL Client snapshot-20241124_1922. One glithch that I noticed is that there is an extra column in Excel. I.e. if I export a table having four columns (with "Auto filter" selected), in the Excel I see the filter sign (a drop down button in the first row) in five columns.

[t603]

I have tested it too on "squirrel-sql-snapshot-20241124_1922-standard.jar" on Adoptium OpenJDK 17 on Windows 11. Auto filter OK, First row frozen OK, First row centered OK, First row bold OK.

There are two little problems:

• One more column in header, as described by @fml2. See the first screenshot below, red square. It looks like +1 problem of counting of columns in ResultSet (first is 0 or 1)?
• When Opening in LibreOffice 24.8 (on Windows), there is message about corrupted file. But when I click Yest (repair file), it opens OK. See the second screenshot below. But who knows, it could be lack of some feature or reading XLSX in LibreOffice. So I just notice it to anybody knows about it. I do not complain about this. Well, and in LibreOffice is one more column too.

Regards, Stepan

Screenshot 01: MS Excel, one more column problem.

Screenshot 02: LibreOffice, message about corrupted file.

--H2 2.3.232 InMemory 
drop table if exists "t"; 
create table "t" ("id" int, "name" varchar(256)); 
insert into "t" ("id", "name") values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); 
@msExcelWorkbook 'E:\Temp\formatting_01.xlsx' @sheet 'List1' select * from "t"; 

[t603]

"One more column" problem is just only filtered, not centered, not bolded. So just only AutoFilter is applied do the C, the third, column.

[gerdwagner]

The One more column filtered problem was already fixed last Monday (11/25/2024). Excerpt from change log:

#46
Excel export: The "Auto filter" option activated the auto filter for
one extra (empty) column at the end of the Excel sheet.