public ActionResult RegisterFastLoadData(int page, int size, int source, int status, string sale, 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 RegisterFastViewModel();
var paramss = new DynamicParameters();
paramss.Add("page", page);
paramss.Add("size", size);
paramss.Add("source", source);
paramss.Add("status", status);
paramss.Add("sale", sale);
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<RegisterFastModel>("GetRegisterFasts", paramss, commandType: CommandType.StoredProcedure);
_sql.Close();
model.RegisterFasts = 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;
model.Saleses = new List<Sales>
{
new Sales { FulName = "Xuan Tran", Email = "*****@*****.**" },
new Sales { FulName = "My Dang", Email = "*****@*****.**" },
new Sales { FulName = "Tien Hoang", Email = "*****@*****.**" },
new Sales { FulName = "Thuy Trang", Email = "*****@*****.**" },
new Sales { FulName = "Son Tung", Email = "*****@*****.**" },
new Sales { FulName = "Tuyen Pham", Email = "*****@*****.**" },
new Sales { FulName = "Cuc Nhu", Email = "*****@*****.**" },
new Sales { FulName = "Minh Dong", Email = "*****@*****.**" },
new Sales { FulName = "Kim Hue", Email = "*****@*****.**" },
new Sales { FulName = "Hong Vi", Email = "*****@*****.**" },
new Sales { FulName = "Yen My", Email = "*****@*****.**" },
new Sales { FulName = "Thu Ha", Email = "*****@*****.**" },
new Sales { FulName = "Kim Ngan", Email = "*****@*****.**" },
new Sales { FulName = "Thuy Nguyen", Email = "*****@*****.**" },
new Sales { FulName = "Đoan Trang", Email = "*****@*****.**" },
new Sales { FulName = "Thua Trang", Email = "*****@*****.**" },
new Sales { FulName = "Co Thuy", Email = "*****@*****.**" },
new Sales { FulName = "Tam Thang", Email = "*****@*****.**" },
new Sales { FulName = "Nguyet Minh", Email = "*****@*****.**" },
new Sales { FulName = "The Cung", Email = "*****@*****.**" },
new Sales { FulName = "Le Hoan", Email = "*****@*****.**" },
new Sales { FulName = "Thu Thuy", Email = "*****@*****.**" },
new Sales { FulName = "Thanh Trinh", Email = "*****@*****.**" },
new Sales { FulName = "Nhu Ngoc", Email = "*****@*****.**" },
new Sales { FulName = "Hanh Dung", Email = "*****@*****.**" }
};
return PartialView("Partial/_RegisterFast", model);
}
public void ExportFastRegisterToExcel(int page, int size, int source, int status, string sale, 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 RegisterFastViewModel();
var paramss = new DynamicParameters();
paramss.Add("page", page);
paramss.Add("size", size);
paramss.Add("source", source);
paramss.Add("status", status);
paramss.Add("sale", sale);
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<RegisterFastModel>("GetRegisterFasts", paramss, commandType: CommandType.StoredProcedure);
_sql.Close();
model.RegisterFasts = 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("Register");
// 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("Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Phone");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("City");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("District");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Status");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Created");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Source");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Assigned");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var master in model.RegisterFasts)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(master.Id);
row.CreateCell(1).SetCellValue(master.Name);
row.CreateCell(2).SetCellValue(master.Email);
row.CreateCell(3).SetCellValue(master.Phone);
row.CreateCell(4).SetCellValue(master.City);
row.CreateCell(5).SetCellValue(master.District);
row.CreateCell(6).SetCellValue(master.StatusText);
row.CreateCell(7).SetCellValue(master.Created.ToString("dd/MM/yyyy"));
row.CreateCell(8).SetCellValue(master.SourceText);
row.CreateCell(9).SetCellValue(master.Sale);
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("register-{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();
}
}
