private void radButton3_Click(object sender, EventArgs e)
{
try
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow ExcelRow = null; //行
ICell ExcelCell = null; //列
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.Filter = "(*.xlsx)|*.xlsx|(*.xls)|*.xls";
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{ //文件路径
string Path = fileDialog.FileName;
try
{
using (fs = System.IO.File.OpenRead(Path))
{
#region 判断版本
//2007版本及以上
if (Path.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
//2003版本
else if (Path.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
#endregion
int i = 1;
int NodataFlg = 0; //跳出循环的Flag
if (workbook != null)
{ //读取第一个sheet，当然也可以循环读取每个sheet
sheet = workbook.GetSheetAt(0);
//遍历所有数据
//下拉框内容
//RadListDataItem selectedItem = (RadListDataItem)rddl_addTags.SelectedItem.Value;//必须取value然后转换再获取value
RadListDataItem selectedItem = null;
var DrillId = -1;
if (int.TryParse(selectedItem.Value.ToString(), out DrillId))
{
if (DrillId != -1)
{
while (NodataFlg != 1)
{
#region 判断第i行是否为空
ExcelRow = sheet.GetRow(i); //第i行
//如果为null，证明已经没有可以读取的行
if (ExcelRow == null)
{
break;
}
ExcelCell = ExcelRow.GetCell(0);//读取excel第i行的第1列数据（测点）
if ((ExcelCell == null) || (ExcelCell.ToString() == ""))
{
break;
}
#endregion
#region 数据插入
var TransferType = sheet.GetRow(0).GetCell(1).ToString().Trim(); //语言
var Tag = sheet.GetRow(i).GetCell(0).ToString().Trim(); //基础测点值必须存在，不然导入失败
var TransferName = sheet.GetRow(i).GetCell(1) == null ? "" : sheet.GetRow(i).GetCell(1).ToString().Trim(); //装翻译的结果
var drillTag = AlldataList.Where(o => o.Tag == Tag && o.DrillId == DrillId).FirstOrDefault(); //在DrillTag中查询当前测点是否存在
var tagdictionary = TagDictionary.Where(o => o.Basic == Tag && o.TransferType == TransferType).FirstOrDefault(); //检索当前测点及语言类型是否存在
//声明一堆需要插入数据的变量
string vfrom = sheet.GetRow(i).GetCell(2) == null ? "" : sheet.GetRow(i).GetCell(2).ToString().Trim();
string vto = sheet.GetRow(i).GetCell(3) == null ? "" : sheet.GetRow(i).GetCell(3).ToString().Trim();
string unit = sheet.GetRow(i).GetCell(4) == null ? "" : sheet.GetRow(i).GetCell(4).ToString().Trim();
string hvalue = sheet.GetRow(i).GetCell(5) == null ? "" : sheet.GetRow(i).GetCell(5).ToString().Trim();
string lvalue = sheet.GetRow(i).GetCell(6) == null ? "" : sheet.GetRow(i).GetCell(6).ToString().Trim();
string type = sheet.GetRow(i).GetCell(7) == null ? "" : sheet.GetRow(i).GetCell(7).ToString().Trim();
string isbool = sheet.GetRow(i).GetCell(8) == null ? "" : sheet.GetRow(i).GetCell(8).ToString().Trim();
DrillTag model = new DrillTag();
TagDictionary tagdic = new TagDictionary();
if (drillTag == null)
{
//添加到DrillTag表
model.DrillId = DrillId;
if (string.IsNullOrEmpty(Tag))
{
i++;
continue;
}
else
{
model.Tag = Tag;
}
if (string.IsNullOrEmpty(TransferName))
{
i++;
continue;
}
if (!string.IsNullOrEmpty(vfrom))//判断vfrom值是否为空
{
model.DefaultFrom = Convert.ToDecimal(vfrom);
}
else
{
model.DefaultFrom = 0;
}
if (!string.IsNullOrEmpty(vto))//判断vto值是否为空
{
model.DefaultTo = Convert.ToDecimal(vto);
}
else
{
model.DefaultTo = 1000;
}
if (!string.IsNullOrEmpty(unit))//判断unit是否为空
{
model.Unit = unit;
}
if (!string.IsNullOrEmpty(hvalue))//判断hvalue是否为空
{
model.HValue = Convert.ToDecimal(hvalue);
}
if (!string.IsNullOrEmpty(lvalue))//判断lvalue是否为空
{
model.LValue = Convert.ToDecimal(lvalue);
}
if (!string.IsNullOrEmpty(type))//判断type是否为空
{
model.Type = type;
}
if (!string.IsNullOrEmpty(isbool))//判断type是否为空
{
model.IsBool = bool.Parse(isbool);
}
else
{
model.IsBool = false;
}
model.dataMakePGM = "Setting";
model.dataMakeTime = DateTime.Now;
model.dataMakeUser = AppDrill.username;
model.dataUpdPGM = "Setting";
model.dataUpdTime = DateTime.Now;
model.dataUpdUser = AppDrill.username;
_db.DrillTag.Add(model);
//因为这个基础测点是新增的，字典表肯定不存在，直接插入
tagdic.Basic = Tag; //基础测点
tagdic.TransferType = TransferType; //语言类型
tagdic.TagShowName = TransferName; //翻译结果
tagdic.dataMakePGM = "Setting";
tagdic.dataMakeTime = DateTime.Now;
tagdic.dataMakeUser = AppDrill.username;
tagdic.dataUpdPGM = "Setting";
tagdic.dataUpdTime = DateTime.Now;
tagdic.dataUpdUser = AppDrill.username;
_db.TagDictionary.Add(tagdic);
}
else
{
//更新操作
if (!string.IsNullOrEmpty(vfrom))//判断vfrom值是否为空
{
drillTag.DefaultFrom = Convert.ToDecimal(vfrom);
}
if (!string.IsNullOrEmpty(vto))//判断vto值是否为空
{
drillTag.DefaultTo = Convert.ToDecimal(vto);
}
if (!string.IsNullOrEmpty(unit))//判断unit是否为空
{
drillTag.Unit = unit;
}
if (!string.IsNullOrEmpty(hvalue))//判断hvalue是否为空
{
drillTag.HValue = Convert.ToDecimal(hvalue);
}
if (!string.IsNullOrEmpty(lvalue))//判断lvalue是否为空
{
drillTag.LValue = Convert.ToDecimal(lvalue);
}
if (!string.IsNullOrEmpty(type))//判断type是否为空
{
drillTag.Type = type;
}
drillTag.dataUpdPGM = "Setting";
drillTag.dataUpdTime = DateTime.Now;
drillTag.dataUpdUser = AppDrill.username;
//因为基础测点表中存在此测点，所以需要判断字典表中是否存在次测点的翻译
if (tagdictionary == null)
{
tagdic.Basic = Tag; //基础测点
tagdic.TransferType = TransferType; //语言类型
tagdic.TagShowName = TransferName; //翻译结果
tagdic.dataMakePGM = "Setting";
tagdic.dataMakeTime = DateTime.Now;
tagdic.dataMakeUser = AppDrill.username;
tagdic.dataUpdPGM = "Setting";
tagdic.dataUpdTime = DateTime.Now;
tagdic.dataUpdUser = AppDrill.username;
_db.TagDictionary.Add(tagdic);
}
else
{
tagdictionary.TagShowName = TransferName;//修改只用修改翻译结果就行了
tagdictionary.dataUpdPGM = "Setting";
tagdictionary.dataUpdTime = DateTime.Now;
tagdictionary.dataUpdUser = AppDrill.username;
}
}
#endregion
i++;
}
try
{
lbl_error.Text = AppDrill.message[6];//等待中
lbl_error.ForeColor = Color.White;
backgroundWorker2.WorkerSupportsCancellation = true;
backgroundWorker2.RunWorkerAsync();//进行保存操作
}
catch (Exception)
{
MessageBox.Show(AppDrill.message[5]);
}
}
else
{
lbl_error.Text = list_error[2];//井号未选择
lbl_error.ForeColor = Color.Red;
}
}
}
}
}
catch
{
lbl_error.Text = list_error[1];//保存失败
lbl_error.ForeColor = Color.Red;
}
}
}
catch
{
MessageBox.Show(AppDrill.message[5]);
}
}
/// <summary>
/// list_channel 条目点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void list_channel_SelectedIndexChanged(object sender, EventArgs e)
{
//先判值赋给上一次的index
if (TempAlarmList.ContainsKey(current_index))
{
TempAlarmList[current_index].H = string.IsNullOrEmpty(txt_HighAlarm.Text) ? 99999 : double.Parse(txt_HighAlarm.Text);
TempAlarmList[current_index].L = string.IsNullOrEmpty(txt_LowAlarm.Text) ? -99999 : double.Parse(txt_LowAlarm.Text);
TempAlarmList[current_index].HIsActive = chb_H.Checked;
TempAlarmList[current_index].LIsActive = chb_L.Checked;
TempAlarmList[current_index].Tag = AlarmtagList[current_index].Tag;
}
else if (!TempAlarmList.ContainsKey(current_index) && (lst_channel.SelectedIndex != current_index))
{
JsonAlarm tag = new JsonAlarm();
tag.H = string.IsNullOrEmpty(txt_HighAlarm.Text) ? 99999 : double.Parse(txt_HighAlarm.Text);
tag.L = string.IsNullOrEmpty(txt_LowAlarm.Text) ? -99999 : double.Parse(txt_LowAlarm.Text);
tag.HIsActive = chb_H.Checked;
tag.LIsActive = chb_L.Checked;
tag.Tag = AlarmtagList[current_index].Tag;
TempAlarmList.Add(current_index, tag);
}
int index = lst_channel.SelectedIndex;
this.lbl_Status.Text = "";
txt_HighAlarm.Text = "";
txt_LowAlarm.Text = "";
lbl_Tags.Text = "";
lbl_TagId.Text = "";
lbl_DrillId.Text = "";
chb_H.Checked = false;
chb_L.Checked = false;
//0714修改，先从缓存判断有没有值
if (TempAlarmList.ContainsKey(index))
{
txt_HighAlarm.Text = TempAlarmList[index].H.ToString();
txt_LowAlarm.Text = TempAlarmList[index].L.ToString();
lbl_TagId.Text = TempAlarmList[index].Tag;
lbl_Tags.Text = Transformation(TempAlarmList[index].Tag);
lbl_DrillId.Text = "1";
chb_H.Checked = TempAlarmList[index].HIsActive;
chb_L.Checked = TempAlarmList[index].LIsActive;
}
//0710修改，用JsonAlarm更新
else if (JsonAlarmList.Count != 0)
{
JsonAlarm tag = JsonAlarmList.Where(o => o.Tag == AlarmtagList[lst_channel.SelectedIndex].Tag).FirstOrDefault();
if (tag != null)
{
txt_HighAlarm.Text = tag.H.ToString();
txt_LowAlarm.Text = tag.L.ToString();
lbl_TagId.Text = tag.Tag;
lbl_Tags.Text = Transformation(tag.Tag);
lbl_DrillId.Text = "1";
chb_H.Checked = tag.HIsActive;
chb_L.Checked = tag.LIsActive;
}
else
{
DrillTag tag2 = AlarmtagList[lst_channel.SelectedIndex];
txt_HighAlarm.Text = tag2 == null ? "" : (tag2.HValue >= 99999 ? "" : tag2.HValue.ToString());
txt_LowAlarm.Text = tag2 == null ? "" : (tag2.LValue <= -99999 ? "" : tag2.LValue.ToString());
lbl_Tags.Text = tag2 == null ? "" : Transformation(tag2.Tag);
lbl_TagId.Text = tag2 == null ? "" : tag2.Tag;
lbl_DrillId.Text = tag2 == null ? "" : tag2.DrillId.ToString();
chb_H.Checked = tag2.HisActive == null ? false : (bool)tag2.HisActive;
chb_L.Checked = tag2.LisActive == null ? false : (bool)tag2.LisActive;
}
}
else
{
if (AlarmtagList.Count != 0)
{
DrillTag tag = AlarmtagList[lst_channel.SelectedIndex];
txt_HighAlarm.Text = tag == null ? "" : (tag.HValue >= 99999 ? "" : tag.HValue.ToString());
txt_LowAlarm.Text = tag == null ? "" : (tag.LValue <= -99999 ? "" : tag.LValue.ToString());
lbl_Tags.Text = tag == null ? "" : Transformation(tag.Tag);
lbl_TagId.Text = tag == null ? "" : tag.Tag;
lbl_DrillId.Text = tag == null ? "" : tag.DrillId.ToString();
chb_H.Checked = tag.HisActive == null ? false : (bool)tag.HisActive;
chb_L.Checked = tag.LisActive == null ? false : (bool)tag.LisActive;
}
}
current_index = lst_channel.SelectedIndex;
}