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);
}