CSV to XLSX

[SerPivas]

Hi!
I've opened csv file, and created date filter
i need remove current day, and empty cells, but filter don't work
also
cells format don't change.

Code: Select all

 File file = fileChooser.showOpenDialog(new Stage());
        file.getAbsoluteFile();
        fileError = String.valueOf(file);
        Workbook wb = new Workbook();
        wb.loadFromFile(fileError, ",", 1, 1);
        Worksheet sheet = wb.getWorksheets().get(0);
        int lastRow = sheet.getLastRow();
        sheet.getCellRange("A1:V" + lastRow).setIgnoreErrorOptions(EnumSet.of(IgnoreErrorType.NumberAsText));
        CellRange range = sheet.getCellRange("M1:M" + lastRow);
        range.setNumberFormat("dd.mm.yyyy");
        //Перенос текста по столбцам и применение автофильтра
        AutoFiltersCollection filters = sheet.getAutoFilters();
        filters.setRange(sheet.getCellRange(1, 1, lastRow, 22));
        //Фильтр колонки "Статус"
        //filters.addFilter(6, "Сформирован");
        //Фильтр колонки "Завершили формирование"
        if (currentDate == null) {
            currentDate = LocalDate.now();
        }
        filters.customFilter(12, FilterOperatorType.NotEqual, currentDate, true, FilterOperatorType.NotEqual, "");
        filters.filter();

        wb.saveToFile("C:\\Users\\" + user + "\\Desktop\\Ошибки\\503.xlsx");


[William.Zhang]

Hello,

Thanks for your inquiry.
To help us further investigate your issue, please provide us with your CSV file, you can upload it here as an attachment or send it to this email address: [email protected]. Thank you in advance.

Sincerely,
William
E-iceblue support team

[SerPivas]

Unfortunately, i can't do that
cuz this file from my work
it's forbidden.

[William.Zhang]

Hello,

Thanks for your reply.
Sorry, if you cannot provide the documents, it will be difficult for us to further investigate. You can remove key information from the document and send it to this email: [email protected]. We will keep your documents confidential. Thank you for your understanding.

Sincerely,
William
E-iceblue support team

[SerPivas]

I can't remove key information
Cuz it's csv file
And it contains a lot of information
If i delete some value, formatting file will be incorrect
Anyway
All information is key

[William.Zhang]

Hello,

Thanks for your reply.
Sorry, regarding the issue you mentioned, we cannot further investigate without the source file. Could you please simulate a document based on your source file and provide it to us? Thanks for your understanding.

Sincerely,
William
E-iceblue support team

[SerPivas]

I need to remove today (20.03.2024), yesterday (19.03.2024)
and empty cells in the column "Завершили формирование"
Column "Статус" must be "Сформирован"

[William.Zhang]

Hello,

Thanks for your file.
I created a filter using Our Spire.Xls to test your case, and I found that it did not work. I have logged this issue in our tracking system with ticket number SPIREXLS-5178. Our development team will further investigate and I will inform you promptly of any progress. Sorry for any inconvenience caused to you.

Sincerely,
William
E-iceblue support team

[SerPivas]

Thanks
I'll be waiting

[William.Zhang]

Hello,

Thanks for your patience.
Regarding the issue SPIREXLS-5178, our development team did further investigation. They point out that LocalDate is a class in Java8, but our product is compiled on java6. Therefore, when using a custom filter, you cannot pass a LocalDate object, and you need to replace it with a string in date format. Based on your previous requirements, do not keep today and yesterday's dates, and do not keep empty cells. We suggest that you set the condition of the filter to be less than before yesterday. The code is as follow:

Code: Select all

String localDate1 = "2024/3/19 21:46";
sheet.getAutoFilters().customFilter(filterColumn,  FilterOperatorType.LessThan, localDate1,true,FilterOperatorType.None,null);

If you have any further questions, please feel free to write to us at any time.

Sincerely,
William
E-iceblue support team

[SerPivas]

Hello.
Your code is not working
All cells is empty

Your code.png (4.29 KiB) Viewed 5558 times

But i've tried do this

Code: Select all

String localDate = "2024-3-30";
filters.customFilter(14, FilterOperatorType.LessThan,localDate,true,FilterOperatorType.NotEqual,"");


And empty cells are gone
But all date 2024.03.31still here

My code.png (20.01 KiB) Viewed 5558 times

[William.Zhang]

Hello,

Thanks for your reply.
Please refer to the complete code below for testing. If your issue still exists, please provide us with your current testing file and we will further investigate. Thank you in advance.

Code: Select all

Workbook workbook = new Workbook();
workbook.loadFromFile("test.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
sheet.getAutoFilters().setRange(sheet.getCellRange(1,1,sheet.getLastDataRow(),sheet.getLastDataColumn()));
FilterColumn filterColumn = sheet.getAutoFilters().get(0);
String localDate = "2024-3-30";
sheet.getAutoFilters().customFilter(filterColumn, FilterOperatorType.LessThan,localDate,true,FilterOperatorType.None,null);
sheet.getAutoFilters().filter();
workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);


Sincerely,
William
E-iceblue support team