public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.CustomerColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "ClientList_" +
DateTime.Now.ToString("MMddyyyy_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.StudentDeclarationBooksellerColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "征订计划导出_学生用书_" +
DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new Serenity.Reporting.DynamicDataReport(data, request.IncludeColumns, typeof(Columns.LQASColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "LQAS_" +
System.DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, CustomerGrossSalesListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns,
typeof(Columns.CustomerGrossSalesColumns), HttpContext.RequestServices);
var bytes = ReportRepository.Render(report);
return(ExcelContentResult.Create(bytes, "CustomerGrossSales_" +
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.Thesis.Columns.ThesisRegistrationColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "RegistrationList_" +
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"));
}
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));
}
}
