Read multiple excels with the same structure

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();

                    } 
                }
                        }

Answer 1

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();
                            }
                          }                 
                        }
                      }

Answer 2

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

Answer 3

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

Answer 4

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?

Answer 5

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