예제 #1
0
        private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
        {
            IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);

            Int32 cellCount = 0;

            foreach (var item in sheetDetailDataWrapper.Datas)
            {
                if (item == null)
                {
                    StartRow++;
                    continue;
                }

                IRow newRow = sheet.CreateRow(StartRow);

                buildContext.Row = newRow;

                foreach (PropertyInfo property in checkPropertyInfos)
                {
                    Object obj = property.GetValue(item, null);

                    Type t = property.PropertyType;

                    ICell cell;

                    if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable <>))
                    {
                        var ssd = ((IEnumerable)obj).Cast <IExtendedBase>();

                        if (ssd == null)
                        {
                            continue;
                        }

                        foreach (var v in sheetDetailDataWrapper.Titles)
                        {
                            IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();

                            SetCell(newRow, cellCount++, sv.TypeValue);
                        }

                        continue;
                    }

                    SetCell(newRow, cellCount++, obj);
                }

                StartRow++;
                cellCount = 0;
            }

            return(sheet);
        }
예제 #2
0
        /// <summary>
        /// 导入
        /// </summary>
        /// <typeparam name="T">具体对象</typeparam>
        /// <param name="fs"></param>
        /// <param name="fileName"></param>
        /// <param name="isFirstRowColumn"></param>
        /// <returns></returns>
        public static IEnumerable <T> ExcelToDataTable <T>(Stream fs, bool isFirstRowColumn = false) where T : new()
        {
            List <T> ts = new List <T>();

            Type type = typeof(T);

            IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);

            try
            {
                IWorkbook workbook = WorkbookFactory.Create(fs);

                fs.Dispose();

                ISheet sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);

                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    Int32 startRow = isFirstRowColumn ? 1 : 0;

                    int rowCount = sheet.LastRowNum; //行数

                    int length = checkPropertyInfos.Count;

                    length = length > cellCount + 1 ? cellCount + 1 : length;

                    Boolean haveValue = false;

                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);

                        if (row == null)
                        {
                            continue;              //没有数据的行默认是null       
                        }
                        T t = new T();

                        for (int f = 0; f < length; f++)
                        {
                            ICell cell = row.GetCell(f);

                            if (cell == null || String.IsNullOrEmpty(cell.ToString()))
                            {
                                continue;
                            }

                            object b = cell.ToString();

                            if (cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型
                                if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                {
                                    b = cell.DateCellValue;
                                }
                                else
                                {
                                    b = cell.NumericCellValue;
                                }
                            }

                            PropertyInfo pinfo = checkPropertyInfos[f];

                            if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候,强转
                            {
                                b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());
                            }

                            type.GetProperty(pinfo.Name).SetValue(t, b, null);

                            if (!haveValue)
                            {
                                haveValue = true;
                            }
                        }
                        if (haveValue)
                        {
                            ts.Add(t); haveValue = false;
                        }
                    }
                }

                return(ts);
            }
            catch (Exception ex)
            {
                return(null);
            }
        }
예제 #3
0
        private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
        {
            IRow titleRow = null;

            ICell titleCell = null;

            if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))
            {
                titleRow = sheet.CreateRow(StartRow);

                buildContext.Row = titleRow;

                StartRow++;

                titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);

                if (OnHeadCellSetAfter != null)
                {
                    OnHeadCellSetAfter(buildContext);
                }
            }

            IRow row = sheet.CreateRow(StartRow);

            buildContext.Row = row;

            IList <PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);

            int i = 0;

            foreach (PropertyInfo property in checkPropertyInfos)
            {
                DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;

                if (dn != null)
                {
                    SetCell(row, i++, dn.DisplayName);
                    continue;
                }

                Type t = property.PropertyType;

                if (t.IsGenericType)
                {
                    if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)
                    {
                        continue;
                    }

                    foreach (var item in sheetDetailDataWrapper.Titles)
                    {
                        SetCell(row, i++, item.TypeName);
                    }
                }
            }

            if (titleCell != null && i > 0)
            {
                titleCell.MergeTo(titleRow.CreateCell(i - 1));

                titleCell.CellStyle = this.CenterStyle;
            }

            StartRow++;

            return(sheet);
        }
예제 #4
0
        private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
        {
            IList <PropertyInfoDetail> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);

            Int32 cellCount = 0;

            foreach (var item in sheetDetailDataWrapper.Datas)
            {
                if (item == null)
                {
                    _startRow++;
                    continue;
                }

                IRow newRow = sheet.CreateRow(_startRow);

                _buildContext.Row = newRow;

                foreach (PropertyInfoDetail propertyInfoDeatil in checkPropertyInfos)
                {
                    Object obj = propertyInfoDeatil.PropertyInfoV.GetValue(item, null);

                    Type t = propertyInfoDeatil.PropertyInfoV.PropertyType;

                    if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable <>))
                    {
                        var ssd = ((IEnumerable)obj).Cast <IExtendedBase>();

                        if (ssd == null)
                        {
                            continue;
                        }

                        foreach (var title in sheetDetailDataWrapper.Titles)
                        {
                            IExtendedBase sv = ssd.SingleOrDefault(s => s.TypeId == title.TypeId);

                            Object val = null;

                            if (sv == null || sv.TypeValue == null)
                            {
                                val = ExcelModelsPropertyManage.GetExtendedDefaultValue(title.GetType());
                            }
                            else
                            {
                                val = sv.TypeValue;
                            }


                            SetCell(newRow, cellCount++, val);
                        }

                        continue;
                    }

                    SetCell(newRow, cellCount++, obj == null && propertyInfoDeatil.DefaultVale != null ? propertyInfoDeatil.DefaultVale : obj);
                }

                _startRow++;
                cellCount = 0;
            }

            return(sheet);
        }