public void ExportLeadProjectToExcel(int page, int size, string project, string emailCustomer, string phoneCustomer, string fromDate, string toDate) { // Get the data to report on var fromDateTrue = string.Empty; var toDateTrue = string.Empty; if (!string.IsNullOrEmpty(fromDate)) { fromDateTrue = fromDate.Split('/')[2] + '-' + fromDate.Split('/')[1] + '-' + fromDate.Split('/')[0]; } if (!string.IsNullOrEmpty(toDate)) { toDateTrue = toDate.Split('/')[2] + '-' + toDate.Split('/')[1] + '-' + toDate.Split('/')[0]; } var model = new LeadProjectViewModel(); var paramss = new DynamicParameters(); paramss.Add("page", page); paramss.Add("size", size); paramss.Add("project", project); paramss.Add("emailCustomer", emailCustomer); paramss.Add("phoneCustomer", phoneCustomer); paramss.Add("fromDate", fromDateTrue); paramss.Add("toDate", toDateTrue); paramss.Add("out", dbType: DbType.Int32, direction: ParameterDirection.Output); _sql.Open(); var data = _sql.Query<MbndCommonContactModel>("GetCommonContacts", paramss, commandType: CommandType.StoredProcedure); _sql.Close(); model.LeadProjects = data.ToList(); var total = paramss.Get<int>("out"); model.TotalRecords = total; var totalTemp = Convert.ToDecimal(model.TotalRecords) / Convert.ToDecimal(size); var totalPages = Convert.ToInt32(Math.Ceiling(totalTemp)); model.TotalPages = totalPages; model.CurrentPage = page; model.PageSize = size; // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else currencyCellStyle.DataFormat = formatId; #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else detailCurrencySubtotalCellStyle.DataFormat = formatId; #endregion #endregion #region Master sheet var sheet = workbook.CreateSheet("Project"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Project"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Tel"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Email"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Room"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Finance"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Ip"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Email Received Lead"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows foreach (var master in model.LeadProjects) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(master.Id); row.CreateCell(1).SetCellValue(master.Project); row.CreateCell(2).SetCellValue(master.Name); row.CreateCell(3).SetCellValue(master.Tel); row.CreateCell(4).SetCellValue(master.Email); row.CreateCell(5).SetCellValue(master.Type); row.CreateCell(6).SetCellValue(master.Room); row.CreateCell(7).SetCellValue(master.Finance); row.CreateCell(8).SetCellValue(master.DateTime.ToString("dd/MM/yyyy")); row.CreateCell(9).SetCellValue(master.Ip); row.CreateCell(10).SetCellValue(master.EmailCustomer); rowIndex++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("leadproject-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
public ActionResult LeadProjectLoadData(int page, int size, string project, string emailCustomer, string phoneCustomer, string fromDate, string toDate) { var fromDateTrue = string.Empty; var toDateTrue = string.Empty; if (!string.IsNullOrEmpty(fromDate)) { fromDateTrue = fromDate.Split('/')[2] + '-' + fromDate.Split('/')[1] + '-' + fromDate.Split('/')[0]; } if (!string.IsNullOrEmpty(toDate)) { toDateTrue = toDate.Split('/')[2] + '-' + toDate.Split('/')[1] + '-' + toDate.Split('/')[0]; } var model = new LeadProjectViewModel(); var paramss = new DynamicParameters(); paramss.Add("page", page); paramss.Add("size", size); paramss.Add("project", project); paramss.Add("emailCustomer", emailCustomer); paramss.Add("phoneCustomer", phoneCustomer); paramss.Add("fromDate", fromDateTrue); paramss.Add("toDate", toDateTrue); paramss.Add("out", dbType: DbType.Int32, direction: ParameterDirection.Output); _sql.Open(); var data = _sql.Query<MbndCommonContactModel>("GetCommonContacts", paramss, commandType: CommandType.StoredProcedure); _sql.Close(); model.LeadProjects = data.ToList(); var total = paramss.Get<int>("out"); model.TotalRecords = total; var totalTemp = Convert.ToDecimal(model.TotalRecords) / Convert.ToDecimal(size); var totalPages = Convert.ToInt32(Math.Ceiling(totalTemp)); model.TotalPages = totalPages; model.CurrentPage = page; model.PageSize = size; return PartialView("Partial/_LeadProject", model); }