private string CreateExport(DataTable filtered_data)
{
string FileExtension = "EXCEL";
DateTime now = DateTime.Now;
string user_id = HttpContext.Current.Session["employee_id"].ToString();
Warning[] warnings = null;
string[] streamids = null;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string file_name = "LeaveRegisterReport" + user_id + "-" + now.ToString("yyyyMMdd_HHmmss") + ".";
byte[] bytes = null;
ReportDataSource rds = new ReportDataSource("LeaveRegister_templregister1", filtered_data);
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/exports/templates/lrgster.rdlc");
#region Code for showing logo
HttpContext context = Context;
string imageUrl = context.Server.MapPath("~/uploads/CompanyLogo/");
DBConnection db_connection = new DBConnection();
string query = "select compname from templregister1";
string companyName = db_connection.ExecuteQuery_WithReturnValueString(query);
CompanyLogoStuff companyLogoStuff = new CompanyLogoStuff();
string companyCode = companyLogoStuff.getCompanyImageUrl(companyName);
imageUrl = imageUrl + companyCode;
viewer.LocalReport.EnableExternalImages = true;
ReportParameter[] params1 = new ReportParameter[1];
params1[0] = new ReportParameter("image_path", "file:///" + imageUrl, false);
viewer.LocalReport.SetParameters(params1);
#endregion //commented after anand modifications
viewer.LocalReport.DataSources.Add(rds);
bytes = viewer.LocalReport.Render(FileExtension, null, out mimeType, out encoding, out extension, out streamids, out warnings);
using (FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/exports/data/").ToString() + file_name + extension, FileMode.Create))
{
fs.Write(bytes, 0, bytes.Length);
}
return(file_name + extension);
}
private string CreateExport(DataTable filtered_data, string companyname)
{
string FileExtension = "EXCEL";
DateTime now = DateTime.Now;
string user_id = HttpContext.Current.Session["employee_id"].ToString();
Warning[] warnings = null;
string[] streamids = null;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string file_name = "PayRollLinkReport" + user_id + "-" + now.ToString("yyyyMMdd_HHmmss") + ".";
byte[] bytes = null;
ReportDataSource rds = new ReportDataSource("PayrollLink_Payrolllinkreport1", filtered_data);
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/exports/templates/payroll_link_report.rdlc");
#region Code for showing logo
HttpContext context = Context;
string imageUrl = context.Server.MapPath("~/uploads/CompanyLogo/");
CompanyLogoStuff companyLogoStuff = new CompanyLogoStuff();
string companyCode = companyLogoStuff.getCompanyImageUrl(companyname);
imageUrl = imageUrl + companyCode;
viewer.LocalReport.EnableExternalImages = true;
ReportParameter[] params1 = new ReportParameter[1];
params1[0] = new ReportParameter("image_path", "file:///" + imageUrl, false);
viewer.LocalReport.SetParameters(params1);
#endregion
viewer.LocalReport.DataSources.Add(rds);
bytes = viewer.LocalReport.Render(FileExtension, null, out mimeType, out encoding, out extension, out streamids, out warnings);
using (FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/exports/data/").ToString() + file_name + extension, FileMode.Create))
{
fs.Write(bytes, 0, bytes.Length);
}
return(file_name + extension);
}
public static void ExportDataToExcel(string file_name, string report_title, DataTable data, HttpContext context, string[] column_names, string company_name)
{
string imagePath = context.Server.MapPath("~/uploads/CompanyLogo/");
DBConnection db_connection = new DBConnection();
DataTable leave_status = new DataTable();
string query = string.Empty;
string status_description = string.Empty;
var work_book = new XLWorkbook();
var work_sheet = work_book.Worksheets.Add("Sheet 1");
var dataForExport = new List <string[]>();
string export_path = context.Server.MapPath("~/exports/data/");
int counter = 0, data_count = 0;
DataTable totals = new DataTable();
var title_styles = work_book.Style;
data_count = data.Rows.Count;
// Background color for the cell
title_styles.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
// Font configuration
title_styles.Font.Bold = true;
title_styles.Font.FontColor = XLColor.White;
title_styles.Font.FontSize = 30;
// Text Alignment
title_styles.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// Adding the Title in the Cell A1,1
work_sheet.Cell(1, 1).Value = report_title;
// Get workbook range for Title
var title_range = work_sheet.Range(1, 1, 3, 24);
// Merge the cells in the above range to form the title.
title_range.Merge();
// Apply the styles defined before
title_range.Style = title_styles;
// For Showing Company Name below report title
work_sheet.Cell(4, 1).Value = "Company: " + company_name;
work_sheet.Range(4, 1, 4, 20).Style.Font.Bold = true;
work_sheet.Range(4, 1, 4, 20).Style.Font.FontSize = 16;
// Manually adding the column names, as the data adding functions don't display the column names.
for (counter = 0; counter < column_names.Length; counter++)
{
work_sheet.Cell(5, counter + 1).Value = column_names[counter];
work_sheet.Cell(5, counter + 1).Style.Font.Bold = true;
work_sheet.Cell(5, counter + 1).Style.Font.FontSize = 16;
}
// Adding the datatable to the selected range
work_sheet.Cell(6, 1).Value = data.AsEnumerable();
// Added for showing various count //
if (report_title == "DAILY PERFORMANCE REPORT")
{
work_sheet.Cell(7 + data_count, 7).Value = "Summary Sheet";
work_sheet.Cell(7 + data_count, 7).Style.Font.Bold = true;
work_sheet.Cell(7 + data_count, 7).Style.Font.FontSize = 16;
// Getting all the calculated data from function
query = "select * from [FetchTotalCounts]('1')";
totals = db_connection.ReturnDataTable(query);
work_sheet.Cell(8 + data_count, 1).Value = "Total Count";
work_sheet.Cell(8 + data_count, 1).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 1).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 3).Value = "Days";
work_sheet.Cell(8 + data_count, 3).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 3).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 4).Value = totals.Rows[0]["TotalDays"].ToString();
work_sheet.Cell(8 + data_count, 6).Value = "Employees";
work_sheet.Cell(8 + data_count, 6).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 6).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 7).Value = totals.Rows[0]["TotalEmployee"].ToString();
work_sheet.Cell(8 + data_count, 9).Value = "Work Hours";
work_sheet.Cell(8 + data_count, 9).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 9).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 10).Value = totals.Rows[0]["Total_Hours"].ToString();
work_sheet.Cell(8 + data_count, 12).Value = "Early Hours";
work_sheet.Cell(8 + data_count, 12).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 12).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 13).Value = totals.Rows[0]["Total_Earlyby"].ToString();
work_sheet.Cell(8 + data_count, 15).Value = "Late Hours";
work_sheet.Cell(8 + data_count, 15).Style.Font.Bold = true;
work_sheet.Cell(8 + data_count, 15).Style.Font.FontSize = 10;
work_sheet.Cell(8 + data_count, 16).Value = totals.Rows[0]["Total_Lateby"].ToString();
}
status_description = "Description:- P=Present, A=Absent, L=Leave, AHL=Half Absent Half Leave, PHL=Half Day Present Half Day Leave, V=Vacation, OD=On Duty,CO=Comp. Off,MI=Manual in punch,MO=Manual Out Punch,M=Manual Punch,MS=Missing Swipe";
//getting Leave code description
leave_status = db_connection.ReturnDataTable("select leavecode,status from leavemaster where CompanyCode in ( select companycode from CompanyMaster where CompanyName='" + company_name + "') ");
//leaveStatus
string leaveCodeColumn = string.Empty, leaveStatusColumn = string.Empty;
string leaveStatus = string.Empty;
int count = 0;
foreach (DataRow row in leave_status.Rows)
{
count++;
foreach (DataColumn col in leave_status.Columns)
{
leaveCodeColumn = row["leavecode"].ToString();
leaveStatusColumn = row["status"].ToString();
}
if (count == 1)
{
leaveStatus = leaveCodeColumn + " = " + leaveStatusColumn;
}
else
{
leaveStatus = leaveStatus + "," + leaveCodeColumn + " = " + leaveStatusColumn;
}
}
var description_styles = work_book.Style;
description_styles.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
description_styles.Font.Bold = false;
description_styles.Font.FontColor = XLColor.AliceBlue;
description_styles.Font.FontSize = 11;
description_styles.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
work_sheet.Cell(9 + data_count, 1).Value = status_description;
// description_range = work_sheet.Range(10 + data_count, 1, 10 + data_count, 24);
work_sheet.Range(9 + data_count, 1, 9 + data_count, 24).Merge();
work_sheet.Range(9 + data_count, 1, 9 + data_count, 24).Style = description_styles;
work_sheet.Cell(11 + data_count, 1).Value = leaveStatus;
work_sheet.Range(11 + data_count, 1, 11 + data_count, 21).Style = description_styles;
work_sheet.Range(11 + data_count, 1, 11 + data_count, 15).Merge();
work_book.SaveAs(export_path + file_name);
CompanyLogoStuff companyStuff = new CompanyLogoStuff();
imagePath = imagePath + companyStuff.getCompanyImageUrl(company_name);
string fullPath = export_path + file_name;
// createLogo(fullPath, imagePath);
}