protected void btn_Click(object sender, EventArgs e)
{
var data = Session["Tmp_data"] as IEnumerable <AClass>;
if (data == null)
{
return;
}
var filepath = Server.MapPath("~/TempFile/" + Guid.NewGuid().ToString() + ".xls");
using (IExcel xlc = new WpsExcel())
{
int i = 2;
xlc.SetValue("A1", "日期");
xlc.SetValue("B1", "生产总数");
xlc.SetValue("C1", "良品数");
xlc.SetValue("D1", "不良品数");
xlc.SetValue("E1", "不良率");
foreach (var x in data)
{
xlc.SetValue("A" + i.ToString(), x.Date.ToString("MM-dd"));
xlc.SetValue("B" + i.ToString(), x.ZongShu);
xlc.SetValue("C" + i.ToString(), x.Good);
xlc.SetValue("D" + i.ToString(), x.Bad);
xlc.SetValue("E" + i.ToString(), (((float)x.Bad * 100 / (float)x.ZongShu)).ToString("f2") + "%");
i++;
}
xlc.SaveAs(filepath);
}
Response.ContentType = "application/vnd.ms-excel;charset=utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=data.xls");
Response.WriteFile(filepath);
Response.Flush();
Response.End();
}
protected string XLS(char mid, WorkStep ws, DateTime dt)
{
var filepath = Server.MapPath("~/TempFile/" + Guid.NewGuid().ToString() + ".xls");
var db = BAL.DB.Current;
var q = from x in db.sm_biz_Workpiece
join s in db.sm_biz_State on x.State equals s.ID
where x.MachineID == mid && x.Date >= dt && x.Date < dt.AddMonths(1) &&
(x.State == 0 || x.State / 10 == (int)ws)
select new { Workpiece = x, State = s };
var data = q.ToList();
using (IExcel xls = new WpsExcel())
{
xls.ChangeCurrentSheet(2);
int i = 2;
foreach (var e in data)
{
xls.SetValue("A" + i.ToString(), e.Workpiece.FullSerial);
xls.SetValue("B" + i.ToString(), e.State.Name);
xls.SetValue("C" + i.ToString(), ((WorkStep)e.State.Group).ToString());
xls.SetValue("D" + i.ToString(), e.State.ID);
i++;
}
xls.ChangeCurrentSheet(1);
var q1 = (from x in data
where x.State.ID != 0
group x by x.Workpiece.Date into g
select new { Key = g.Key, Count = g.Count() }).ToList();
xls.SetValue("A1", "时间");
xls.SetValue("B1", "不良数");
i = 3;
var sts = db.sm_biz_State.Where(x => x.Group == (int)ws).ToList();
foreach (var e in sts)
{
xls.SetValue(1, i, e.Name);
i++;
}
i = 2;
int cc = 0;
foreach (var e in q1)
{
xls.SetValue("A" + i.ToString(), e.Key.ToString("MM月dd日"));
xls.SetValue("B" + i.ToString(), e.Count);
for (int j = 0; j < sts.Count; j++)
{
cc = (from x in db.sm_biz_Workpiece
where x.Date == e.Key && x.State == sts[j].ID
select x).Count();
xls.SetValue(i, j + 3, cc);
}
i++;
}
var cht = new GoogleApi.Chart(new GoogleApi.ChartType.Bar(GoogleApi.ChartType.BarType.bvs));
cht.Size.Height = 200;
cht.Size.Width = 500;
//cht.Data.Group.Add(new List<float>(q1.Select(x => (float)x.Key.Day)));
cht.Data.Group.Add(new List <float>(q1.Select(x => (float)x.Count)));
//cht.Fix.Items.Add(q1.Select(x => x.Count).Min());
cht.Fix.Items.Add(0);
cht.Fix.Items.Add(q1.Select(x => x.Count).Max());
cht.Axis.AxisX.AddRange(q1.Select(x => x.Key.Day.ToString()));
for (var j = 0; j <= q1.Select(x => x.Count).Max(); j++)
{
cht.Axis.AxisY.Add(j.ToString());
}
//cht.Axis.AxisY.Add(.ToString());
i += 2;
//xls.InsertPicture("A" + i.ToString(), cht.Download(Server.MapPath("~/TempFile/")));
xls.SaveAs(filepath);
}
return(filepath);
}
