读不规则表数据

最近做了一个读取不规则表格的程序（是从一个表中读出地理信息的经纬高、航向角、增量航向角、车站名称、轨道名称、点类型）
在一个xlsx文件存在5个sheet，因为是固定的格式

月 日 | | | | | | | | | | | | | | | 制表单位责任人： ， 年
月 日 | | | | | | | | | | | | | | | 建设单位责任人： ， 年
月 日 | | | | | | | | | | | | | |

也有空的表格——之后会说明！如果遇到空表格，如果不是关键信息无关紧要，但是其他需要定义的信息会出错

第三个表原本是这样的—-更不规则了

改成这样了

上行正向同
第五个表

下面是正文来啦
首先我自己定义了一个保存点的类
才发现C#的一个小招 public int Id { get; set; } = 0;这样相当于设置一个缺省值，不至于为空

public class TxtPoint
    {
        public PointType type;
        public int Id { get; set; } = 0;
        public string OverlayName { get; set; } = "UnKnown";
        public string StationName { get; set; } = "UnKnown";
        public double KiloPos { get; set; } = 0.0;
        public string TrackName { get; set; } = "UnKnown";
        public double Latitude { get; set; } = 0.0;
        public double Longtitude { get; set; } = 0.0;
        public double Height { get; set; } = 0.0;
        public double Bear { get; set; } = 0.0;
        public double DeltaBear { get; set; } = 0.0;
        public string Tag { get; set; } = "UnKnown";
        public TxtPoint()
        {
        }
        public TxtPoint(int index, string overlayname, string stationName, string trackname, double lat, double lng, double
             height, double bear, double deltabear, double KiloPos)
        {
            this.Id = index;
            this.OverlayName = overlayname;
            this.StationName = StationName;
            this.TrackName = trackname;
            this.Latitude = lat;
            this.Longtitude = lng;
            this.Height = height;
            this.Bear = bear;
            this.DeltaBear = deltabear;
            this.KiloPos = KiloPos;
        }
    }
    public enum PointType
    {
        Unknown = 0,
        Signal = 1,
        PiecePoint = 2,
        Switch = 3,
        Joint = 4
    }

之后就是打开Excel表操作
NPOI操作分07年前版和07年后版，可以从文件名后缀看出来
XSSFWorkbook—对应xlsx，就是新版，和XSSFRow
HSSFWorkbook-对应xls，旧版—-HSSFRow
这是我的一个Excel提取经纬度的按钮

private void btnImportExcel_ItemClick(object sender, ItemClickEventArgs e)
        {
            SetOFD(false, "Excel表格文件(*.xlsx,*.xls)|*.xlsx;*.xls");
            if (oFD.ShowDialog() == DialogResult.OK)
            {
                string filetype = System.IO.Path.GetExtension(oFD.FileName);
                if (filetype == ".xlsx")
                {
                    XSSFWorkbook xSSFSheets = new XSSFWorkbook(oFD.FileName);
                    DataTable dt = ImportToDataTable(oFD.FileName, 0, 1);
                    DataTable dt2 = ImportToDataTable(oFD.FileName, 1, 1);
                    DataTable dt4 = ImportToDataTable(oFD.FileName, 3, 1);
                    DataTable dt3 = ImportToDataTable(oFD.FileName, 2, 1);
                    DataTable dt5 = ImportToDataTable(oFD.FileName, 4, 1);
                    //注意此处一个xlxs文件读取了五个DataTable表还有
                    //因为没有考虑其他不规则表的情况，写的简单了，以后会改
                    AddExcelMap2(dt, oFD.FileName);
                    AddExcelMap2(dt2, oFD.FileName);
                    AddExcelMap2(dt3, oFD.FileName);
                    AddExcelMap2(dt4, oFD.FileName);
                    AddExcelMap2(dt5, oFD.FileName);
                }
                else if (filetype == ".xls")
                {
                }
            }
        }

这部分是我写的导入DataTable的
有几个重点我要写下来怕忘了
NotNullCellCount是自己写的来判断不是合并项，比如表第一行的样式，还有表后也有合并项
还有rows.MoveNext（）我试了一下，并不是移动下一行，没有数据就停，他多会儿停我也没搞懂是怎么回事
如果觉得movenext影响速率，可以设置一个计数，记录为这个表整行为空的行数，While（rows.movenext()&&count<N次）的时候可以停

/// <summary>
        /// 从 Xlxs文件导入数据到 DataTable 
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="SheetIndex">工作表的索引</param>
        /// <param name="headerindex">该工作表标题的行数</param>
        /// <returns></returns>
        public static DataTable ImportToDataTable(string filePath, int SheetIndex, int headerindex)
        {
            XSSFWorkbook xssfworkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                xssfworkbook = new XSSFWorkbook(file);
            }
            ISheet sheet = xssfworkbook.GetSheetAt(SheetIndex);
            String SheetName = xssfworkbook.GetSheetName(SheetIndex);
            string filename = filePath.Substring(filePath.LastIndexOf("\\") + 1);
            if (sheet == null)
            {
                return new DataTable();
            }
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable(filename + SheetName);
            int columncount = NotNullCellCount((XSSFRow)sheet.GetRow(headerindex));
            for (int j = 0; j < columncount; j++)
            {
                ICell cell = sheet.GetRow(headerindex).Cells[j];
                dt.Columns.Add(cell.ToString());
            }
            for (int i = 0; i < headerindex + 1; i++)
            {
                rows.MoveNext();
            }
            int flag = 0;//计数：移动至下一行时不为合并行的个数，避免多次计算
            while (rows.MoveNext() && flag < 10)//合并行>10停止计算
            {
                XSSFRow row = (XSSFRow)rows.Current;
                int count = NotNullCellCount(row);//返回行的cell个数避免合并情况和无值情况
                if (count != 0 && count != 1)
                {
                    DataRow Row = dt.NewRow();
                    for (int i = 0; i < columncount; i++)
                    {
                        ICell cell = row.GetCell(i);
                        Row[i] = cell == null ? null : cell.ToString();
                    }
                    dt.Rows.Add(Row);
                }
                else
                {
                    flag++;//计算合并行个数
                }
            }
            xssfworkbook = null;
            return dt;
        }
        /// <summary>
        /// 返回不为空的cell值
        /// </summary>
        /// <param name="Row"></param>
        /// <returns></returns>
        public static int NotNullCellCount(XSSFRow Row)
        {
            int j = 0;
            for (int i = 0; i < Row.Cells.Count; i++)
            {
                ICell cell = Row.GetCell(i);
                if (!string.IsNullOrEmpty(GetCellValue(cell).ToString().Trim()))
                {
                    j++;
                }
            }
            return j;
        }
        /// <summary>
        /// 返回不为空的cell值
        /// </summary>
        /// <param name="Row"></param>
        /// <returns></returns>
        public static int NotNullCellCount(HSSFRow Row)
        {
            int j = 0;
            for (int i = 0; i < Row.Cells.Count; i++)
            {
                ICell cell = Row.GetCell(i);
                if (!string.IsNullOrEmpty(GetCellValue(cell).ToString().Trim()))
                {
                    j++;
                }
            }
            return j;
        }

这个是一个很重要的代码，是NPOI操作Excel判断celltype的程序
Excel上的文本、科学计数、日期什么的

/// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public static object GetCellValue(ICell item)
        {
            if (item == null)
            {
                return string.Empty;
            }
            switch (item.CellType)
            {
                case CellType.BOOLEAN:
                    return item.BooleanCellValue;
                case CellType.ERROR:
                    return ErrorEval.GetText(item.ErrorCellValue);
                case CellType.FORMULA:
                    switch (item.CachedFormulaResultType)
                    {
                        case CellType.BOOLEAN:
                            return item.BooleanCellValue;
                        case CellType.ERROR:
                            return ErrorEval.GetText(item.ErrorCellValue);
                        case CellType.NUMERIC:
                            if (DateUtil.IsCellDateFormatted(item))
                            {
                                return item.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return item.NumericCellValue;
                            }
                        case CellType.STRING:
                            string str = item.StringCellValue;
                            if (!string.IsNullOrEmpty(str))
                            {
                                return str.ToString();
                            }
                            else
                            {
                                return string.Empty;
                            }
                        case CellType.Unknown:
                        case CellType.BLANK:
                        default:
                            return string.Empty;
                    }
                case CellType.NUMERIC:
                    if (DateUtil.IsCellDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        return item.NumericCellValue;
                    }
                case CellType.STRING:
                    string strValue = item.StringCellValue;
                    return strValue.ToString().Trim();
                case CellType.Unknown:
                case CellType.BLANK:
                default:
                    return string.Empty;
            }
        }

        private void AddExcelMap2(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
            {
                List<TxtPoint> points = DataTableToPoints(dt);
                Hashtable.Add(dt.TableName, points);
                TxtPointsToMap(points, dt.TableName);
            }
        }
        private List<TxtPoint> DataTableToPoints(DataTable dt)
        {
            List<TxtPoint> points = new List<TxtPoint>();
            string[] strColumns = new string[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strColumns[i] = dt.Columns[i].ColumnName.ToString();//DataTable的列名集合
            }
            string[] id = new string[] { "数据编号", "编号", "序号" };
            string[] latname = new string[] { "纬度", "lat", "Lat", "Latitude", "latitude", "纬度(毫秒)" };
            string[] lngname = new string[] { "经度", "lon", "Lon", "Longtitude", "longtitude", "Lng", "经度(毫秒)" };
            string[] hgtname = new string[] { "高程", "hgt", "Hgt" };
            string[] kiloname = new string[] { "公里标", "里程" };
            string[] stationName = new string[] { "车站名", "车站名称" };
            string[] trackName = new string[] { "所在轨道名称" };
            string[] bear = new string[] { "航向角", "航向角(毫秒)" };
            string[] deltabear = new string[] { "增量航向角" };
            string[] tag = new string[] { "备注" };
            //
            string idc = ReturnColumnName(id, strColumns);
            string latc = ReturnColumnName(latname, strColumns);
            string lngc = ReturnColumnName(lngname, strColumns);
            string hgtc = ReturnColumnName(hgtname, strColumns);
            string kiloc = ReturnColumnName(kiloname, strColumns);
            string stac = ReturnColumnName(stationName, strColumns);
            string tc = ReturnColumnName(trackName, strColumns);
            string bearc = ReturnColumnName(bear, strColumns);
            string dbc = ReturnColumnName(deltabear, strColumns);
            string tagc = ReturnColumnName(tag, strColumns);
            foreach (DataRow dr in dt.Rows)
            {
                TxtPoint t = new TxtPoint();
                t.OverlayName = dt.TableName;
                if (!string.IsNullOrEmpty(idc) && dr[idc] != DBNull.Value)
                {
                    t.Id = Convert.ToInt32(dr[idc]);
                }
                if (!string.IsNullOrEmpty(latc) && dr[latc] != DBNull.Value)
                {
                    t.Latitude = Convert.ToDouble(dr[latc]);
                }
                if (!string.IsNullOrEmpty(lngc) && dr[lngc] != DBNull.Value)
                {
                    t.Longtitude = Convert.ToDouble(dr[lngc]);
                }
                if (!string.IsNullOrEmpty(hgtc) && dr[hgtc] != DBNull.Value)
                {
                    t.Height = Convert.ToDouble(dr[hgtc]);
                }
                if (!string.IsNullOrEmpty(kiloc) && dr[kiloc] != DBNull.Value)
                {
                    t.KiloPos = Convert.ToDouble(dr[kiloc]);
                }
                if (!string.IsNullOrEmpty(stac) && dr[stac] != DBNull.Value)
                {
                    t.StationName = dr[stac].ToString();
                }
                if (!string.IsNullOrEmpty(tc) && dr[tc] != DBNull.Value)
                {
                    t.TrackName = dr[tc].ToString();
                }
                if (!string.IsNullOrEmpty(bearc) && dr[bearc] != DBNull.Value)
                {
                    t.Bear = Convert.ToDouble(dr[bearc]);
                }
                if (!string.IsNullOrEmpty(dbc) && dr[dbc] != DBNull.Value)
                {
                    t.DeltaBear = Convert.ToDouble(dr[dbc]);
                }
                if (!string.IsNullOrEmpty(tagc) && dr[tagc] != DBNull.Value)
                {
                    t.Tag = dr[tagc].ToString();
                }
                points.Add(t);
            }
            return points;
        }
        public string ReturnColumnName(string[] keyword, string[] columnNames)
        {
            string temp = "";
            for (int i = 0; i < columnNames.Length; i++)
            {
                if (string.IsNullOrEmpty(temp))
                {
                    for (int j = 0; j < keyword.Length; j++)
                    {
                        if (columnNames[i].Contains(keyword[j]))
                        {
                            temp = columnNames[i];
                            break;
                        }
                    }
                }
                else
                {
                    break;
                }
            }
            return temp;
        }
//因为GMap里的数据都是纠偏之后的，所以只好保存原始数据的TxtPoint来进行其他操作
        private void TxtPointsToMap(List<TxtPoint> points, string overlayname)
        {
            List<string[]> latlngs = new List<string[]>();
            foreach (TxtPoint p in points)
            {
                double lat = Math.Round(p.Latitude / 3600000.0, 10);
                double lng = Math.Round(p.Longtitude / 3600000.0, 10);
                if (lat != 0 && lng != 0)
                {
                    latlngs.Add(new string[2] { lng.ToString(), lat.ToString() });
                }
            }
            ShowFileLatLng(latlngs, overlayname, true);
        }

懒了—下次再写———2020.5.20