public DataTable GetExcelData(string filename) { string filename1 = filename; // #region 1.0获取XLS数据 Excel.XlsOparete excel = new Excel.XlsOparete(); DataTable dtSource = excel.XlsToDataTable(filename1); DataRow dr = null; StringBuilder strDel = new StringBuilder(); if (dtSource.Rows.Count > 0) { dr = dtSource.Rows[0]; } else { foreach (DataColumn dc in dtSource.Columns) { if (!string.IsNullOrEmpty(strDel.ToString())) { strDel.Append(","); } strDel.Append(dc.ColumnName); } } #endregion #region 2.0修改列 if (dr != null) { foreach (DataColumn dc in dtSource.Columns) { #region 2.1集中器 if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("房间号")) { dc.ColumnName = "CoName"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("上期计数")) { dc.ColumnName = "FirstVal"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("本期计数")) { dc.ColumnName = "LastVal"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("上期时间")) { dc.ColumnName = "FirstTime"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("本期时间")) { dc.ColumnName = "LastTime"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("水费单价")) { dc.ColumnName = "Price"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Contains("物业费")) { dc.ColumnName = "ChargVal"; } #endregion else { if (!string.IsNullOrEmpty(strDel.ToString())) { strDel.Append(","); } strDel.Append(dc.ColumnName); } } dtSource.Rows.RemoveAt(0); } #endregion #region 3.0删除列 if (!string.IsNullOrEmpty(strDel.ToString())) { foreach (string s in strDel.ToString().Split(',')) { dtSource.Columns.Remove(s); } } #endregion #region 4.0增加列 string strCs = "ErrTxt"; string intCs = "Co_id,ErrCode"; foreach (string s in strCs.Split(',')) { if (dtSource.Columns.Contains(s) == true) { continue; } dtSource.Columns.Add(s, typeof(System.String)); } foreach (string s in intCs.Split(',')) { if (dtSource.Columns.Contains(s) == true) { continue; } dtSource.Columns.Add(s, typeof(System.Int32)); } #endregion #region 5.0删除空白行 (必须倒叙删除,因为每删除一行,索引就会发生改变) for (int i = dtSource.Rows.Count - 1; i >= 0; i--) { if (string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["CoName"])) && string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["FirstVal"])) && string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["LastVal"]))) { dtSource.Rows[i].Delete(); } } #endregion dtSource.AcceptChanges(); return(dtSource); }
//批量上载采集器Excel数据 public DataTable GetExcelDataOfBuilding(string filename) { string filename1 = filename; // #region 1.0获取XLS数据 Excel.XlsOparete excel = new Excel.XlsOparete(); DataTable dtSource = excel.XlsToDataTable(filename1); DataRow dr = null; StringBuilder strDel = new StringBuilder(); if (dtSource.Rows.Count > 0) { dr = dtSource.Rows[0]; } else { foreach (DataColumn dc in dtSource.Columns) { if (!string.IsNullOrEmpty(strDel.ToString())) { strDel.Append(","); } strDel.Append(dc.ColumnName); } } #endregion #region 2.0修改列 if (dr != null) { foreach (DataColumn dc in dtSource.Columns) { if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("集中器名称")) { dc.ColumnName = "GwName"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("集中器IP地址")) { dc.ColumnName = "GwIp"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("采集器名称")) { dc.ColumnName = "EspName"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("采集器地址")) { dc.ColumnName = "EspAddr"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("通讯方式(0:COM;1:TCP/Client;3:TCP/Server;4:IOServer)")) { dc.ColumnName = "TransferType"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("TCP端口")) { dc.ColumnName = "EspPort"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("COM口")) { dc.ColumnName = "ComPort"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("波特率")) { dc.ColumnName = "Baud"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("数据位")) { dc.ColumnName = "DataBit"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("停止位")) { dc.ColumnName = "StopBit"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("校验方式(0 无/1 奇/2 偶/3标志/4 空格)")) { dc.ColumnName = "Parity"; } //else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("电表地址")) //{ // dc.ColumnName = "ModuleAddr"; //} else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("设备地址")) { dc.ColumnName = "MeterAddr"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("倍率")) { dc.ColumnName = "Multiply"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("电表型号(如DDS3366L)")) { dc.ColumnName = "ModuleType"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("回路地址")) { dc.ColumnName = "ModuleAddr"; } else if (CommFunc.ConvertDBNullToString(dr[dc.ColumnName]).Trim().Equals("房间(约定定义)(如男生宿舍->南苑1栋->南苑1层->101房间)")) { dc.ColumnName = "CoFullName"; } else { if (!string.IsNullOrEmpty(strDel.ToString())) { strDel.Append(","); } strDel.Append(dc.ColumnName); } } dtSource.Rows.RemoveAt(0); } #endregion #region 3.0删除列 if (!string.IsNullOrEmpty(strDel.ToString())) { foreach (string s in strDel.ToString().Split(',')) { dtSource.Columns.Remove(s); } } #endregion #region 4.0增加列 string strCs = "ErrTxt,ErrCode"; foreach (string s in strCs.Split(',')) { if (dtSource.Columns.Contains(s) == true) { continue; } dtSource.Columns.Add(s, typeof(System.String)); } //string strCs = "ErrTxt,Co_idS,GwAddr,GwIp,ModuleName,MeterAddr,ModuleAddr"; //foreach (string s in strCs.Split(',')) //{ // if (dtSource.Columns.Contains(s) == true) continue; // dtSource.Columns.Add(s, typeof(System.String)); //} //string intCs = "Gw_id,Esp_id,Mm_id,Module_id,Meter_id,Parent_id,Attrib,Layer,IsDefine,ErrCode,Co_id"; //foreach (string s in intCs.Split(',')) //{ // if (dtSource.Columns.Contains(s) == true) continue; // dtSource.Columns.Add(s, typeof(System.Int32)); //} #endregion #region 5.0删除空白行 (必须倒叙删除,因为每删除一行,索引就会发生改变) for (int i = dtSource.Rows.Count - 1; i >= 0; i--) { if (string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["EspName"])) && string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["EspName"])) && string.IsNullOrEmpty(CommFunc.ConvertDBNullToString(dtSource.Rows[i]["ModuleAddr"]))) { dtSource.Rows[i].Delete(); } } #endregion dtSource.AcceptChanges(); return(dtSource); }