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 =
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.Cells[1,1].Value = "Job Estimate Press";
ws1.Cells[1, 1].Value = "Saving Sheet";
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);
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);
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);
logo.SetSize(logoWidth,
logoHeight);
int headerStartRow = 8;
int headerStartColumn = logoTopLeftColumn +
logoWidth + 1;
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 =
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);
logo.SetSize(logoWidth,
logoHeight);
int headerStartRow = 8;
int headerStartColumn = logoTopLeftColumn +
logoWidth + 1;
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++;
}
}