Creating Excel with EPPlus: Set Column Widths in Pixels and Add Logo

kadir 60 Reputation points
2024-06-03T15:08:17.0233333+00:00

Hello Friends;

I have a question for you, I want to pull the data according to id and process it with excell, I even want to add a logo

but I couldn't do it somehow, the thing is that I actually want to give the size to the columns and add a logo, but every

I'm having trouble sizing, the size I want is actually

Column A 97 px

B, C, D, E, F, G, H columns will be 41px, these columns will merge and the logo will be added exactly inside these merged columns.

public ActionResult Excel(int? id)

    {

        List<TBL_GenelBilgi> FileData = db.TBL_GenelBilgi.Where(x => x.GenelBilgiid == id).ToList();

        try

        {

            // EPPlus lisanslama bağlamını ayarlama

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (var memoryStream = new MemoryStream())

            {

                using (var excelPackage = new ExcelPackage(memoryStream))

                {

                    var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");

                    foreach (var data in FileData)

                    {

                        worksheet.Cells["D7"].Value = data.GenelBilgiid;

                        worksheet.Cells["D8"].Value = data.Ad;

                        worksheet.Cells["D9"].Value = data.Soyad;

                        worksheet.Cells["D10"].Value = data.DogumYeri;

                        worksheet.Cells["D11"].Value = data.Email;

                        worksheet.Cells["D12"].Value = data.Tel;

                    }

                    var Foto = worksheet.Cells["B1:H4"];

                    Foto.Merge = true;

                    Foto.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    Foto.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    Foto.Style.Font.Bold = true;


                   

                    worksheet.Column(1).Width = 3.09;

                    worksheet.Column(2).Width = 3.09; // B sütunu genişliği

                    worksheet.Column(3).Width = 3.09; // C sütunu genişliği

                    worksheet.Column(4).Width = 3.09; // D sütunu genişliği

                    worksheet.Column(5).Width = 3.09; // E sütunu genişliği

                    worksheet.Column(6).Width = 3.09; // F sütunu genişliği

                    worksheet.Column(7).Width = 3.09; // G sütunu genişliği

                    worksheet.Column(8).Width = 3.09; // H sütunu genişliği

                    worksheet.Column(9).Width = 4;

                    worksheet.Column(10).Width = 5;

                    worksheet.Column(11).Width = 5;

                    worksheet.Column(12).Width = 5;

                    worksheet.Column(13).Width = 5;

                    worksheet.Column(14).Width = 5;

                    worksheet.Column(15).Width = 5;

                    worksheet.Column(16).Width = 5;

                    worksheet.Column(17).Width = 5;

                    worksheet.Column(18).Width = 5;

                    worksheet.Column(19).Width = 5;

                    worksheet.Column(20).Width = 5;

                    worksheet.Column(21).Width = 5;

                    worksheet.Column(22).Width = 5;

                    worksheet.Column(23).Width = 5;

                    worksheet.Column(24).Width = 5;

                    var mergeCell = worksheet.Cells["I1:X4"];

                    mergeCell.Merge = true;

                    mergeCell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    mergeCell.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    mergeCell.Style.Font.Bold = true;

                    mergeCell.Value = "İŞ BAŞVURU FORMU";

                    worksheet.Cells["A1:X1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // Ortala

                    worksheet.Cells["A1:X1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // Ortala

                    worksheet.Cells["A1:X1"].Style.Font.Bold = true; // Kalın yap

                    worksheet.DefaultRowHeight = 18;

                    worksheet.Column(2).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                    worksheet.DefaultColWidth = 20;

                    worksheet.Column(2).AutoFit();

                    excelPackage.Save();

                }

                // Excel dosyasını kullanıcıya indirmesi için sunma

                memoryStream.Position = 0;

                return File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "GenelBilgi.xlsx");

            }

        }

        catch (Exception ex)

        {

            // Hata durumunda doğrudan bir text mesajı döndür

            return new ContentResult { Content = "Excel dosyası oluşturulurken bir hata oluştu: " + ex.Message };

        }

    }
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,281 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,358 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,592 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,632 questions
0 comments No comments
{count} votes