Get the table from inside excel Using Spire.Xls

[velkumarIyyappan]

I need to get the last row of the table which is inside the excel sheet. For that I tried to get the Table from the excel sheet. I have searched and found the below code for getting the Table.

sheet.ListObjects or IListObject table1 = sheet.ListObjects[0];

I have tried this code but It was not successful. It is returning the items count in ListObjects as 0.

Could you please help me to get the table details from the excel.

I have attached the excel file. Please refer the excel file.

[Wenly.Zhang]

Hello,

Thanks for your inquiry.
The ListObjects is used to retrieve the tables in a sheet, similar to the table shown below. However, since the documentation you provided does not include this type of table, the returned value will be 0.

sample.png (11.62 KiB) Viewed 467 times

You can use the following code to retrieve the last row data from an Excel sheet:

Code: Select all

  Workbook workbook = new Workbook();

            workbook.LoadFromFile("F:\\LP-Nijmegen Grote Markt 2-3-2023.xlsm");

            Worksheet sheet = workbook.Worksheets[1];
            // Gets the index of the last row
            int lastRowIndex = sheet.LastRow;

            StringBuilder sb = new StringBuilder();
       
             foreach (CellRange cell in sheet.Rows[lastRowIndex - 1].CellList)
            {
                    sb.AppendLine(cell.Value);
            }
            string result = "F:\\output\\LastRow.txt";
            File.WriteAllText(result, sb.ToString());

If you have any other questions or concerns, please feel free to contact us anytime.

Sincerely
Wenly
E-iceblue support team

[velkumarIyyappan]

I need the LastRow of the table from that excel not the LastRow of excel. Could you please give a solution for getting last row of that table from the excel sheet.

Thanks,
Velkumar

[Wenly.Zhang]

Hello,

Thanks for your response.
You can use the following code to retrieve the LastRow of the table from the excel:

Code: Select all

Workbook dstBook = new Workbook();
            dstBook.LoadFromFile("F:\\Sample.xlsx");
            StringBuilder sb = new StringBuilder();

            int num = 0;
            CellRange range = dstBook.Worksheets[0].Range;
            foreach (Worksheet i in dstBook.Worksheets)
            {
                foreach (IListObject j in i.ListObjects)
                {
                    CellRange location = j.Location as CellRange;
                    ExportTableOptions exportTableOptions = new ExportTableOptions();
                    exportTableOptions.ExportColumnNames = true;
                    System.Data.DataTable dataTable = location.ExportDataTable(exportTableOptions);

                    Workbook resultBook = new Workbook();
                    resultBook.Worksheets.Clear();
                    Worksheet worksheet = resultBook.Worksheets.Add(i.Name);
                    worksheet.InsertDataTable(dataTable, true, 1, 1);

                    int lastRowIndex = worksheet.LastRow;

                    foreach (CellRange cell in worksheet.Rows[lastRowIndex - 1].CellList)
                    {
                        sb.AppendLine(cell.Value);                 
                    }
                    num++;
                }
            }
            string result = "F:\\LastRow.txt";
            File.WriteAllText(result, sb.ToString());

However, as we mentioned earlier, the Excel file you provided does not contain any tables. Therefore, it is not possible to retrieve the last row of a table from that Excel file using the code provided. Sorry for the inconvenience caused.

Sincerely,
Wenly
E-iceblue support team