Generated Excel with ClosedXML does not use the specified font/size during printing

6.8k Views Asked by At

I am creating an offer generator for my company. The generated offer must contains the General Conditions of Sale in the second worksheet (the offer details are displayed in the first worksheet). The General Conditions of Sale are contained into a word document, so I have to inject them into the worksheet. Last constraint, I have to respect the word's two column layout.

I already have the logic (it can be improved but that's not the point of my question) to extract and insert them into the worksheet. I am using a predefined excel file (with header, footer, column sizes, etc, already defined) and I simply insert the terms in the right merged cells, using ClosedXml.

My issue concerns the printing of that offer. When I open the generated offer, directly click on Print and select print the entire workbook, I can then scrolldown in the preview to check if everything is good. When I arrive to the second worksheet, the font and/or the font size are not the ones I specified in the code.

If I open the second worksheet at least once, and then do the former procedure, everything is back to normal and I can print my offer with the right font and font size. I encounter the same issue when I try to export the offer to pdf.

Here is the preview when I do not open the second worksheet, with wrong font size. And Here is the preview if I open the second worksheet at least one before printing, with good font and font size.

Here is my code (notice in the PrintTerms function the specification of the font and the size):

class Program
{
    const int nbRowByPage = 67;
    const int nbCharByPage = 3650;

    const string generalTermsFileName = "ConditionsGeneralSales_R5.docx";
    const string baseTemplateFileName = "TemplateOffer.xlsx";
    const string generatedExcelFileName = "Offer.xlsx";

    static void Main(string[] args)
    {
        // Deletes any existing excel file
        var fi = new FileInfo(generatedExcelFileName);
        if (fi.Exists)
        {
            fi.Delete();
        }

        using (var workbookDocument = new XLWorkbook(baseTemplateFileName))
        {
            // Fills first worksheet
            // [..]

            // Gets the general terms from the word and injects them into the second worksheet
            using (WordprocessingDocument wordprocessingDocument = WordprocessingDocument.Open(generalTermsFileName, true))
            {
                // Extracts the paragraphs
                var paragraphs = wordprocessingDocument.MainDocumentPart.Document.Body
                    .Descendants<Paragraph>()
                    .Where(p => !String.IsNullOrWhiteSpace(p.InnerText))
                    .ToList();

                // Gets the second worksheet
                var conditionsWS = workbookDocument.Worksheet(2);

                // Injects the paragraphs in the worksheets
                PrintTerms(conditionsWS, paragraphs);
            }

            // Saves the excel
            workbookDocument.SaveAs(generatedExcelFileName, true);
        }

        // Opens the generated excel with MS Excel
        Process.Start(generatedExcelFileName);
    }

    /// <summary>
    /// Adds the specified paragraph into the specified worksheet according this methodology:
    /// - Splits the paragraphs into two columns 
    /// - Fills the excel columns alternatively in order to looks like a two columns word text
    /// - Manage the break page
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="generalTerms"></param>
    static void PrintTerms(IXLWorksheet ws, List<Paragraph> generalTerms)
    {
        var currentParagraphIndex = 0;
        var index = 1;

        while (currentParagraphIndex < generalTerms.Count)
        {
            var nbChar = 0;
            var currentParagraphs = generalTerms.Skip(currentParagraphIndex).TakeWhile(p => (nbChar += p.InnerText.Length) < nbCharByPage).ToList();

            var row = ((int)Math.Ceiling(index / 2d) - 1) * nbRowByPage + 1;
            var column = index % 2 == 0 ? 3 : 1;

            var currentCell = ws.Cell(row, column);
            currentCell.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Justify);
            currentCell.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
            currentCell.Style.Font.SetFontName("Arial");
            currentCell.Style.Font.SetFontSize(8);

            foreach (var paragraph in currentParagraphs)
            {
                foreach (var run in paragraph.Descendants<Run>())
                {
                    var text = currentCell.RichText.AddText(run.InnerText);

                    if (run.RunProperties.Bold != null)
                    {
                        text.SetBold();
                    }

                    if (run.RunProperties.Italic != null)
                    {
                        text.SetItalic();
                    }

                    if (run.RunProperties.Underline != null)
                    {
                        text.SetUnderline();
                    }
                }

                // Break line
                currentCell.RichText.AddNewLine();
                currentCell.RichText.AddNewLine();
            }

            ws.Range(row, column, row + nbRowByPage - 1, column).Merge();

            index++;
            currentParagraphIndex += currentParagraphs.Count();
        }
    }
}

To run this code, you will need to put at the root of the console application the two following files: terms and template (check copy if newer in the files properties).

I also tried a lot of combination like:

Setting the workbook global style:

workbookDocument.Style
    .Font.SetFontName("Arial")
    .Font.SetFontSize(8);

Setting the worksheet style:

offerWS.Style
    .Font.SetFontName("Arial")
    .Font.SetFontSize(8);

Setting the cell's RichText style:

generalTermsCell.RichText
    .SetFontName("Arial")
    .SetFontSize(8);

All of this, at the begining or at the ending of the file modification. Nothing works.

I don't think it is a bug into ClosedXML. Excel does not ask me for saving the changes when leaving the app if I had opened the second worksheet. So the generated files do not seem corrupted. It looks like an Excel bug, but may be there is a way in ClosedXML to workaround that?

0

There are 0 best solutions below