Exemplo n.º 1
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="path">Excel文件路径</param>
        /// <param name="objectProps">转换规则</param>
        /// <param name="worksheetIndex">获取工作表Index 默认获取第一张工作表内容( Index = 0 )</param>
        /// <param name="isContainLieDingYi">是否含有列定义行, 默认true</param>
        /// <param name="lieDingYi_RowIndex">首位列定义 RowIndex</param>
        /// <param name="lieDingYi_ColumnIndex">首位列定义 ColumnIndex</param>
        /// <returns></returns>
        public List <T> WorkSheet2List <T>(
            string path,
            List <PropertyColumn> objectProps,
            int worksheetIndex          = 0,
            bool isContainLieDingYi     = true,
            int lieDingYi_RowIndex      = 0, int lieDingYi_ColumnIndex = 0,
            bool ignoreRepeatColumnName = false
            ) where T : class, new()
        {
            #region  制需要导入的文件到 \exe目录\Temp\ExcelFiles, 用于解决文件占用问题

            string copyToTempDirectory = System.IO.Path.Combine(Environment.CurrentDirectory, "Temp", "ExcelFiles");
            if (System.IO.Directory.Exists(copyToTempDirectory) == false)
            {
                System.IO.Directory.CreateDirectory(copyToTempDirectory);
            }

            string copyToTempPath = System.IO.Path.Combine(copyToTempDirectory, "{0}.{1}".FormatWith(Guid.NewGuid().ToString(), path.Substring(path.LastIndexOf(".") + 1)));
            System.IO.File.Copy(path, copyToTempPath, true);

            #endregion  制需要导入的文件到 \exe目录\Temp\ExcelFiles, 用于解决文件占用问题

            Workbook  workbook       = new Aspose.Cells.Workbook(copyToTempPath);
            Worksheet worksheet      = workbook.Worksheets[worksheetIndex]; // 默认取第一张工作表
            var       maxRowIndex    = worksheet.Cells.MaxDataRow;          // 读取工作表中最大的行指针 ( 由0开始 )
            var       maxColumnIndex = worksheet.Cells.MaxDataColumn;       // 读取工作表中最大的列指针 ( 由0开始 )

            var rowsCount    = maxRowIndex + 1;                             // 总行数
            var columnsCount = maxColumnIndex + 1;                          // // 总列数

            StringBuilder columnNotExist = new StringBuilder();

            #region Columns

            List <PropertyColumn> header = null;

            // 若已定义 Header Index, 则跳过下面方法
            if (objectProps.Exists(i => i.ColumnIndex < 0) == true) // 获取工作表列头信息
            {
                header = this.GetHeader
                         (
                    worksheet: worksheet,
                    columnCount: columnsCount,
                    lieDingYi_RowIndex: lieDingYi_RowIndex,
                    lieDingYi_ColumnIndex: lieDingYi_ColumnIndex,
                    ignoreRepeatColumnName: ignoreRepeatColumnName
                         );

                for (int i = 0; i < objectProps.Count; i++)
                {
                    var matchObjectProps = header.FirstOrDefault(h => h.ExcelColumn == objectProps[i].ExcelColumn);

                    if (matchObjectProps == null)
                    {
                        columnNotExist.AppendLine("找不到列【{0}】;".FormatWith(objectProps[i].ExcelColumn));
                    }
                    else
                    {
                        objectProps[i].ColumnIndex     = matchObjectProps.ColumnIndex;
                        objectProps[i].ColumnIndexName = Util.Excel.ExcelCommonMethod.ToExcelColumnName(objectProps[i].ColumnIndex);
                    }
                }
            }
            else if (isContainLieDingYi == false && objectProps.Exists(i => i.ColumnIndex < 0) == true)
            {
                columnNotExist.Append("无列定义行,且程序员未指定固定的列位置与对象的属性关联。");
            }

            #endregion

            string errorMsg = columnNotExist.ToString();
            if (errorMsg.IsNullOrEmpty() == false)
            {
                workbook = null;
                GC.Collect();
                throw new Exception(errorMsg);
            }

            List <T> result = new List <T>();

            #region Rows

            Type type = typeof(T);

            // 若 T 包含 ExcelRowNumber属性 (int),将 Excel行号赋值给 ExcelRowNumber属性
            System.Reflection.PropertyInfo prop_ExcelRowNumber = type.GetProperty("ExcelRowNumber"); // Excel 行号

            // 若 T 包含 ExcelRowErrorInfo (string),将 Excel 每行转换时遇到的问题赋值到本属性
            System.Reflection.PropertyInfo prop_ExcelRowErrorInfo = type.GetProperty("ExcelRowErrorInfo"); // 记录读取 Excel 的异常信息
            StringBuilder errorInfoStringBuilder = new StringBuilder();                                    // 每行转换时遇到的问题记录

            // 若为无单头, for ( lieDingYi_RowIndex + 1 == >  for ( lieDingYi_RowIndex + 0
            if (isContainLieDingYi == false)
            {
                lieDingYi_RowIndex = lieDingYi_RowIndex - 1;
            }

            for (int rowIndex = lieDingYi_RowIndex + 1; rowIndex <= worksheet.Cells.MaxDataRow; rowIndex++)
            {
                T item = new T();

                if (prop_ExcelRowNumber != null)
                {
                    prop_ExcelRowNumber.SetValue(item, rowIndex + 1, null); // RowNumber = 指针 + 1
                }

                errorInfoStringBuilder.Clear();

                foreach (PropertyColumn itemColumn in objectProps)
                {
                    Cell cell = worksheet.Cells[rowIndex, itemColumn.ColumnIndex];

                    System.Reflection.PropertyInfo propInfo = type.GetProperty(itemColumn.PropertyName);
                    if (propInfo == null)
                    {
                        throw new Exception("返回结果List<{0}>,{0} 不包含有属性【{1}】。".FormatWith(type.Name, itemColumn.PropertyName));
                    }

                    object value = GetValueType(cell);
                    try
                    {
                        PropertyInfoSetValue(propInfo, item, value);
                    }
                    catch (Exception ex)
                    {
                        if (propInfo.PropertyType.FullName.ToString().IndexOf("System.Nullable") == 0) // 可空属性
                        {
                            propInfo.SetValue(item, null, null);
                        }
                        else
                        {
                            errorInfoStringBuilder.AppendLine("读取【{0}】发生错误({1});".FormatWith(itemColumn.ExcelColumn, ex.Message));
                        }
                    }
                }

                string errorInfo = errorInfoStringBuilder.ToString();

                if (prop_ExcelRowErrorInfo != null && errorInfo.IsNullOrEmpty() == false)
                {
                    prop_ExcelRowErrorInfo.SetValue(item, errorInfo, null);
                }

                result.Add(item);
            }

            #endregion

            workbook = null;
            GC.Collect();
            return(result);
        }
Exemplo n.º 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);
        }