private string CreateXlsVehicleReport(VehicleOverallReportModel model)
{
//get data
var data = GetVehicleData(model.SearchView);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "VEHICLE REPORT");
slDocument.MergeWorksheetCells(1, 1, 1, 32);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderVehicleReport(slDocument);
//create data
slDocument = CreateDataExcelVehicleReport(slDocument, data);
var fileName = "Vehicle_report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
protected Dictionary <string, SLStyle> CreateFieldStyles(Dictionary <string, FieldInfoAttribute> fieldInfo)
{
var result = new Dictionary <string, SLStyle>();
// стиль для ячейки таблицы
foreach (string name in fieldInfo.Keys)
{
SLStyle style = Document.CreateStyle();
style.Font.FontSize = FontSize;
style.Font.FontName = FontName;
style.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.FormatCode = fieldInfo[name].Format ?? string.Empty;
style.Alignment.WrapText = true;
style.SetVerticalAlignment(VerticalAlignmentValues.Center);
style.SetHorizontalAlignment(fieldInfo[name].HorizontalAlignment);
result[name] = style;
}
return(result);
}
/// <summary> создание стилей </summary>
protected void CreateDefaultStyles()
{
// стиль для титула отчета
TitleStyle = Document.CreateStyle();
TitleStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
TitleStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
TitleStyle.Font.FontSize = FontSize + 5;
TitleStyle.Font.FontName = FontName;
TitleStyle.Font.Bold = true;
// стиль для заголовка полей таблицы
CaptionStyle = Document.CreateStyle();
CaptionStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Silver, System.Drawing.Color.Silver);
CaptionStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
CaptionStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
CaptionStyle.Font.FontSize = FontSize;
CaptionStyle.Font.FontName = FontName;
CaptionStyle.Font.Bold = true;
CaptionStyle.Alignment.WrapText = true;
}
private string CreateXlsMasterEmployee()
{
//get data
List <EmployeeDto> employee = _employeeBLL.GetEmployee();
var listData = Mapper.Map <List <EmployeeItem> >(employee);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Employee");
slDocument.MergeWorksheetCells(1, 1, 1, 18);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelMasterEmployee(slDocument);
//create data
slDocument = CreateDataExcelMasterEmployee(slDocument, listData);
var fileName = "Master Data Employee " + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsPersonal()
{
List <TraCafDto> CRF = new List <TraCafDto>(); //_CRFBLL.GetCRF();
CRF = _cafBLL.GetCafPersonal(CurrentUser);
var listData = Mapper.Map <List <TraCafItemDetails> >(CRF);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Personal Dashboard CAF");
slDocument.MergeWorksheetCells(1, 1, 1, 8);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelCRF(slDocument);
//create data
slDocument = CreateDataExcelCRF(slDocument, listData);
var fileName = "Data_CAF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsMasterCostOb(CostObModel model)
{
//get data
var costOb = new List <CostObItem>();
costOb = GetCostOb(model.SearchView);
var listData = Mapper.Map <List <CostObItem> >(costOb);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Cost OB");
slDocument.MergeWorksheetCells(1, 1, 1, 11);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelMasterCostOb(slDocument);
//create data
slDocument = CreateDataExcelMasterCostOb(slDocument, listData);
var fileName = "Master_Data_CostOb" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsRptFuel(RptFuelByParamInput input)
{
//get data
List <RptFuelDto> data = _rptFuelBLL.GetRptFuel(input);
var listData = Mapper.Map <List <RptFuelItem> >(data);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Fuel Report Data");
slDocument.MergeWorksheetCells(1, 1, 1, 19);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelDashboard(slDocument);
//create data
slDocument = CreateDataExcelDashboard(slDocument, listData, input);
var fileName = "RptFuel" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsFile()
{
//get data
var listData = Mapper.Map <List <POAViewDetailModel> >(_poaBll.GetAll());
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master POA");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, listData);
var fileName = "MasterData_MasterPoa" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsDashboard()
{
//get data
List <EpafDto> epaf = _epafBLL.GetEpafByDocType(Enums.DocumentType.CRF);
var listData = Mapper.Map <List <EpafData> >(epaf);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Dashboard CRF");
slDocument.MergeWorksheetCells(1, 1, 1, 12);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelDashboard(slDocument);
//create data
slDocument = CreateDataExcelDashboard(slDocument, listData);
var fileName = "Dashboard_CRF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsMasterHolidayCalender()
{
//get data
List <HolidayCalenderDto> penalty = _HolidayCalenderBLL.GetHolidayCalender();
var listData = Mapper.Map <List <HolidayCalenderItem> >(penalty);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Holiday Calender");
slDocument.MergeWorksheetCells(1, 1, 1, 7);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelMasterHolidayCategory(slDocument);
//create data
slDocument = CreateDataExcelHolidayCalender(slDocument, listData);
var fileName = "Master_Data_Holiday_Calender" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
public void CreateXls(List <BagiShuDto> Data, string FilePath)
{
try
{
var slDocument = new SLDocument();
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.ScalePage(50);
ps.PaperSize = SLPaperSizeValues.FolioPaper;
ps.LeftMargin = 0;
ps.RightMargin = 0;
slDocument.SetPageSettings(ps);
//title
slDocument.SetCellValue(1, 1, "Detail SHU Koperasi Polowijo Karya Abadi");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
slDocument.SetCellValue(2, 1, "Periode " + _intTahun);
slDocument.MergeWorksheetCells(2, 1, 2, 9);
total_shu.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(3, 1, "Total SHU = Rp. " + total_shu.Text); }));
DanaCadangan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(4, 1, "Dana Cadangan (30%) = Rp. " + DanaCadangan.Text); }));
JasaModal.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(4, 4, "Jasa Modal (20%) = Rp. " + JasaModal.Text); }));
JasaAnggota.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(5, 1, "Jasa Anggota (10%) = Rp. " + JasaAnggota.Text); }));
PengurusPengawas.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(5, 4, "Dana Pengawas (10%) = Rp. " + PengurusPengawas.Text); }));
KesejahteraanPegawai.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(6, 1, "Kesejahteraan Pegawai (10%) = Rp. " + KesejahteraanPegawai.Text); }));
Pendidikan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(6, 4, "Pendidikan (10%) = Rp. " + Pendidikan.Text); }));
DanaSosial.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(7, 1, "Dana Sosial (10%) = Rp. " + DanaSosial.Text); }));
total_simpanan.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(8, 1, "Total Simpanan = Rp. " + total_simpanan.Text); }));
total_pinjaman.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(8, 4, "Total Piutang = Rp. " + total_pinjaman.Text); }));
total_belanja.Dispatcher.BeginInvoke(new Action(() => { slDocument.SetCellValue(9, 1, "Total Penjualan = Rp. " + total_belanja.Text); }));
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 16;
slDocument.SetCellStyle(1, 1, 2, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, Data);
var FullPath = FilePath;
if (System.IO.File.Exists(FullPath))
{
System.IO.File.Delete(FullPath);
}
slDocument.SaveAs(FullPath);
}
catch (Exception)
{
Dialog_Loading.Close();
throw;
}
}
private SLStyle colunaStyleAux()
{
SLStyle style = new SLStyle();
style.SetFont("Microsoft Sans Serif", 10);
style.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
return(style);
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <DataAnggotaDto> listData)
{
int iRow = 4; //starting row data
int row = 1;
try
{
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.Id);
slDocument.SetCellValue(iRow, 2, data.IdAnggota);
slDocument.SetCellValue(iRow, 3, data.NamaAnggota);
slDocument.SetCellValue(iRow, 4, data.TanggalGabung);
slDocument.SetCellValue(iRow, 5, data.Status?"Aktif":"Tidak Aktif");
iRow++;
row++;
}
}
catch (Exception)
{
throw;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle dateStyle = slDocument.CreateStyle();
dateStyle.FormatCode = "dd-MMM-yyyy";
SLStyle hourStyle = slDocument.CreateStyle();
hourStyle.FormatCode = "HH:mm";
SLStyle decimalFormat = slDocument.CreateStyle();
decimalFormat.FormatCode = "#,##0.00";
SLStyle decimalFormat2 = slDocument.CreateStyle();
decimalFormat2.FormatCode = "Rp #,##0.00";
slDocument.SetCellStyle(4, 4, iRow, 4, dateStyle);
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
valueStyle.Font.FontSize = 10;
slDocument.SetCellStyle(4, 1, iRow, 5, valueStyle);
return(slDocument);
}
private SLStyle cabecalhoStyleAux()
{
SLStyle sr = new SLStyle();
sr.SetFontBold(true);
sr.SetFont("Microsoft Sans Serif", 10);
sr.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
return(sr);
}
private string CreateXlsDetailsVehicle(VehicleOverallItem model)
{
//get data
var filter = new VehicleOverallSearchView();
var data = GetVehicleData(filter).Where(x => (x.ChasisNumber == null ? "" : x.ChasisNumber.ToUpper()) == (model.ChasisNumber == null ? "" : model.ChasisNumber.ToUpper())).FirstOrDefault();
data.MainMenu = _mainMenu;
data.CurrentLogin = CurrentUser;
var History = _fleetBLL.GetFleet().Where(x => (x.ChasisNumber == null ? "" : x.ChasisNumber.ToUpper()) == (data.ChasisNumber == null ? "" : data.ChasisNumber.ToUpper()) &&
(x.PoliceNumber == null ? "" : x.PoliceNumber.ToUpper()) == (data.PoliceNumber == null ? "" : data.PoliceNumber.ToUpper()) &&
x.StartContract == data.StartContract && x.EndContract == data.EndContract).GroupBy(x => x.CreatedDate)
.Select(x => new VehicleHistory
{
Employee = x.First().EmployeeName,
Date = x.First().CreatedDate
//Description = x.First().VehicleStatus
}).OrderBy(x => x.Date).ToList();
data.DetailsHistory = new List <VehicleHistory>();
if (History != null && History.Count > 0)
{
data.DetailsHistory = History;
}
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "DETAILS VEHICLE REPORT");
slDocument.MergeWorksheetCells(1, 1, 1, 5);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderDetailsVehicle(slDocument);
//create data
slDocument = CreateDataExcelDetailsVehicle(slDocument, data);
var fileName = "Vehicle_report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsFile(MaterialListViewModel model)
{
//var data = _materialBll.getByID(mn, p);
////Mapper.Map(data,model);
var input = Mapper.Map <MaterialInput>(model.SearchView);
var tempData = _materialBll.GetMaterialByParam(input);
var data = Mapper.Map <List <MaterialDto> >(tempData);
//if(model.SearchView.GoodTypeSource != null) data = data.Where(c=>c.EXC_GOOD_TYP == model.SearchView.GoodTypeSource).ToList();
//get data
var listData = Mapper.Map <List <MaterialDetailViewModel> >(data);
var slDocument = new SLDocument();
//string goodTypeName = "";
//if (listData.Count > 0)
// goodTypeName = "-" + listData[0].GoodTypeName;
//create filter
//slDocument.SetCellValue(1, 1, "Excisable Goods Type");
//slDocument.SetCellValue(1, 2, ": " + model.SearchView.GoodTypeSource + goodTypeName);
//title
slDocument.SetCellValue(2, 1, "Material Master");
slDocument.MergeWorksheetCells(2, 1, 2, 15);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(2, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, listData);
var fileName = "MasterData_MaterialMaster" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
static void makeStickers(List <Unit> units, string spName)
{
using (SLDocument doc = new SLDocument()) {
var style_12 = new SLStyle();
style_12.SetFont("arial", 12);
style_12.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
style_12.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
SLPageSettings ps = new SLPageSettings();
ps.TopMargin = 0;
ps.BottomMargin = 0;
ps.LeftMargin = 0;
ps.RightMargin = 0;
doc.SetPageSettings(ps);
int col = 1; int row = 1;
foreach (Unit unit in units)
{
for (int qty = 0; qty < unit.qty; qty++)
{
if (col == 5)
{
col = 1;
row++;
}
doc.SetColumnWidth(col, COLUMN_WIDTH_FOR_ARIAL_14);
doc.SetRowHeight(row, ROW_HEIGHT_FOR_ARIAL_14);
doc.SetCellStyle(row, col, style_12);
doc.SetCellValue(row, col++, unit.GetSticker());
}
}
using (var dlg = new SaveFileDialog()) {
dlg.Title = "Сохранение файла наклеек";
dlg.FileName = "Наклейки -- " + spName.Split('\\').Last();
var res = dlg.ShowDialog();
if (res != DialogResult.OK)
{
return;
}
doc.SaveAs(dlg.FileName);
Console.WriteLine("Сохранено в " + dlg.FileName);
}
}
}
public void CreateXls(List <IptAnggotaDto> Data, string FilePath)
{
try
{
var slDocument = new SLDocument();
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.ScalePage(50);
ps.PaperSize = SLPaperSizeValues.FolioPaper;
ps.LeftMargin = 0;
ps.RightMargin = 0;
slDocument.SetPageSettings(ps);
//title
slDocument.SetCellValue(1, 1, "Detail SHU Koperasi Polowijo Karya Abadi");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
slDocument.SetCellValue(2, 1, "Periode 2020");
slDocument.MergeWorksheetCells(2, 1, 2, 9);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 16;
slDocument.SetCellStyle(1, 1, 2, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, Data);
var FullPath = FilePath;
if (System.IO.File.Exists(FullPath))
{
System.IO.File.Delete(FullPath);
}
slDocument.SaveAs(FullPath);
}
catch (Exception)
{
Dialog_Loading.Close();
throw;
}
}
private string CreateXlsFile()
{
//get data
var rawData = _brandRegistrationBll.GetAllBrands();
var listData = Mapper.Map <List <BrandRegistrationDetailsViewModel> >(rawData);
var materialList = rawData.Select(x => x.FA_CODE).Distinct().ToList();
var plantList = rawData.Select(x => x.WERKS).Distinct().ToList();
var dataMaterial = _materialBll.getAllMaterialByListMaterialAndPlant(materialList, plantList);
foreach (var dataBrand in listData)
{
var zaidmExMaterial = dataMaterial.FirstOrDefault(x => x.STICKER_CODE == dataBrand.FaCode && x.WERKS == dataBrand.PlantName);
if (zaidmExMaterial != null)
{
dataBrand.SAPBrandDescription = zaidmExMaterial.MATERIAL_DESC;
}
}
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Brand Registration");
slDocument.MergeWorksheetCells(1, 1, 1, 30);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, listData);
var fileName = "MasterData_MasterBrandRegistration" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private void ApplyHorizontalAlignment(SLStyle cellStyle, HorizontalAlignment horizotalAligment)
{
switch (horizotalAligment)
{
case HorizontalAlignment.General:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.General);
break;
case HorizontalAlignment.Left:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
break;
case HorizontalAlignment.Center:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
break;
case HorizontalAlignment.Right:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
break;
case HorizontalAlignment.Fill:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Fill);
break;
case HorizontalAlignment.Justify:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Justify);
break;
case HorizontalAlignment.CenterAcrossSelection:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.CenterContinuous);
break;
case HorizontalAlignment.Distributed:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Distributed);
break;
default:
throw new ArgumentOutOfRangeException(nameof(horizotalAligment));
}
}
private string CreateXlsCAF(bool isCompleted)
{
//get data
List <TraCafDto> CRF = new List <TraCafDto>(); //_CRFBLL.GetCRF();
if (isCompleted)
{
CRF = _cafBLL.GetCaf().Where(x => x.DocumentStatus == (int)Enums.DocumentStatus.Completed || x.DocumentStatus == (int)Enums.DocumentStatus.Cancelled).ToList();
}
else
{
CRF = _cafBLL.GetCaf().Where(x => x.DocumentStatus != (int)Enums.DocumentStatus.Completed || x.DocumentStatus != (int)Enums.DocumentStatus.Cancelled).ToList();
}
var listData = Mapper.Map <List <TraCafItemDetails> >(CRF);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, isCompleted ? "Completed Document CAF" : "Open Document CAF");
slDocument.MergeWorksheetCells(1, 1, 1, 8);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelCRF(slDocument);
//create data
slDocument = CreateDataExcelCRF(slDocument, listData);
var fileName = "Data_CAF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsReportGs(ReportFilter filter)
{
//get data
List <GsDto> Gs = _gsBLL.GetGsReport(new RptGsInput()
{
EndDateBegin = filter.EndDateBegin,
EndDateEnd = filter.EndDateEnd,
StartDateBegin = filter.StartDateBegin,
StartDateEnd = filter.StartDateEnd,
Location = filter.Location,
VehicleUsage = filter.VehicleUsage
});
var listData = Mapper.Map <List <GsItem> >(Gs);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Gs Report");
slDocument.MergeWorksheetCells(1, 1, 1, 13);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelReportGs(slDocument);
//create data
slDocument = CreateDataExcelReportGs(slDocument, listData);
var fileName = "Gs_Report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsKpiMonitoring(RptKpiMonitoringModel model)
{
var ListTransaction = GetTransaction(model.SearchView);
var UserLogin = GetUserLogin();
foreach (var item in ListTransaction)
{
var data = GetDateworkflow(item, UserLogin);
model.ListTransaction.Add(data);
}
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "KPI MONITORING");
slDocument.MergeWorksheetCells(1, 1, 1, 19);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelKpiMonitoring(slDocument);
//create data
slDocument = CreateDataExcelKpiMonitoring(slDocument, model.ListTransaction);
var fileName = "Kpi_Monitoring" + DateTime.Now.ToString(" yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private string CreateXlsDashboard(RptAutoGrModel model)
{
//get data
var data = _autoGrBLL.GetAutoGR(new RptAutoGrInput()
{
PeriodEnd = model.PeriodEnd,
PeriodStart = model.PeriodStart
});
var listData = Mapper.Map <List <RptAutoGrItem> >(data);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Auto GR Report");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelDashboard(slDocument);
//create data
slDocument = CreateDataExcelDashboard(slDocument, listData);
var fileName = "Auto_GR_Report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private void mergeButton_Click(object sender, EventArgs e)
{
bool bRC;
int nInfoRow = nFirstRow - 4;
//int itemCnt = 0;
string fullTemplateName = Path.Combine(Properties.Settings.Default.LastTemplatePath, sourceTemplateInput.Text);
bRC = sl.SelectWorksheet("Form1");
string partNo = sl.GetCellValueAsString(nInfoRow, 2);
string partName = sl.GetCellValueAsString(nInfoRow, 4);
string reportNo = sl.GetCellValueAsString(nInfoRow, 8);
string serialNo = String.Empty;
//SLDocument sl = new SLDocument(fullTemplateName, "Form1");
SLStyle styleFloat = sl.CreateStyle();
styleFloat.FormatCode = fontFormat;
styleFloat.SetFontColor(System.Drawing.Color.Black);
styleFloat.Font.FontName = fontName;
styleFloat.Font.FontSize = Convert.ToInt16(fontSize);
styleFloat.Alignment.Horizontal = HorizontalAlignmentValues.Center;
styleFloat.Alignment.Vertical = VerticalAlignmentValues.Center;
styleFloat.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
SLStyle styleFloatV2 = sl.CreateStyle();
styleFloatV2.Font.FontName = fontName;
styleFloatV2.Font.FontSize = Convert.ToInt16(fontSize);
styleFloatV2.Alignment.Horizontal = HorizontalAlignmentValues.Center;
styleFloatV2.Alignment.Vertical = VerticalAlignmentValues.Center;
styleFloatV2.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
SLStyle styleFloatV2LJ = sl.CreateStyle();
styleFloatV2LJ.Font.FontName = fontName;
styleFloatV2LJ.Font.FontSize = Convert.ToInt16(fontSize);
styleFloatV2LJ.Alignment.Horizontal = HorizontalAlignmentValues.Left;
styleFloatV2LJ.Alignment.Vertical = VerticalAlignmentValues.Center;
styleFloatV2LJ.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
SLStyle styleFloatPass = sl.CreateStyle();
styleFloatPass.FormatCode = fontFormat;
//styleFloatPass.Fill.SetPatternBackgroundColor(System.Drawing.Color.LightGreen);
styleFloatPass.SetFontColor(System.Drawing.Color.Black);
styleFloatPass.Font.FontName = fontName;
styleFloatPass.Font.FontSize = Convert.ToInt16(fontSize);
styleFloatPass.Font.Bold = false;
styleFloatPass.Font.Italic = false;
styleFloatPass.Alignment.Horizontal = HorizontalAlignmentValues.Center;
styleFloatPass.Alignment.Vertical = VerticalAlignmentValues.Center;
styleFloatPass.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleFloatPass.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.White, System.Drawing.Color.White);
SLStyle styleFloatDev = sl.CreateStyle();
styleFloatDev.FormatCode = fontFormat;
styleFloatDev.SetFontColor(System.Drawing.Color.Black);
styleFloatDev.Font.FontName = fontName;
styleFloatDev.Font.FontSize = Convert.ToInt16(fontSize);
styleFloatDev.Font.Bold = false;
styleFloatDev.Font.Italic = false;
styleFloatDev.Alignment.Horizontal = HorizontalAlignmentValues.Center;
styleFloatDev.Alignment.Vertical = VerticalAlignmentValues.Center;
styleFloatDev.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleFloatDev.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightPink, System.Drawing.Color.Red);
SLStyle styleString = sl.CreateStyle();
styleString.SetFontColor(System.Drawing.Color.Black);
styleString.Font.FontName = fontName;
styleString.Font.FontSize = Convert.ToInt16(fontSize);
// foreach (resultGrid grid in grids.Values)
foreach (resultGrid grid in fileToGrid.Values)
{
int nRow;
TabPage tabPage = grid.tabPage;
DataGridView gridCtl = grid.gridControl;
string runNo = grid.runNo;
string title = tabPage.Text;
bRC = sl.SelectWorksheet("Form1");
bRC = sl.CopyWorksheet("Form3", title);
bRC = sl.SelectWorksheet(title);
// set this sheets serial number
serialNo = String.Format("{0}-{1}", partNo, runNo);
sl.SetCellValue(nInfoRow, 12, serialNo);
foreach (DataGridViewRow row in gridCtl.Rows)
{
if (row.IsNewRow)
{
continue;
}
string charNo = row.Cells["CharNo"].Value.ToString();
string nominal = row.Cells["Nominal"].Value.ToString();
string upper = row.Cells["Upper"].Value.ToString();
string lower = row.Cells["Lower"].Value.ToString();
string actual = row.Cells["Actual"].Value.ToString();
string deviation = row.Cells["Deviation"].Value.ToString();
nRow = charInfos[charNo].charRow;
// Actual
double actualVal;
if (actual.Length > 0)
{
actualVal = Math.Abs(Convert.ToDouble(actual));
sl.SetCellValue(nRow, 9, actualVal);
sl.SetCellStyle(nRow, 9, styleFloat);
}
sl.SetCellValue(nRow, 9, actual);
sl.SetCellStyle(nRow, 9, styleFloat);
sl.SetCellStyle(nRow, 2, styleFloatV2);
sl.SetCellStyle(nRow, 4, styleFloatV2LJ);
sl.SetCellStyle(nRow, 5, styleFloatV2LJ);
sl.SetCellStyle(nRow, 6, styleFloatV2);
sl.SetCellStyle(nRow, 7, styleFloatV2);
sl.SetCellStyle(nRow, 8, styleFloatV2);
// Exceed
double exceed = 0;
if (deviation.Length > 0)
{
exceed = Math.Abs(Convert.ToDouble(deviation));
sl.SetCellValue(nRow, 11, exceed);
sl.SetCellStyle(nRow, 11, styleFloatDev);
}
else
{
if (charInfos[charNo].isBasic)
{
string sRequirement = sl.GetCellValueAsString(nRow, 5);
if (sRequirement.StartsWith("("))
{
sl.SetCellValue(nRow, 11, "REFERENCE");
}
else
{
sl.SetCellValue(nRow, 11, "BASIC");
}
}
else
{
sl.SetCellValue(nRow, 11, "PASS");
}
sl.SetCellStyle(nRow, 11, styleFloatPass);
}
}
}
if (statsIncluded)
{
SLPageSettings ps = sl.GetPageSettings("CMMStats");
bRC = sl.SelectWorksheet("CMMStats");
if (bRC)
{
sl.SetCellValue(nInfoRow, 2, partNo);
sl.SetCellValue(nInfoRow, 6, partName);
//sl.SetCellValue(nInfoRow, 12, serialNo);
sl.SetCellValue(nInfoRow, 13, reportNo);
int nRow = nFirstRow;
foreach (DataGridViewRow row in statsGrid.gridControl.Rows)
{
if (row.IsNewRow)
{
continue;
}
//CharNo, Nominal, Mean, Variance, StdDev
string charNo = row.Cells["CharNo"].Value.ToString();
string nominal = row.Cells["Nominal"].Value.ToString();
string mean = row.Cells["Mean"].Value.ToString();
string variance = row.Cells["Variance"].Value.ToString();
string stdDev = row.Cells["StdDev"].Value.ToString();
string desc = charInfos[charNo].desc;
if (nRow > (nFirstRow + 20))
{
sl.InsertRow(nRow, 1);
sl.MergeWorksheetCells(nRow, 3, nRow, 9);
sl.CopyRowStyle(nFirstRow, nRow);
}
sl.SetCellValue(nRow, 2, charNo);
sl.SetCellStyle(nRow, 2, sl.GetCellStyle(nFirstRow, 2));
sl.SetCellValue(nRow, 3, desc);
sl.SetCellStyle(nRow, 3, sl.GetCellStyle(nFirstRow, 3));
sl.SetCellStyle(nRow, 4, sl.GetCellStyle(nFirstRow, 4));
sl.SetCellStyle(nRow, 5, sl.GetCellStyle(nFirstRow, 5));
sl.SetCellStyle(nRow, 6, sl.GetCellStyle(nFirstRow, 6));
sl.SetCellStyle(nRow, 7, sl.GetCellStyle(nFirstRow, 7));
sl.SetCellStyle(nRow, 8, sl.GetCellStyle(nFirstRow, 8));
sl.SetCellStyle(nRow, 9, sl.GetCellStyle(nFirstRow, 9));
sl.SetCellValue(nRow, 10, nominal);
sl.SetCellStyle(nRow, 10, sl.GetCellStyle(nFirstRow, 10));
sl.SetCellValue(nRow, 11, mean);
sl.SetCellStyle(nRow, 11, sl.GetCellStyle(nFirstRow, 11));
sl.SetCellValue(nRow, 12, variance);
sl.SetCellStyle(nRow, 12, sl.GetCellStyle(nFirstRow, 12));
sl.SetCellValue(nRow, 13, stdDev);
sl.SetCellStyle(nRow, 13, sl.GetCellStyle(nFirstRow, 13));
nRow++;
}
sl.SetPageSettings(ps);
}
}
else
{
sl.SelectWorksheet("Form1");
sl.DeleteWorksheet("CMMStats");
}
sl.SelectWorksheet("Form1");
sl.DeleteWorksheet("Form3");
string xlsFilename = Path.Combine(Properties.Settings.Default.LastReportPath, targetInput.Text);
sl.SaveAs(xlsFilename);
MessageBox.Show(String.Format("Result data merge complete: \n\r{0}", xlsFilename), "FAI Report");
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <BagiShuDto> listData)
{
int iRow = 11; //starting row data
int row = 1;
try
{
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.IdAnggota);
slDocument.SetCellValue(iRow, 2, data.NamaAnggota);
slDocument.SetCellValue(iRow, 3, data.TotalSimpanan);
slDocument.SetCellValue(iRow, 4, data.TotalBelanja);
slDocument.SetCellValue(iRow, 5, data.TotalBungaPinjaman);
slDocument.SetCellValue(iRow, 6, data.Jma);
slDocument.SetCellValue(iRow, 7, data.Jua);
slDocument.SetCellValue(iRow, 8, data.Jpa);
slDocument.SetCellValue(iRow, 9, data.TotalShu);
iRow++;
row++;
}
}
catch (Exception)
{
throw;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle dateStyle = slDocument.CreateStyle();
dateStyle.FormatCode = "dd-MMM-yyyy";
SLStyle hourStyle = slDocument.CreateStyle();
hourStyle.FormatCode = "HH:mm";
SLStyle decimalFormat = slDocument.CreateStyle();
decimalFormat.FormatCode = "#,##0.00";
SLStyle decimalFormat2 = slDocument.CreateStyle();
decimalFormat2.FormatCode = "Rp #,##0.00";
slDocument.SetCellStyle(11, 3, iRow, 9, decimalFormat);
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
valueStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, valueStyle);
return(slDocument);
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// This setting creator has user-defined styles and columns for the four data-tables in
/// mock data; Directors, Managers, Team Leads & Associates.
/// Displays a variety of ways to access the Export library with Constructor and
/// Property Dependency Injection.
/// </summary>
/// <returns>Settings: Custom Styling</returns>
/// -----------------------------------------------------------------------------------------------
public static Settings SetupCustomStyles()
{
try
{
var childList = new List <ChildSetting>();
/* -------------------------------------------------------------
* Setup the column header base style for the child datasets
* -----------------------------------------------------------*/
var baseColumnHeaderStyle = new SLStyle();
baseColumnHeaderStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
baseColumnHeaderStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
baseColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DimGray, Color.White);
baseColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetTopBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Medium, Color.Black);
baseColumnHeaderStyle.SetFont("Britannic Bold", 12);
baseColumnHeaderStyle.SetFontColor(Color.White);
baseColumnHeaderStyle.SetFontBold(true);
/* -------------------------------------------------------------
* Setup the odd row style for the child datasets
* -----------------------------------------------------------*/
var oddRowStyle = new SLStyle();
oddRowStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
oddRowStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
oddRowStyle.Fill.SetPattern(PatternValues.Solid, Color.White, Color.Black);
oddRowStyle.SetFont("Helvetica", 10);
oddRowStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Setup the even row style derived from the odd,
* change only what is necessary.
* -----------------------------------------------------------*/
var evenRowStyle = oddRowStyle.Clone();
evenRowStyle.Fill.SetPattern(PatternValues.Solid, Color.WhiteSmoke, Color.Black);
/* ------------------------------------------------------------
* Create the user-defined columns with property dependency
* injection for the base dataset.
* With this method hover the cursor over the property and
* intellisense will show the comments for it.
* ----------------------------------------------------------*/
var columns = new List <Column>
{
// Since this id column is not set to visible, you can just leave it out and it will be ignored
new Column
{
BoundColumnName = "DID",
UserDefinedColumnName = "ID",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = false,
FieldOrder = 0
},
new Column
{
BoundColumnName = "SheetName",
UserDefinedColumnName = "Director",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Left,
ShowField = true,
FieldOrder = 1
},
new Column
{
BoundColumnName = "Age",
UserDefinedColumnName = "Chronology",
NumberFormat = NumberFormats.Decimal0,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 2
},
new Column
{
BoundColumnName = "Income",
UserDefinedColumnName = "Compensation",
NumberFormat = NumberFormats.Accounting2Red,
HorizontalAlignment = HorizontalAlignmentValues.Right,
ShowField = true,
FieldOrder = 3
},
new Column
{
BoundColumnName = "Member",
UserDefinedColumnName = "Member ?",
NumberFormat = NumberFormats.General,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 4
},
new Column
{
BoundColumnName = "Registered",
UserDefinedColumnName = "Date Registered",
NumberFormat = NumberFormats.DateShort5,
HorizontalAlignment = HorizontalAlignmentValues.Center,
ShowField = true,
FieldOrder = 5
}
};
/* -------------------------------------------------------------
* Define and style base child settings.
* This Child will always be present, it represents the
* primary dataset for every export and is not really a child.
* Using Property Injection Technique
* -----------------------------------------------------------*/
childList.Add(new ChildSetting
{
// Optional name
SheetName = "Directors",
// Set column visibility
ShowColumnHeader = true,
// Make the base column header row a little larger
// so it will stand out. Value is in pixels
ColumnHeaderRowHeight = 25,
// Setup the style for Column Headers
ColumnHeaderStyle = baseColumnHeaderStyle,
// Row and Alternating Row Styles
// If set to false then the odd row style will be overall row style
ShowAlternatingRows = false,
// Setup the style for all rows
OddRowStyle = oddRowStyle,
EvenRowStyle = null,
// Add the user-defined columns
UserDefinedColumns = columns
});
/* ------------------------------------------------------------
* The first child column headers stylings will be derived
* from the base, change only what needs to be changed.
* ----------------------------------------------------------*/
var firstColumnHeaderStyle = baseColumnHeaderStyle.Clone();
firstColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.DarkGray, Color.Black);
firstColumnHeaderStyle.SetBottomBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetTopBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetVerticalBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetRightBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.Border.SetLeftBorder(BorderStyleValues.Thin, Color.DarkSlateGray);
firstColumnHeaderStyle.SetFont("Helvetica", 10);
firstColumnHeaderStyle.SetFontColor(Color.Black);
/* ------------------------------------------------------------
* Create the user-defined columns with constructor dependency
* injection for the base dataset.
* Hover the cursor over the property and intellisense will
* show the comments for it.
* ----------------------------------------------------------*/
columns = new List <Column>
{
new Column
(
"SheetName",
"Managers",
NumberFormats.General,
HorizontalAlignmentValues.Left,
true,
1
),
new Column
(
"Age",
"Age",
NumberFormats.Decimal0,
HorizontalAlignmentValues.Center,
true,
2
),
new Column
(
"Income",
"Compensation",
NumberFormats.Currency0Black,
HorizontalAlignmentValues.Right,
true,
3
),
new Column
(
"Registered",
"Date Registered",
NumberFormats.DateShort1,
HorizontalAlignmentValues.Center,
true,
5
)
};
/* -------------------------------------------------------------
* Define and add the first child
* Using Constructor dependency injection
* -----------------------------------------------------------*/
childList.Add(new ChildSetting
(
"Managers", // SheetName
true, // Show Column Headers
1, // Column Offset to the Right
null, // Column Header Row Height
firstColumnHeaderStyle, // Column Header Style
false, // Show Alternating Rows, false will default to Odd
oddRowStyle, // Odd Row Style
null, // Even Row Style
columns // User-Defined Column (UDCs)
));
/* -------------------------------------------------------------
* The second child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var secondColumnHeaderStyle = firstColumnHeaderStyle.Clone();
secondColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.CadetBlue, Color.White);
secondColumnHeaderStyle.SetFontColor(Color.White);
/* -------------------------------------------------------------
* Define and add the second child
* Using Constructor dependency injection
* -----------------------------------------------------------*/
childList.Add(new ChildSetting(
"Team Leads", // SheetName
true, // Show Column Headers
2, // Column Offset to the Right
null, // Column Header Row Height
secondColumnHeaderStyle, // Column Header Style
false, // Show Alternating Rows, false will default to Odd
oddRowStyle, // Odd Row Style
null, // Even Row Style
new List <Column> // User-Defined Column (UDCs)
{
new Column("TLID", "Team Lead ID", NumberFormats.General, HorizontalAlignmentValues.Left, true,
6),
new Column("Registered", "Registration Date", NumberFormats.UserDefined,
HorizontalAlignmentValues.Center, true, 2, "d-mmm-yy"),
new Column("SheetName", "Team Leads", NumberFormats.General, HorizontalAlignmentValues.Left,
true, 0),
new Column("Age", "How Old?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 1),
new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 3),
new Column("Income", "Income", NumberFormats.Accounting2Red, HorizontalAlignmentValues.Right,
true, 4),
new Column("MID", "Foreign Key", NumberFormats.General, HorizontalAlignmentValues.Right, false)
}
));
/* -------------------------------------------------------------
* The third child column headers stylings will be derived
* from the first, change only what needs to be changed.
* -----------------------------------------------------------*/
var thirdColumnHeaderStyle = firstColumnHeaderStyle.Clone();
thirdColumnHeaderStyle.Fill.SetPattern(PatternValues.Solid, Color.Aqua, Color.Black);
thirdColumnHeaderStyle.SetFont("Blackadder ITC", 11);
thirdColumnHeaderStyle.SetFontColor(Color.Black);
/* -------------------------------------------------------------
* Define and add the third child
* Constructor Injection on all
* -----------------------------------------------------------*/
childList.Add(new ChildSetting("Associates", true, 3, 30, thirdColumnHeaderStyle, true, oddRowStyle,
evenRowStyle,
new List <Column>
{
new Column("Registered", "Date", NumberFormats.TimeStamp124, HorizontalAlignmentValues.Left,
true, 3),
new Column("Member", "Member?", NumberFormats.General, HorizontalAlignmentValues.Center, true, 2),
new Column("SheetName", "Associate", NumberFormats.General, HorizontalAlignmentValues.Left, true,
0)
}
));
/* -------------------------------------------------------------
* Setup and return the primary container for the child datasets
* Using Constructor Injection as well
* -----------------------------------------------------------*/
return(new Settings("Organization", childList));
}
catch (Exception ex)
{
Assert.Fail("Exception Fail: " + ex.Message);
}
return(null);
}
public FileResult ExportarEncargado()
{
/*StringBuilder csv = new StringBuilder();
* string Columnas = string.Format("{0};{1};{2};{3};{4}", "N", "Nombre", "Productos", "Precio", "Local");
* csv.AppendLine(Columnas);
*
* decimal costoTotal = 0;
* TanoNEEntities ctx = new TanoNEEntities();
* Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
*
* Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
* var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres }).ToList().Select(a => new
* {
* idCompra = a.idCompra,
* nombre = a.Vecinos.nombres,
* productos = string.Join(" - ", a.CompraProducto.ToList().Select(b => "(" + b.cantidad + ") " + b.Productos.producto + " - " + b.Productos.marca + " - " + b.Productos.presentacion + " - " + b.Productos.Precios.LastOrDefault(precio => a.fecha > precio.fecha).precio + "\015")),
* precio = a.CompraProducto.ToList().Sum(b => b.cantidad * b.Productos.Precios.LastOrDefault(precio => a.fecha >= precio.fecha).precio),
* retiro = a.EstadosCompra.codigo,
* local = a.Locales.direccion
* }).ToArray();
*
* for (int x = 0; x < lista.Count(); x++)
* {
* var compra = lista[x];
* string filas = string.Format("{0};{1};{2};${3};{4}", compra.idCompra, compra.nombre, compra.productos, compra.precio, compra.local);
* csv.AppendLine(filas);
* }
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* return File(memoryStream.ToArray() as byte[], "application/vnd.ms-excel", "Reporte.csv");
* }*/
TanoNEEntities ctx = new TanoNEEntities();
Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
DateTime ProximaEntrea = ultima.fechaVenta.HasValue ? ultima.fechaVenta.Value : DateTime.Now;
string nombreLibro = ProximaEntrea.ToString("dd-MM-yyyy") + " Entrega";
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, nombreLibro);
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
sl.SetColumnWidth(1, 30);
sl.SetColumnWidth(2, 65);
sl.SetColumnWidth(3, 10);
sl.SetColumnWidth(4, 10);
sl.SetColumnWidth(5, 35);
sl.SetCellValue(1, 1, "Nombre");
sl.SetCellValue(1, 2, "Producto");
sl.SetCellValue(1, 3, "Precio");
sl.SetCellValue(1, 4, "Total");
sl.SetCellValue(1, 5, "Observaciones");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
string urla = ConfigurationManager.AppSettings["UrlSitio"];
int row = 3;
bordeNegrita.Font.Bold = false;
var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres });
foreach (var compra in lista)
{
int totalVecinx = (row + compra.CompraProducto.Count - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 5, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 5, bordeAb);
for (int x = 1; x < 6; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, new System.Globalization.CultureInfo("en-US", false).TextInfo.ToTitleCase(compra.Vecinos.nombres.ToLower()) + "\n" + compra.Vecinos.telefono + "\n" + compra.Vecinos.correo);
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
var ordenado = compra.CompraProducto.OrderBy(a => a.Productos.producto);
decimal totaltotal = 0;
foreach (var compraProducto in ordenado)
{
decimal total = compraProducto.Precios.precio * compraProducto.cantidad;
totaltotal += total;
centrado.Font.Bold = false;
sl.SetCellValue(row, 2, compraProducto.cantidad + ": " + compraProducto.Productos.producto + " - " + compraProducto.Productos.marca + " - " + compraProducto.Productos.presentacion);
sl.SetCellValue(row, 3, total);
sl.SetCellStyle(row, 3, centrado);
if (compraProducto.cantidad > 6)
{
sl.SetCellStyle(row, 2, row, 5, rojo);
sl.SetCellValue(row, 6, "Alerta! Mucha cantidad");
}
row++;
}
centrado.Font.Bold = true;
sl.SetCellValue(row - 1, 4, totaltotal);
sl.SetCellStyle(row - 1, 4, centrado);
}
sl.SaveAs(mem);
mem.Position = 0;
return(File(mem.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reporte.xlsx"));
}
}
private string CreateXlsCfmIdle(CfmIdleReportModel model)
{
//get data
var data = GetVehicleData(model.SearchView);
foreach (var item in data)
{
var CfmIdleVehicle = new CfmIdleVehicle();
var StartIdle = (decimal)(item.StartIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays - 1;
var today = DateTime.Today;
var EndIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays + 1;
if (item.EndIdle.HasValue)
{
EndIdle = (decimal)(item.EndIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays + 1;
}
item.IdleDuration = Math.Round((decimal)(EndIdle - StartIdle) / 30, 2);
item.TotalMonthly = Math.Round((decimal)(item.IdleDuration * item.MonthlyInstallment), 2);
var Ctf = _ctfBLL.GetCtf().Where(x => (x.PoliceNumber == null ? "" : x.PoliceNumber.ToUpper()) == (item.PoliceNumber == null ? "" : item.PoliceNumber.ToUpper())).FirstOrDefault();
if (Ctf != null)
{
item.GroupLevel = Ctf.GroupLevel;
}
}
var GrandTotal = new CfmIdleVehicle
{
PoliceNumber = "",
Note = "GrandTotal",
CreatedDate = DateTime.Today,
IdleDuration = data.Sum(x => x.IdleDuration),
TotalMonthly = data.Sum(x => x.TotalMonthly)
};
if (data.Count > 0 && data != null)
{
var result = data.GroupBy(x => x.PoliceNumber).Select(grouping => new CfmIdleVehicle
{
PoliceNumber = grouping.First().PoliceNumber,
Note = "SubTotal",
CreatedDate = DateTime.Today,
IdleDuration = grouping.Sum(x => x.IdleDuration),
TotalMonthly = grouping.Sum(x => x.TotalMonthly)
}).ToList();
foreach (var itemResult in result)
{
data.Add(itemResult);
}
}
data = data.OrderBy(x => x.PoliceNumber).ToList();
if (data.Count > 0 && data != null)
{
data.Add(GrandTotal);
}
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "CFM IDLE Report");
slDocument.MergeWorksheetCells(1, 1, 1, 16);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderCfmIdle(slDocument);
//create data
slDocument = CreateDataExcelCfmIdle(slDocument, data);
var fileName = "Cfm_Idle_Report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
public ActionResult CrearExcelFinanzas(int idTanda)
{
/*string handle = Guid.NewGuid().ToString();
*
* StringBuilder csv = new StringBuilder();
* string Columnas = string.Format(";{0};{1};{2};{3};{4}", "Cantidad", "Producto", "Costo", "Precio", "Diferencia");
* csv.AppendLine(Columnas);
*
*
* TanoNEEntities ctx = new TanoNEEntities();
* Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
*
*
* var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().ToList();
* foreach (var local in locales)
* {
* csv.AppendLine(local.direccion);
* decimal costoLocal = 0;
* decimal precioLocal = 0;
* var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
*
* Costos ultimoc = prod.Costos.Count > 1 ? prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Costos.FirstOrDefault();
* decimal costo = ultimoc.costo * compra.Cantidad;
* //decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
*
* Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
* decimal precio = ultimop.precio * compra.Cantidad;
* //decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
* costoLocal += costo;
* precioLocal += precio;
* string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
* csv.AppendLine(string.Format(";;;${0};${1};${2}", costoLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precioLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precioLocal - costoLocal).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture)));
* }
* //string cierre = string.Format("Total;;;${0}", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* //csv.AppendLine(cierre);
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* TempData[handle] = memoryStream.ToArray();
* }
*
* return Json(new { FileGuid = handle, FileName = "Reporte.csv" });*/
string handle = Guid.NewGuid().ToString();
TanoNEEntities ctx = new TanoNEEntities();
Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
EstadosCompra entre = ctx.EstadosCompra.FirstOrDefault(a => a.codigo == 3);
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Finanzas");
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
SLStyle negrita = sl.CreateStyle();
negrita.Font.Bold = true;
int row = 3;
sl.SetColumnWidth(1, 16);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 15);
sl.SetColumnWidth(5, 15);
sl.SetColumnWidth(6, 15);
sl.SetCellValue(1, 1, "Local");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Costo");
sl.SetCellValue(1, 5, "Precio");
sl.SetCellValue(1, 6, "Diferencia");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
var locales = ctx.Compras.Where(a => a.tandaId == idTanda && a.estadoId != entre.idEstadoCompra).Select(a => a.Locales).Distinct().OrderBy(a => new { a.comuna, a.nombre }).ToList();
foreach (var local in locales)
{
var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad), CompraProducto = a.FirstOrDefault() }).ToArray();
int totalVecinx = (row + listado.Count() - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 6, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 6, bordeAb);
for (int x = 1; x < 7; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, local.direccion + (local.nombre != null ? "\n" + local.nombre : ""));
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
decimal costoLocal = 0;
decimal precioLocal = 0;
for (int x = 0; x < listado.Count(); x++)
{
var compra = listado[x];
Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
Costos ultimoc = compra.CompraProducto.Costos;
decimal costo = ultimoc.costo * compra.Cantidad;
//decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
//Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
decimal precio = compra.CompraProducto.Precios.precio * compra.Cantidad;
//decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
costoLocal += costo;
precioLocal += precio;
//string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
sl.SetCellValue(row, 2, compra.Cantidad);
sl.SetCellValue(row, 3, prod.producto + " - " + prod.marca + " - " + prod.presentacion);
sl.SetCellValue(row, 4, costo);
sl.SetCellValue(row, 5, precio);
sl.SetCellValue(row, 6, precio - costo);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
}
sl.SetCellValue(row, 3, "Totales por local: ");
sl.SetCellValue(row, 4, costoLocal);
sl.SetCellValue(row, 5, precioLocal);
sl.SetCellValue(row, 6, precioLocal - costoLocal);
sl.SetCellStyle(row, 3, negrita);
sl.SetCellStyle(row, 4, negrita);
sl.SetCellStyle(row, 5, negrita);
sl.SetCellStyle(row, 6, negrita);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
row++;
}
sl.SaveAs(mem);
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(Json(new { FileGuid = handle, FileName = "Finanzas.xlsx" }));
}
}
