public override void WriteFile()
{
//读取目标
Workbook md; // 目标Workbook
FileStream ms; // 目标文件流
ExistOrNot = PingHanReport.GetTargetWorkbook(targetFile, out ms, out md);
// 读取模版
Workbook ww;
PingHanReport.GetTemplate(template, out ww);
// 获取测量时段
List <DateTime> datePeriod = PingHanReport.GetDatePeriod(dtTime);
// 获取隧道截面
List <SectionSensors> sectionSensors = new List <SectionSensors>();
try
{
sectionSensors = DataAccess.GetSectionSensors(structId, factorId);
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
// 复制sheet
if (sectionSensors.Count > 0)
{
try
{
ww = PingHanReport.SetSheetName(sectionSensors, ww, factorName);
for (int i = 0; i < sectionSensors.Count; i++)
{
var sheet = ww.Worksheets[i];
var chart = sheet.Charts[0];
chart.Title.Text = ww.Worksheets[i].Name + "累计变化趋势";
sheet.Cells[1, 1].PutValue(sectionSensors[i].SectionName);
var sectionName = sheet.Cells[4, 0].StringValue;
sheet.Cells[4, 0].PutValue(sectionName.Replace("[sectionName]", sectionSensors[i].SectionName));
var date = sheet.Cells[5, 8].StringValue;
string temp = date.Replace("[dateStart]", datePeriod[0].ToShortDateString())
.Replace("[dateEnd]", datePeriod[1].ToShortDateString());
sheet.Cells[5, 8].PutValue(temp);
int sensorId = sectionSensors[i].Sensors[0].SensorId;
var sensorLocation = sectionSensors[i].Sensors[0].Location;
sheet.Cells[7, 0].PutValue(sensorLocation);
InitValues initValue = DataAccess.GetSensorInitValue(sensorId);
decimal? initHeight = initValue.h0;
sheet.Cells[7, 2].PutValue(initHeight);
int startDataRow = 7;
for (int j = 0; j < 7; j++)
{
MonitorData lastData = new MonitorData();
MonitorData currentData = new MonitorData();
var getDate = PingHanReport.GetLastWeekEachDay((j + 1).ToString(), dtTime);
var getData = DataAccess.GetUnifyData(new[] { sensorId }, getDate, getDate.AddDays(+1).AddMilliseconds(-1), true);
var getLastDate = getDate.AddDays(-1).Date;
var getLastData = DataAccess.GetUnifyData(new[] { sensorId }, getLastDate, getLastDate.AddDays(+1).AddMilliseconds(-1), true);
// 上次累计变量
if (getLastData.Count != 0)
{
lastData = getLastData.ContainsKey(sensorLocation) ? getLastData[sensorLocation] : new MonitorData();
}
// 本次累计变量
if (getData.Count != 0)
{
currentData = getData.ContainsKey(sensorLocation) ? getData[sensorLocation] : new MonitorData();
if (currentData.Data != null && currentData.Data[0].Values != null)
{
sheet.Cells[startDataRow, 6].PutValue(currentData.Data[0].Values[0]);
}
else
{
sheet.Cells[startDataRow, 9].PutValue("/");
}
}
else
{
sheet.Cells[startDataRow, 9].PutValue("/");
}
if (currentData.Data != null && lastData.Data != null)
{
// 本次变量
if (currentData.Data[0].Values != null && lastData.Data[0].Values != null)
{
decimal bcbl = currentData.Data[0].Values[0] - lastData.Data[0].Values[0];
sheet.Cells[startDataRow, 5].PutValue(bcbl);
}
else
{
sheet.Cells[startDataRow, 5].PutValue("/");
}
}
else
{
sheet.Cells[startDataRow, 5].PutValue("/");
}
if (lastData.Data != null)
{
if (lastData.Data[0].Values != null)
{
// 上次高程
decimal?scgc = initHeight - lastData.Data[0].Values[0] / 1000;
sheet.Cells[startDataRow, 3].PutValue(scgc);
}
else
{
sheet.Cells[startDataRow, 3].PutValue("/");
}
}
else
{
sheet.Cells[startDataRow, 3].PutValue("/");
}
if (currentData.Data != null)
{
if (currentData.Data[0].Values != null)
{
// 本次高程
decimal?bcgc = initHeight - currentData.Data[0].Values[0] / 1000;
sheet.Cells[startDataRow, 4].PutValue(bcgc);
}
else
{
sheet.Cells[startDataRow, 4].PutValue("/");
}
}
else
{
sheet.Cells[startDataRow, 4].PutValue("/");
}
//测量日期
sheet.Cells[startDataRow, 7].PutValue(getDate.ToShortDateString());
startDataRow++;
}
}
if (ExistOrNot)
{
for (int i = 0; i < ww.Worksheets.Count; i++)
{
string tmpname = sectionSensors[i].SectionName ?? "断面" + (0 + 1);
tmpname += TemplateHandlerPrams.Factor.NameCN;
md.Worksheets.RemoveAt(tmpname);
Worksheet tmp = md.Worksheets.Add(ww.Worksheets[i].Name);
tmp.Copy(ww.Worksheets[i]);
}
}
else
{
for (int i = 0; i < ww.Worksheets.Count; i++)
{
if (i == 0)
{
md.Worksheets[i].Copy(ww.Worksheets[i]);
md.Worksheets[i].Name = ww.Worksheets[i].Name;
}
else
{
Worksheet tmp = md.Worksheets.Add(ww.Worksheets[i].Name);
tmp.Copy(ww.Worksheets[i]);
}
}
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("结构物ID：{0}，监测因素ID：{1} 施工状态下的截面没检测到可用传感器", Convert.ToString(structId), Convert.ToString(factorId));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
//读取目标
Workbook md; // 目标Workbook
FileStream ms; // 目标文件流
ExistOrNot = PingHanReport.GetTargetWorkbook(targetFile, out ms, out md);
// 读取模版
Workbook ww;
PingHanReport.GetTemplate(template, out ww);
// 获取施工中的隧道截面及其下的传感器
List <SectionSensors> sectionSensors = new List <SectionSensors>();
try
{
sectionSensors = DataAccess.GetProcessingSensor(structId, factorId, flag, status);
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
// 复制sheet
string tmpname = "PH-10" + factorName;
int sensorCount = 0;
var flagSensorCount = false;
if (sectionSensors.Count > 0)
{
try
{
ww.Worksheets[0].Name = tmpname;
var sheet = ww.Worksheets[0];
sheet.Cells.InsertRows(8, 6);
int count = 1;
for (int i = 0; i < sectionSensors.Count; i++)
{
List <Sensor> sensors = sectionSensors[i].Sensors;
if (sensors.Any())
{
for (int k = 0; k < sensors.Count; k++)
{
sensorCount++;
if (sensorCount > 5)
{
flagSensorCount = true;
break;
}
int sensorId = sectionSensors[i].Sensors[k].SensorId;
var sensorLocation = sectionSensors[i].Sensors[k].Location;
InitValues initValue = DataAccess.GetSensorInitValue(sensorId);
decimal? initHeight = initValue.h0;
sheet.Cells[4, count].PutValue(sensorLocation);
sheet.Cells[5, count].PutValue(initHeight);
int startDataRow = 7;
for (int j = 0; j < 7; j++)
{
MonitorData currentData = new MonitorData();
var getDate = PingHanReport.GetLastWeekEachDay((j + 1).ToString(), dtTime);
var getData = DataAccess.GetUnifyData(new[] { sensorId }, getDate,
getDate.AddDays(1).AddMilliseconds(-1), true);
// 本次累计变量
if (getData.Count != 0)
{
currentData = getData.ContainsKey(sensorLocation)
? getData[sensorLocation]
: new MonitorData();
if (currentData.Data != null && currentData.Data[0].Values != null)
{
sheet.Cells[startDataRow, count + 1].PutValue(currentData.Data[0].Values[0]);
}
else
{
sheet.Cells[startDataRow, count + 1].PutValue("/");
}
}
else
{
sheet.Cells[startDataRow, count + 1].PutValue("/");
}
if (currentData.Data != null && currentData.Data[0].Values != null)
{
// 本次高程
decimal?bcgc = initHeight - currentData.Data[0].Values[0] / 1000;
sheet.Cells[startDataRow, count].PutValue(bcgc);
}
else
{
sheet.Cells[startDataRow, count].PutValue("/");
}
//测量日期
sheet.Cells[startDataRow, 0].PutValue(getDate.ToShortDateString());
startDataRow++;
}
count += 2;
}
}
if (flagSensorCount)
{
break;
}
}
if (flagSensorCount)
{
logger.InfoFormat("施工中截面下的测点超过报表模板..{0}..设定的数目,暂只取模板设定的测点的数目,其余测点数据报表不填充", template);
}
if (ExistOrNot)
{
md.Worksheets.RemoveAt(tmpname);
Worksheet tmp = md.Worksheets.Add(tmpname);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = tmpname;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("结构物ID：{0}，监测因素ID：{1} 施工状态下的截面没检测到可用传感器", Convert.ToString(structId), Convert.ToString(factorId));
ms.Close();
throw new ArgumentException();
}
}
public override void WriteFile()
{
//读取目标
Workbook md; // 目标Workbook
FileStream ms; // 目标文件流
ExistOrNot = PingHanReport.GetTargetWorkbook(targetFile, out ms, out md);
// 读取模版
Workbook ww;
PingHanReport.GetTemplate(template, out ww);
// 获取测量日期列表
List <DateTime> dateList = PingHanReport.GetLastWeekDateList(dtTime);
// 获取施工中的隧道截面及其下的传感器
List <SectionSensors> sectionSensors = new List <SectionSensors>();
try
{
sectionSensors = DataAccess.GetProcessingSensor(structId, factorId, flag, status);
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
// 复制sheet
if (sectionSensors.Count > 0)
{
try
{
ww = PingHanReport.SetSheetName(sectionSensors, ww, factorName);
for (int i = 0; i < sectionSensors.Count; i++)
{
var sheet = ww.Worksheets[i];
sheet.Cells[3, 15].PutValue(sectionSensors[i].SectionName);// 截面名称
List <Sensor> sensors = sectionSensors[i].Sensors;
if (sensors.Count > 0)
{
int rowIndex = 7;
foreach (var sensor in sensors)
{
// 插入7行
sheet.Cells.InsertRows(rowIndex, 7);
//为每个测点填写7行对应一周数据
int sensorId = sensor.SensorId;
var sensorLocation = sensor.Location;
InitValues initValue = DataAccess.GetSensorInitValue(sensorId);
decimal? len0 = initValue.len0;
int startDataRow = rowIndex;
bool lastFlag = false;
decimal? lastAvgData = 0;// 上次测量的平均值
// 获得上一次的测量值
var getLastFirstStartDate = dateList[0].AddDays(-1);
var getLastFirstData = new Dictionary <string, MonitorData>();
var getLastSecondData = new Dictionary <string, MonitorData>();
var lastFirstData = new MonitorData();
var lastSecondData = new MonitorData();
PingHanReport.GetFirstAndSecondMinitorData(getLastFirstStartDate, new[] { sensorId }, out getLastFirstData,
out getLastSecondData);
if (lastFirstData.Data != null && lastSecondData.Data != null)
{
if (lastFirstData.Data[0].Values.Any() && lastSecondData.Data[0].Values.Any())
{
decimal?temp = (lastFirstData.Data[0].Values[0] + lastSecondData.Data[0].Values[0] + 2 * 1000 * len0) / 2;
decimal z = Math.Floor(Convert.ToDecimal(temp / 1000));
decimal x = Convert.ToDecimal(Convert.ToDecimal(temp % 1000).ToString("#0.0"));
lastAvgData = z * 1000 + x;// 上次测量的平均值
}
else
{
lastFlag = true;
}
}
else
{
lastFlag = true;
}
for (int j = 0; j < 7; j++)
{
// 测点
sheet.Cells[startDataRow, 0].PutValue(sensorLocation);
//测量日期
sheet.Cells[startDataRow, 1].PutValue(dateList[j].Year);
sheet.Cells[startDataRow, 2].PutValue(dateList[j].Month);
sheet.Cells[startDataRow, 3].PutValue(dateList[j].Day);
decimal?currentAvgData = 0;// 本次测量的平均值
bool curFlag = false;
var firstData = new MonitorData();
var secondData = new MonitorData();
var getFirstStartDate = PingHanReport.GetLastWeekEachDay((j + 1).ToString(), dtTime);
var getFirstData = new Dictionary <string, MonitorData>();
var getSecondData = new Dictionary <string, MonitorData>();
PingHanReport.GetFirstAndSecondMinitorData(getFirstStartDate, new[] { sensorId }, out getFirstData,
out getSecondData);
// 测量值1
PingHanReport.GetMonitorData(getFirstData, sensorLocation, ref firstData);
PingHanReport.GetMonitorData(getLastFirstData, sensorLocation, ref lastFirstData);
// 测量值2
PingHanReport.GetMonitorData(getSecondData, sensorLocation, ref secondData);
PingHanReport.GetMonitorData(getLastSecondData, sensorLocation, ref lastSecondData);
//观测值1
PingHanReport.FillDataCrackReport(sheet, firstData, len0, startDataRow, 6);
//观测值2
PingHanReport.FillDataCrackReport(sheet, secondData, len0, startDataRow, 8);
//平均值
if (firstData.Data != null && secondData.Data != null)
{
if (firstData.Data[0].Values.Any() && secondData.Data[0].Values.Any())
{
decimal z1 = Convert.ToDecimal(sheet.Cells[startDataRow, 6].Value);
decimal x1 = Convert.ToDecimal(sheet.Cells[startDataRow, 7].Value);
decimal z2 = Convert.ToDecimal(sheet.Cells[startDataRow, 8].Value);
decimal x2 = Convert.ToDecimal(sheet.Cells[startDataRow, 9].Value);
decimal?temp = (z1 * 1000 + z2 * 1000 + x1 + x2) / 2;
decimal z = Math.Floor(Convert.ToDecimal(temp / 1000));
decimal x = Convert.ToDecimal(Convert.ToDecimal(temp % 1000).ToString("#0.0"));
currentAvgData = z * 1000 + x;
//平均值
sheet.Cells[startDataRow, 10].PutValue(z);
sheet.Cells[startDataRow, 11].PutValue(x);
// 修正后观测值
sheet.Cells[startDataRow, 13].PutValue(z);
sheet.Cells[startDataRow, 14].PutValue(x);
//相对第一次收敛值
sheet.Cells[startDataRow, 15].PutValue(Convert.ToDecimal(Convert.ToDecimal(len0 * 1000 - currentAvgData).ToString("#0.0")));
}
else
{
curFlag = true;
}
}
else
{
curFlag = true;
}
if (curFlag)
{
//平均值
sheet.Cells[startDataRow, 10].PutValue("/");
sheet.Cells[startDataRow, 11].PutValue("/");
// 修正后观测值
sheet.Cells[startDataRow, 13].PutValue("/");
sheet.Cells[startDataRow, 14].PutValue("/");
//相对第一次收敛值
sheet.Cells[startDataRow, 15].PutValue("/");
}
if (!curFlag && !lastFlag)
{
var varValue = currentAvgData - lastAvgData;
sheet.Cells[startDataRow, 16].PutValue(Convert.ToDecimal(Convert.ToDecimal(varValue).ToString("#0.0"))); // 相对上一次收敛值
sheet.Cells[startDataRow, 18].PutValue(Convert.ToDecimal(Convert.ToDecimal(varValue).ToString("#0.0"))); // 收敛速率
}
else
{
sheet.Cells[startDataRow, 16].PutValue("/"); // 相对上一次收敛值
sheet.Cells[startDataRow, 18].PutValue("/"); // 收敛速率
}
lastAvgData = currentAvgData;
lastFlag = curFlag;
sheet.Cells[startDataRow, 17].PutValue(1);//时间间隔
startDataRow++;
}
rowIndex += 7;
}
}
}
PingHanReport.CopyExcelSheet(ExistOrNot, sectionSensors, factorName, ref ww, ref md);
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("结构物ID：{0}，监测因素ID：{1} 施工状态下的截面没检测到可用传感器", Convert.ToString(structId), Convert.ToString(factorId));
ms.Close();
throw new ArgumentException();
}
}