public static void ExportPrepaidBills(IEnumerable <PrepaidBill> prepaidBills, string[] titles, string fileName)
{
workbook = new HSSFWorkbook();
InitializeWorkbook();
ISheet sheet = workbook.CreateSheet("能耗预付费实时账单");
//SetPrintSetting(sheet); // 设置打印格式
SetHeaderTitle(sheet, titles); // 设置标题栏
int rowIndex = 0;
for (int i = 0; i < prepaidBills.Count(); i++)
{
PrepaidBill bill = prepaidBills.ElementAt(i);
int deviceCount = bill.PrepaidDeviceBills.Count();
IRow row = CreateCommonRow(sheet, ++rowIndex);
// 房间编号
CreateCommonCell(sheet, row, 0, bill.RoomNo);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 0, 0));
// 建筑编号
CreateCommonCell(sheet, row, 1, bill.BuildingNo);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 1, 1));
// 业主姓名
CreateCommonCell(sheet, row, 2, bill.RealName);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 2, 2));
// 总价格
CreateCommonCell(sheet, row, 9, bill.SumMoney);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 9, 9));
// 账户余额
CreateCommonCell(sheet, row, 10, bill.AccountBalance);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 10, 10));
// 结算余额
CreateCommonCell(sheet, row, 11, bill.BilledBalance);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + deviceCount - 1, 11, 11));
for (int j = 0; j < deviceCount; j++)
{
PrepaidDeviceBill deviceBill = bill.PrepaidDeviceBills.ElementAt(j);
CreateCommonCell(sheet, row, 3, deviceBill.DeviceNo);
CreateCommonCell(sheet, row, 4, deviceBill.DeviceName);
CreateCommonCell(sheet, row, 5, deviceBill.PreValue.ToString());
CreateCommonCell(sheet, row, 6, deviceBill.CurValue.ToString());
CreateCommonCell(sheet, row, 7, deviceBill.Price);
CreateCommonCell(sheet, row, 8, deviceBill.Money);
if (j != deviceCount - 1)
{
row = CreateCommonRow(sheet, ++rowIndex);
}
}
}
WriteToFile(fileName);
}
private IEnumerable <PrepaidDeviceBill> GetPrepaidDeviceBills(string RoomNo)
{
List <PrepaidDeviceBill> bills = new List <PrepaidDeviceBill>();
var devices = from item in db.Devices where item.RoomNo == RoomNo select item;
foreach (var item in devices)
{
PrepaidDeviceBill bill = new PrepaidDeviceBill();
bill.DeviceNo = item.DeviceNo;
bill.DeviceName = item.DeviceName;
bill.PreValue = Math.Round(item.PreValue ?? 0.00, 2);
bill.CurValue = Math.Round((item.Value * item.Rate) ?? 0.00, 2);
bill.Price = TextHelper.ConvertMoney(item.DeviceType.Price1);
bill.IntMoney = (int)((bill.CurValue - bill.PreValue) * item.DeviceType.Price1);
bill.Money = TextHelper.ConvertMoney(bill.IntMoney);
bills.Add(bill);
}
return(bills);
}