public ActionResult ExportTargetSales(TargetSalesDatatableViewModel model)
{
TargetSalesBusiness business = new TargetSalesBusiness();
business.SetUserAuth(ViewBag.UserAuth);
AlertMessage alert = business.ExportTargetSalesToExcel(model);
if (alert.Status == 1)
{
var bytes = alert.Data as byte[];
return(File(bytes, System.Net.Mime.MediaTypeNames.Application.Octet, string.Format("TargetSales-{0}.xlsx", DateTime.UtcNow.ToUtcID().ToString("yyyyMMdd-HHmm"))));
}
return(RedirectToAction("Index"));
}
public ActionResult DatatableIndex(TargetSalesDatatableViewModel model)
{
if (!Request.IsAjaxRequest())
{
return(RedirectToAction("Index"));
}
JDatatableResponse resp = new JDatatableResponse();
TargetSalesBusiness business = new TargetSalesBusiness();
business.SetUserAuth(ViewBag.UserAuth);
resp = business.GetDatatable(model);
return(new MyJsonResult(resp, JsonRequestBehavior.AllowGet));
}
public JDatatableResponse GetDatatable(TargetSalesDatatableViewModel model)
{
JDatatableResponse result = new JDatatableResponse();
string[] arrOrderColumn = new string[] { "RayonCode"
, "RayonCode"
, "SLM"
, "SLM"
, "FSS"
, "FSS"
, "AchiGroup"
, "Division"
, "Material"
, "Bulan"
, "Tahun"
, "Target" };
int month = 0;
int year = 0;
try
{
string[] arr = model.Periode.Split('-');
month = Convert.ToInt16(arr[0]);
year = Convert.ToInt16(arr[1]);
}
catch (Exception ex)
{
return(result);
}
if (_userAuth == null)
{
return(result);
}
List <RHHeader> rhHeader = GetRHHeaderByUserAuth();
var rCode = rhHeader.Select(r => r.RayonCode);
IRepository <SalesTarget> repo = _unitOfWork.GetRepository <SalesTarget>();
repo.Condition = PredicateBuilder.True <SalesTarget>();
repo.Condition = repo.Condition.And(x => x.Bulan == month && x.Tahun == year);
if (model.RayonCode.Equals("All"))
{
repo.Condition = repo.Condition.And(x => rCode.Contains(x.RayonCode));
}
else
{
repo.Condition = repo.Condition.And(x => x.RayonCode.Equals(model.RayonCode));
}
SetDatatableRepository(model, arrOrderColumn, ref repo, ref result);
if (model.Length > -1 && result.TotalRecords == 0)
{
return(result);
}
List <SalesTarget> listItem = repo.Find();
if (listItem == null)
{
return(result);
}
List <TargetSalesViewModel> listData = new List <TargetSalesViewModel>();
foreach (var item in listItem)
{
listData.Add(GetTargetSalesViewModel(item, rhHeader));
}
result.Data = listData;
return(result);
}
public AlertMessage ExportTargetSalesToExcel(TargetSalesDatatableViewModel model)
{
AlertMessage alert = new AlertMessage();
model.Length = -1;
JDatatableResponse response = GetDatatable(model);
IWorkbook workbook = new XSSFWorkbook();
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet("Sheet 1");
string dateFormat = "yyyy/MM/dd";
ICreationHelper creationHelper = workbook.GetCreationHelper();
ICellStyle cellStyleDate = workbook.CreateCellStyle();
cellStyleDate.DataFormat = creationHelper.CreateDataFormat().GetFormat(dateFormat);
ICellStyle cellStyleText = workbook.CreateCellStyle();
cellStyleText.DataFormat = creationHelper.CreateDataFormat().GetFormat("text");
ICellStyle cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = creationHelper.CreateDataFormat().GetFormat("0");
var fontBold = workbook.CreateFont();
fontBold.FontHeightInPoints = 11;
fontBold.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellStyleHeader = workbook.CreateCellStyle();
cellStyleHeader.SetFont(fontBold);
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.FillForegroundColor = IndexedColors.Grey25Percent.Index;
cellStyleHeader.FillPattern = FillPattern.SolidForeground;
IRow row = sheet1.CreateRow(rowIndex);
row = sheet1.CreateRow(rowIndex);
int cellIndex = 0;
ICell cell = null;
//
#region cell header
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("FSS_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("SLM_Name");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("RayonCode");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("AchiGroup");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Division");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Material");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Bulan");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Tahun");
cell.CellStyle = cellStyleHeader;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue("Target");
cell.CellStyle = cellStyleHeader;
#endregion
rowIndex++;
#region cell data
List <TargetSalesViewModel> listData = response.Data as List <TargetSalesViewModel>;
if (listData != null)
{
foreach (var item in listData)
{
row = sheet1.CreateRow(rowIndex);
cellIndex = 0;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.FSS_Name);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_NIK);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.SLM_Name);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.RayonCode);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.AchiGroup);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Division);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Material);
cell.CellStyle = cellStyleText;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Bulan);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(item.Tahun);
cell.CellStyle = cellStyleNumber;
cell = row.CreateCell(cellIndex++);
cell.SetCellValue(double.Parse(item.Target));
cell.CellStyle = cellStyleNumber;
rowIndex++;
}
}
#endregion
#region auto size column
for (int i = 0; i < 13; i++)
{
sheet1.AutoSizeColumn(i);
}
#endregion
using (var ms = new MemoryStream())
{
workbook.Write(ms);
alert.Data = ms.ToArray();
}
alert.Status = 1;
return(alert);
}
