locked
Read multiple excels with the same structure RRS feed

  • Question

  • User1059168712 posted

    In this case, how can I call only the first sheet in all files? All files are identical but have the different information. As a result, I need to have an array with info of Cell B1 from all excel files, sheet 1. And so on.

               foreach (string filename in Directory.GetFiles(filepath))
                {  if (Path.GetExtension(filename) != ".xlsx") { }
                        else
                    {
                        var files = new string[] { filename };
                           foreach (var file in files)
                        {
                            XLWorkbook projects = new XLWorkbook(file);
                         //   foreach (var sheet in projects.Worksheets) { }
                            ViewBag.name = projects.Worksheet(1).Cell("B1").GetFormattedString();
    
                        } 
                    }
                            }

    Friday, January 15, 2021 2:01 PM

Answers

  • User-1330468790 posted

    Hi misfowl,

     

    What do you mean "a project <card>"? Is it a bootstrap card?

    https://getbootstrap.com/docs/4.0/components/card/

    We need to know the question more clear since we would not point you a wrong direction.

     

    Can the data in files be connected? 

    Sure, you could fetch the data and assign them into a collection, e.g. List<T>, and then use for-loop or foreach-loop to populate the data into your <card> one by one.

     

    Is it possible to place them in card by its file?

    From my point of view, it would be better to contruct a class as a data container and populate data in the container from file first.

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2021 3:27 AM

All replies

  • User1059168712 posted

    I need to visualize some data from excel files. I read multiple excels from one folder. All of them are identically structured. Each excel is a project and will be visualized as a card. But the thing is I want to add these excels and its sheets to arrays in order to access them in ViewBag. When I use my code, it shows only the cell from first file. Or would it be a better idea to convert them to JSON and visualize using d3js?

    string filepath = "/Documents/Excels/";
                foreach (string filename in Directory.GetFiles(filepath))
                {  if (Path.GetExtension(filename) != ".xlsx") { }
                        else
                    {
                        var files = new string[] { filename };
                           foreach (var file in files)
                        {
                            XLWorkbook project = new XLWorkbook(file);   
                                int n = project.Worksheets.Count;   
                                for (int i=1; i<=n; i++) {
                                var sheet = project.Worksheet(i);
                                ViewBag.name = sheet.Cell("B1").GetFormattedString();
                                }
                              }                 
                            }
                          }

    Sunday, January 17, 2021 2:50 PM
  • User-1330468790 posted

    Hi misfowl,

     

    You don't need to use foreach-loop for twice. You already got the file collection which could be used for accessing the file one by one.

     

    More details, please refer to below codes. I assume you are using ClosedXML.Excel library hence the demo is constructed with it. 

    Controller:

     public ActionResult DisplayExcelB1()
            {
                string filepath = @"C:\PATH\TO\FILLE\XLSX";
                foreach (string filename in Directory.GetFiles(filepath))
                {
                    if (Path.GetExtension(filename) != ".xlsx") { }
                    else
                    {
                        XLWorkbook projects = new XLWorkbook(filename);
                        ViewBag.name += projects.Worksheet(1).Cell("B1").GetFormattedString() + "<br/>";
                    }
                }
    
                return View();
            }

    View:

    @{
        ViewBag.Title = "DisplayExcelB1";
    }
    
    <h2>DisplayExcelB1</h2>
    
    
    <div>
        <label>Result: </label>
        <br/>
        <label>@Html.Raw(@ViewBag.name)</label>
    </div>

    Data:

    TestExcel.xlsx - B1: 123

    TestExcel1.xlsx - B1: 456

    TestExcel2.xlsx - B1: 789

     

    Demo:

     

    Hope helps.

    Best regards,

    Sean

    Monday, January 18, 2021 10:15 AM
  • User-939850651 posted

    Hi misfowl,

    According to the code you provided, I tested the code, and I found that when after traversing, it will only save the last excel file.

    var files = new string[] { filename };

    It will be reset every time instead of adding new files to it.

    You could try to use List<string> to save filtered files name. Something like:

    var files = new List<string>();
    
    foreach (string filename in Directory.GetFiles(filepath))
                {  
                    if (Path.GetExtension(filename) != ".xlsx") { }
                    else
                    {
                        files.Add(filename);
                        //......
                    }
                }

    Hope this can help.

    Best regards,

    Xudong Peng

    Monday, January 18, 2021 10:37 AM
  • User1059168712 posted

    Thanks you for the great answer.

    Can the data in files be connected? 

    I mean, if I want every excel file to be a project <card> and to place all file's content to these cards. Is it possible to place them in card by its file?

    Monday, January 18, 2021 6:19 PM
  • User-1330468790 posted

    Hi misfowl,

     

    What do you mean "a project <card>"? Is it a bootstrap card?

    https://getbootstrap.com/docs/4.0/components/card/

    We need to know the question more clear since we would not point you a wrong direction.

     

    Can the data in files be connected? 

    Sure, you could fetch the data and assign them into a collection, e.g. List<T>, and then use for-loop or foreach-loop to populate the data into your <card> one by one.

     

    Is it possible to place them in card by its file?

    From my point of view, it would be better to contruct a class as a data container and populate data in the container from file first.

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2021 3:27 AM