C#调用Spire.XLS 操作EXCEL表格

【实例简介】

该实例用来统计火车走行的公里数,使用到Excel表格的读取和写入。

【实例截图】

【核心代码】

        private DataTable ExcelTable2DataTable(string filePath, int columnRowNo, int[] useColumnNo, string[] useColumnName)
        {
            DataTable dt = null;
            DataRow dataRow = null;

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath);

            if (workbook.Worksheets.Count <= 0)
                return null;
            dt = new DataTable();

            Worksheet sheet = workbook.Worksheets[0];
            char ch = 'A';
            string cellName = null;

            dt = new DataTable(sheet.Name);
            for (int i = 0; i < useColumnName.Length; i )
                dt.Columns.Add(new DataColumn(useColumnName[i]));

            for (int i = columnRowNo 1; i <= 100000; i )
            {
                dataRow = dt.NewRow();
                for (int j = 0; j < useColumnNo.Length; j )
                {
                    if (useColumnNo[j] < 0)
                        break;

                    ch = (char)('A' useColumnNo[j] - 1);
                    cellName = ch.ToString() i.ToString();
                    if (sheet.Range[cellName].HasString)
                        dataRow[j] = sheet.Range[cellName].Text;
                    else if (sheet.Range[cellName].HasNumber)
                    {
                        dataRow[j] = (float)sheet.Range[cellName].NumberValue;
                    }
                    else if (sheet.Range[cellName].HasDateTime)
                        dataRow[j] = sheet.Range[cellName].DateTimeValue.ToString();
                    else if (sheet.Range[cellName].HasFormulaDateTime)
                        dataRow[j] = sheet.Range[cellName].FormulaDateTime.ToString();
                    else
                        ;
                }

                if (ExcelTableComeTail(dataRow, useColumnNo.Length))//表结束
                    break;

                dt.Rows.Add(dataRow);
            }

            return dt;
        }