[Solved] Need advise to format my file

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

[Solved] Need advise to format my file

Post by EnolaH »

Hello guys !

I'm here to have some advices. I never used language VBA like, so I really don't know a lot about it, but I have few knowledge on python.
I have basically one file on LibreOffice 6.2.8.2 (x64) with a lot of sheets. (almost 80, but for my training, I work with only 22.)
This file is directly created with a python script I writed and looks like this.
2021-04-20 11_08_36-.png
Every sheets is in this format.
On the second screenshot you can see what result I'm expecting :
2021-04-20 11_11_03-secheresse_tours.xlsx - Excel.png
To resume : i want to insert a list in cell B1 linked to data in another file, (or maybe insert that data in my file) and add new columns, and formulas on line 7 and 8 (which are identical for every columns)

I'm not asking how to do it, but if you could advise me some function that can be usefull and maybe more documentation than the tutorial that can help me doing this ?
I could have done this on python but I'm thinking it would be easier to do it with LibreOffice Basic ?

EDIT : You can see in the file exemple_file an exemple of my raw data in the sheet "raw_data" and what i want in "result_expected"

EDIT 2: I'm not looking for someone to do it for me, that's my job to learn, I'm only here to have good advices, function, or link that may be really usefull for me. I'm really sorry if my post makes you feel like I'm here to have something ready-to-use.


Thanks for your answers
Attachments
exemple_file.ods
(61.29 KiB) Downloaded 256 times
Last edited by EnolaH on Tue Apr 20, 2021 3:09 pm, edited 2 times in total.
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

No macro required. Just learn how to work with multiple selections of ranges and sheets and learn everything about cell styles.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

Villeroy wrote:No macro required. Just learn how to work with multiple selections of ranges and sheets and learn everything about cell styles.
I'm doing that for work, and my superiors want a file they can use in few clic. Is it possible to do it with that ?
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

I can't answer your questions from screenshots and without knowing anything about the final goal. This data layout does not look healthy. If you really want to keep this layout and everything is just about visual appearance of this mess ("formatting"), you have to use templates and styles. Templates and styles make the difference between 100 clicks and 10 clicks, between 100 lines of macro code and 10 lines of macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

The worst problem I can see on the second screenshot is that most your data are numeric text because you imported plain text (?) with a wrong locale setting. These numeric strings are meant to be comma decimals whereas your import locale assumed point decimals. If you simply copied the plain unformatted data from the first screenshot's document to the second screenshot's document, this would be no problem because the first screenshot has only numbers. If the second document would be a preformatted template, the result would be correct and good looking at the same time. This is why my first recommendation is to learn about the tool you are trying to use before you start hacking on it. You can not program something you don't even know.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

I'll try to explain better, and i'll upload an exemple of my file with less data.

In sheet "raw_data" you have an exemple of what I have, in sheet Final_Data you can see what I'm expecting at the end of whatever I choose to do.
My objective is to add a "header" above the series and specify the format of my columns "dte_xxxx" as a date automatically for each sheets.
Doing it manually for few files it's easy, but when it comes to 80 it's way too long. Moreover, every weeks, a new file'll be generated and I'll need to format it everytime.
This header is composed of a dropdown list linked to data in another sheet named "break_even" on the matching list.

Every columns named SQ_x should have the same formula.

I thought about "register a macro" would be the easier but all my data havn't exactly the same number of columns and rows, so it makes it complicated.

Maybe it'll easier for you to understand what I mean

NB : I hope there won't be any problem, I use libreoffice in french, so I may write function in french and I havn't find how change it for english.
Attachments
exemple_file.ods
(63.51 KiB) Downloaded 236 times
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

Tools>Options... LanguageSettings -> Languages -> User Interface: English switches the entire office suite.
Tools>Options... Calc -> Formula -> "Use English function names" keeps everything French except function names.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

I understand very well what you mean. Daily business on this forum. "I never read any book on software. I don't know how to use this software. Can you write me a program which uses this software on my behalf?" What you have in mind might take more than one labour day.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

Villeroy wrote:I understand very well what you mean. Daily business on this forum. "I never read any book on software. I don't know how to use this software. Can you write me a program which uses this software on my behalf?" What you have in mind might take more than one labour day.
I never asked you to do a code for me, that's my job to learn here, sorry if you had this feeling, I was just looking for ressources I could use to learn it if someone had a good link, or function that may be usefull.
Last edited by EnolaH on Tue Apr 20, 2021 2:25 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64) on windows 10
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

Here is my file with english function, thank you for the advice !
Attachments
exemple_file.ods
(61.29 KiB) Downloaded 243 times
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

I do something similar several times per week. I open a preformatted spreadsheet and get a prompt "This document contains links to external data, do you want to update the linked database ranges?". After clicking [OK], all the data are dumped from a database into the preformatted spreadsheet. The creation of the spreadsheet took me 20 minutes without a single line of macro code. If I wanted to avoid the single [OK] cick I could use 3 lines of code, but why should I?
So the very first question is: where do all these data come from? Certainly, it is some kind of database because nobody types all that shit from keyboard into a spreadsheet. If these data fly to you as text files, things are very complicated because tabular text files (aka csv) are not standardised.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

Villeroy wrote: So the very first question is: where do all these data come from? Certainly, it is some kind of database because nobody types all that shit from keyboard into a spreadsheet. If these data fly to you as text files, things are very complicated because tabular text files (aka csv) are not standardised.
My data comes from a database I don't know a lot about it. I can extract what I want in .csv. the file is pretty simple : column A -> date column B-> the data.

Then i use a python script to create a dataframe like you saw in "raw_data"

EDIT : At the end of the script, I save the dataframe in .xlsx
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

It may be that you make things a lot more complicated than necessary. What kind of database are we talking about? Find an expert for this kind of database. Then establish a (preferably read-only) connection from that database to a LibreOffice Base document. Create a query or copy an existing one from the database. In Calc open the data source window (Ctrl+Shift+F4) and drag the query onto the top-left target cell.
Click any cell in the import range and call Data>Define...
In the database range dialog select the name of the import range ("Import1" or something), click [More Options] and check "Insert or delete" and check "Don't save imported data"
Add some cell styles for the incoming data types. Add formulas and charts. Formulas and charts adjust to the changing size of the incoming data. Incoming data are not saved in the document. The document loads fast and prompts for update.
You can do this with hundreds of sheets. But you have to do it only once.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

thank you, i take not of that it'll be really usefull !

Just to be sure, the raw data is just two columns, from year number one to year number X. I need to have one column per year. That method'll allow me to split how I want my data ?

EDIT : sorry but my english isn't the best in the world and when you say "query" I'm not sure to understand it very well
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need advise to format my file

Post by Villeroy »

Then create a pivot table from the 2 column data. You do not even have to import the 2 columns.
Data>Pivot>Create...
Create from registered data source
Specify the data source (name of Base document as shown in the data source window) and the query.
Drag years to column fields, values to data fields.
Modify the predefined cell styles for pivot tables.
Voilà
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: Need advise to format my file

Post by EnolaH »

thank you very much for your help !
Sorry if you had a bad impression from me, sure, I'm a beginner in openoffice/libreoffice/excel, but with your help, I'll learn everything I know quicker, I havn't found any of these information on google (maybe I just didn't type what I should.. )

Have a good day sir !
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Need advise to format my file

Post by Villeroy »

Pivot tables are not very flexible when it comes to formatting, however you can display negative numbers in red font instead of the grey background (cell style "Pivot table value").
If the grey background is of any importance, you can still write a simple macro based on 2 data field styles.
Pivot tables show the overall result in the bottom right corner. You can still have those header rows with function GETPIVOTDATA which looks up calculated results from data fields by given criteria from row and column fields.
Instead of having dozends of similar pivot tables for each category on separate sheets, you can merge all the data in one and use a page field from where you pick the category instead of picking them from the sheet tabs.
This would make the chart much easier because you only need one. Contrary to OpenOffice, LibreOffice supports pivot charts whcih automatically adjust to the changing size of the pivot. You pick one item from the page field and both, the chart and GETPIVOTDATA adjust to the new pivot table size. The more features you use, the simpler becomes everything. No trouble with storage, file format, export, import, macro programming, complex formulas losing their scope. Your data reside in a database, Base connects it with your spreadsheet document, everything is dumped into the sheet and chart automagically.
OpenOffice wiki on pivot tables wrote:Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.
Everybody thinks that pivot tabes (aka data pilots) are an highly advanced feature for the highly experienced spreadsheet wizards. The plain opposite is true. Nobody uses spreadsheets for arithmetics. Everybody tries to use it as a database surrogate or tabular layout program. The pivot table is a concession to these users of Excel/Calc. Somehow it breaks the fundamental concept of the original application but this is what most people want it to do anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Need advise to format my file

Post by Villeroy »

Pivot and chart grouped by year and month from two columns of your source data.
Date as row field.
Values as data field with sum function.
Created a 3 click chart.
Then I grouped the date field by month and year.
Dragged the year to column field to compare monthly results of different years.
Of course you can import the 2 source columns from csv but if you connect a Base document to your database, specifying the 2 database columns as source of the pivot would be enough to reflect the current state of the database.
Attachments
t105064.ods
(61.22 KiB) Downloaded 270 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
EnolaH
Posts: 9
Joined: Tue Apr 20, 2021 11:00 am

Re: [Solved] Need advise to format my file

Post by EnolaH »

Thank you for that answer really usefull.

Just how did you add the parameter "Years" in the pivot table ? when I first created one yesterday, I could just select my "dte" and "res" .

Moreover is pivot table allowing to have all the data for each day ? and just don't sum it, or doing whatever mathematical operation on it?
For work I need to have the value of each day to my analysis, not a sum.
I passed some times yesterday to read the calc guide but it didn't help me about these questions.
LibreOffice 6.2.8.2 (x64) on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Need advise to format my file

Post by Villeroy »

1) Create the pivot with the date as row field (vertical orientation) because there may be many of them and you only have 1024 horizontal columns. Finish the dialog.
2) Click any date cell, call menu:Group>Group... and choose the time units you want to group by. Always choose the year together with month, otherwise you will aggregate all Aprils, all Mays etc. In your particular case you should group by year and day (why didn't I do that?).
3) After grouping you can drag around the fields on sheet or in the pivot dialog just like "normal" fields.
btw: If the row/column field represents integer or decimal values, you can group by intervals (0-9, 10-19, ...)

The best thing is, you can always delete the whole thing without affecting the source data or any formulas.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply