Can the header height be adaptive

[raochenglong]

need to manually calculate the header content and dynamically set the margin information?
Environmental information:
Spire.Office:6.7.0
.net core:3.1
my code:

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromStream(excelStream);
            workbook .Worksheets[0].PageSetup.IsFitToPage = true;
            workbook .Worksheets[0].PageSetup.FitToPagesTall = 1;
            workbook .Worksheets[0].PageSetup.FitToPagesWide = 1;
            string CenterHeaderStr = "&\"Arial\"&B&16&K800000&UDEMO\nDemo诊所\nDEMO\n預設醫生";
            workbook.SaveToFile(@"D:\1.xlsx");


the save excel file

D8A5A99A-8894-43a0-99F3-5BA01053FB99.png (50.5 KiB) Viewed 2226 times

the pdf preview

F63ED615-710B-471c-8C9B-49449D7B6D93.png (120.54 KiB) Viewed 2226 times

[Annika.Zhou]

Hi,

Thank you for your inquiry.
Yes, you can set the top margin by calculating the height of the header content. Please refer to the following code. If there is any problem, please provide your sample excel file for our reference. You could attach it here or send to us via email (support@e-iceblue.com). Thanks in advance.
In addition, you are using the .NetCore platform, I suggest that you download the latest package Spire.Office.NETCore 6.8.2 in NuGet manager.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromStream(stream);
PageSetup setup = workbook.Worksheets[0].PageSetup;
setup.IsFitToPage = true;
setup.FitToPagesTall = 1;
setup.FitToPagesWide = 1;           
string CenterHeaderStr = "&\"Arial\"&B&16&K800000&UDEMO\nDemo诊所\nDEMO\n預設醫生";
setup.CenterHeader = CenterHeaderStr;           
Font font = new Font(new FontFamily("Arial"),16,FontStyle.Underline);
//Gets the font line spacing
float height = font.Height * 4;
double herders = setup.HeaderMarginInch;
//Convert the font pixel to the page margin inch
PdfUnitConvertor convertor = new PdfUnitConvertor();
float heighInInch =convertor.ConvertUnits(height, PdfGraphicsUnit.Pixel, PdfGraphicsUnit.Inch);
//Set the margins
setup.TopMargin = heighInInch + herders;
workbook.Worksheets[0].ViewMode = ViewMode.Layout;
workbook.SaveToFile("1.xlsx");

Sincerely,
Annika
E-iceblue support team

[raochenglong]

compile error
The type "FontStyle" exists in "spire. Email, version = 4.7.0.0, culture = neutral, publickeytoken = 663f351905198cb3" and "spire. PDF, version = 7.8.9.0, culture = neutral, publickeytoken = 663f351905198cb3"

[Annika.Zhou]

Hi,

You are using the .NetCore platform, the compilation error will be solved by using corresponding NETCore dll. Please remove the Spire.Office dll from your project, then directly download the Spire.Office.NETCore 6.8.2 in NuGet manager to test again.

Code: Select all

System.Drawing.Font font = new System.Drawing.Font(new System.Drawing.FontFamily("Arial"),16, System.Drawing.FontStyle.Underline);

Sincerely,
Annika
E-iceblue support team

[raochenglong]

there is my code now.

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromStream(excelStream);
            string CenterHeaderStr = "&\"Arial\"&B&16&K800000&UDEMO\nDemo诊所\nDEMO\n預設醫生";
            font = new System.Drawing.Font(family: new System.Drawing.FontFamily("PMingLiU"),
                                                           emSize: 48,
                                                           style: System.Drawing.FontStyle.Bold | System.Drawing.FontStyle.Underline);
            float height = font.Height * 4;
            //Convert the font pixel to the page margin inch
            float heighInInch = new Spire.Pdf.Graphics.PdfUnitConvertor()
                .ConvertUnits(height, Spire.Pdf.Graphics.PdfGraphicsUnit.Pixel, Spire.Pdf.Graphics.PdfGraphicsUnit.Inch);
            //Set the margins
            double headerHeight = System.Math.Round(heighInInch, 1, MidpointRounding.ToPositiveInfinity);
            if (headerHeight > wk.Worksheets[0].PageSetup.TopMargin)
            {
                wk.Worksheets[0].PageSetup.TopMargin = headerHeight;
            }
            wk.Worksheets[0].ViewMode = Spire.Xls.ViewMode.Layout;
            workbook.SaveToFile(@"D:\1.xlsx");


pdf review

5A1F649E-3E9B-48e4-9BFC-9D7B806C6A0E.png (28.35 KiB) Viewed 2151 times

the save excel

DB2BA4DB-8E2A-497b-AC25-4F894AE03FCF.png (28.49 KiB) Viewed 2151 times

but the page header is not fit.I want the header like this.

pdf review

5A1F649E-3E9B-48e4-9BFC-9D7B806C6A0E.png (28.35 KiB) Viewed 2151 times

[Annika.Zhou]

Hello,

Thank you for your feedback.
The cause of your issue is that you used the font size 48 to calculate the string height while the font size of CenterHeaderStr is 16. Besides, the code I provided before has deviation in calculating string height, please correct your code as below, this way is more accurate. If there is still any question, please provide your sample excel file for a better reference. You could attach it here or send to us via email (support@e-iceblue.com). Thanks in advance.

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromStream(stream);
            PageSetup setup = workbook.Worksheets[0].PageSetup;
            string CenterHeaderStr = "&\"Arial\"&B&16&K800000&UDEMO\nDemo诊所\nDEMO\n預設醫生";
            setup.CenterHeader = CenterHeaderStr;
            //Create sheet1 to calculate string height
            Worksheet sheet1 = workbook.Worksheets.Add("sheet2");
            sheet1.Range["A1"].Value = "預設醫生";
            sheet1.Range["A1"].Style.Font.FontName = "Arial";
            sheet1.Range["A1"].Style.Font.Underline = FontUnderlineType.Single;
            sheet1.Range["A1"].Style.Font.Size = 16;
            sheet1.Range["A1"].Style.Font.IsBold = true;
            sheet1.AutoFitRow(1);
            float height = sheet1.GetRowHeightPixels(1) * 4;

            //Convert the font pixel to the page margin inch
            PdfUnitConvertor convertor = new PdfUnitConvertor();
            float heighInInch = convertor.ConvertUnits(height, PdfGraphicsUnit.Pixel, PdfGraphicsUnit.Inch);
            double herders = setup.HeaderMarginInch;
            //Set the margins
            setup.TopMargin = heighInInch +herders;
            workbook.Worksheets[0].ViewMode = ViewMode.Layout;
            //Remove sheet1
            workbook.Worksheets.Remove(sheet1);
            //Save excel file
            workbook.SaveToFile("result.xlsx");

Sincerely,
Annika
E-iceblue support team

[Annika.Zhou]

Hello,

Hope you're doing well!
How is your issue going? Did the code we provided work for you? Any feedback will be greatly appreciated.

Sincerely,
Annika
E-iceblue support team