public ActionResult DATATEST()
{
var ss = Request.Files[0].FileName;
Request.Files[0].SaveAs(Server.MapPath("/uploadXLSXfile/" + ss));
//insert(ss);
DataTable table = AsposeOfficeHelper.ReadExcel(Server.MapPath(@"\uploadXLSXfile\" + ss));
//SessionHelper.Session["data"] = table;
//SessionHelper.Session["url"] = ss;
List <Base_User> userlist = new List <Base_User>();
foreach (DataRow item in table.Rows)
{
Base_User user = new Base_User();
user.RealName = item["姓名"].ToString();
user.Birthday = DateTime.Parse(item["生日"].ToString());
userlist.Add(user);
}
return(View("show", userlist));
}
public async Task ExcelExport()
{
DataTable dt = new DataTable();
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("age", typeof(int));
DataRow dr = dt.NewRow();
dr["name"] = "111";
dr["age"] = 11;
dt.Rows.Add(dr);
HttpContext.Response.Clear();
// HttpContext.Response.Buffer = true;
// HttpContext.Response.c = "utf-8";
Response.Headers.Add("Access-Control-Allow-Origin", "*");
Response.Headers.Add("Access-Control-Allow-Headers", Request.Headers["Access-Control-Request-Headers"]);
Response.Headers.Add("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS");
HttpContext.Response.ContentType = "application/vnd.ms-excel";
byte[] b = AsposeOfficeHelper.DataTableToExcelBytes(dt);
await Response.Body.WriteAsync(b);
}
public ActionResult insert()
{
string filename = SessionHelper.Session["url"].ToString();
DataTable table = AsposeOfficeHelper.ReadExcel(Server.MapPath(@"\uploadXLSXfile\" + filename));
SqlConnection con = new SqlConnection("server=.;database=exsil;uid=sa;pwd=123;");
con.Open();
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con);
sqlBulkCopy.DestinationTableName = "users";
sqlBulkCopy.BatchSize = table.Rows.Count;
sqlBulkCopy.ColumnMappings.Add("序号", "ID");
sqlBulkCopy.ColumnMappings.Add("姓名", "name");
sqlBulkCopy.ColumnMappings.Add("年龄", "age");
sqlBulkCopy.ColumnMappings.Add("生日", "birthday");
sqlBulkCopy.WriteToServer(table);
return(Content("ok"));
}
public ActionResult DataSetToExcel()
{
// string ss= ConfigHelper.GetConnectionString("BaseDb");
SqlConnection con = new SqlConnection("server=.;database=exsil;uid=sa;pwd=123;");
string sql = "select * from users";
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable user = ds.Tables[0];
var userbaty = AsposeOfficeHelper.DataTableToExcelBytes(user);
DataTable dataTable = ds.Tables[0];
int rowNumber = dataTable.Rows.Count;
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
return(Content("d"));;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//excel.Application.Workbooks.Open()
excel.Visible = true;//是否打开该Excel文件
//读取用户字段
string jsonfile = Server.MapPath("/Config/user.json");
System.IO.StreamReader file = System.IO.File.OpenText(jsonfile);
//加载问卷
JsonTextReader reader = new JsonTextReader(file);
//转化为JObject
JObject ojb = (JObject)JToken.ReadFrom(reader);
var jj = ojb["user"].ToString();
JObject jo = (JObject)JsonConvert.DeserializeObject(jj);
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = jo[col.ColumnName];
}
//填充数据
for (int c = 1; c <= rowNumber; c++)
{
for (int j = 0; j < columnNumber; j++)
{
excel.Cells[c + 1, j + 1] = dataTable.Rows[c - 1].ItemArray[j];
}
}
return(Content("ds"));
}
/// <summary>
/// 导出测评
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task <byte[]> ExportHrEvaluationInfo(string id)
{
HrEvaluationDTO vm = await GetHrEvaluationInfo(new IdInputDTO()
{
id = id
});
DataTable tblDatas = new DataTable();
var evaluation_name = new List <object> {
"测评名称", vm.evaluation_name
};
var user_name = new List <object> {
"测评对象", vm.user_name
};
tblDatas.Columns.Add("编号");
tblDatas.Columns.Add("考核项目");
var totalRow = new List <object> {
"", "合计"
};
//动态添加测评人表头
vm.TemplateInfo.templatItems?[0].H_Score?.ForEach(user =>
{
tblDatas.Columns.Add(user.assessment_user_name);
totalRow.Add(0);//合计行 默认都是0
});
//循环模板项
int i = 0;
vm.TemplateInfo.templatItems?.ForEach((template) =>
{
i++;
int j = 1;
var row = new List <object> {
i, template.item_name
};
template.H_Score.ForEach(user =>
{
j++;
row.Add(user.score);
totalRow[j] = (int)totalRow[j] + user.score;
});
tblDatas.Rows.Add(row.ToArray());
});
//合计列
tblDatas.Rows.Add(totalRow.ToArray());
tblDatas.Rows.Add(tblDatas.NewRow());//空列
//测评名称和测评对象
tblDatas.Rows.Add(evaluation_name.ToArray());
tblDatas.Rows.Add(user_name.ToArray());
tblDatas.Rows.Add(tblDatas.NewRow());//空列
//测评人
vm.H_users.GroupBy(g => new { g.user_type })
.ForEach(groupItem =>
{
var H_User = new List <object>()
{
(AssessmentUserType)groupItem.Key.user_type
};
H_User.AddRange(groupItem.Select(x => x.user_name).ToList());
tblDatas.Rows.Add(H_User.ToArray());
});
return(AsposeOfficeHelper.DataTableToExcelBytes(tblDatas));
}
public async Task <byte[]> ExcelToExport(ConditionDTO input)
{
Expression <Func <F_Order, F_UserInfo, IF_OrderResultDTO> > select = (a, b) => new IF_OrderResultDTO
{
UserName = b.UserName,
DepartmentName = b.Department,
FoodName = string.Join(",", (from c in Service.GetIQueryable <F_OrderInfo>()
join d in Service.GetIQueryable <F_PublishFood>() on c.PublishFoodId equals d.Id
where c.OrderCode == a.OrderCode
select d.FoodName)),
SupplierName = string.Join(",", (from c in Service.GetIQueryable <F_OrderInfo>()
join d in Service.GetIQueryable <F_PublishFood>() on c.PublishFoodId equals d.Id
where c.OrderCode == a.OrderCode
select d.SupplierName)),
OldDepartmentName = Service.GetIQueryable <Base_DepartmentRelation>().FirstOrDefault(c => c.Department == b.FullDepartment).OldDepartment
};
select = select.BuildExtendSelectExpre();
var q = from a in GetIQueryable().AsExpandable()
join b in Service.GetIQueryable <F_UserInfo>() on a.UserInfoId equals b.Id into ab
from b in ab.DefaultIfEmpty()
select @select.Invoke(a, b);
var where = LinqHelper.True <IF_OrderResultDTO>();
var search = input;
//筛选
if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
{
where = where.And(a =>
a.CreateTime >= input.Keyword.ToDateTime().Date&& a.CreateTime < input.Keyword.ToDateTime().Date.AddDays(1) &&
a.Status != 4);
}
//增加按照部门排序
DataTable dt = q.Where(where).OrderBy(a => a.OldDepartmentName).Select(a => new
{
OldDepartmentName = a.OldDepartmentName,
UserName = a.UserName,
SupplierName = a.SupplierName,
FoodName = a.FoodName,
OrderCount = a.OrderCount,
Price = a.Price,
OrderCode = a.OrderCode,
CreateTime = a.CreateTime
}).ToList().ToDataTable();
if (dt != null && dt.Rows.Count == 0)
{
throw new BusException("无下载数据!");
}
if (dt.Columns.Contains("UserName"))
{
dt.Columns["UserName"].ColumnName = "用户名";
}
if (dt.Columns.Contains("SupplierName"))
{
dt.Columns["SupplierName"].ColumnName = "商户名称";
}
if (dt.Columns.Contains("FoodName"))
{
dt.Columns["FoodName"].ColumnName = "菜品名称";
}
if (dt.Columns.Contains("DepartmentName"))
{
dt.Columns["DepartmentName"].ColumnName = "部门名称";
}
if (dt.Columns.Contains("OldDepartmentName"))
{
dt.Columns["OldDepartmentName"].ColumnName = "部门名称";
}
if (dt.Columns.Contains("OrderCount"))
{
dt.Columns["OrderCount"].ColumnName = "数量";
}
if (dt.Columns.Contains("Price"))
{
dt.Columns["Price"].ColumnName = "价格";
}
if (dt.Columns.Contains("OrderCode"))
{
dt.Columns["OrderCode"].ColumnName = "订单编号";
}
if (dt.Columns.Contains("CreateTime"))
{
dt.Columns["CreateTime"].ColumnName = "下单时间";
}
await Task.CompletedTask;
return(AsposeOfficeHelper.DataTableToExcelBytes(dt));
}
public async Task <byte[]> SumExcelToExport(ConditionDTO input)
{
Expression <Func <F_Order, F_UserInfo, F_PublishFood, IF_OrderResultDTO> > select = (a, b, c) =>
new IF_OrderResultDTO
{
UserName = b.UserName,
DepartmentName = b.FullDepartment,
FoodName = c.FoodName,
TakeFoodCode = a.TakeFoodCode,
TakeFoodName = a.TakeFoodName,
OldDepartmentName = Service.GetIQueryable <Base_DepartmentRelation>()
.FirstOrDefault(d => d.Department == b.FullDepartment).OldDepartment
};
select = select.BuildExtendSelectExpre();
var q = from a in GetIQueryable().AsExpandable()
join b in Service.GetIQueryable <F_UserInfo>() on a.UserInfoId equals b.Id into ab
from b in ab.DefaultIfEmpty()
join c in Service.GetIQueryable <F_OrderInfo>() on a.OrderCode equals c.OrderCode
join d in Service.GetIQueryable <F_PublishFood>() on c.PublishFoodId equals d.Id
select @select.Invoke(a, b, d);
var where = LinqHelper.True <IF_OrderResultDTO>();
var search = input;
//筛选
if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
{
where = where.And(a =>
a.CreateTime >= input.Keyword.ToDateTime().Date&& a.CreateTime <
input.Keyword.ToDateTime().Date.AddDays(1) &&
a.Status != 4);
}
//增加按照部门排序
var orderResultList = q.Where(where).OrderBy(a => a.OldDepartmentName).ToList();
DataTable dt = orderResultList.OrderBy(a => a.TakeFoodCode.ToInt()).GroupBy(a => new { DepartmentName = a.OldDepartmentName ?? a.DepartmentName, a.FoodName }).
Select(a => new
{
TakeFoodCode = "A" + a.FirstOrDefault().TakeFoodCode,
DepartmentName = a.Key.DepartmentName,
FoodName = a.Key.FoodName,
Count = a.Count(),
TakeFoodName = a.FirstOrDefault().TakeFoodName,
}
).ToDataTable();
if (dt != null && dt.Rows.Count == 0)
{
throw new BusException("无下载数据!");
}
if (dt.Columns.Contains("TakeFoodCode"))
{
dt.Columns["TakeFoodCode"].ColumnName = "取餐码";
}
if (dt.Columns.Contains("DepartmentName"))
{
dt.Columns["DepartmentName"].ColumnName = "部门名称";
}
if (dt.Columns.Contains("FoodName"))
{
dt.Columns["FoodName"].ColumnName = "菜品";
}
if (dt.Columns.Contains("Count"))
{
dt.Columns["Count"].ColumnName = "数量";
}
if (dt.Columns.Contains("TakeFoodName"))
{
dt.Columns["TakeFoodName"].ColumnName = "领餐人";
}
await Task.CompletedTask;
return(AsposeOfficeHelper.DataTableToExcelBytes(dt));
}