public void ExcelReport(IQueryable <Rest> rests)
{
DataTableClass dt = new DataTableClass(rests);
using (var stream = new MemoryStream())
{
//var builder = new ExcelReportBuilder();
var builder = new ReportViewExcelBuilder();
builder.Create(stream, dt.dt);
Response.Clear();
Response.ContentType = "text/html";
Response.AddHeader("content-disposition", "attachment;fileName=RestReport" + DateTime.Now.ToString("_dd_MM_yyyy") + ".xlsx");
Response.ContentEncoding = Encoding.UTF8;
Response.BinaryWrite(stream.ToArray());
Response.Flush();
Response.End();
}
// XLWorkbook wb = new XLWorkbook();
// wb.Worksheets.Add(dt.dt, "WorksheetName");
// var work = wb.Worksheets;
// var z = "C:\\Users\\" + wb.Author + "\\Downloads\\RestReport" + DateTime.Now.ToString("_dd_MM_yyyy") + ".xlsx";
//wb.SaveAs("C:\\Users\\" + wb.Author + "\\Downloads\\RestReport" + DateTime.Now.ToString("_dd_MM_yyyy") + ".xlsx");
//ViewBag.Rests = db.GetRests();
//return RedirectToAction("Index");
}
private static Dictionary <int, List <List <string> > > AnalysisExcel(DataTableClass dataTableClass, int index)
{
if (dataTableClass == null)
{
return(null);
}
List <List <string> > dataList = new List <List <string> >();
//解析需要保存的XML名(第一行第一列的值)
string _fileName = dataTableClass.GetValue(0, 0).ToString();
//将文件名存入到第一个数据
List <string> fileNameList = new List <string>();
fileNameList.Add(_fileName);
dataList.Add(fileNameList);
int columns = dataTableClass.Cols;
int rows = dataTableClass.Rows;
//遍历所有属性名（第二行，第二列以后整行数据）
List <string> AttributeList = new List <string>();
for (int i = 1; i < columns; ++i)
{
string value = dataTableClass.GetValue(1, i).ToString();
AttributeList.Add(value);
}
// 第二个数据保存属性值
dataList.Add(AttributeList);
// 表规则为从第四行，第二列开始读数据
for (int i = 3; i < rows; i++)
{
List <string> rowList = new List <string>();
bool isAvalidRow = true;
for (int j = 1; j < columns; j++)
{
string value = dataTableClass.GetValue(i, j).ToString();
if (j == 1 && i >= 3 && string.IsNullOrEmpty(value))
{
isAvalidRow = false;
break;
}
rowList.Add(value);
}
if (isAvalidRow)
{
dataList.Add(rowList);
}
}
dataDic[index] = dataList;
return(dataDic);
}
private static void GetCSAttributeFile(DataTableClass dataTableClass)
{
StringBuilder CSContentStr = new StringBuilder();
List <List <string> > dataList = new List <List <string> >();
int columns = dataTableClass.Cols;
int rows = dataTableClass.Rows;
string _fileName = dataTableClass.GetValue(0, 0).ToString();
//将文件名存入到第一个数据
List <string> fileNameList = new List <string>();
fileNameList.Add(_fileName);
dataList.Add(fileNameList);
//第三行第三列存储的是数据类型
CSContentStr.AppendLine("using System;");
CSContentStr.AppendLine("using System.Collections.Generic;");
CSContentStr.AppendLine("using System.Text;");
string className = ProcessName(_fileName);
CSContentStr.AppendLine("public class " + className + "{");
//遍历所有属性名（第二行，第二列以后整行数据）
List <string> AttributeList = new List <string>();
for (int i = 1; i < columns; ++i)
{
string value = dataTableClass.GetValue(2, i).ToString();
AttributeList.Add(value);
//第三行数据数据结构处理
//解析前三行 生成表结构
string paramDesc = dataTableClass.GetValue(0, i).ToString();
string paramType = dataTableClass.GetValue(2, i).ToString();
paramType = GetAvalidType(paramType);
string paramName = dataTableClass.GetValue(1, i).ToString();
if (string.IsNullOrEmpty(paramName) || paramName.CompareTo("") == 0)
{
continue;
}
CSContentStr.AppendLine("\t/// <summary>");
CSContentStr.AppendLine("\t///" + paramDesc);
CSContentStr.AppendLine("\t/// <summary>");
//当前列的结构语句
CSContentStr.AppendLine(string.Format("\tpublic {0} {1} ", paramType, paramName) + "{get;private set;}");
}
CSContentStr.AppendLine("}");
// 第二个数据保存属性值
dataList.Add(AttributeList);
_classCustom.Add(className, CSContentStr);
}
private static Dictionary <int, List <List <string> > > AnalysisExcel(DataTableClass dataTableClass, int index)
{
if (dataTableClass == null)
{
return(null);
}
List <List <string> > dataList = new List <List <string> >();
//解析需要保存的XML名(第一行第一列的值)
//将文件名存入到第一个数据
string _fileName = dataTableClass.GetValue(0, 0).ToString();
List <string> fileNameList = new List <string>();
fileNameList.Add(_fileName);
dataList.Add(fileNameList);
int rows = dataTableClass.Rows;
int columns = dataTableClass.Cols;
// 遍历前三行
// 第一行 属性说明
// 第二行 属性名
// 第三行 类型声明(int, string、、)
for (int i = 0; i < rows; ++i)
{
List <string> AttributeList = new List <string>();
bool isAvalidRow = true;
for (int j = 1; j < columns; ++j)
{
string value = dataTableClass.GetValue(i, j).ToString();
if (j == 1 && i >= 3 && string.IsNullOrEmpty(value))
{
isAvalidRow = false;
break;
}
AttributeList.Add(value);
}
if (isAvalidRow)
{
dataList.Add(AttributeList);
}
}
dataDic[index] = dataList;
return(dataDic);
}
public JsonResult IndexJson(DataTableClass responseBody)
{
DataTableClass test1 = responseBody;
Microsoft.EntityFrameworkCore.Query.IIncludableQueryable <Payment, Category> paymentsContext = _context.Payments.Include(p => p.Category);
// return Json("{\"draw\": 1,\"recordsTotal\": 3,\"recordsFiltered\": 3,\"data\":"+ Json(paymentsContext.Take(3)) +"}");
string test2 = "{\"draw\": 1,\"recordsTotal\": 3,\"recordsFiltered\": 3,\"data\":[{\"id\":1,\"payDate\":\"2018-07-31T00:00:00\",\"amount\":1,\"description\":\"111\",\"catogoryId\":1,\"category\":{\"id\":1,\"name\":\"CDA\",\"isActive\":true}},{\"id\":2,\"payDate\":\"2018-08-07T15:00:00\",\"amount\":2345789,\"description\":\"dfh dj\",\"catogoryId\":6,\"category\":{\"id\":6,\"name\":\"ENJ\",\"isActive\":true}},{\"id\":1003,\"payDate\":\"2018-08-09T11:03:00\",\"amount\":3457,\"description\":\"eyjtjrj\",\"catogoryId\":9,\"category\":{\"id\":9,\"name\":\"HLS\",\"isActive\":true}}]}";
string test = "{\"draw\":1,\"recordsTotal\":11,\"recordsFiltered\":11,\"data\":[[\"Airixi\",\"Satou\",\"Accountant\",\"Tokyo\",\"28th Nov 08\",\"$162,700\"],[\"Angeli\",\"Ramos\",\"Chief Executive Officer (CEO)\",\"London\",\"9th Oct 09\",\"$1,200,000\"],[\"Ashton\",\"Cox\",\"Junior Technical Author\",\"San Francisco\",\"12th Jan 09\",\"$86,000\"],[\"Bradly\",\"Greer\",\"Software Engineer\",\"London\",\"13th Oct 12\",\"$132,000\"],[\"Brendn\",\"Wagner\",\"Software Engineer\",\"San Francisco\",\"7th Jun 11\",\"$206,850\"],[\"Brielo\",\"Williamson\",\"Integration Specialist\",\"New York\",\"2nd Dec 12\",\"$372,000\"],[\"Brunos\",\"Nash\",\"Software Engineer\",\"London\",\"3rd May 11\",\"$163,500\"],[\"Caesar\",\"Vance\",\"Pre-Sales Support\",\"New York\",\"12th Dec 11\",\"$106,450\"],[\"Carata\",\"Stevens\",\"Sales Assistant\",\"New York\",\"6th Dec 11\",\"$145,600\"],[\"Cedric\",\"Kelly\",\"Senior Javascript Developer\",\"Edinburgh\",\"29th Mar 12\",\"$433,060\"]]}";
return(Json(test));
//return test2;
}