Worksheet columns are getting squeezed to width 0

Kaushal03 · February 20, 2023, 7:19am

We have been using the Gembox trial version in the docker container(with Linux host) but the worksheet’s columns are getting squeezed.

Below is the docker file content:

WORKDIR "/src/Benchmarking360.API"
RUN dotnet build "Benchmarking360.API.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "Benchmarking360.API.csproj" -c Release -o /app/publish /p:UseAppHost=false

FROM base AS final

# Update package sources to include supplemental packages (contrib archive area).
RUN sed -i 's/main/main contrib/g' /etc/apt/sources.list

# Downloads the package lists from the repositories.
RUN apt-get update

# Install font configuration.
RUN apt-get install -y fontconfig

# Install Microsoft TrueType core fonts.
RUN apt-get install -y ttf-mscorefonts-installer

# Or install Liberation TrueType fonts.
#RUN apt-get install -y fonts-liberation

WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "Benchmarking360.API.dll"]

Adding code for the reference:

public static ExcelFile GenerateFile(List<Sec10K> sec10Ks)
{
    SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
    // Continue to use the component in a Trial mode when free limit is reached.
    SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial;

    List<string> consolidatedTagValues = new List<string>();

    // initiate an instance of Workbook
    var book = new ExcelFile();

    var worksheet = book.Worksheets.Add("Summary of Sig Acc Polici");
    worksheet.Rows[0].Style = book.Styles[BuiltInCellStyleName.Heading4];
    worksheet.Rows[0].Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(211, 211, 211), SpreadsheetColor.FromArgb(211, 211, 211));

    worksheet.SetDefaultColumnWidth(150, LengthUnit.Pixel);
    //worksheet.Columns[0].SetWidth(500, LengthUnit.Pixel);
    worksheet.Cells[0, 0].Value = "Company";
    worksheet.Cells[0, 0].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black);
    worksheet.Columns[0].Style.WrapText = true;
    worksheet.Columns[0].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
    worksheet.Columns[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
    var cells = worksheet.Cells;


    int index = 1;
    foreach (var company in sec10Ks)
    {
        cells[index, 0].Value = company.CompanyName;
        cells[index, 0].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        index++;
        foreach (var tag in company.SignificantAccountingPolicies.Select(x => x.SourceTagName))
        {
            if (!consolidatedTagValues.Contains(tag))
            {
                consolidatedTagValues.Add(tag);
            }
        }
    }

    //columnpolicies
    int header = 1;
    for (int i = 0; i < consolidatedTagValues.Count; i++)
    {
        cells[0, header].Style.WrapText = true;
        cells[0, header].Value = consolidatedTagValues[i];


        header++;
    }


    int rowCount = 1;
    int columnCount = 1;
    for (int i = 0; i < consolidatedTagValues.Count; i++)
    {
        rowCount = 1;
        foreach (var c in sec10Ks)
        {
            if (c.SignificantAccountingPolicies.Any(item => item.SourceTagName == consolidatedTagValues[i]))
            {

                HtmlLoadOptions h = new HtmlLoadOptions();

                var htmk = "<span style='font-size:10pt;text-align:center'>&#10003;</span>";
                cells[rowCount, columnCount].SetValue(htmk, h);

            }
            else
            {
                HtmlLoadOptions h = new HtmlLoadOptions();
                var htmk = "<span style='font-size:10pt;text-align:center'>&#10007;</span>";
                cells[rowCount, columnCount].SetValue(htmk, h);

            }
            cells[rowCount, columnCount].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

            rowCount++;
        }
        columnCount++;
    }


    var hyperlinkStyle = book.Styles[BuiltInCellStyleName.Hyperlink];

    //creating sheets & adding hyperlinks to tag
    int headerTag = 1;
    for (int i = 0; i < consolidatedTagValues.Count; i++)
    {
        string sheetname = consolidatedTagValues[i];
        ExcelWorksheet m;
        if (sheetname.Length > 31)
        {
            m = book.Worksheets.Add(sheetname.Substring(0, 31));
        }
        else
        {
            m = book.Worksheets.Add(sheetname);
        }

        if (sheetname.Length > 31)
        {
            cells[0, headerTag].Hyperlink.Location = "'" + m.Name.Substring(0, 31) + "'!A1";
            cells[0, headerTag].Hyperlink.ToolTip = consolidatedTagValues[i];
            cells[0, headerTag].Style = hyperlinkStyle;
            cells[0, headerTag].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
            cells[0, headerTag].Style.VerticalAlignment = VerticalAlignmentStyle.Center;


        }
        else
        {
            cells[0, headerTag].Hyperlink.Location = "'" + m.Name + "'!A1";
            cells[0, headerTag].Hyperlink.ToolTip = consolidatedTagValues[i];
            cells[0, headerTag].Style = hyperlinkStyle;
            cells[0, headerTag].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
            cells[0, headerTag].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
        }
        cells[0, headerTag].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        headerTag++;

    }

    //set border
    var n = worksheet.GetUsedCellRange(true);
    n?.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thick);

    int sheetNo = 1;

    //filling each sheet
    for (int i = 0; i < consolidatedTagValues.Count; i++)
    {
        var sheet1 = book.Worksheets[sheetNo];
        sheet1.Rows[0].Style = book.Styles[BuiltInCellStyleName.Heading4];
        sheet1.Rows[0].Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(211, 211, 211), SpreadsheetColor.FromArgb(211, 211, 211));

        sheet1.SetDefaultColumnWidth(150, LengthUnit.Pixel);
        sheet1.Columns[0].SetWidth(35, LengthUnit.Pixel);
        sheet1.Columns[2].SetWidth(50, LengthUnit.Pixel);
        sheet1.Columns[3].SetWidth(130, LengthUnit.Pixel);

        sheet1.Rows[0].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black);
        sheet1.Columns[0].Style.WrapText = true;

        // access CellsCollection of first worksheet
        var cellsperSheet = sheet1.Cells;

        cellsperSheet["A1"].Value = "S.No";
        cellsperSheet["A1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        cellsperSheet["B1"].Value = "Company Name";
        cellsperSheet["B1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        cellsperSheet["C1"].Value = "Ticker";
        cellsperSheet["C1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        cellsperSheet["D1"].Value = "SectionName";
        cellsperSheet["D1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        cellsperSheet["E1"].Value = "OriginalTag";
        cellsperSheet["E1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        cellsperSheet["F1"].Value = "Disclosure";
        cellsperSheet["F1"].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

        sheet1.Columns[5].SetWidth(7, LengthUnit.Inch);
        sheet1.Columns[1].Style.WrapText = true;
        sheet1.Columns[3].Style.WrapText = true;
        sheet1.Columns[4].Style.WrapText = true;
        sheet1.Columns[5].Style.WrapText = true;
        sheet1.Columns[5].Style.VerticalAlignment = VerticalAlignmentStyle.Top;
        sheet1.Columns[5].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        int serialNo = 0;
        int columnC = 0;
        int rowC = 0;
        foreach (var c in sec10Ks)
        {

            if (c.SignificantAccountingPolicies.Any(x => x.SourceTagName == consolidatedTagValues[i]))
            {
                var policydet = c.SignificantAccountingPolicies.First(x => x.SourceTagName == consolidatedTagValues[i]);
                rowC++;
                serialNo++;
                cellsperSheet[rowC, columnC].Value = serialNo;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);
                cellsperSheet[rowC, (++columnC)].Value = c.CompanyName;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

                cellsperSheet[rowC, (++columnC)].Value = c.TickerId;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

                cellsperSheet[rowC, (++columnC)].Value = policydet.SectionName;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

                cellsperSheet[rowC, (++columnC)].Value = policydet.OriginalTag;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

                cellsperSheet[rowC, (++columnC)].Value = policydet.PolicyDescription;
                cellsperSheet[rowC, columnC].Style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Bottom | MultipleBorders.Top | MultipleBorders.Left, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);

            }
            columnC = 0;
        }

        CellRange? k = sheet1?.GetUsedCellRange(true);
        k?.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thick);

        sheetNo++;
    }

    return book;
}

mario.gembox · February 20, 2023, 12:10pm

Hi,

I was unable to reproduce your issue.
I’m not 100% sure, but it seems that the issue is with some other part of your code.

Just in case, please check that you have the following NuGet packages included in your Docker project:

<PackageReference Include="HarfBuzzSharp.NativeAssets.Linux" Version="*" />
<PackageReference Include="SkiaSharp.NativeAssets.Linux" Version="*" />

Also, check our Docker example:
https://www.gemboxsoftware.com/spreadsheet/examples/create-excel-pdf-on-docker-net-core/5902

If the problem remains, please send us a small Visual Studio project that reproduces the issue so that we can investigate it.

Regards,
Mario

Naina · February 20, 2023, 2:09pm

Hi Mario,

We checked the docker example that is available on the link. But even for that the column width was not the one set in the code for us.

On premise, our code generates excel with proper width but on docker the width collapses.
Surprising thing is on docker all other styling attributes like font style, background color , border are all preserved. The issue is solely with column width.
We added the package reference also but still it gives collapsed width. My colleague will share the sample project with you soon.

Kaushal03 · February 20, 2023, 6:18pm

Hi Mario, please let me know how I can attach VS solution zip folder here or do I need to send the code in email? if yes then please provide email address.

mario.gembox · February 21, 2023, 3:27am

You can send the attachment via email or a support ticket, see our Contact page.

kronx · July 11, 2023, 10:01am

Hi, I have the same problem with application running in OpenShift container.
I use method column.AutoFit(), but all columns are shrinked to width 0.
I have references to packages HarfBuzzSharp, SkiaSharp, and System.Drawing.Common but it doesn’t help.
Kaushal03, did you manage to solve this problem?

kronx · July 11, 2023, 2:27pm

I have found that my problem was in the line “sheet.Cells.Style.ShrinkToFit = false;”
Also DefaultColumnWidth() method squeeze all columns width to 0.
Also AutoFit() and SetWidth() methods don’t work in container. They don’t change anything.
Tested with GemBox.Spreadsheet library Version 49.0.1398

marko.gembox · July 14, 2023, 8:14am

Kronx, you need to make sure that the docker container has the right fonts installed. All these GemBox methods require the right font to be able to calculate widths correctly.