Automation of printing in corporate systems or how to make friends with your printer
Hi, harazhitel! I want to share my painful experience with you , I hope it will be useful. Today I’ll talk about how we developed a document printing system in a corporate system.
It all started with the development of an ERP platform in one trading company about 2 years ago. Linux, the C ++ / Qt stack, PostgreSql and the front under the web were selected. An application server was implemented in C ++ / Qt, and business logic was written there through the JS interpreter layer. Why so is a separate story, here we will consider how the printing system was developed.
Initially, while all the documents were printed from 1C (we use a bunch of 1C + our own “ERP” for management accounting in the company) everything was fine, but the plans were to switch to our system. Then it was necessary to fasten the seal to the system.
The first idea was to layout the html form, programmatically fill out the data, html the user to the browser and let it print from there.
Immediately found out some nuances:
As a result, only one template was made for printing an application for logistics (single-page form) which is still successfully used
The second idea was to work with XLSX documents. Google quickly prompted about the QtXlsxWriter library . There were still options , but eventually settled on QtXlsxWriter.
What the library was able to do:
This made it possible to immediatelyreduce the cost of taking document templates in xlsx format from other systems (Hi 1C), filling out the necessary data and statics to give the user an xlsx file with which he can work as he likes.
Pitfalls immediately appeared, QtXlsxWriter “poorly” handled loading a document from a template, lost cell formats (wrap / aling, etc.). After hours of digging “xlsx format (if anyone 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 stored differently, or
or
and QtXlsxWriter in places the parcel is only 1/0, in some places only true / false, and in some places this and that. 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, it starts to swear.
In this case, after opening, visually, all the data was in place. At the same time, many ordinary users (our customers) could be scared of such a message when opening our price lists.
After many hours of sticking MS Office and QtXlsxWriter files into xml and searching for one,I don’t know why MS didn’t like it, a crutch was invented. If you take the file generated by QtXlsxWriter and process it using LibreOffice, you get a valid xlsx file with all the data from the original, but MS Office does not swear at it:
And it became good to live, a little code was written for the required reports on the formation of an xlsx document, uploaded to users, they worked with it if necessary, and MS Office no longer scared them. They even managed to unload OLAP reports in xlsx with groupsand courtesans .
The company grew, there were more customers, more documents (applications, sales, invoices, etc.), printing began to take up a lot of working time. Moreover, part of the documents was printed from 1C part of our system. We decided to somehow automate this matter. Before that (about 5-7 years ago), I had the experience of printing 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 with it, and the platform is spinning on Linux and dragging I did not want the Windows module here (although the print server on Windows was considered as an extreme option).
Linux has CUPS and everything seems to be fine with this, with the lpr command you can easily send a pdf file for printing. That's just pdf we do not know how to generate. The solution was found quickly.
But not everything turned out to be so simple. The files were converted at 100% scale and did not fit the page size (A4 / A3, portrait / landscape, indentation), or rather, everything was adjusted according to standard parameters (A4, portrait). It turned out that if you set these settings through LibreOffice (open LibreOffice Calc with your hands), save in xlsx and convert via libreoffice --convert-to pdf, everything worked almost perfectly.
Regarding point 2, I unsubscribed in support of LibreOffice, I look forward to hearing from them.
The benefit of paragraph 3 is working correctly, we decided to build on it. Now we need to teach QtXlsxWriter how to work with page settings. Uncovering xml files in xlsx documents, we found the places responsible for this business
xl / worksheets / sheet1.xml
What is interesting here:
pageMargins - I think everything is clear
fitToPage - fit to the size / number of pages or use the scale
fitToWidth - number of pages by width
fitToHeight - number of pages by height
scale - scale in%
paperSize - sheet size (9 = A4)
orientation - portrait / landscape
Added work with these parameters to QtXlsxWriter. It remains only to form an xlsx document with indentation in the right places so that pieces of incomplete content are not printed on different sheets. With this, it wasn’t quite easy.
Print
Consider the situation when we print a route sheet on a sheet of A4 book orientation, without indentation.
It is necessary that the width of the document fits in 1 page. We set the settings:
fitToPage = false
fitToWidth = 1
fitToHeight = 100
pageMargins - all for 0
Under these conditions, fitToHeight must be known to be larger than the number of expected pages when printing.
The route list is a heading indicating the route and a list of customers with ext. information on which delivery will be made.
If you leave it as it is, it is likely that part of the block with customer information falling at the end of the sheet will be broken, part will be at the end of the first and part at the beginning of the second, and this is unacceptable to us.
As a result, the following approach was born (possibly a crutch).
We initially know the A4 sheet size:
Width 21 cm
Height 29.7 cm
And we know that our content will be tailored to the width of the sheet, i.e. we can calculate the relative compression ratio of the content:
scale = sheet width / width of the content
Here a surprise was waiting for us, 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 right solution can be found here , but could not, in the end, the magic number was found empirically 5.10238
1 cm = 5.10238 e.i.sh.k. (unit of column width)
Next, we calculate the size of the content that we can fit across the width of the sheet
Another magic number appeared, you guessed it, to translate the line height into from see in e.i.v.s (a unit for measuring the line height, on the Internet I found this information „r.Height = ht * 28.3464567 // Convert CM to postscript points “) The
line height can be found in:
Using the height parameter, we hammer the content into the xlsx file while the content height is <= height. If, when adding a new block B, we go beyond the boundaries of 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 an empty line can be calculated by knowing the height of the content (sum (rowHeight)) inserted before block B.
I do not consider here the calculation of pagination using indents (pageMargins), I can only say that the xml data stores the values of these variables in inches (1 inch = 2.54 cm ).
Thus, an xlsx file is obtained with ready-made settings and a breakdown by line for printing. Next, using libreoffice --convert-to pdf, convert to pdf and our document is ready to print.
It remains to print:
Now we are doing printing automation on the MFP with the finisher (stapling). Already played a little with the test apparatus and under Linux, it was all just for stapling.
Staple printing with one clip in the upper left corner:
That's all. I will be glad to know other approaches to the implementation of this task.
Thanks for attention )
How it all started
It all started with the development of an ERP platform in one trading company about 2 years ago. Linux, the C ++ / Qt stack, PostgreSql and the front under the web were selected. An application server was implemented in C ++ / Qt, and business logic was written there through the JS interpreter layer. Why so is a separate story, here we will consider how the printing system was developed.
The first samples of the pen
HTML
Initially, while all the documents were printed from 1C (we use a bunch of 1C + our own “ERP” for management accounting in the company) everything was fine, but the plans were to switch to our system. Then it was necessary to fasten the seal to the system.
The first idea was to layout the html form, programmatically fill out the data, html the user to the browser and let it print from there.
Immediately found out some nuances:
- Under all documents you have to make up a template from 0
- End user unable to edit html
- Managers led some analytics in excel
- Line wrapping issues when printing multi-page files
As a result, only one template was made for printing an application for logistics (single-page form) which is still successfully used
Xlsx
The second idea was to work with XLSX documents. Google quickly prompted about the QtXlsxWriter library . There were still options , but eventually settled on QtXlsxWriter.
What the library was able to do:
- Open / Create xlsx and read cell values
- Change cell value, save file
- Work with cell format (including border)
- Height / Width of Rows / Columns
- Cell Union
- Row / Column Grouping
- Insert Images
This made it possible to immediately
Pitfalls immediately appeared, QtXlsxWriter “poorly” handled loading a document from a template, lost cell formats (wrap / aling, etc.). After hours of digging “xlsx format (if anyone 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 stored differently, or
or
and QtXlsxWriter in places the parcel is only 1/0, in some places only true / false, and in some places this and that. 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, it starts 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 ...
In this case, after opening, visually, all the data was in place. At the same time, many ordinary users (our customers) could be scared of such a message when opening our price lists.
After many hours of sticking MS Office and QtXlsxWriter files into xml and searching for one,
libreoffice --headless --invisible --quickstart --convert-to xlsx test.xlsx --outdir valid_xlsxAnd it became good to live, a little code was written for the required reports on the formation of an xlsx document, uploaded to users, they worked with it if necessary, and MS Office no longer scared them. They even managed to unload OLAP reports in xlsx with groups
Automation
The company grew, there were more customers, more documents (applications, sales, invoices, etc.), printing began to take up a lot of working time. Moreover, part of the documents was printed from 1C part of our system. We decided to somehow automate this matter. Before that (about 5-7 years ago), I had the experience of printing 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 with it, and the platform is spinning on Linux and dragging I did not want the Windows module here (although the print server on Windows was considered as an extreme option).
All in pdf
Linux has CUPS and everything seems to be fine with this, with the lpr command you can easily send a pdf file for printing. That's just pdf we do not know how to generate. The solution was found quickly.
libreoffice --convert-to pdf 1.xlsx --headlessBut not everything turned out to be so simple. The files were converted at 100% scale and did not fit the page size (A4 / A3, portrait / landscape, indentation), or rather, everything was adjusted according to standard parameters (A4, portrait). It turned out that if you set these settings through LibreOffice (open LibreOffice Calc with your hands), save in xlsx and convert via libreoffice --convert-to pdf, everything worked almost perfectly.
- Indentation and page settings were handled correctly.
- If it was necessary to scale, then this parameter was ignored and converted with a scale of 100%.
- If there were settings to fit the size / number of pages, everything worked
Regarding point 2, I unsubscribed in support of LibreOffice, I look forward to hearing from them.
The benefit of paragraph 3 is working correctly, we decided to build on it. Now we need to teach QtXlsxWriter how to work with page settings. Uncovering xml files in xlsx documents, we found the places responsible for this business
xl / worksheets / sheet1.xml
...
What is interesting here:
pageMargins - I think everything is clear
fitToPage - fit to the size / number of pages or use the scale
fitToWidth - number of pages by width
fitToHeight - number of pages by height
scale - scale in%
paperSize - sheet size (9 = A4)
orientation - portrait / landscape
Added work with these parameters to QtXlsxWriter. It remains only to form an xlsx document with indentation in the right places so that pieces of incomplete content are not printed on different sheets. With this, it wasn’t quite easy.
Consider the situation when we print a route sheet on a sheet of A4 book orientation, without indentation.
Route sheet example
It is necessary that the width of the document fits in 1 page. We set the settings:
fitToPage = false
fitToWidth = 1
fitToHeight = 100
pageMargins - all for 0
Under these conditions, fitToHeight must be known to be larger than the number of expected pages when printing.
The route list is a heading indicating the route and a list of customers with ext. information on which delivery will be made.
If you leave it as it is, it is likely that part of the block with customer information falling at the end of the sheet will be broken, part will be at the end of the first and part at the beginning of the second, and this is unacceptable to us.
As a result, the following approach was born (possibly a crutch).
We initially know the A4 sheet size:
Width 21 cm
Height 29.7 cm
And we know that our content will be tailored to the width of the sheet, i.e. we can calculate the relative compression ratio of the content:
scale = sheet width / width of the content
Here a surprise was waiting for us, 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 right solution can be found here , but could not, in the end, the magic number was found empirically 5.10238
1 cm = 5.10238 e.i.sh.k. (unit of column width)
scale = А4_ширина * 5.10238 / sum(columnWidth) Next, we calculate the size of the content that we can fit across the width of the sheet
height=А4_высота * 28.3464567 / scaleAnother magic number appeared, you guessed it, to translate the line height into from see in e.i.v.s (a unit for measuring the line height, on the Internet I found this information „r.Height = ht * 28.3464567 // Convert CM to postscript points “) The
line height can be found in:
double QXlsx::Document::rowHeight(int column);Using the height parameter, we hammer the content into the xlsx file while the content height is <= height. If, when adding a new block B, we go beyond the boundaries of 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 an empty line can be calculated by knowing the height of the content (sum (rowHeight)) inserted before block B.
I do not consider here the calculation of pagination using indents (pageMargins), I can only say that the xml data stores the values of these variables in inches (1 inch = 2.54 cm ).
Thus, an xlsx file is obtained with ready-made settings and a breakdown by line for printing. Next, using libreoffice --convert-to pdf, convert to pdf and our document is ready to print.
It remains to print:
lpr -pFS-4300DN test.pdfNow we are doing printing automation on the MFP with the finisher (stapling). Already played a little with the test apparatus and under Linux, it was all just for stapling.
Staple printing with one clip in the upper left corner:
lpr -P printer_name -o StapleLocation="UpperLeft" order.pdfthe end
That's all. I will be glad to know other approaches to the implementation of this task.
Thanks for attention )