Example #1
0
        // IWorkbook
        // HSSFWorkbook -- XLS
        // XSSFWorkbook -- XLSX

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="fileName">文件名称</param>
        /// <returns>DataSet 结果集</returns>
        public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null)
        {
            DataSet result = new DataSet();

            IWorkbook iWorkbook = null;


            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.EndsWith("xlsx"))
                {
                    iWorkbook = new XSSFWorkbook(fs);
                }
                else if (filePath.EndsWith("xls"))
                {
                    iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
                }
                else
                {
                    throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。");
                }

                int sheetCount = iWorkbook.NumberOfSheets; // 工作表总数
                for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++)
                {
                    SheetReadConfig matchReadConfig = null;
                    ISheet          sheet           = iWorkbook.GetSheetAt(sheetNo);

                    if (config != null && config.Config != null)
                    {
                        matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo));
                        if (matchReadConfig == null)
                        {
                            continue;
                        }
                    }

                    DataTable dt = new DataTable();

                    #region 表头

                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    if (header == null)
                    {
                        continue;
                    }

                    List <int> columns = new List <int>();
                    for (int i = 0; i < header.LastCellNum; i++)
                    {
                        if (header.GetCell(i) != null)
                        {
                            object obj = NPOIHelper.GetValueType(header.GetCell(i) as ICell);
                            if (obj == null || obj.ToString() == string.Empty)
                            {
                                dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                                //continue;
                            }
                            else
                            {
                                dt.Columns.Add(new DataColumn(obj.ToString()));
                            }
                            columns.Add(i);
                        }
                    }
                    #endregion

                    #region 数据

                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        DataRow dr          = dt.NewRow();
                        bool    rowHasValue = false; // TODO : 遇到空行 计数问题未解决
                        // TODO : 计算行号
                        foreach (int j in columns)
                        {
                            if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
                            {
                                if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j))
                                {
                                    CellType t = CellType.Blank;
                                    if (matchReadConfig.CellReadRule.TryGetValue(j, out t))
                                    {
                                        try
                                        {
                                            switch (t)
                                            {
                                            case CellType.String:
                                            {
                                                dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
                                                if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
                                                {
                                                    rowHasValue = true;
                                                }
                                            }
                                            break;

                                            case CellType.Formula:
                                            {
                                                dr[j] = sheet.GetRow(i).GetCell(j).CellFormula;
                                                if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
                                                {
                                                    rowHasValue = true;
                                                }
                                            }
                                            break;

                                            case CellType.DateTime:
                                            {
                                                dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
                                                if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false)
                                                {
                                                    rowHasValue = true;
                                                }
                                            }
                                            break;

                                            case CellType.Blank:
                                                dr[j] = string.Empty;
                                                break;

                                            default:
                                                dr[j] = string.Empty;
                                                break;
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex);
                                        }
                                    }
                                    else
                                    {
                                        dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
                                        if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
                                        {
                                            rowHasValue = true;
                                        }
                                    }
                                }
                                else
                                {
                                    // TODO : 读取日期Cell问题仍需解决
                                    dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
                                    if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
                                    {
                                        rowHasValue = true;
                                    }
                                }
                            }
                        }

                        if (rowHasValue)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                    #endregion

                    result.Tables.Add(dt);
                }
            }
            return(result);
        }
Example #2
0
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="fileName">文件名称</param>
        /// <returns>DataSet 结果集</returns>
        public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null)
        {
            // TO___DO 完善此方法读取数据部分

            DataSet result = new DataSet();

            Workbook iWorkbook = null;


            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.EndsWith("xlsx"))
                {
                    iWorkbook = new Workbook(fs);
                }
                else if (filePath.EndsWith("xls"))
                {
                    iWorkbook = new Workbook(fs);
                }
                else
                {
                    throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。");
                }

                int sheetCount = iWorkbook.Worksheets.Count; // 工作表总数
                for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++)
                {
                    SheetReadConfig matchReadConfig = null;
                    Worksheet       sheet           = iWorkbook.Worksheets[sheetNo];

                    var TestSheeName = sheet.Name;
                    if (config != null && config.Config != null) // 跳过不读取的Sheet (名称 或 顺序)
                    {
                        // matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || j.SheetNo == sheetNo);
                        matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.Name || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo));
                        if (matchReadConfig == null)
                        {
                            continue;
                        }
                    }

                    DataTable dt = new DataTable();
                    if (sheet.Cells.Rows.Count <= 0)
                    {
                        result.Tables.Add(dt);
                        continue;
                    }

                    bool isFirstRowDefineColumn = true; // TO___DO 加一个配置 定义第一行是否列定义行

                    #region 表头

                    if (isFirstRowDefineColumn == true)
                    {
                        int cellRow         = 0;
                        int cellColumnCount = sheet.Cells.Columns.Count;
                        Row header          = sheet.Cells.Rows[0];

                        if (header == null)
                        {
                            continue;
                        }

                        List <int> columns = new List <int>();
                        for (int cellColumn = 0; cellColumn < cellColumnCount; cellColumn++)
                        {
                            Cell   cell = sheet.Cells[cellRow, cellColumn];
                            object obj  = ExcelUtils_Aspose.GetValueType(cell);
                            if (obj == null || obj.ToString() == string.Empty)
                            {
                                dt.Columns.Add(new DataColumn("Columns" + cellColumn.ToString()));
                            }
                            else
                            {
                                dt.Columns.Add(new DataColumn(obj.ToString()));
                            }
                            columns.Add(cellColumn);
                        }
                    }

                    #endregion

                    #region 数据

                    //for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    //{
                    //    DataRow dr = dt.NewRow();
                    //    bool rowHasValue = false; // TODO : 遇到空行 计数问题未解决
                    //    // TO__DO : 计算行号
                    //    foreach (int j in columns)
                    //    {
                    //        if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
                    //        {
                    //            if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j))
                    //            {
                    //                NPOICellType t = NPOICellType.Blank;
                    //                if (matchReadConfig.CellReadRule.TryGetValue(j, out t))
                    //                {
                    //                    try
                    //                    {
                    //                        switch (t)
                    //                        {
                    //                            case NPOICellType.String:
                    //                                {
                    //                                    dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
                    //                                    if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
                    //                                    {
                    //                                        rowHasValue = true;
                    //                                    }
                    //                                }
                    //                                break;
                    //                            case NPOICellType.Formula:
                    //                                {
                    //                                    dr[j] = sheet.GetRow(i).GetCell(j).CellFormula;
                    //                                    if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
                    //                                    {
                    //                                        rowHasValue = true;
                    //                                    }
                    //                                }
                    //                                break;
                    //                            case NPOICellType.DateTime:
                    //                                {
                    //                                    dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    //                                    if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false)
                    //                                    {
                    //                                        rowHasValue = true;
                    //                                    }
                    //                                }
                    //                                break;
                    //                            case NPOICellType.Blank:
                    //                                dr[j] = string.Empty;
                    //                                break;
                    //                            default:
                    //                                dr[j] = string.Empty;
                    //                                break;
                    //                        }
                    //                    }
                    //                    catch (Exception ex)
                    //                    {
                    //                        throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex);
                    //                    }
                    //                }
                    //                else
                    //                {
                    //                    dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
                    //                    if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
                    //                    {
                    //                        rowHasValue = true;
                    //                    }
                    //                }
                    //            }
                    //            else
                    //            {
                    //                // TO___DO : 读取日期Cell问题仍需解决
                    //                dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
                    //                if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
                    //                {
                    //                    rowHasValue = true;
                    //                }
                    //            }
                    //        }
                    //    }

                    //    if (rowHasValue)
                    //    {
                    //        dt.Rows.Add(dr);
                    //    }
                    //}
                    #endregion

                    result.Tables.Add(dt);
                }
            }

            return(result);
        }