EPPlus columns is not fit

EPPlus usage

Noncommercial use

Environment

C#

Epplus version

6.2.7

Spreadsheet application

Excel

Description

Since the texts I use when printing to Excel are long, for example, when I write a long label in line A1, I need to automatically expand that line again after taking the printout, but expanding does not work for me.

ExcelRange rangeW = worksheet.Cells["A1:L15"];

rangeW.AutoFitColumns();

Jun 28 '24 08:06 EmirhanBarlas

Try using worksheet.Cells["A1:L15"].AutoFitColumns();

Jun 28 '24 10:06 AdrianEPPlus

Try using worksheet.Cells["A1:L15"].AutoFitColumns();

Nothing noticed, still the same...

Jun 28 '24 10:06 EmirhanBarlas

That's odd. Can you upload a workbook with your issue for us to test with?

Jun 28 '24 11:06 AdrianEPPlus

of course, thank you in advance

Jun 28 '24 11:06 EmirhanBarlas

`void SaveToExcel(string musteriNO, string date, string BOM, string METRE, string bom1, string bom2, string bom3, string bom4, string bom5, string desenNO1, string desenNO2, string desenNO3, string desenNO4, string desenNO5, string metre1, string metre2, string metre3, string metre4, string metre5, string sarım1, string sarım2, string sarım3, string sarım4, string sarım5, string topuz1, string topuz2, string topuz3, string topuz4, string topuz5) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel Files|*.xlsx"; saveFileDialog.Title = "EXCEL DOSYASINI KAYDET " + musteriNO + " " + date; saveFileDialog.FileName = "BUFERA_SIPARIS_" + musteriNO + "_" + date + ".xlsx";

if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
    string filePath = saveFileDialog.FileName;
    FileInfo file = new FileInfo(filePath);
    using (ExcelPackage package = new ExcelPackage(file))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("SİPARİS");


        ExcelRange rangefont16 = worksheet.Cells["F2"];
        ExcelRange rangefont = worksheet.Cells["C2,C3,B5,D5,G5,I5,L5,K2,K3"];

        rangefont.Style.Font.Bold = true;
        rangefont.Style.Font.Name = "Arial Black";
        rangefont.Style.Font.Size = 12;
        rangefont.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        rangefont.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

        rangefont16.Style.Font.Bold = true;
        rangefont16.Style.Font.Name = "Arial Black";
        rangefont16.Style.Font.Size = 16;
        rangefont16.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        rangefont16.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

        ExcelRange rangeST = worksheet.Cells["B7:L7,B8:L8,B9:L9,B10:L10,B11:L11,B5:B6,D5:D6,G5:G6,I5:I6,L5:L6"]; 
        ExcelRange rangeLR = worksheet.Cells["B7,B8,B9,B10,B11,D7,D8,D9,D10,D11,G7,G8,G9,G10,G11,I7,I8,I9,I10,I11,L7,L8,L9,L10,L11,B5,B6,D5,D6,G5,G6,I5,I6,L5,L6"];
        ExcelRange rangeW = worksheet.Cells["A1:L15"];

        rangeST.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        rangeST.Style.Border.Top.Style = ExcelBorderStyle.Thin;

        rangeLR.Style.Border.Right.Style = ExcelBorderStyle.Thin;
        rangeLR.Style.Border.Left.Style = ExcelBorderStyle.Thin;

        worksheet.Cells["A1:L15"].AutoFitColumns();

        worksheet.Cells["C2"].Value = "BOM:";
        worksheet.Cells["C3"].Value = "METRE:";
        worksheet.Cells["B5"].Value = "BOM";
        worksheet.Cells["D5"].Value = "DESEN NO";
        worksheet.Cells["G5"].Value = "METRE";
        worksheet.Cells["I5"].Value = "SARIM ŞEKLİ";
        worksheet.Cells["L5"].Value = "TOP UZUNLUĞU";
        worksheet.Cells["K2"].Value = "TARİH:";
        worksheet.Cells["K3"].Value = "MÜŞTERİ NO:";
        worksheet.Cells["F2"].Value = "BUFERA SİPARİS KARTI";

        worksheet.Cells["B7"].Value = bom1;
        worksheet.Cells["B8"].Value = bom2;
        worksheet.Cells["B9"].Value = bom3;
        worksheet.Cells["B10"].Value = bom4;
        worksheet.Cells["B11"].Value = bom5;
        worksheet.Cells["D7"].Value = desenNO1;
        worksheet.Cells["D8"].Value = desenNO2;
        worksheet.Cells["D9"].Value = desenNO3;
        worksheet.Cells["D10"].Value = desenNO4;
        worksheet.Cells["D11"].Value = desenNO5;
        worksheet.Cells["G7"].Value = metre1;
        worksheet.Cells["G8"].Value = metre2;
        worksheet.Cells["G9"].Value = metre3;
        worksheet.Cells["G10"].Value = metre4;
        worksheet.Cells["G11"].Value = metre5;
        worksheet.Cells["I7"].Value = sarım1;
        worksheet.Cells["I8"].Value = sarım2;
        worksheet.Cells["I9"].Value = sarım3;
        worksheet.Cells["I10"].Value = sarım4;
        worksheet.Cells["I11"].Value = sarım5;
        worksheet.Cells["L7"].Value = topuz1;
        worksheet.Cells["L8"].Value = topuz2;
        worksheet.Cells["L9"].Value = topuz3;
        worksheet.Cells["L10"].Value = topuz4;
        worksheet.Cells["L11"].Value = topuz5;
        worksheet.Cells["L2"].Value = date;
        worksheet.Cells["L3"].Value = musteriNO;
        worksheet.Cells["D2"].Value = BOM;
        worksheet.Cells["D3"].Value = METRE;
        package.Save();
    }
    MessageBox.Show("Veriler Excel dosyasına kaydedildi.\nDosya yolunuz: " + filePath, "EXCEL DOSYANIZ KAYDEDİLDİ " + date, MessageBoxButtons.OK, MessageBoxIcon.Information);
}

}`

Jun 28 '24 11:06 EmirhanBarlas

It seems that you are using worksheet.Cells["A1:L15"].AutoFitColumns(); before you set the values in your cell. Autofit sets the width of a column based on the contents of its cells. Call the autofit method after assiging the values to your cells.

Jun 28 '24 11:06 AdrianEPPlus

Are there any changes to sending for example?

Jun 28 '24 11:06 EmirhanBarlas

Okay, I understand, so it has to be in stock for me to call it, right?

Jun 28 '24 11:06 EmirhanBarlas

Sorry, I don't understand what you mean.

Here is a small piece of your code with the autofit call after setting the values to the cells.

        worksheet.Cells["L11"].Value = topuz5;
        worksheet.Cells["L2"].Value = date;
        worksheet.Cells["L3"].Value = musteriNO;
        worksheet.Cells["D2"].Value = BOM;
        worksheet.Cells["D3"].Value = METRE;
       
        worksheet.Cells["A1:L15"].AutoFitColumns();
       
        package.Save();
    }
    MessageBox.Show("Veriler Excel dosyasına kaydedildi.\nDosya yolunuz: " + filePath, "EXCEL DOSYANIZ KAYDEDİLDİ " + date, MessageBoxButtons.OK, MessageBoxIcon.Information);
}

Jun 28 '24 11:06 AdrianEPPlus

Does it need to be set as default before I can call the method?

Jun 28 '24 11:06 EmirhanBarlas

this code ins't working on my project

Jun 28 '24 11:06 EmirhanBarlas

ow okey i understand sorry thanks your help : )

Jun 28 '24 11:06 EmirhanBarlas

not working : ((

Jun 28 '24 11:06 EmirhanBarlas

Could you provide the .xlsx file for us to test with?

Jun 28 '24 11:06 AdrianEPPlus

yeah of course BUFERA_SIPARIS__a.xlsx

Jun 28 '24 11:06 EmirhanBarlas

The 5th line must be like this, otherwise we will have to go into it and do it manually, one by one, every time we print.

Jun 28 '24 11:06 EmirhanBarlas

This code appears to work. It correctly autofits each column so all text inside is visible. I've provided a screenshot of the resulting xlsx file.

            using var p = new ExcelPackage();
            var worksheet = p.Workbook.Worksheets.Add("Sheet1");

            ExcelRange rangefont16 = worksheet.Cells["F2"];
            ExcelRange rangefont = worksheet.Cells["C2,C3,B5,D5,G5,I5,L5,K2,K3"];

            rangefont.Style.Font.Bold = true;
            rangefont.Style.Font.Name = "Arial Black";
            rangefont.Style.Font.Size = 12;
            rangefont.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            rangefont.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            rangefont16.Style.Font.Bold = true;
            rangefont16.Style.Font.Name = "Arial Black";
            rangefont16.Style.Font.Size = 16;
            rangefont16.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            rangefont16.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            ExcelRange rangeST = worksheet.Cells["B7:L7,B8:L8,B9:L9,B10:L10,B11:L11,B5:B6,D5:D6,G5:G6,I5:I6,L5:L6"];
            ExcelRange rangeLR = worksheet.Cells["B7,B8,B9,B10,B11,D7,D8,D9,D10,D11,G7,G8,G9,G10,G11,I7,I8,I9,I10,I11,L7,L8,L9,L10,L11,B5,B6,D5,D6,G5,G6,I5,I6,L5,L6"];
            ExcelRange rangeW = worksheet.Cells["A1:L15"];

            rangeST.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            rangeST.Style.Border.Top.Style = ExcelBorderStyle.Thin;

            rangeLR.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            rangeLR.Style.Border.Left.Style = ExcelBorderStyle.Thin;

            worksheet.Cells["A1:L15"].AutoFitColumns();

            worksheet.Cells["C2"].Value = "BOM:";
            worksheet.Cells["C3"].Value = "METRE:";
            worksheet.Cells["B5"].Value = "BOM";
            worksheet.Cells["D5"].Value = "DESEN NO";
            worksheet.Cells["G5"].Value = "METRE";
            worksheet.Cells["I5"].Value = "SARIM ŞEKLİ";
            worksheet.Cells["L5"].Value = "TOP UZUNLUĞU";
            worksheet.Cells["K2"].Value = "TARİH:";
            worksheet.Cells["K3"].Value = "MÜŞTERİ NO:";
            worksheet.Cells["F2"].Value = "BUFERA SİPARİS KARTI";

            worksheet.Cells["B7"].Value = "bom1";
            worksheet.Cells["B8"].Value = "bom2";
            worksheet.Cells["B9"].Value = "bom3";
            worksheet.Cells["B10"].Value = "bom4";
            worksheet.Cells["B11"].Value = "bom5";
            worksheet.Cells["D7"].Value = "desenNO1";
            worksheet.Cells["D8"].Value = "desenNO2";
            worksheet.Cells["D9"].Value = "desenNO3";
            worksheet.Cells["D10"].Value = "desenNO4";
            worksheet.Cells["D11"].Value = "desenNO5";
            worksheet.Cells["G7"].Value = "metre1";
            worksheet.Cells["G8"].Value = "metre2";
            worksheet.Cells["G9"].Value = "metre3";
            worksheet.Cells["G10"].Value = "metre4";
            worksheet.Cells["G11"].Value = "metre5";
            worksheet.Cells["I7"].Value = "sarım1";
            worksheet.Cells["I8"].Value = "sarım2";
            worksheet.Cells["I9"].Value = "sarım3";
            worksheet.Cells["I10"].Value = "sarım4";
            worksheet.Cells["I11"].Value = "sarım5";
            worksheet.Cells["L7"].Value = "topuz1";
            worksheet.Cells["L8"].Value = "topuz2";
            worksheet.Cells["L9"].Value = "topuz3";
            worksheet.Cells["L10"].Value = "topuz4";
            worksheet.Cells["L11"].Value = "topuz5";
            worksheet.Cells["L2"].Value = "date";
            worksheet.Cells["L3"].Value = "musteriNO";
            worksheet.Cells["D2"].Value = "BOM";
            worksheet.Cells["D3"].Value = "METRE";

            worksheet.Cells["A1:L15"].AutoFitColumns();
            p.SaveAs("C:\\epplusTest\\Testoutput\\result.xlsx");

This is what the workbook looks like in Excel:

Jun 28 '24 11:06 AdrianEPPlus

It doesn't look like that for me, it looks like this

Jun 28 '24 11:06 EmirhanBarlas

I tested some more and the same code I provided above works and I get the same file output in EPPlus 7.2.0.

Are you opening the opening the correct file and not an old one?

Jun 28 '24 11:06 AdrianEPPlus

I'm using the latest version, I just downloaded it. Since I print it out, I can choose where I saved it, so it is not possible to get the same printout, the names are different.

Jun 28 '24 11:06 EmirhanBarlas

This seems to work when I try it as well. Are you using Visual Studio 2022 under Windows? Which .NET version are you using? Can you create a project and zip it that replicates this issue?

Jun 28 '24 12:06 JanKallman

i use vs 2022 and latest .net version and sorry i dont give project because its a company project :((

Jun 28 '24 12:06 EmirhanBarlas

Just create a simple project to replicate the issue.

Jun 28 '24 12:06 JanKallman

I need to put it at the bottom line of the method, it should come after the data, it should be like this as an example for friends who get errors, SOLVED.

worksheet.Cells["C3"].Value = desen; // DESEN BİLGİSİ A3 SATIRINA YAZDIRILMISTIR. worksheet.Cells["C2"].Value = makine; // MAKİNE BİLGİSİNİN B3 SATIRINA YAZDIRILMASI. worksheet.Cells["C7"].Value = cozgu; // COZGU BİLGİSİNİN C3 SATIRINA YAZDIRILMASII. worksheet.Cells["C5"].Value = varyant; worksheet.Cells["C9"].Value = metraj; worksheet.Cells["C13"].Value = iplik1; worksheet.Cells["C15"].Value = iplik2; worksheet.Cells["C17"].Value = iplik3; worksheet.Cells["C19"].Value = iplik4; worksheet.Cells["C21"].Value = iplik5; worksheet.Cells["C23"].Value = iplik6; worksheet.Cells["C25"].Value = iplik7; worksheet.Cells["L13"].Value = iplikkilo1; worksheet.Cells["L15"].Value = iplikkilo2; worksheet.Cells["L17"].Value = iplikkilo3; worksheet.Cells["L19"].Value = iplikkilo4; worksheet.Cells["L21"].Value = iplikkilo5; worksheet.Cells["L23"].Value = iplikkilo6; worksheet.Cells["L25"].Value = iplikkilo7; worksheet.Cells["I5"].Value = "1. " + sıklık1; worksheet.Cells["J5"].Value = "2. " + sıklık2; worksheet.Cells["K5"].Value = "3. " + sıklık3; worksheet.Cells["L5"].Value = "4. " + sıklık4; worksheet.Cells["I6"].Value = "5. " + sıklık5; worksheet.Cells["J6"].Value = "6. " + sıklık6; worksheet.Cells["K6"].Value = "7. " + sıklık7; worksheet.Cells["L6"].Value = "8. " + sıklık8; worksheet.Cells["K2"].Value = date; worksheet.Cells["K4"].Value = id; // FİX: insertedId// // bu kısmın kesinlikle düzenlenmesi gerekmektedir. worksheet.Cells.AutoFitColumns();

Jul 10 '24 11:07 EmirhanBarlas