public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Create, FileAccess.Write);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Any())
{
try
{
DateTime dtTime = TemplateHandlerPrams.Date;
var t = TemplateHandlerPrams.Organization.SystemName;
FillBasicInformation(ww, 0, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 2, 0, "contractorUnit",
TemplateHandlerPrams.Structure.ConstructionCompany);
FillBasicInformation(ww, 2, 11, "contractNo", "数据库暂无");
FillBasicInformation(ww, 3, 0, "supervisingUnit", "数据库暂无");
FillBasicInformation(ww, 3, 11, "factorNo", Convert.ToString(TemplateHandlerPrams.Factor.Id));
FillBasicInformation(ww, 4, 0, "position", TemplateHandlerPrams.Structure.Name);
FillBasicInformation(ww, 5, 0, "date", dtTime.AddDays(-1).ToShortDateString());
FillBasicInformation(ww, 5, 4, "time", dtTime.ToShortTimeString());
FillBasicInformation(ww, 6, 0, "instrumentName", this.SensorProduct.ProductName);
FillBasicInformation(ww, 6, 4, "instrumentNo", this.SensorProduct.ProductCode);
var dateTime = dtTime.Date;
var todayData = DataAccess.GetUnifyData(SensorsId, dateTime.AddDays(-1), dateTime.AddMilliseconds(-1), true);
var yesterData = DataAccess.GetUnifyData(SensorsId, dateTime.AddDays(-2), dateTime.AddDays(-1).AddMilliseconds(-1),
true);
var sheet = ww.Worksheets[0];
int StartRowIndex = 9;
int AnotherRowIndex = 9;
for (int i = 0; i < Cdbh.Length; i++)
{
if (i >= 8)
{
sheet.Cells[AnotherRowIndex, 7].PutValue(Cdbh[i]);
FillDayData(ww, AnotherRowIndex, 11, 0, 0, Cdbh[i], yesterData);
FillDayData(ww, AnotherRowIndex, 12, 0, 0, Cdbh[i], todayData);
FillVariation(ww, AnotherRowIndex, 8, 12, 1);
FillLJVariation(ww, AnotherRowIndex, 9, 12, 2);
AnotherRowIndex++;
}
else
{
sheet.Cells[StartRowIndex, 0].PutValue(Cdbh[i]);
FillDayData(ww, StartRowIndex, 4, 0, 0, Cdbh[i], yesterData);
FillDayData(ww, StartRowIndex, 5, 0, 0, Cdbh[i], todayData);
FillVariation(ww, StartRowIndex, 1, 5, 1);
FillLJVariation(ww, StartRowIndex, 2, 5, 2);
StartRowIndex++;
}
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN);
Worksheet tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName,
Convert.ToString(TemplateHandlerPrams.Structure.Id),
Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Any())
{
try
{
DateTime dtTime = TemplateHandlerPrams.Date;
FillBasicInformation(ww, 0, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 2, 0, "contractorUnit", TemplateHandlerPrams.Structure.ConstructionCompany);
FillBasicInformation(ww, 2, 12, "contractNo", "数据库暂无");
FillBasicInformation(ww, 3, 0, "supervisingUnit", "数据库暂无");
FillBasicInformation(ww, 3, 12, "factorNo", TemplateHandlerPrams.Factor.Id.ToString());
FillBasicInformation(ww, 4, 0, "position", TemplateHandlerPrams.Structure.Name);
FillBasicInformation(ww, 5, 3, "instrumentName", SensorProduct.ProductName);
FillBasicInformation(ww, 5, 10, "instrumentNo", SensorProduct.ProductCode);
//根据上月天数添加列数
ExtensionColumn(ww, dtTime, 9);
//监测日期范围
FillDateTimeRange(ww, 5, 0, dtTime);
//获取上月每天数据
int k;
DateTime dd;
DateTime zz;
if (dtTime.Month == 1)
{
k = DateTime.DaysInMonth(dtTime.Year - 1, 12);
dd = new DateTime(dtTime.Year - 1, 12, 1).Date;
zz = new DateTime(dtTime.Year - 1, 12, k).Date;
}
else
{
k = DateTime.DaysInMonth(dtTime.Year, dtTime.Month - 1);
dd = new DateTime(dtTime.Year, dtTime.Month - 1, 1).Date;
zz = new DateTime(dtTime.Year, dtTime.Month - 1, k).Date;
}
for (int i = 1; i <= k; i++)
{
FillEveryDayDate(ww, 7, i + 2, GetLastMonthEachDay(i.ToString(), dtTime));
}
var sheet = ww.Worksheets[0];
int startRowIndex = 8;
for (int i = 0; i < Cdbh.Length; i++)
{
sheet.Cells[startRowIndex, 0].PutValue(Cdbh[i]);
for (int j = 1; j <= k; j++)
{
var date = GetLastMonthEachDay(j.ToString(), dtTime);
var Data = DataAccess.GetUnifyData(SensorsId, date, date.AddDays(+1).AddMilliseconds(-1), true);
FillEveryDayData(ww, startRowIndex, j + 2, 0, 2, startRowIndex, 1, Cdbh[i], Data);
}
var dyData = DataAccess.GetUnifyData(SensorsId, dd, dd.AddDays(+1).AddMilliseconds(-1), false);
var zhData = DataAccess.GetUnifyData(SensorsId, zz, zz.AddDays(+1).AddMilliseconds(-1), true);
FillVariation(ww, startRowIndex, 0, 2, Cdbh[i], k, dyData, zhData);
FillLJVariation(ww, startRowIndex, k);
FillLastMonthLJVariation(ww, startRowIndex, 2, k);
FillVaryRate(ww, startRowIndex, k);
var BcData = DataAccess.GetUnifyData(SensorsId, zz.Date, zz.Date.AddDays(+1).AddMilliseconds(-1), true);
FillBCData(ww, startRowIndex, 0, 2, k, 6, Cdbh[i], BcData);
startRowIndex++;
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN);
Worksheet tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
ms.Close();
logger.Warn(ex.Message);
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.ReadWrite);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Any())
{
try
{
DateTime dtTime = TemplateHandlerPrams.Date;
FillBasicInformation(ww, 0, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 2, 0, "contractorUnit", TemplateHandlerPrams.Structure.ConstructionCompany);
FillBasicInformation(ww, 2, 13, "contractNo", "数据库暂无");
FillBasicInformation(ww, 3, 0, "supervisingUnit", "数据库暂无");
FillBasicInformation(ww, 3, 13, "factorNo", TemplateHandlerPrams.Factor.Id.ToString());
FillBasicInformation(ww, 4, 0, "position", TemplateHandlerPrams.Structure.Name);
FillBasicInformation(ww, 5, 3, "instrumentName", SensorProduct.ProductName);
FillBasicInformation(ww, 5, 10, "instrumentNo", SensorProduct.ProductCode);
//获取采集时间上周一数据
var mondayDate = GetLastWeekEachDay("Monday", dtTime);
var mondayData = DataAccess.GetUnifyData(SensorsId, mondayDate, mondayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 3, mondayDate);
FillDateTime(ww, 5, 0, "dateFrom", mondayDate);
//获取采集时间上周二数据
var tuesdayDate = GetLastWeekEachDay("Tuesday", dtTime);
var tuesdayData = DataAccess.GetUnifyData(SensorsId, tuesdayDate, tuesdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 4, tuesdayDate);
//获取采集时间上周三数据
var wednesdayDate = GetLastWeekEachDay("Wednesday", dtTime);
var wednesdayData = DataAccess.GetUnifyData(SensorsId, wednesdayDate, wednesdayDate.AddDays(+1).AddMilliseconds(-1),
true);
FillDateTime(ww, 7, 5, wednesdayDate);
//获取采集时间上周四数据
var thursdayDate = GetLastWeekEachDay("Thursday", dtTime);
var thursdayData = DataAccess.GetUnifyData(SensorsId, tuesdayDate, thursdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 6, thursdayDate);
//获取采集时间上周五数据
var fridayDate = GetLastWeekEachDay("Friday", dtTime);
var fridayData = DataAccess.GetUnifyData(SensorsId, fridayDate, fridayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 7, fridayDate);
//获取采集时间上周六数据
var saturdayDate = GetLastWeekEachDay("Saturday", dtTime);
var saturdayData = DataAccess.GetUnifyData(SensorsId, saturdayDate, saturdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 8, saturdayDate);
//获取采集时间上周日数据
var sundayDate = GetLastWeekEachDay("Sunday", dtTime);
var sundayData = DataAccess.GetUnifyData(SensorsId, sundayDate, sundayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 5, 0, "dateTo", sundayDate);
FillDateTime(ww, 7, 9, sundayDate);
//本周变化量
var mondayFirstData = DataAccess.GetUnifyData(SensorsId, mondayDate, mondayDate.AddDays(+1).AddMilliseconds(-1), false);
var sheet = ww.Worksheets[0];
int startRowIndex = 8;
for (int i = 0; i < Cdbh.Length; i++)
{
sheet.Cells[startRowIndex, 0].PutValue(Cdbh[i]);
FillEveryDayData(ww, startRowIndex, 3, 0, 0, 1, Cdbh[i], mondayData);
FillEveryDayData(ww, startRowIndex, 4, 0, 0, 1, Cdbh[i], tuesdayData);
FillEveryDayData(ww, startRowIndex, 5, 0, 0, 1, Cdbh[i], wednesdayData);
FillEveryDayData(ww, startRowIndex, 6, 0, 0, 1, Cdbh[i], tuesdayData);
FillEveryDayData(ww, startRowIndex, 7, 0, 0, 1, Cdbh[i], fridayData);
FillEveryDayData(ww, startRowIndex, 8, 0, 0, 1, Cdbh[i], saturdayData);
FillEveryDayData(ww, startRowIndex, 9, 0, 0, 1, Cdbh[i], saturdayData);
FillBCData(ww, startRowIndex, 13, 0, 0, Cdbh[i], sundayData);
FillBCData(ww, startRowIndex, 14, 0, 1, Cdbh[i], sundayData);
FillHsVariation(ww, startRowIndex, 10, 0, 0, Cdbh[i], mondayFirstData, sundayData);
FillLJVariation(ww, startRowIndex, 11);
FillVaryRate(ww, startRowIndex, 12);
startRowIndex++;
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN);
Worksheet tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
_existOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
}
else
{
_existOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Count() > 0)
{
try
{
string sb = TemplateHandlerPrams.Structure.Id.ToString() + '_' + TemplateHandlerPrams.Factor.Id.ToString();
Thread.Sleep(500);
int cnt = ReportCounter.Get(sb);
cnt += 1;
DateTime dtTime = TemplateHandlerPrams.Date;
if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
{
FillBasicInformation(ww, 3, 0, "[projName]", TemplateHandlerPrams.Organization.SystemName);
}
FillBasicInformation(ww, 2, 0, "[rptNo]", this.rptNo);
FillBasicInformation(ww, 4, 0, "[date]",
TemplateHandlerPrams.Date.AddDays((-1)).ToString("yyyy年MM月dd日"));
FillBasicInformation(ww, 4, 10, "[count]", cnt.ToString());
// 查询水位数据
var todayData = new Dictionary <string, string>();
var yesterData = new Dictionary <string, string>();
using (var db = new DW_iSecureCloud_EmptyEntities())
{
int stcid = TemplateHandlerPrams.Structure.Id;
DateTime dateToday = TemplateHandlerPrams.Date;
DateTime dateYesterday = TemplateHandlerPrams.Date.AddDays(-1);
var query = from sw in db.T_THEMES_ENVI_WATER_LEVEL
from s in db.T_DIM_SENSOR
where s.STRUCT_ID == stcid && s.SENSOR_ID == sw.SENSOR_ID
select new
{
sw.ID,
sw.SENSOR_ID,
s.SENSOR_LOCATION_DESCRIPTION,
sw.WATER_LEVEL_CUMULATIVEVALUE,
sw.ACQUISITION_DATETIME
};
var td = query.Where(d => d.ACQUISITION_DATETIME < dateToday)
.GroupBy(d => d.SENSOR_ID).Select(d => d.Select(s => s.ID).Max());
var ys =
query.Where(d => d.ACQUISITION_DATETIME < dateYesterday)
.GroupBy(d => d.SENSOR_ID)
.Select(d => d.Select(s => s.ID).Max());
todayData =
query.Where(d => td.Contains(d.ID))
.Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
.ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
yesterData =
query.Where(d => ys.Contains(d.ID))
.Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
.ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
}
var sheet = ww.Worksheets[0];
int StartRowIndex = 6;
int InsertRowIndex = 8;
Array.Sort(Cdbh);
if (Cdbh.Count() > 3)
{
if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
{
if (Cdbh.Count() > 4)
{
sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 4);
}
}
else
{
sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 3);
}
}
int structId = TemplateHandlerPrams.Structure.Id;
int factorId = TemplateHandlerPrams.Factor.Id;
List <SensorList> list = DataAccess.FindSensorsByStructAndFactor(structId, factorId);
foreach (SensorList sensorList in list)
{
foreach (Sensor sensor in sensorList.Sensors)
{
bool todayFlag = false;
bool yestodayFlag = false;
//测点位置
sheet.Cells[StartRowIndex, 0].PutValue(sensor.Location);
//初始高程
decimal?waterLevelInit = DataAccess.GetWaterLevelInit(sensor.SensorId);
if (waterLevelInit == null)
{
sheet.Cells[StartRowIndex, 1].PutValue("/");
}
else
{
sheet.Cells[StartRowIndex, 1].PutValue(Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000")));
}
//本次高程
if (todayData.ContainsKey(sensor.Location) && todayData[sensor.Location] != null)
{
sheet.Cells[StartRowIndex, 2].PutValue(Convert.ToDecimal(todayData[sensor.Location]));
}
else
{
sheet.Cells[StartRowIndex, 2].PutValue("/");
todayFlag = true;
}
//上次高程
if (yesterData.ContainsKey(sensor.Location) && yesterData[sensor.Location] != null)
{
sheet.Cells[StartRowIndex, 3].PutValue(Convert.ToDecimal(yesterData[sensor.Location]));
}
else
{
sheet.Cells[StartRowIndex, 3].PutValue("/");
yestodayFlag = true;
}
//本次变化量
//变化速率
if (todayFlag || yestodayFlag)
{
sheet.Cells[StartRowIndex, 4].PutValue("/");
sheet.Cells[StartRowIndex, 6].PutValue("/");
}
else
{
var temp = (Convert.ToDecimal(todayData[sensor.Location]) - Convert.ToDecimal(yesterData[sensor.Location])) * 1000;
sheet.Cells[StartRowIndex, 4].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
sheet.Cells[StartRowIndex, 6].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
}
//累计变化量
if (waterLevelInit == null || todayFlag)
{
sheet.Cells[StartRowIndex, 5].PutValue("/");
}
else
{
var init = Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000"));
var value = (Convert.ToDecimal(todayData[sensor.Location]) - init) * 1000;
sheet.Cells[StartRowIndex, 5].PutValue(value);
}
// 预警值
sheet.Cells[StartRowIndex, 7].PutValue(limit);
StartRowIndex++;
}
}
Thread.Sleep(500);
ReportCounter.Inc(sb);
ww.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Create, FileAccess.Write);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
Workbook ww;
Worksheet tmp;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Any())
{
try
{
DateTime dtTime = TemplateHandlerPrams.Date;
FillBasicInformation(ww, 1, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 1, 5, "factorName", TemplateHandlerPrams.Factor.NameCN);
FillBasicInformation(ww, 2, 0, "address", TemplateHandlerPrams.Organization.Address);
FillBasicInformation(ww, 2, 5, "instrumentNo", this.SensorProduct.ProductCode);
FillBasicInformation(ww, 3, 1, "monitorday", dtTime.AddDays(-1).ToString("yyyy-MM-dd"));
var dateTime = dtTime.Date;
var todayData = DataAccess.GetUnifyData(SensorsId, dateTime.AddDays(-1), dateTime, true);
var sortedData = todayData.OrderBy(r => r.Key);
int sheetidx = 0;
var groupid = -1;
int rowidx = 5;
foreach (var data in sortedData)
{
var strGroupid = data.Key.ToString().Substring(0, 1);
int tempid = 0;
tempid = int.TryParse(strGroupid, out tempid) ? tempid : 0;
if (groupid == -1)
{
groupid = tempid;
}
if (groupid != tempid)
{
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN + groupid);
tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN + groupid);
tmp.Copy(ww.Worksheets[sheetidx]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[sheetidx]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN + groupid;
ExistOrNot = true;
}
AddSheetCopy(ww, sheetidx);
sheetidx++;
DeleteRows(ww, 5, rowidx - 5, sheetidx);
rowidx = 5;
groupid = tempid;
}
InsertRow(ww, rowidx, sheetidx);
CellMerge(ww, sheetidx, rowidx, 0, 1, 2);
CellMerge(ww, sheetidx, rowidx, 2, 1, 2);
CellMerge(ww, sheetidx, rowidx, 4, 1, 2);
CellMerge(ww, sheetidx, rowidx, 6, 1, 2);
CellFormula(ww, sheetidx, rowidx, 6, string.Format("=(C{0}-E{0})/C{0}*100", rowidx + 1));
CellMerge(ww, sheetidx, rowidx, 8, 1, 2);
CellFormula(ww, sheetidx, rowidx, 8, string.Format("=C{0}*60% + C{0}", rowidx + 1));
FillDayDataEx(ww, sheetidx, rowidx, 0, data.Key);
FillDayDataEx(ww, sheetidx, rowidx, 2, DataAccess.GetSensorParm(data.Value.SensorId).ToString("0.000"));
FillDayDataEx(ww, sheetidx, rowidx, 4, data.Value.Data[0].Values[0].ToString());
//FillDayDataEx(ww, sheetidx, rowidx, 6, ((data.Value.Data[0].Values[0] - data.Value.Data[0].Values[0]) / data.Value.Data[0].Values[0]*100).ToString());
//FillDayDataEx(ww, sheetidx, rowidx, 8, (data.Value.Data[0].Values[0]*(decimal)1.6).ToString());
rowidx++;
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN + groupid);
tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN + groupid);
tmp.Copy(ww.Worksheets[sheetidx]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[sheetidx]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN + groupid;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName,
Convert.ToString(TemplateHandlerPrams.Structure.Id),
Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Create, FileAccess.Write);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Any())
{
try
{
DateTime dtTime = TemplateHandlerPrams.Date;
FillBasicInformation(ww, 1, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 2, 5, "monitorday", dtTime.AddDays(-1).ToString("yyyy-MM-dd"));
var dateTime = dtTime.Date;
var todayData = DataAccess.GetUnifyData(SensorsId, dateTime.AddDays(-1), dateTime, true);
var yesterData = DataAccess.GetUnifyData(SensorsId, dateTime.AddDays(-2), dateTime.AddDays(-1),
true);
var sheet = ww.Worksheets[0];
int rowidx = 4;
for (int i = 0; i < Cdbh.Length; i++)
{
var todayMaxValue = todayData.ContainsKey(Cdbh[i])
? todayData[Cdbh[i]].Data[0].Values[0]
: 0;
var yesterdayMaxValue = yesterData.ContainsKey(Cdbh[i])
? yesterData[Cdbh[i]].Data[0].Values[0]
: 0;
InsertRow(ww, rowidx, 0);
CellMerge(ww, 0, rowidx, 1, 1, 2);
CellMerge(ww, 0, rowidx, 3, 1, 2);
CellMerge(ww, 0, rowidx, 5, 1, 2);
FillDayDataEx(ww, 0, rowidx, 0, Cdbh[i]);
FillDayDataEx(ww, 0, rowidx, 1, (todayMaxValue - yesterdayMaxValue).ToString("0.000"));
FillDayDataEx(ww, 0, rowidx, 3, (todayMaxValue - yesterdayMaxValue).ToString("0.000"));
FillDayDataEx(ww, 0, rowidx, 5, (todayMaxValue).ToString("0.000"));
rowidx++;
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN);
Worksheet tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName,
Convert.ToString(TemplateHandlerPrams.Structure.Id),
Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
