public DataTable GetData(string filePath)
        {
            HSSFWorkbook hssfworkbook;

            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            using (NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0))
            {
                DataTable             table     = new DataTable();
                NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0); //第一行为标题行
                int cellCount = headerRow.LastCellNum;             //LastCellNum = PhysicalNumberOfCells
                int rowCount  = sheet.LastRowNum;                  //LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    NPOI.SS.UserModel.Row row = sheet.GetRow(i);
                    DataRow dataRow           = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j));
                            }
                        }
                    }

                    table.Rows.Add(dataRow);
                }
                return(table);
            }
        }
        /**
         * @return <c>null</c> if cell is1 missing, empty or blank
         */
        private static String GetTargetFunctionName(NPOI.SS.UserModel.Row r)
        {
            if (r == null)
            {
                Console.Error.WriteLine("Warning - given null row, can't figure out function name");
                return(null);
            }
            Cell cell = r.GetCell(SS.COLUMN_INDEX_FUNCTION_NAME);

            if (cell == null)
            {
                Console.Error.WriteLine("Warning - NPOI.SS.UserModel.Row " + r.RowNum + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name");
                return(null);
            }
            if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK)
            {
                return(null);
            }
            if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
            {
                return(cell.RichStringCellValue.String);
            }

            throw new AssertFailedException("Bad cell type for 'function name' column: ("
                                            + cell.CellType + ") row (" + (r.RowNum + 1) + ")");
        }
 private static Cell GetExpectedValueCell(NPOI.SS.UserModel.Row row, short columnIndex)
 {
     if (row == null)
     {
         return(null);
     }
     return(row.GetCell(columnIndex));
 }
Exemple #4
0
        /// <summary>
        /// Get a specific cell from a row. If the cell doesn't exist,
        /// </summary>
        /// <param name="row">The row that the cell is part of</param>
        /// <param name="column">The column index that the cell is in.</param>
        /// <returns>The cell indicated by the column.</returns>
        public static NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Row row, int column)
        {
            NPOI.SS.UserModel.Cell cell = row.GetCell(column);

            if (cell == null)
            {
                cell = row.CreateCell(column);
            }
            return(cell);
        }
        /// <summary>
        /// Gets the cell to manage it.
        /// </summary>
        /// <param name="rowPos">The row pos.</param>
        /// <param name="columnPos">The column pos.</param>
        /// <returns>Cell of the spreadsheet</returns>
        public NPOI.SS.UserModel.Cell GetCell(int rowPos, int columnPos)
        {
            NPOI.SS.UserModel.Row row = _sheet.GetRow(rowPos);

            if (row == null)
            {
                AddRow(rowPos);
                row = _sheet.GetRow(rowPos);
            }

            //Creating the cell
            NPOI.SS.UserModel.Cell cell = row.GetCell(columnPos);

            if (cell == null)
            {
                row.CreateCell(columnPos);
                cell = row.GetCell(columnPos);
            }

            return(cell);
        }
Exemple #6
0
        public void TestCountifFromSpreadsheet()
        {
            String FILE_NAME       = "countifExamples.xls";
            int    START_ROW_IX    = 1;
            int    COL_IX_ACTUAL   = 2;
            int    COL_IX_EXPECTED = 3;

            int          failureCount = 0;
            HSSFWorkbook wb           = HSSFTestDataSamples.OpenSampleWorkbook(FILE_NAME);

            NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
            HSSFFormulaEvaluator    fe    = new HSSFFormulaEvaluator(wb);
            int maxRow = sheet.LastRowNum;

            for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++)
            {
                NPOI.SS.UserModel.Row row = sheet.GetRow(rowIx);
                if (row == null)
                {
                    continue;
                }
                Cell cell = row.GetCell(COL_IX_ACTUAL);
                NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
                double actualValue             = cv.NumberValue;
                double expectedValue           = row.GetCell(COL_IX_EXPECTED).NumericCellValue;
                if (actualValue != expectedValue)
                {
                    Console.Error.WriteLine("Problem with Test case on row " + (rowIx + 1) + " "
                                            + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
                    failureCount++;
                }
            }

            if (failureCount > 0)
            {
                throw new AssertFailedException(failureCount + " countif evaluations failed. See stderr for more details");
            }
        }
        /**
         *
         * @return a constant from the local Result class denoting whether there were any evaluation
         * cases, and whether they all succeeded.
         */
        private int ProcessFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName,
                                       NPOI.SS.UserModel.Row formulasRow, NPOI.SS.UserModel.Row expectedValuesRow)
        {
            int result    = Result.NO_EVALUATIONS_FOUND; // so far
            int endcolnum = formulasRow.LastCellNum;

            //evaluator.SetCurrentRow(formulasRow);

            // iterate across the row for all the evaluation cases
            for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
            {
                Cell c = formulasRow.GetCell(colnum);
                if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA)
                {
                    continue;
                }

                NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c);

                Cell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
                try
                {
                    ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
                                          expectedValueCell, actualValue);
                    _evaluationSuccessCount++;
                    if (result != Result.SOME_EVALUATIONS_FAILED)
                    {
                        result = Result.ALL_EVALUATIONS_SUCCEEDED;
                    }
                }
                catch (AssertFailedException)
                {
                    _evaluationFailureCount++;
                    //printShortStackTrace(System.err, e);
                    result = Result.SOME_EVALUATIONS_FAILED;
                }
            }
            return(result);
        }
Exemple #8
0
        /// <summary>
        /// 根据Excel获取集合信息
        /// </summary>
        /// <param name="fileName">Excel存储路径</param>
        /// <returns></returns>
        public List <IMovieShowList.MovieShow> GetList4Excel(string fileName)
        {
            List <IMovieShowList.MovieShow> list = new List <IMovieShowList.MovieShow>();

            HSSFWorkbook wk = null;

            using (FileStream fs = System.IO.File.Open(fileName, FileMode.Open,
                                                       FileAccess.Read, FileShare.ReadWrite))
            {
                //把xls文件读入workbook变量里,之后就可以关闭了
                wk = new HSSFWorkbook(fs);
                fs.Close();
            }
            HSSFSheet sheet = (HSSFSheet)wk.GetSheetAt(0);

            if (sheet != null)
            {
                int rowIndex = 0;
                NPOI.SS.UserModel.Row row = null;
                while ((row = sheet.GetRow(rowIndex)) != null)
                {
                    try
                    {
                        Cell   c1   = row.GetCell(0);
                        string room = c1.RichStringCellValue.String;

                        Cell   c2  = row.GetCell(1);
                        string tim = c2.RichStringCellValue.String;

                        Cell   c3   = row.GetCell(2);
                        string name = c3.RichStringCellValue.String;

                        if (string.IsNullOrWhiteSpace(room) || string.IsNullOrWhiteSpace(tim) || string.IsNullOrWhiteSpace(name))
                        {
                            rowIndex++;
                            continue;
                        }

                        string time = ParseBeginTime(tim);

                        list.Add(new IMovieShowList.MovieShow()
                        {
                            Room      = room,
                            BeginTime = time,
                            Name      = name
                        });
                        rowIndex++;
                    }
                    catch (Exception ex)
                    {
                        isOk = false;
                        Msg  = "xcel文件中的时间有错误,在" + (rowIndex + 1) + "行" + Environment.NewLine + ex.Message;

                        break;
                    }
                }
                sheet.Dispose();
            }

            return(list);
        }