C# 把Excel转换成Table

最新推荐文章于 2024-07-03 11:30:27 发布
原创最新推荐文章于 2024-07-03 11:30:27 发布 · 695 阅读
2 ·
CC 4.0 BY-SA版权
文章标签：
#excel #c# #sql
C# 专栏收录该内容
3 篇文章
订阅专栏
本文介绍了一个将Excel文件转换为DataTable的函数，处理不同版本Excel、指定sheet和列数，支持日期格式识别。核心内容涉及数据读取、格式转换和异常处理。
#region Excel转换成Table
        /// <summary>
        /// Excel转换成Table
        /// </summary>
        /// <param name="fileNameurl"></param>
        /// <param name="sheetName"></param>
        /// <param name="ColumnNum"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTableHelper(string fileNameurl, string sheetName, int ColumnNum)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileNameurl, FileMode.Open, FileAccess.Read);
                if (fileNameurl.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileNameurl.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow;

                    // firstRow = sheet.GetRow(0);
                    if (ColumnNum > 0)
                    {
                        firstRow = sheet.GetRow(ColumnNum - 1);
                    }
                    else
                    {
                        firstRow = sheet.GetRow(0);
                    }
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null && cellValue != "")
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    cellCount = data.Columns.Count;

                    //找出第几行是列名
                    startRow = ColumnNum;

                    //startRow = firstDataNum-1;
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                //dataRow[j] = row.GetCell(j).ToString();
                                //读取Excel格式，根据格式读取数据类型
                                ICell cell = row.GetCell(j);
                                dataRow[j] = parseExcel(cell);
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }
        }

        private static String parseExcel(ICell cell)
        {
            string result = "";
            switch (cell.CellType)
            {
                case CellType.Formula:
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
                    result = e.Evaluate(cell).StringValue;
                    break;
                case CellType.Numeric:// 数字类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {// 处理日期格式、时间格式
                        string sdf = "";
                        if (cell.CellStyle.DataFormat == HSSFDataFormat
                                .GetBuiltinFormat("h:mm"))
                        {
                            sdf = "HH:mm";
                        }
                        else
                        {// 日期
                            sdf = "yyyy-MM-dd";
                        }
                        DateTime date = cell.DateCellValue;
                        result = date.ToString(sdf);
                    }
                    else if (cell.CellStyle.DataFormat == 58)
                    {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        string sdf = "yyyy-MM-dd";
                        double value = cell.NumericCellValue;
                        DateTime date = new DateTime(1899, 12, 30); // 起始时间
                        date = date.AddDays(value);
                        result = date.ToString(sdf);
                    }
                    else
                    {
                        result = cell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.String:// String类型
                    result = cell.StringCellValue;
                    break;
                case CellType.Blank:
                    result = "";
                    break;
                default:
                    result = "";
                    break;
            }
            return result;
        }
        #endregion