public virtual ActionResult Select(JsSelectOptions Options)//string EntitySetName, string EntityName, Objects.JsIncludeParameter[] Includes = null, Objects.JsWhereParameter[] Wheres = null, Objects.JsOrderParameter[] Orders = null, int Skip = -1, int Take = -1, string WhereMethod = null, string OrderMethod = null)
{
string json = this.GetCache(Options);
if (json.IsNotNullOrEmpty())
{
return(this.Content(json, "application/json"));
}
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
Selecter <ObjectContext> selecter = new Selecter <ObjectContext>(model);
JsSelectResult data = selecter.Select(Options);//EntitySetName, EntityName, Includes, Wheres, Orders, Skip, Take, WhereMethod, OrderMethod);
List <string> log = data.Data as List <string>;
if (log != null)
{
log.Add(string.Format("{0:hh:mm.ss:ffff} Select data serialized", DateTime.Now));
}
var result = new
{
code = 200,
result = data.ToJson()
};
json = serializer.Serialize(result);
this.SetCache(json, Options);
return(this.Content(json, "application/json"));
}
public JsonResult Export(JsExportOptions Options)
{
byte[] content = null;
int startRow = 1, startColumn = 1;
JsonResult result = new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
string id = Guid.NewGuid().ToString();
JsSelectOptions so = Options.SelectOptions;
if (Options.Type == ExportTypesEnum.AllRows)
{
so.Skip = -1;
so.Take = -1;
}
Selecter <ObjectContext> selecter = new Selecter <ObjectContext>(model);
JsSelectResult selectResult = selecter.Select(so);
IEnumerable <EntityObject> data = selectResult.Collections[so.EntitySetName];
string fileName = Options.Name.StringAndTrim();
fileName += !fileName.EndsWith(".xlsx") ? ".xlsx" : "";
fileName = fileName.Replace(System.IO.Path.GetInvalidFileNameChars(), "");
OfficeOpenXml.ExcelPackage p = new OfficeOpenXml.ExcelPackage();
OfficeOpenXml.ExcelWorkbook book = p.Workbook;
OfficeOpenXml.ExcelWorksheet sheet;
Type itType;
EntityObject first;
try
{
first = data.FirstOrDefault();
itType = first != null?first.GetType() : null;
sheet = book.Worksheets.Add(Options.Name);
int i = 0;
foreach (JsExportParameter ep in Options.Parameters)
{
ep.Compile(itType);
sheet.Cells[startRow, startColumn + i].Value = ep.Name;
sheet.Column(startColumn + i).AutoFit();
sheet.Column(startColumn + i).BestFit = true;
i++;
}
if (!data.Any())
{
sheet.Row(startRow).Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Row(startRow).Style.Font.Bold = true;
using (MemoryStream ms = new MemoryStream())
{
p.SaveAs(ms);
content = ms.ToArray();
result.Data = new { Success = true, Result = data.Count(), ID = id };
HttpContext.Cache[id] = File(content, XlsxContentType, fileName);
return(result);
}
}
i = startRow + 1;
foreach (EntityObject item in data)
{
for (int j = 0; j < Options.Parameters.Length; j++)
{
JsExportParameter par = Options.Parameters[j];
object value = par.GetValue(item);
if (value != null && value.GetType() == typeof(DateTime))
{
value = ((DateTime)value).ToShortDateString();
}
sheet.Cells[i, startColumn + j].Value = value;
}
i++;
}
sheet.Row(startRow).Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Row(startRow).Style.Font.Bold = true;
for (i = 0; i < sheet.Dimension.End.Column; i++)
{
sheet.Column(startColumn + i).AutoFit();
sheet.Column(startColumn + i).BestFit = true;
}
using (MemoryStream ms = new MemoryStream())
{
p.SaveAs(ms);
content = ms.ToArray();
result.Data = new { Success = true, Result = new { Count = data.Count(), ID = id }, Code = 200 };
HttpContext.Cache[id] = File(content, XlsxContentType, fileName);
}
}
catch (Exception ex)
{
result.Data = new { Success = false, Error = ex.Message };
}
finally
{
p.Dispose();
}
return(result);
}