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); }