Read all the values of a cell in excel using aspose cells

Question

I have an excel file in below format.

col 1|col 2 | col 3 | status|some col|
-------------------------------------
1    | 23   | test  | UDS   | Test
-------------------------------------
12   | 2   | test2  | ADS   | Test23

I need to read all values in the status column into a List. How can I do it? Here 'Status' column is always at a fixed area in excel. ex. Row 8, column AA will be always 'Status'

Some of the column headers are repeating so I can't read this excel into a datatable and read the column value from datatable.

Answer 1

Using the Aspose.Cells APIs, here is another simple way to accomplish the task:e.g Sample code:

        //Open your template file.
        Workbook wb = new Workbook("e:\\test2\\Book1.xlsx");
        //Get the first worksheet.
        Worksheet worksheet = wb.Worksheets[0];
        //Get the cells collection.
        Cells cells = worksheet.Cells;

        //Define the list.
        List<string> myList = new List<string>();
        //Get the AA column index. (Since "Status" is always @ AA column.
        int col = CellsHelper.ColumnNameToIndex("AA");

        //Get the last row index in AA column.
        int last_row = worksheet.Cells.GetLastDataRow(col);

        //Loop through the "Status" column while start collecting values from row 9
        //to save each value to List
        for (int i = 8; i <= last_row; i++)
        {
            myList.Add(cells[i, col].Value.ToString());
        } 
    } 

I am working as Support developer/ Evangelist at Aspose.

Answer 2

Using Aspose.Cells I can do this as follows,

            Workbook workbook = new Workbook(exlFile);
            Worksheet worksheet = workbook.Worksheets[0];
            int column = 3; //this is fixed 
            int row = 5;     // this is fixed
            int rows = worksheet.Cells.MaxRow;
            Range range = worksheet.Cells.CreateRange(row, column, rows - row + 1, 1);
            DataTable dataTable = range.ExportDataTable();

Answer 3

I have been working with excel reading too. This is the way I had implemented which might help you. I have updated the code to match for the question being asked. Let me know if I could help you by any means!

public static List<string> ReadExcelDataFile(string fileFullPath)
    {
        Application xlApp = new Application();
        Workbook xlWorkBook = null;
        Worksheet dataSheet = null;
        Range dataRange = null;
        List<string> data = new List<string>();
        object[,] valueArray;

        try
        {
            // Open the excel file
            xlWorkBook = xlApp.Workbooks.Open(fileFullPath, null, true);

            if (xlWorkBook.Worksheets != null
                && xlWorkBook.Worksheets.Count > 0)
            {
                // Get the first data sheet
                dataSheet = xlWorkBook.Worksheets[1];

                // Get range of data in the worksheet
                dataRange = dataSheet.UsedRange;

                // Read all data from data range in the worksheet
                valueArray = (object[,])dataRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                // Here if you sure of the column index then you need not loop and find the column in excel shet. Just directly index to the column and skip first loop
                for (int colIndex = 0; colIndex < valueArray.GetLength(1); colIndex++)
                {
                    if (valueArray[0, colIndex] != null
                        && !string.IsNullOrEmpty(valueArray[0, colIndex].ToString())
                        && valueArray[0, colIndex].ToString().Equals("status"))
                    {
                        for (int rowIndex = 1; rowIndex < valueArray.GetLength(0); rowIndex++)
                        {
                            if (valueArray[rowIndex, colIndex] != null
                                && !string.IsNullOrEmpty(valueArray[rowIndex, colIndex].ToString()))
                            {
                                // Get data from each column which is not null and is a numeric value
                                data.Add(valueArray[rowIndex, colIndex].ToString());
                            }
                        }
                    }
                }
            }
            else
            {
                throw new Exception("Invalid or Empty sheet");
            }
        }
        catch (Exception generalException)
        {
            throw generalException;

        }
        finally
        {
            if (xlWorkBook != null)
            {
                // Close the workbook after job is done
                xlWorkBook.Close();
                xlApp.Quit();
            }
        }

        return data;
    }