1

13693261870

2022-10-11 a034f7f703dc0328858c0fbcdcf19031d9d9152f

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
 
namespace Community.Excel.Common
{
    /// <summary>
    /// 读取Excel类
    /// </summary>
    public class ReadExcel
    {
        private string intName = typeof(int).Name;
 
        private string doubleName = typeof(double).Name;
 
        private string stringName = typeof(string).Name;
 
        /// <summary>
        /// 读取Excel
        /// </summary>
        public static DataTable ReadXls(string file, int index)
        {
            if (!File.Exists(file)) return null;
 
            string ext = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //IWorkbook wk = ext == ".xls" ? new HSSFWorkbook(fs) : new XSSFWorkbook();
                IWorkbook wk = new HSSFWorkbook(fs);
                ISheet sheet = wk.GetSheetAt(index);
                if (sheet == null)
                {
                    return null;
                }
 
                return SheetToDataTable(sheet);
            }
        }
 
        /// <summary>
        /// Sheet转换为DataTable
        /// </summary>
        public static DataTable SheetToDataTable(ISheet sheet)
        {
            IRow firstRow = sheet.GetRow(0);
            int cellCount = firstRow.LastCellNum;
            int rowCount = sheet.LastRowNum;
 
            DataTable dt = CreateDataTable(cellCount);
            for (int i = sheet.FirstRowNum; i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
 
                DataRow dtRow = dt.NewRow();
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dtRow[j] = cell == null ? string.Empty : cell.ToString();
                }
 
                dt.Rows.Add(dtRow);
            }
 
            return dt;
        }
 
        /// <summary>
        /// 创建DataTable
        /// </summary>
        public static DataTable CreateDataTable(int columns)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < columns; i++)
            {
                DataColumn dc = new DataColumn("Col_" + i, typeof(string));
                dt.Columns.Add(dc);
            }
 
            return dt;
        }
 
        // 查找列位置
        private int FindColumn(DataRow firstRow, int columns, string name)
        {
            for (int i = 0, c = columns; i < c; i++)
            {
                string colName = firstRow[i] == null ? null : firstRow[i].ToString().Trim();
                if (string.IsNullOrEmpty(colName))
                {
                    break;
                }
 
                if (name == colName)
                {
                    return i;
                }
            }
 
            return -1;
        }
    }
}