Import a whole Excel workbook

1

All the excel import nodes seem to require sheet names.

Is it possible to just import the entire workbook?

I’d like all the tabs, not just one.

image
image416×501 34.1 KB

1 Like
2

What have you tried so far? I’d take a look at the python the node uses and see if you find any options there. You can always look up the Excel API and see if there’s a way to either return all workbook data or to parse the existing worksheets one by one. There may even be a node already that can tell you all the worksheets in a file.

EDIT: A quick search shows that Sparrow and Data-Shapes both have nodes for getting worksheet names from a given file.

1 Like
3

I was working on something like this today.
It is possible in the Excel Api.

In this case I need all the data of all the worksheets.
I need all the rows from the first, after that I need the second row to the last row.

Over here a C# example:

// Lees het Excel-bestand
                    var excelApp = new Application();

                    // Open het Excel-bestand
                    Workbook workbook = excelApp.Workbooks.Open(first_excel);

                    // Het eerste werkblad gebruiken
                    Worksheet firstSheet = workbook.Worksheets[1];

                    // StringBuilder voor elke rij
                    StringBuilder resultBuilder = new StringBuilder();

                    string firstName = firstSheet.Name;
                    int startIndex = 1;


                    foreach (Worksheet ws in workbook.Sheets)
                    {
                        string wsName = ws.Name.ToLower();

                       // Krijg het gebruikt bereik in het werkblad
                            var usedRange = ws.UsedRange;


                            if (ws.Name != firstName)
                            {
                                startIndex = 2;
                            }


                          for (int i = startIndex; i <= usedRange.Rows.Count; i++)
                          {
                              string result = ConvertWorksheetToString(ws, i);
                              resultBuilder.Append(result);
                          }

                          ReleaseComObject(ws);

               }

Release object:

static void ReleaseComObject(object obj)
        {
            try
            {
                Marshal.ReleaseComObject(obj);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Fout bij het vrijgeven van COM-object: {ex.Message}");
            }
            finally
            {
                obj = null;
            }
        }

ConvertWorkSheetToString:

static string ConvertWorksheetToString(Worksheet worksheet, int rowIndex)
        {
            // StringBuilder wordt gebruikt om efficiënt strings op te bouwen
            var resultBuilder = new System.Text.StringBuilder();

            // Krijg het gebruikt bereik in het werkblad
            var usedRange = worksheet.UsedRange;


            // Voeg kolomkoppen toe aan de resultaten
            for (int col = 1; col <= usedRange.Columns.Count; col++)
            {

                string cleanedValue = null;
                try
                {
                    string cellValue = usedRange.Cells[rowIndex, col].Value2.ToString();

                    // Controleer of de celwaarde null of leeg is, en trim eventuele voorloopse en volgroepruimte
                    cleanedValue = (!string.IsNullOrWhiteSpace(cellValue)) ? cellValue.ToString().Trim() : "-";


                }
                catch
                {
                    cleanedValue = "-";
                }



                resultBuilder.Append(cleanedValue);
                resultBuilder.Append(";");
            }
            resultBuilder.AppendLine();

            // Converteer de StringBuilder naar een string en retourneer deze
            return resultBuilder.ToString();
        }
2 Likes
4

I’d recommend looking into the source code form the OpenXML nodes, as the worksheets can be exposed that way as well.

1 Like