Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        //批量上载采集器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);
        }