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