Click here to Skip to main content
15,846,899 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I'm facing an issue. My Company logo is overlapping the header data, but I want header data next to Company logo.

What I have tried:

Here is my Export Excel Data Code =
C#
private void ExportToexcel(DataTable dt, DataTable dtMaster, DataTable dtpreview, int jEPID)
   {
       try
       {
           System.Data.DataTable table = dt;
           System.Data.DataTable tables = dtpreview;
           DataTable Groups = new DataTable();
           int StartRow = 1;
           int EndRow = table.Rows.Count;
           int EndRows = tables.Rows.Count;
           int StartColumn = 1;
           int startRew = 0;
           int EndColumn = table.Columns.Count;
           int EndColumns = tables.Columns.Count;

           string logoPath =
             Server.MapPath("~/Images/UploadedImages/RGBlueLogo.png");
           byte[] logoBytes = File.ReadAllBytes(logoPath);
           string logoBase64 = Convert.ToBase64String(logoBytes);
           string logoDataUri = "data:image/png;base64," + logoBase64;

           using (ExcelPackage pck = new ExcelPackage())
           {
               DataTable dtHeaderData = dtMaster;
               DataTable dtReportData = dt;
               DataTable dtPreviewData = dtpreview;

               ExcelWorksheet ws = pck.Workbook.Worksheets.Add
                                   ("Job Estimate Press ");
               ExcelWorksheet ws1 = pck.Workbook.Worksheets.Add
                                    ("Saving Sheet");

               // ws.View.ShowGridLines = false;

               ws.Cells[1,1].Value = "Job Estimate Press";
               ws1.Cells[1, 1].Value = "Saving Sheet";

               //setReportInfoSection(ws, StaticCol, ValueCol);
               DataTable dtHeader = GetReportHeader(ws, ws1, dtHeaderData);

               ws.Cells[2, 1].LoadFromDataTable(dtHeader, false);
               ws1.Cells[2, 1].LoadFromDataTable(dtHeader, false);

               ExcelReportStylev2.ReportTitle(ws,ws1);

               ExcelReportStylev2.ReportHeaderPanelBackground
                        (ws, 1, 1, dtHeader.Rows.Count + 1, 20);
               ExcelReportStylev2.ReportLabel
                        (ws, 2, 1, dtHeader.Rows.Count + 1, 1);
               ExcelReportStylev2.ReportValue
                        (ws, 2, 2, dtHeader.Rows.Count + 1, 2);

               ExcelReportStylev2.ReportDataHeader
               (ws, dtHeader.Rows.Count + 3, 1, dtHeader.Rows.Count + 3, 20);
               ExcelReportStylev2.ReportData
               (ws, dtHeader.Rows.Count + 4, 1, dtHeader.Rows.Count + 4 +
                dtReportData.Rows.Count, 20);
               ManipulateReportColsAndAddNetTotal
               (ws, dtHeader.Rows.Count + 3 + dtReportData.Rows.Count, 1,
                dtHeader.Rows.Count + 3 + dtReportData.Rows.Count,
                dtReportData.Columns.Count);

               //PreviewDetail
               ExcelReportStylev3.ReportHeaderPanelBackgrounds
                (ws1, 1, 1, dtHeader.Rows.Count + 1, 13);
               ExcelReportStylev3.ReportLabels
                (ws1, 2, 1, dtHeader.Rows.Count + 1, 1);
               ExcelReportStylev3.ReportValues
                (ws1, 2, 2, dtHeader.Rows.Count + 1, 2);

               ExcelReportStylev3.ReportDataHeaders
               (ws1, dtHeader.Rows.Count + 3, 1, dtHeader.Rows.Count + 3, 13);
               ExcelReportStylev3.ReportDatas
               (ws1, dtHeader.Rows.Count + 4, 1, dtHeader.Rows.Count + 4 +
                dtPreviewData.Rows.Count, 13);
               ManipulateReportColsAndAddNetTotals
               (ws1, dtHeader.Rows.Count + 3 + dtPreviewData.Rows.Count, 1,
                dtHeader.Rows.Count + 3 + dtPreviewData.Rows.Count,
                dtPreviewData.Columns.Count);

               int cntHeaderRows = dtHeader.Rows.Count;
               int cntReportDataRows = dtReportData.Rows.Count;
               int cntReportDatasRowss = dtPreviewData.Rows.Count;

               using (ExcelRange rng = ws.Cells[cntHeaderRows + 4, 4,
               cntHeaderRows + 4 + cntReportDataRows,
               dtReportData.Columns.Count])
               {
                   rng.Style.Numberformat.Format = "#,##0.00";
               }
               using (ExcelRange rng = ws1.Cells[cntHeaderRows + 4, 4,
               cntHeaderRows + 4 + cntReportDatasRowss,
               dtPreviewData.Columns.Count])
               {
                   rng.Style.Numberformat.Format = "#,##0.00";
               }

               AddDetailData(ws, dtReportData, dtHeader.Rows.Count);

               AddDetailDataForSavingSheet
                  (ws1, dtPreviewData, dtHeader.Rows.Count);

               // Add logo to the worksheet
               int logoTopLeftRow = 8;
               int logoTopLeftColumn = 8;
               int logoWidth = 150;
               int logoHeight = 50;

               Byte[] imageBytes = Convert.FromBase64String(logoBase64);
               using (MemoryStream ms = new MemoryStream(imageBytes))
               {
                   System.Drawing.Image image =
                           System.Drawing.Image.FromStream(ms);
                   var logo = ws.Drawings.AddPicture("CompanyLogo", image);
                   logo.SetPosition(logoTopLeftRow,
                   logoTopLeftColumn);  // Adjust the position as needed
                   logo.SetSize(logoWidth,
                        logoHeight);   // Set the size of the logo

                   // Determine start row and column for header data
                   int headerStartRow = 8; // Set this to the row where
                                // you want to start the header data
                   int headerStartColumn = logoTopLeftColumn +
                   logoWidth + 1; // Add 1 for the space between logo and header data

                   // Populate header data next to the logo
                   int currentRow = headerStartRow;
                   foreach (DataRow row in dtHeader.Rows)
                   {
                       ws.Cells[currentRow, headerStartColumn].Value =
                           row["paraName"].ToString();
                       ws.Cells[currentRow, headerStartColumn + 1].Value =
                           row["paraValue"].ToString();
                       currentRow++;
                   }
               }

               Response.Clear();
               Response.ContentType =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
               Response.AddHeader("content-disposition", "attachment;
               filename=JobEstimatePress.xlsx");
               Response.BinaryWrite(pck.GetAsByteArray());
               Response.Flush();
           }
       }
       catch (Exception ex)
       {

       }
   }

And this is my logo creation Code =
C#
// Add logo to the worksheet
                int logoTopLeftRow = 8;
                int logoTopLeftColumn = 8;
                int logoWidth = 150;
                int logoHeight = 50;

                Byte[] imageBytes = Convert.FromBase64String(logoBase64);
                using (MemoryStream ms = new MemoryStream(imageBytes))
                {
                    System.Drawing.Image image = 
                         System.Drawing.Image.FromStream(ms);
                    var logo = ws.Drawings.AddPicture("CompanyLogo", image);
                    logo.SetPosition(logoTopLeftRow, 
                       logoTopLeftColumn);  // Adjust the position as needed
                    logo.SetSize(logoWidth, 
                       logoHeight);   // Set the size of the logo

                    // Determine start row and column for header data
                    int headerStartRow = 8; // Set this to the row where 
                               // you want to start the header data
                    int headerStartColumn = logoTopLeftColumn + 
                    logoWidth + 1; // Add 1 for the space between logo and header data

                    // Populate header data next to the logo
                    int currentRow = headerStartRow;
                    foreach (DataRow row in dtHeader.Rows)
                    {
                        ws.Cells[currentRow, headerStartColumn].Value = 
                                 row["paraName"].ToString();
                        ws.Cells[currentRow, headerStartColumn + 1].Value = 
                                 row["paraValue"].ToString();
                        currentRow++;
                    }                   
                }
Posted
Updated 27-Oct-23 8:22am
v3
Comments
Richard Deeming 26-Oct-23 5:10am    
Are you absolutely sure it's overlapping the header data? According to your code, the header data starts in column 159 (FC in Excel's address format). Perhaps you just need to scroll further to the right?
Mr Talha saeed 26-Oct-23 6:16am    
Yes, How can I fixed this issue

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900