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