public FileResult ExportExcel(string dateBegin, string dateEnd)
{
string Condition = " and 1=1";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and PatiInBillItem.CreateDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and PatiInBillItem.CreateDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserBillTotal> billTotal = new List <UserBillTotal>();
DataTable lstBillItem = billitemLogic.GetPersonBillTypeAmount(Condition, 9999, 1, "", "");
if (lstBillItem != null && lstBillItem.Rows.Count > 0)
{
foreach (DataRow drNew in lstBillItem.Rows)
{
UserBillTotal item = new UserBillTotal();
item.BillDate = drNew[0].ToString();
item.TotalAmount = decimal.Parse(drNew[1].ToString());
billTotal.Add(item);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
// List<modelList> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("就餐日期");
row1.CreateCell(1).SetCellValue("金额");
//将数据逐步写入sheet1各个行
for (int i = 0; i < billTotal.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(billTotal[i].BillDate.ToString());
rowtemp.CreateCell(1).SetCellValue(billTotal[i].TotalAmount.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
public ActionResult UserMonthDayList(string dateBegin, string dateEnd, int?pageIndex, int?pageSize)
{
pageIndex = pageIndex + 1;
if (pageSize == null)
{
pageSize = 15;
}
string Condition = " and 1=1";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and PatiInBillItem.CreateDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and PatiInBillItem.CreateDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserBillTotal> billTotal = new List <UserBillTotal>();
DataTable lstBillItem = billitemLogic.GetPersonBillMonthAmount(Condition, pageSize.Value, pageIndex.Value, "", "");
if (lstBillItem != null && lstBillItem.Rows.Count > 0)
{
foreach (DataRow drNew in lstBillItem.Rows)
{
UserBillTotal item = new UserBillTotal();
item.BillDate = drNew[0].ToString();
item.TotalAmount = decimal.Parse(drNew[1].ToString());
billTotal.Add(item);
}
}
IDictionary <string, object> dic = new Dictionary <string, object>();
int count = billTotal.Count();
dic.Add("total", count);
dic.Add("data", billTotal);
return(Json(dic, JsonRequestBehavior.AllowGet));
}
