Automation of printing in corporate systems or how to make friends with your “bike” with a printer
Hello, habrazhitel! I want to share with you sore experience, I hope it will be useful. Today I will talk about how to develop a system for printing documents in the corporate system.
It all began with the development of ERP-platform in one trading company about 2 years ago. Linux was chosen, the C ++ / Qt stack, PostgreSql and the front for the web. In C ++ / Qt, an application server was implemented and in the same place, business logic was written through the interpreter's JS layer. Why this is a separate story, here we consider how the printing system was developed.
Initially, as long as all documents were printed from 1C (we use the 1C + bundle of our own “ERP” for management accounting in the company) everything was fine, and the plans were to switch to our system. Then it took to fasten the seal to the system.
The first idea was to make up the html form, programmatically fill in the data, html the user to the browser and let them print from there.
')
Immediately revealed some nuances:
As a result, only one template was made for printing an application to the logistics (one-page form) which is still successfully used
The second idea was to work with XLSX documents. Google quickly suggested the QtXlsxWriter library. There were more options , but eventually settled on QtXlsxWriter.
What did the library do:
This made it possible to immediately take the document templates in xlsx format from other systems (Hi 1C), fill in the required data and give the user xlsx a file with which he can already work with as he pleases.
Immediately, pitfalls appeared, QtXlsxWriter “poorly” handled the loading of a document from a template, lost cell formats (wrap / aling, etc.). After hours of digging “xlsx format (if someone does not know, xlsx is a zip archive with a set of xml documents), we found out that, in different versions of boolean, attributes in xml files are saved differently, or
or
and QtXlsxWriter in places parsil is only 1/0, in places only true / false, and in some places both. But nothing, there are hands, fixed.
There was also an unpleasant moment, after the formation of the xlsx file through QtXlsxWriter, if you open it in MS Office, he began to swear.
At the same time, after opening, visually, all the data was in place. At the same time, many ordinary users (our customers) could be frightened by such a message when opening our price lists.
After many hours sticking in the xml files of MS Office and QtXlsxWriter and searching for that,I don’t know what MS didn’t like, a crutch was invented. If you take the file generated by QtXlsxWriter and process it with the help of LibreOffice, you get a valid xlsx file with all the initial data, but MS Office does not swear at it:
And it became good to live, a small code was written for the necessary reports to form the xlsx document, it was unloaded to users, they worked with it if they had to type and MS Office did not scare them anymore. We were even able to implement OLAP report unloading in xlsx with groupingsand courtesans .
The company grew, there were more customers, more documents (applications, sales, invoices, etc.), printing began to take a lot of working time. At the same time, some documents were printed from 1C part from our system. We decided to automate this matter. Before that (5-7 years ago) there was a printing experience through Windows OLE containers (a container was created with Excel, a file was opened, print settings were set and sent to print), but I didn’t really want to get involved, and the platform is spinning on Linux and dragging I did not want a Windows module here (although a print server on Windows was considered as an extreme option).
Linux has CUPS, and with this, it seems like everything is fine, the lpr command can easily print a pdf file. Here only we do not know how to generate pdf. The solution was found quickly.
But it was not so easy. Files were converted with a 100% scale and were not adapted to the size of the pages (A4 / A3, portrait / landscape, indents), or rather everything was adjusted according to standard parameters (A4, portrait). It turned out that if you set these attitudes through LibreOffice (open LibreOffice Calc by hand), save to xlsx and convert via libreoffice --convert-to pdf, everything worked almost perfectly.
Regarding paragraph 2, unsubscribed in support of LibreOffice, I am waiting for a response from them.
The benefit of paragraph 3 is working correctly, we decided to build on it. Now you need to teach QtXlsxWriter to work with page settings. Scrapping xml files in xlsx documents found places responsible for this business
xl / worksheets / sheet1.xml
What is interesting here:
pageMargins - I think this is all clear
fitToPage - fit to the size / number of pages or use the scale
fitToWidth - number of pages in width
fitToHeight - number of pages by height
scale - scale in%
paperSize - paper size (9 = A4)
orientation - portrait / landscape
Added work with these parameters in QtXlsxWriter. It remains only to form an xlsx document with indents in the right places so that no pieces of unfinished content are printed on different sheets. With this, not everything just turned out to be.
Print
Consider the situation when we print a route list on A4 sheet of portrait orientation, without indents.
It is necessary that the width of the document fit into 1 page. Set the settings:
fitToPage = false
fitToWidth = 1
fitToHeight = 100
pageMargins - all by 0
Under these conditions, fitToHeight should be obviously more than the number of expected pages when printing.
The itinerary is a header with an indication of the route and a list of customers with add. information that will be delivered.
If you leave everything as it is, there is a high probability that the part of the block with customer information falling on the end of the sheet will break, some will be at the end of the first and some at the beginning of the second, which is unacceptable for us.
As a result, the next approach was born (possibly a crutch).
We initially know the size of A4 paper:
Width 21 cm
Height 29.7 cm
And we know that our content will be tailored to the width of the sheet, so You can calculate the relative degree of content compression:
scale = sheet width / content width
Here we were in for a surprise, in order to calculate the width of the content, it is necessary to add the width of all the columns, this is not difficult to do.
It was just completely incomprehensible in what units of measurement the result is obtained. Perhaps the correct solution can be found here , but could not, in the end, the magic number 5.10238 was found empirically
1 cm = 5.10238 e. (column width unit)
next, we calculate the size of the content that we can fit along the width of the sheet
Another magic number appeared, you guessed it, this is to convert the height of the line to from, see e.i.vs (line height measurement unit, on the Internet found such information „r.Height = ht * 28.3464567 // Convert CM to postscript points “)
Line height can be found in:
Using the height parameter, we hammer content into the xlsx file while the content height <= height. If, when adding a new block B, we go beyond the height, then before B we insert an empty line of the required height so that block B is printed from a new line. The height of the empty line can be calculated by knowing the height of the content (sum (rowHeight)) inserted before block B.
I don’t consider here the calculation of pagination using indents (pageMargins), I’ll just say that the xml data stores the values of these variables in inches (1 inch = 2.54 cm).
Thus, it turns out xlsx file with ready settings and a breakdown by line for printing. Next, using libreoffice --convert-to pdf we convert to pdf and our document is ready for printing.
It remains to print:
Now we are doing automation of printing on multifunction printers with a finisher (stapling). Already played a little with the test device and under Linux, it turned out everything was just for stapling.
Stamped printing with one clip in the upper left corner:
That's all. I would be glad to learn other approaches to the implementation of this task.
Thanks for attention )
How it all began
It all began with the development of ERP-platform in one trading company about 2 years ago. Linux was chosen, the C ++ / Qt stack, PostgreSql and the front for the web. In C ++ / Qt, an application server was implemented and in the same place, business logic was written through the interpreter's JS layer. Why this is a separate story, here we consider how the printing system was developed.
The first samples of the pen
HTML
Initially, as long as all documents were printed from 1C (we use the 1C + bundle of our own “ERP” for management accounting in the company) everything was fine, and the plans were to switch to our system. Then it took to fasten the seal to the system.
The first idea was to make up the html form, programmatically fill in the data, html the user to the browser and let them print from there.
')
Immediately revealed some nuances:
- Under all documents, you will have to impose a template from 0
- The end user is not able to edit html
- Managers conducted some analytics in excel.
- Problems with line breaks when printing multi-page files
As a result, only one template was made for printing an application to the logistics (one-page form) which is still successfully used
Xlsx
The second idea was to work with XLSX documents. Google quickly suggested the QtXlsxWriter library. There were more options , but eventually settled on QtXlsxWriter.
What did the library do:
- Open / Create xlsx and read cell value
- Change cell value, save file
- Work with cell format (including framing)
- Height / width of rows / columns
- Cell merging
- Row / column grouping
- Insert images
This made it possible to immediately take the document templates in xlsx format from other systems (Hi 1C), fill in the required data and give the user xlsx a file with which he can already work with as he pleases.
Immediately, pitfalls appeared, QtXlsxWriter “poorly” handled the loading of a document from a template, lost cell formats (wrap / aling, etc.). After hours of digging “xlsx format (if someone does not know, xlsx is a zip archive with a set of xml documents), we found out that, in different versions of boolean, attributes in xml files are saved differently, or
<ta="0" b="1" /> or
<ta="true" b="false" /> and QtXlsxWriter in places parsil is only 1/0, in places only true / false, and in some places both. But nothing, there are hands, fixed.
There was also an unpleasant moment, after the formation of the xlsx file through QtXlsxWriter, if you open it in MS Office, he began to swear.
In the book "test1.xlsx" content was found that could not be read. Try to restore the contents of the book? If you trust the source ...
At the same time, after opening, visually, all the data was in place. At the same time, many ordinary users (our customers) could be frightened by such a message when opening our price lists.
After many hours sticking in the xml files of MS Office and QtXlsxWriter and searching for that,
libreoffice --headless --invisible --quickstart --convert-to xlsx test.xlsx --outdir valid_xlsx And it became good to live, a small code was written for the necessary reports to form the xlsx document, it was unloaded to users, they worked with it if they had to type and MS Office did not scare them anymore. We were even able to implement OLAP report unloading in xlsx with groupings
Automation
The company grew, there were more customers, more documents (applications, sales, invoices, etc.), printing began to take a lot of working time. At the same time, some documents were printed from 1C part from our system. We decided to automate this matter. Before that (5-7 years ago) there was a printing experience through Windows OLE containers (a container was created with Excel, a file was opened, print settings were set and sent to print), but I didn’t really want to get involved, and the platform is spinning on Linux and dragging I did not want a Windows module here (although a print server on Windows was considered as an extreme option).
All to PDF
Linux has CUPS, and with this, it seems like everything is fine, the lpr command can easily print a pdf file. Here only we do not know how to generate pdf. The solution was found quickly.
libreoffice --convert-to pdf 1.xlsx --headless But it was not so easy. Files were converted with a 100% scale and were not adapted to the size of the pages (A4 / A3, portrait / landscape, indents), or rather everything was adjusted according to standard parameters (A4, portrait). It turned out that if you set these attitudes through LibreOffice (open LibreOffice Calc by hand), save to xlsx and convert via libreoffice --convert-to pdf, everything worked almost perfectly.
- Indents and page settings were processed correctly.
- If it was necessary to adjust the scale, this parameter was ignored and converted with a scale of 100%.
- If the settings were customized by the size / number of pages, everything worked
Regarding paragraph 2, unsubscribed in support of LibreOffice, I am waiting for a response from them.
The benefit of paragraph 3 is working correctly, we decided to build on it. Now you need to teach QtXlsxWriter to work with page settings. Scrapping xml files in xlsx documents found places responsible for this business
xl / worksheets / sheet1.xml
<worksheet> <sheetPr filterMode="false"> <pageSetUpPr fitToPage="false"/> </sheetPr> ... ... <pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/> <pageSetup paperSize="9" scale="50" firstPageNumber="0" fitToWidth="1" fitToHeight="1" pageOrder="downThenOver" orientation="portrait" usePrinterDefaults="false" blackAndWhite="false" draft="false" cellComments="none" useFirstPageNumber="false" horizontalDpi="300" verticalDpi="300" copies="1"/> ... </worksheet> What is interesting here:
pageMargins - I think this is all clear
fitToPage - fit to the size / number of pages or use the scale
fitToWidth - number of pages in width
fitToHeight - number of pages by height
scale - scale in%
paperSize - paper size (9 = A4)
orientation - portrait / landscape
Added work with these parameters in QtXlsxWriter. It remains only to form an xlsx document with indents in the right places so that no pieces of unfinished content are printed on different sheets. With this, not everything just turned out to be.
Consider the situation when we print a route list on A4 sheet of portrait orientation, without indents.
Sample route sheet
It is necessary that the width of the document fit into 1 page. Set the settings:
fitToPage = false
fitToWidth = 1
fitToHeight = 100
pageMargins - all by 0
Under these conditions, fitToHeight should be obviously more than the number of expected pages when printing.
The itinerary is a header with an indication of the route and a list of customers with add. information that will be delivered.
If you leave everything as it is, there is a high probability that the part of the block with customer information falling on the end of the sheet will break, some will be at the end of the first and some at the beginning of the second, which is unacceptable for us.
As a result, the next approach was born (possibly a crutch).
We initially know the size of A4 paper:
Width 21 cm
Height 29.7 cm
And we know that our content will be tailored to the width of the sheet, so You can calculate the relative degree of content compression:
scale = sheet width / content width
Here we were in for a surprise, in order to calculate the width of the content, it is necessary to add the width of all the columns, this is not difficult to do.
double QXlsx::Document::columnWidth(int column); It was just completely incomprehensible in what units of measurement the result is obtained. Perhaps the correct solution can be found here , but could not, in the end, the magic number 5.10238 was found empirically
1 cm = 5.10238 e. (column width unit)
scale = 4_ * 5.10238 / sum(columnWidth) next, we calculate the size of the content that we can fit along the width of the sheet
height=4_ * 28.3464567 / scale Another magic number appeared, you guessed it, this is to convert the height of the line to from, see e.i.vs (line height measurement unit, on the Internet found such information „r.Height = ht * 28.3464567 // Convert CM to postscript points “)
Line height can be found in:
double QXlsx::Document::rowHeight(int column); Using the height parameter, we hammer content into the xlsx file while the content height <= height. If, when adding a new block B, we go beyond the height, then before B we insert an empty line of the required height so that block B is printed from a new line. The height of the empty line can be calculated by knowing the height of the content (sum (rowHeight)) inserted before block B.
I don’t consider here the calculation of pagination using indents (pageMargins), I’ll just say that the xml data stores the values of these variables in inches (1 inch = 2.54 cm).
Thus, it turns out xlsx file with ready settings and a breakdown by line for printing. Next, using libreoffice --convert-to pdf we convert to pdf and our document is ready for printing.
It remains to print:
lpr -pFS-4300DN test.pdf Now we are doing automation of printing on multifunction printers with a finisher (stapling). Already played a little with the test device and under Linux, it turned out everything was just for stapling.
Stamped printing with one clip in the upper left corner:
lpr -P printer_name -o StapleLocation="UpperLeft" order.pdf the end
That's all. I would be glad to learn other approaches to the implementation of this task.
Thanks for attention )
Source: https://habr.com/ru/post/325586/
All Articles