public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var fields = MyRow.Fields; //request.IncludeColumns.Clear(); //request.IncludeColumns.Add(fields.ProjectType.PropertyName); //request.IncludeColumns.Add(fields.PurposeTypeId.PropertyName); //request.IncludeColumns.Add(fields.PriceType.PropertyName); //request.IncludeColumns.Add(fields.TrxHeaderId.PropertyName); //request.IncludeColumns.Add(fields.TenantId.PropertyName); //request.IncludeColumns.Add(fields.SiteTypeVal.PropertyName); //request.IncludeColumns.Add(fields.AreaName.PropertyName); //request.IncludeColumns.Add(fields.ShelterOutdoor.PropertyName); //request.IncludeColumns.Add(fields.KWHType.PropertyName); //request.IncludeColumns.Add(fields.PoNumber.PropertyName); //request.IncludeColumns.Add(fields.PONumberVersion.PropertyName); //request.IncludeColumns.Add(fields.InputPONumberDate.PropertyName); //request.IncludeColumns.Add(fields.BoqTotalAmount.PropertyName); //request.IncludeColumns.Add(fields.SiteId.PropertyName); //request.IncludeColumns.Add(fields.SiteName.PropertyName); //request.IncludeColumns.Add(fields.VendorName.PropertyName); //request.IncludeColumns.Add(fields.RefTrxId.PropertyName); var data = List(connection, request).Entities; var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TbKtpReport)); var bytes = new ReportRepository().Render(report); return(ExcelContentResult.Create(bytes, MyRow.Fields.AliasName.ToString() + "_" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xlsx")); }
public FileContentResult ExportDataNINJA(IDbConnection connection, ExcelMassUploadImportRequest request) { request.CheckNotNull(); if (request.IdExport == null) { throw new ValidationError("Batch Id Cannot NULL"); } var template = Server.MapPath(ConfigurationManager.AppSettings["TEMPLATENINJA"].ToString()); var filename = "NINJAAWB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; var templateFile = new System.IO.FileInfo(template); var stream = new MemoryStream(); using (var package = new ExcelPackage(templateFile, false)) { var workbook = package.Workbook; var worksheet = package.Workbook.Worksheets[1]; var fields = MyRow.Fields; string[] idAll = request.IdExport[0].Split(new char[] { ',' }); int idxData = 1; int idxCol = 2; foreach (string id in idAll) { HijrahquDataContext ctx = new HijrahquDataContext(); List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList(); //string cells = ""; if (order.Count > 0) { worksheet.Cells["A" + idxCol.ToString()].Value = order[0].OrderID.ToString(); worksheet.Cells["B" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["C" + idxCol.ToString()].Value = order[0].Address; //worksheet.Cells["D" + idxCol.ToString()].Value = "JAWA BARAT"; worksheet.Cells["E" + idxCol.ToString()].Value = order[0].District; worksheet.Cells["F" + idxCol.ToString()].Value = order[0].City; worksheet.Cells["G" + idxCol.ToString()].Value = order[0].Province; //worksheet.Cells["H" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["I" + idxCol.ToString()].Value = order[0].Handphone; //PROVINCE worksheet.Cells["J" + idxCol.ToString()].Value = order[0].KodePos; //CITY worksheet.Cells["K" + idxCol.ToString()].Value = (DateTime.Now.AddDays(1)).ToString("yyyy-MM-dd"); //COUNTRY //worksheet.Cells["L" + idxCol.ToString()].Value = "INDONESIA"; worksheet.Cells["M" + idxCol.ToString()].Value = 1; worksheet.Cells["N" + idxCol.ToString()].Value = "Standard"; worksheet.Cells["O" + idxCol.ToString()].Value = order[0].OrderID.ToString(); string keterangan = order[0].Keterangan; string intruksi = "UNTUK KURIR MOHON AMANAH DALAM MELAKUKAN PENGANTARAN (HARAP LAKUKAN SESUAI SOP)"; keterangan += intruksi; worksheet.Cells["P" + idxCol.ToString()].Value = keterangan; worksheet.Cells["Q" + idxCol.ToString()].Value = "TRUE"; worksheet.Cells["R" + idxCol.ToString()].Value = order[0].TotalBayar.ToString(); idxData++; idxCol++; } } return(ExcelContentResult.Create(package.GetAsByteArray(), filename)); } }
public FileContentResult ListExcel(IDbConnection connection, TransactionListRequest request) { var data = List(connection, request).Entities; var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TransactionColumns)); var bytes = new ReportRepository().Render(report); return(ExcelContentResult.Create(bytes, "TransactionList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var data = List(connection, request).Entities; var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.ConcessioneColumns), HttpContext.RequestServices); var bytes = ReportRepository.Render(report); return(ExcelContentResult.Create(bytes, "Concessioni_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
//public ListResponse<MyRow> List(IDbConnection connection, ListRequest request) //{ // ListResponse<MyRow> rr = new ListResponse<MyRow>(); // //rr.Entities = (List<MyRow>)connection.Query<MyRow>("InsCampaignDay", commandType: CommandType.StoredProcedure); // rr.Entities = connection.Query<MyRow>("InsCampaignDay", commandType: CommandType.StoredProcedure).ToList(); // connection.Execute("InsDailyVaccinations", commandType: CommandType.StoredProcedure); // connection.Execute("DelTempCampaign", commandType: CommandType.StoredProcedure); // return rr; //} public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var data = List(connection, request).Entities; var report = new Serenity.Reporting.DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TempCampaignColumns)); var bytes = new ReportRepository().Render(report); return(ExcelContentResult.Create(bytes, "TempCampaignOPV_" + System.DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
public FileContentResult ListExcel(IDbConnection connection, ListRequest request, [FromServices] IExcelExporter exporter) { var data = List(connection, request).Entities; var bytes = exporter.Export(data, typeof(Columns.ProductColumns), request.ExportColumns); return(ExcelContentResult.Create(bytes, "ProductList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss", CultureInfo.InvariantCulture) + ".xlsx")); }
public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var data = List(connection, request).Entities; var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TitleColumns)); var bytes = new ReportRepository().Render(report); var reportName = $"{GetType().Name.Replace("Controller", string.Empty)}_"; return(ExcelContentResult.Create(bytes, reportName + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var data = List(connection, request).Entities; var cols = request.IncludeColumns.Where(p => p != MyRow.Fields.ReusedCount.Name); var report = new DynamicDataReport(data, cols, typeof(Columns.IdCardColumns)); var bytes = new ReportRepository().Render(report); return(ExcelContentResult.Create(bytes, "IdCard_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
/// <summary> /// Lists the excel. /// </summary> /// <param name="connection">The connection.</param> /// <param name="request">The request.</param> /// <returns>Excel file</returns> public FileContentResult ListExcel(IDbConnection connection, ListRequest request) { var data = List(connection, request).Entities; var report = new DynamicDataReport(data, request.IncludeColumns, typeof(GraduateThesisManager.ModuleCourses.Columns.ModulesColumns)); var bytes = new ReportRepository().Render(report); return(ExcelContentResult.Create(bytes, "ModulesList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); }
public FileContentResult ExcelTemplate(IDbConnection connection, ListRequest request) { FileInfo file = new FileInfo(Server.MapPath("~/ExcelTemplate/IdCardTemplate.xlsx")); using (FileStream fs = file.OpenRead()) { BinaryReader r = new BinaryReader(fs); r.BaseStream.Seek(0, SeekOrigin.Begin); //将文件指针设置到文件开 byte[] bytes = r.ReadBytes((int)r.BaseStream.Length); return(ExcelContentResult.Create(bytes, "IdCardTemplate.xlsx")); } }
public FileContentResult ExportDataJX(IDbConnection connection, ExcelMassUploadImportRequest request) { request.CheckNotNull(); if (request.IdExport == null) { throw new ValidationError("Batch Id Cannot NULL"); } var template = Server.MapPath(ConfigurationManager.AppSettings["TEMPLATEJX"].ToString()); var filename = "JXAWB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; var templateFile = new System.IO.FileInfo(template); var stream = new MemoryStream(); using (var package = new ExcelPackage(templateFile, false)) { var workbook = package.Workbook; var worksheet = package.Workbook.Worksheets[1]; var fields = MyRow.Fields; string[] idAll = request.IdExport[0].Split(new char[] { ',' }); int idxData = 1; int idxCol = 2; foreach (string id in idAll) { HijrahquDataContext ctx = new HijrahquDataContext(); List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList(); //string cells = ""; worksheet.Cells["A" + idxCol.ToString()].Value = "#" + order[0].OrderID.ToString(); worksheet.Cells["B" + idxCol.ToString()].Value = order[0].NamaPengirim; worksheet.Cells["C" + idxCol.ToString()].Value = order[0].NoPengirim; worksheet.Cells["D" + idxCol.ToString()].Value = "JAWA BARAT"; worksheet.Cells["E" + idxCol.ToString()].Value = "BEKASI"; worksheet.Cells["F" + idxCol.ToString()].Value = "INDONESIA"; worksheet.Cells["G" + idxCol.ToString()].Value = "RUKO HIJRAHQU, JALAN RAYA KARANG SATRIA TAMBUN UTARA BEKASI"; worksheet.Cells["H" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["I" + idxCol.ToString()].Value = order[0].Handphone; //PROVINCE //worksheet.Cells["J" + idxCol.ToString()].Value = order[0].TanggalWa; //CITY //worksheet.Cells["K" + idxCol.ToString()].Value = order[0].TanggalClosing; //COUNTRY worksheet.Cells["L" + idxCol.ToString()].Value = "INDONESIA"; worksheet.Cells["M" + idxCol.ToString()].Value = order[0].Address; worksheet.Cells["N" + idxCol.ToString()].Value = "PAKET BUKU"; worksheet.Cells["O" + idxCol.ToString()].Value = "1"; worksheet.Cells["P" + idxCol.ToString()].Value = "1"; worksheet.Cells["Q" + idxCol.ToString()].Value = "COD"; worksheet.Cells["R" + idxCol.ToString()].Value = "COD"; idxData++; idxCol++; } return(ExcelContentResult.Create(package.GetAsByteArray(), filename)); } }
public ActionResult GetRequestFile(string status) { var req = new ListRequest(); req.EqualityFilter = new Dictionary <string, object>(); req.EqualityFilter.Add("Submit", status); using (var connection = SqlConnections.NewByKey("Default")) { var spsHelper = new SpreedSheetHelper(Server.MapPath("~/Content/templates/export/result/ais/CapNhatPhongBanUser.xlsx")); var stream = spsHelper.ExportXls <MyRow>(new MyController().List(connection, req).Entities); return(ExcelContentResult.Create(stream.GetBuffer())); } }
public FileContentResult LaporanPenjualan(IDbConnection connection, ExcelMassUploadImportRequest request) { request.CheckNotNull(); if (request.IdExport == null) { throw new ValidationError("Batch Id Cannot NULL"); } var template = Server.MapPath(ConfigurationManager.AppSettings["LapPenjualan"].ToString()); var filename = "LapPenjualan_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; var templateFile = new System.IO.FileInfo(template); var stream = new MemoryStream(); using (var package = new ExcelPackage(templateFile, false)) { var workbook = package.Workbook; var worksheet = package.Workbook.Worksheets[1]; var fields = MyRow.Fields; string[] idAll = request.IdExport[0].Split(new char[] { ',' }); int idxData = 1; int idxCol = 4; foreach (string id in idAll) { HijrahquDataContext ctx = new HijrahquDataContext(); List <ViewLapPenjualan> order = ctx.ViewLapPenjualans.Where(x => x.OrderID == int.Parse(id)).ToList(); //string cells = ""; worksheet.Cells["B" + idxCol.ToString()].Value = idxData.ToString(); worksheet.Cells["C" + idxCol.ToString()].Value = "#" + order[0].OrderID.ToString(); worksheet.Cells["D" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["E" + idxCol.ToString()].Value = order[0].Handphone; worksheet.Cells["F" + idxCol.ToString()].Value = "1"; worksheet.Cells["G" + idxCol.ToString()].Value = "1"; worksheet.Cells["H" + idxCol.ToString()].Value = "1"; worksheet.Cells["I" + idxCol.ToString()].Value = "1"; worksheet.Cells["J" + idxCol.ToString()].Value = order[0].Ongkir; worksheet.Cells["K" + idxCol.ToString()].Value = 125000 + order[0].Ongkir; worksheet.Cells["L" + idxCol.ToString()].Value = order[0].TanggalWa; worksheet.Cells["M" + idxCol.ToString()].Value = order[0].TanggalClosing; worksheet.Cells["N" + idxCol.ToString()].Value = order[0].TanggalKirim; worksheet.Cells["O" + idxCol.ToString()].Value = order[0].TanggalBayar; worksheet.Cells["P" + idxCol.ToString()].Value = order[0].TanggalCS; idxData++; idxCol++; } return(ExcelContentResult.Create(package.GetAsByteArray(), filename)); } }
public FileContentResult CreateJOB(IDbConnection connection, ExcelMassUploadImportRequest request) { request.CheckNotNull(); if (request.IdExport == null) { throw new ValidationError("Batch Id Cannot NULL"); } var template = Server.MapPath(ConfigurationManager.AppSettings["JOBTemplate"].ToString()); var filename = "JOB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; var templateFile = new System.IO.FileInfo(template); var stream = new MemoryStream(); using (var package = new ExcelPackage(templateFile, false)) { var workbook = package.Workbook; var worksheet = package.Workbook.Worksheets[1]; var fields = MyRow.Fields; string[] idAll = request.IdExport[0].Split(new char[] { ',' }); int idxData = 1; int idxCol = 2; foreach (string id in idAll) { HijrahquDataContext ctx = new HijrahquDataContext(); List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList(); //string cells = ""; worksheet.Cells["A" + idxCol.ToString()].Value = order[0].NamaPengirim; worksheet.Cells["B" + idxCol.ToString()].Value = "TAMBUN UTARA, BEKASI"; worksheet.Cells["E" + idxCol.ToString()].Value = order[0].NamaPengirim; worksheet.Cells["F" + idxCol.ToString()].Value = "0877-8090-1125"; worksheet.Cells["G" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["H" + idxCol.ToString()].Value = order[0].Address; worksheet.Cells["I" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["L" + idxCol.ToString()].Value = order[0].CustomerName; worksheet.Cells["M" + idxCol.ToString()].Value = order[0].Handphone; worksheet.Cells["N" + idxCol.ToString()].Value = "1"; worksheet.Cells["O" + idxCol.ToString()].Value = "1"; worksheet.Cells["P" + idxCol.ToString()].Value = "BUKU SYAMAIL MUHAMMADIYAH"; idxData++; idxCol++; } return(ExcelContentResult.Create(package.GetAsByteArray(), filename)); } }
public FileContentResult Printlabel(IUnitOfWork uow, ExcelMassUploadImportRequest request) { request.CheckNotNull(); if (request.IdExport == null) { throw new ValidationError("Batch Id Cannot NULL"); } var template = Server.MapPath(ConfigurationManager.AppSettings["LabelTemplate"].ToString()); var filename = "CustomerList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; var templateFile = new System.IO.FileInfo(template); var stream = new MemoryStream(); using (var package = new ExcelPackage(templateFile, false)) { var workbook = package.Workbook; var worksheet = package.Workbook.Worksheets[1]; var fields = MyRow.Fields; string[] idAll = request.IdExport[0].Split(new char[] { ',' }); int idxData = 1; int idxCol = 2; foreach (string id in idAll) { RetrieveRequest retReq = new RetrieveRequest(); retReq.EntityId = id; var ord = new OrderRepository().Retrieve(uow.Connection, retReq); HijrahquDataContext ctx = new HijrahquDataContext(); List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList();//int.Parse("15")).ToList(); string cells = ""; if (order[0].OrderStatus == "NewOrder") { if (idxData == 1) { cells = "D"; worksheet.Cells[cells + "6"].Value = order[0].CustomerName; worksheet.Cells[cells + "7"].Value = order[0].Handphone; worksheet.Cells[cells + "8"].Value = order[0].Address; worksheet.Cells["H3"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 2) { cells = "D"; worksheet.Cells[cells + "21"].Value = order[0].CustomerName; worksheet.Cells[cells + "22"].Value = order[0].Handphone; worksheet.Cells[cells + "23"].Value = order[0].Address; worksheet.Cells["H18"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 3) { cells = "D"; worksheet.Cells[cells + "36"].Value = order[0].CustomerName; worksheet.Cells[cells + "37"].Value = order[0].Handphone; worksheet.Cells[cells + "38"].Value = order[0].Address; worksheet.Cells["H33"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 4) { cells = "D"; worksheet.Cells[cells + "51"].Value = order[0].CustomerName; worksheet.Cells[cells + "52"].Value = order[0].Handphone; worksheet.Cells[cells + "53"].Value = order[0].Address; worksheet.Cells["H48"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 5) { cells = "K"; worksheet.Cells[cells + "6"].Value = order[0].CustomerName; worksheet.Cells[cells + "7"].Value = order[0].Handphone; worksheet.Cells[cells + "8"].Value = order[0].Address; worksheet.Cells["O3"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 6) { cells = "K"; worksheet.Cells[cells + "21"].Value = order[0].CustomerName; worksheet.Cells[cells + "22"].Value = order[0].Handphone; worksheet.Cells[cells + "23"].Value = order[0].Address; worksheet.Cells["O18"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 7) { cells = "K"; worksheet.Cells[cells + "36"].Value = order[0].CustomerName; worksheet.Cells[cells + "37"].Value = order[0].Handphone; worksheet.Cells[cells + "38"].Value = order[0].Address; worksheet.Cells["O33"].Value = "#" + order[0].OrderID.ToString(); } ; if (idxData == 8) { cells = "K"; worksheet.Cells[cells + "51"].Value = order[0].CustomerName; worksheet.Cells[cells + "52"].Value = order[0].Handphone; worksheet.Cells[cells + "53"].Value = order[0].Address; worksheet.Cells["O48"].Value = "#" + order[0].OrderID.ToString(); } ; ord.Entity.OrderStatus = "Printed"; new OrderRepository().Update(uow, new SaveRequest <MyRow> { Entity = ord.Entity, EntityId = ord.Entity.OrderId }); } idxData++; idxCol++; } return(ExcelContentResult.Create(package.GetAsByteArray(), filename)); } }