/// <summary>
/// 导出终端列表到excel
/// </summary>
/// <param name="bll"></param>
/// <param name="excel"></param>
private void ExportTerminalsToExcel(ExcelHandlerBLL bll, TB_ExcelHandler excel)
{
string source = "", data = "";
Application app = null;
Workbook book = null;
Worksheet sheet = null;
try
{
app = new Application();
book = app.Workbooks.Open(EXCEL_PATH + EXCEL_TERMINALS);
sheet = (Worksheet)book.ActiveSheet;
app.Visible = false;
app.AlertBeforeOverwriting = false;
app.DisplayAlerts = false;
using (var tbll = new TerminalBLL())
{
using (var ebll = new EquipmentBLL())
{
int line = 2;
int cnt = 0;
var n = (int?)null;
var list = tbll.FindList(f => f.Delete == false);
foreach (var obj in list)
{
var x = line + cnt;
sheet.Cells[x, 1] = (cnt + 1);
sheet.Cells[x, 2] = obj.Number;
sheet.Cells[x, 3] = n == obj.Satellite ? "-" : obj.TB_Satellite.CardNo;
sheet.Cells[x, 4] = string.IsNullOrEmpty(obj.Firmware) ? "-" : obj.Firmware;
sheet.Cells[x, 5] = obj.Revision;
sheet.Cells[x, 6] = TerminalTypes.GetTerminalType(obj.Type.Value);
sheet.Cells[x, 7] = obj.ProductionDate.Value.ToString("yyyy/MM/dd");
var e = ebll.Find(d => d.Terminal == obj.id && d.Deleted == false);
sheet.Cells[x, 8] = null == e ? "-" : ebll.GetFullNumber(e);
var link = EverdigmUtils.GetOnlineStyle(obj.OnlineStyle, obj.OnlineTime, false);
link = link.Substring(link.IndexOf('>') + 1);
link = link.Substring(0, link.IndexOf('<'));
sheet.Cells[x, 9] = string.IsNullOrEmpty(link) ? "-" : link;
sheet.Cells[x, 10] = null == obj.OnlineTime ? "-" : obj.OnlineTime.Value.ToString("yyyy/MM/dd HH:mm");
cnt++;
}
}
}
// 另存为别的
var date = excel.CreateDate.Value.ToString("yyyyMMdd");
var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
source = path + "\\Terminals2Excel_" + excel.CreateDate.Value.ToString("yyyyMMdd") + ".xlsx";
if (File.Exists(source))
{
File.Delete(source);
}
book.SaveAs(source);
}
catch (Exception e)
{
data = e.StackTrace;
ShowUnhandledMessage(format("{0}Terminal to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
}
finally
{
// 关闭book
if (null != book)
{
book.Close();
book = null;
}
// 关闭application
if (null != app)
{
app.Quit();
app = null;
}
// 释放内存
GC.Collect();
GC.WaitForPendingFinalizers();
}
var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");
bll.Update(f => f.id == excel.id, act =>
{
act.Handled = true;
act.Status = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
act.Target = target;
act.Data = data;
});
}
/// <summary>
/// 导出设备列表到excel
/// </summary>
/// <param name="bll"></param>
/// <param name="excel"></param>
private void ExportEquipmentsToExcel(ExcelHandlerBLL bll, TB_ExcelHandler excel)
{
// data为保存出错时的异常数据
string source = "", data = "";
Application app = null;
Workbook book = null;
Worksheet sheet = null;
try
{
app = new Application();
book = app.Workbooks.Open(EXCEL_PATH + EXCEL_EQUIPMENTS);
sheet = (Worksheet)book.ActiveSheet;
app.Visible = false;
app.AlertBeforeOverwriting = false;
app.DisplayAlerts = false;
using (var ebll = new EquipmentBLL())
{
int line = 3;
int cnt = 0;
var n = (int?)null;
var list = ebll.FindList(f => f.Deleted == false);
foreach (var obj in list)
{
var x = line + cnt;
sheet.Cells[x, 1] = (cnt + 1);
sheet.Cells[x, 2] = n == obj.Model ? "-" : obj.TB_EquipmentModel.TB_EquipmentType.Code;
sheet.Cells[x, 3] = n == obj.Model ? "-" : ebll.GetFullNumber(obj);
sheet.Cells[x, 4] = EverdigmUtils.GetEquipmentFunctional(obj.Functional.Value);
sheet.Cells[x, 5] = EquipmentBLL.GetRuntime(obj.Runtime + obj.InitializedRuntime, obj.CompensatedHours.Value);
sheet.Cells[x, 6] = ebll.GetEngineState(obj);
sheet.Cells[x, 7] = string.IsNullOrEmpty(obj.GpsAddress) ? "-" : obj.GpsAddress;
// 状态
sheet.Cells[x, 8] = obj.TB_EquipmentStatusName.Code;
// customer
sheet.Cells[x, 9] = n == obj.Customer ? "-" : obj.TB_Customer.Code;
sheet.Cells[x, 10] = n == obj.Customer ? "-" : obj.TB_Customer.Name;
// 终端
var link = EverdigmUtils.GetOnlineStyle(obj.OnlineStyle, obj.OnlineTime, false);
link = link.Substring(link.IndexOf('>') + 1);
link = link.Substring(0, link.IndexOf('<'));
sheet.Cells[x, 11] = string.IsNullOrEmpty(link) ? "-" : link;
string alarm = ebll.GetAlarmStatus(obj.Alarm);
alarm = alarm.Substring(alarm.IndexOf("title=\"") + 7);
alarm = alarm.Substring(0, alarm.IndexOf('"'));
sheet.Cells[x, 12] = alarm.Contains("No") ? "-" : alarm;
sheet.Cells[x, 13] = null == obj.LastActionTime ? "-" : obj.LastActionTime.Value.ToString("yyyy/MM/dd HH:mm");
sheet.Cells[x, 14] = n == obj.Terminal ? "-" : obj.TB_Terminal.Number;
bool sat = n != obj.Terminal && n != obj.TB_Terminal.Satellite;
sheet.Cells[x, 15] = sat ? obj.TB_Terminal.TB_Satellite.CardNo : "-";
sheet.Cells[x, 16] = n == obj.Warehouse ? "-" : obj.TB_Warehouse.Name;
cnt++;
}
}
// 另存为别的
var date = excel.CreateDate.Value.ToString("yyyyMMdd");
var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
source = path + "\\Equipments2Excel_" + excel.CreateDate.Value.ToString("yyyyMMdd") + ".xlsx";
if (File.Exists(source))
{
File.Delete(source);
}
book.SaveAs(source);
}
catch (Exception e)
{
data = e.StackTrace;
ShowUnhandledMessage(format("{0}Equipment to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
}
finally
{
// 关闭book
if (null != book)
{
book.Close();
book = null;
}
// 关闭application
if (null != app)
{
app.Quit();
app = null;
}
// 释放内存
GC.Collect();
GC.WaitForPendingFinalizers();
}
var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");
bll.Update(f => f.id == excel.id, act =>
{
act.Handled = true;
act.Target = target;
act.Status = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
act.Data = data;
});
}
private void ExportWorkTimeToExcel(TB_ExcelHandler excel, ExcelHandlerBLL bll)
{
string source = "", data = "";
Application app = null;
Workbook book = null;
Worksheet sheet = null;
try
{
app = new Application();
book = app.Workbooks.Open(EXCEL_PATH + EXCEL_WORKTIME);
sheet = (Worksheet)book.ActiveSheet;
app.Visible = false;
app.AlertBeforeOverwriting = false;
app.DisplayAlerts = false;
var equipment = excel.TB_Equipment.TB_EquipmentModel.Code + excel.TB_Equipment.Number;
sheet.Name = equipment;
// 所属公司
string customer = "";
if (excel.TB_Equipment.Customer == (int?)null)
{
customer = "invalid";
}
else
{
customer = excel.TB_Equipment.TB_Customer.Name;
}
sheet.Cells[3, 3] = customer;
// 设备型号
sheet.Cells[4, 3] = excel.TB_Equipment.TB_EquipmentModel.Name;
// 设备号码
sheet.Cells[5, 3] = excel.TB_Equipment.Number;
// 设备出库日期
string outdoor = "";
if (null == excel.TB_Equipment.OutdoorTime)
{
outdoor = "invalid";
}
else
{
outdoor = excel.TB_Equipment.OutdoorTime.Value.ToString("yyyy/MM/dd");
}
sheet.Cells[6, 3] = outdoor;
// 打印日期
sheet.Cells[6, 9] = excel.CreateDate.Value.ToString("yyyy/MM/dd");
// 出库类型
sheet.Cells[7, 3] = excel.TB_Equipment.TB_EquipmentStatusName.Code;
sheet.Cells[8, 3] = excel.TB_Equipment.GpsAddress;
// 查询开始日期
sheet.Cells[10, 3] = excel.StartDate;
sheet.Cells[11, 3] = excel.EndDate;
// 组织数据
List <WorktimeChart> works = JsonConverter.ToObject <List <WorktimeChart> >(excel.Data);
int row = 0, cell = 1, page = 0, len = works.Count, count = 0;
double total = 0.0, pcount = 0.0;
for (int i = 0; i < len; i++)
{
count++;
page = i / (pageSize);
var baseRow = page * pageRows;
cell = (i / pageCount) % 2 == 0 ? 1 : 6;
row = baseRow + i % pageCount;
row += lineStart;
sheet.Cells[row, cell] = count;
sheet.Cells[row, cell + 1] = works[i].date;
sheet.Cells[row, cell + 2] = works[i].y;
total += works[i].y;
pcount += works[i].y;
// 如果运转时间等于0时，将上一条运转时间复制过来
if (works[i].min == 0 && i > 0)
{
works[i].min = works[i - 1].min;
}
sheet.Cells[row, cell + 3] = works[i].min / 60.0;
if (count % pageCount == 0)
{
sheet.Cells[row + 1, cell + 1] = "subtotal";
sheet.Cells[row + 1, cell + 2] = pcount;
Range range = sheet.Range[sheet.Cells[row + 1, cell], sheet.Cells[row + 1, cell + 3]];
range.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
range.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].Color = XlRgbColor.rgbGray;
range.Cells.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
pcount = 0.0;
}
}
// 总运转时间
sheet.Cells[12, 3] = total;
//sheet.Cells[12, 4] = works[0].add;
// 最后一页中的统计
sheet.Cells[row + 1, cell + 1] = "subtotal";
sheet.Cells[row + 1, cell + 2] = pcount;
Range last = sheet.Range[sheet.Cells[row + 1, cell], sheet.Cells[row + 1, cell + 3]];
last.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
last.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].Color = XlRgbColor.rgbGray;
last.Cells.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
// 另存为别的
var date = excel.CreateDate.Value.ToString("yyyyMMdd");
var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
source = path + "\\Equipment operation report_" + equipment + excel.CreateDate.Value.ToString("_HHmmss") + ".xlsx";
if (File.Exists(source))
{
File.Delete(source);
}
book.SaveAs(source);
}
catch (Exception e)
{
data = e.StackTrace;
ShowUnhandledMessage(format("{0}Worktime to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
}
finally
{
// 关闭book
if (null != book)
{
book.Close();
book = null;
}
// 关闭application
if (null != app)
{
app.Quit();
app = null;
}
// 释放内存
GC.Collect();
GC.WaitForPendingFinalizers();
}
var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");
bll.Update(f => f.id == excel.id, act =>
{
act.Handled = true;
act.Status = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
act.Target = target;
act.Data = data;
});
}
