Exemplo n.º 1
0
        /// <summary>
        /// 读取excel模板,初始化excel数据
        /// </summary>
        /// <param name="HeaderRowIndex"></param>
        /// <returns></returns>
        private List <ExcelModel> RenderDataTableFromExcel(int HeaderRowIndex)
        {
            ISheet sheet     = hssfworkbook.GetSheetAt(0);
            IRow   headerRow = sheet.GetRow(HeaderRowIndex);

            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题,未做调整,实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;

            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value))
                        {
                            continue;
                        }
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName  = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType  = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex    = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }
                }
            }
            return(list);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 解析采集报表模板 Author:彭皓 2017-04-14
        /// </summary>
        /// <param name="indexRow">开始行</param>
        public Dictionary <int, List <ExcelModel> > RenderDataTableFromExcel2(int indexRow)
        {
            Dictionary <int, List <ExcelModel> > map = new Dictionary <int, List <ExcelModel> >();
            ISheet sheet = hssfworkbook.GetSheetAt(0);

            hssfworkbook.SetActiveSheet(0);
            sheet.IsSelected = true;

            IRow headerRow = sheet.GetRow(indexRow);

            cellCount = headerRow.LastCellNum;

            int rowCount = sheet.LastRowNum;

            //XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator((XSSFWorkbook) hssfworkbook);
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = "";
                        if (row.GetCell(j).CellType == CellType.Numeric)
                        {
                            value = row.GetCell(j).NumericCellValue.ToString().Trim();
                        }

                        /*else if (row.GetCell(j).CellType == CellType.Formula)
                         * {
                         *  value = row.GetCell(j).CellFormula;
                         *  value =  eval.EvaluateFormulaCell(row.GetCell(j)).ToString();
                         * }*/
                        else
                        {
                            value = row.GetCell(j).StringCellValue.Trim();
                        }
                        if (string.IsNullOrEmpty(value))
                        {
                            continue;
                        }
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.IndexOf('}') - 3);
                            string index = value.Substring(value.IndexOf('{') + 1, 1);
                            model.DataIndex   = int.Parse(index);
                            model.ColumnType  = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex    = row.RowNum;
                            //FileLog.WriteLog(model.ColumnName + ":第" + model.DataIndex+"行数据,Row="+ model.RowIndex + ",Column" + model.ColumnIndex);
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            if (!map.ContainsKey(model.DataIndex))
                            {
                                map.Add(model.DataIndex, new List <ExcelModel>());
                            }
                            map[model.DataIndex].Add(model);
                        }
                    }
                }
            }

            return(map);
        }
Exemplo n.º 3
0
        private List <ExcelModel> RenderDataTableFromExcel(int sheetIndex, string a)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);

            hssfworkbook.SetActiveSheet(sheetIndex);
            sheet.IsSelected = true;

            IRow headerRow = null;

            // modify by luqy
            if (sheetIndex == 7 || sheetIndex == 8)
            {
                headerRow = sheet.GetRow(1);
            }
            else
            {
                headerRow = sheet.GetRow(2);
            }

            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题,未做调整,实际应该取的是包含列最多的行
            for (int n = 0; n < sheet.LastRowNum; n++)
            {
                headerRow = sheet.GetRow(n);
                if (headerRow != null)
                {
                    if (headerRow.LastCellNum > cellCount)
                    {
                        cellCount = headerRow.LastCellNum;
                    }
                }
            }


            int rowCount = sheet.LastRowNum;

            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = "";
                        if (row.GetCell(j).CellType == CellType.Numeric)
                        {
                            value = row.GetCell(j).NumericCellValue.ToString().Trim();
                        }
                        else
                        {
                            value = row.GetCell(j).StringCellValue.Trim();
                        }
                        if (string.IsNullOrEmpty(value))
                        {
                            continue;
                        }
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName  = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType  = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex    = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }
                }
            }
            return(list);
        }