CODEWITHSUNDEEP
c#.net.net-4.5
peter
peter

Reputation: 8662

How to return mutiple data from a method in c#

I was trying to retrieve data from excel file and storing in to a datatable.It worked well for file with single sheet .But if an excel file contains multilple sheets i have to retrieve all values from sheets.Main problem now i am facing is some sheets contains same column name with other sheets.if i store it in a single datatable i will get duplicate columnname exception.How to solve this problems.I thought of to return table for each sheet by using tuple and params but no luck any idea to solve this issue.. code for single sheet which was working well is shown below

I created an extension method like below

public static DataTable getDataTableFromExcel(string path)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        DataTable tbl = new DataTable();
        try
        {
            using (var stream = File.OpenRead(path))
            {
                pck.Load(stream);
            }
            var ws = pck.Workbook.Worksheets.First();
            bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
            string ErrorMessage = string.Empty;
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                var row = tbl.NewRow();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
                tbl.Rows.Add(row);
            }
        }
        catch (Exception exp)
        {

        }
        return tbl;
    }
}

for reading all sheets i changed line var ws = pck.Workbook.Worksheets.First(); to var ws = pck.Workbook.Worksheets; and iterated collection but end with duplicate column exception since multiple sheets having same column names.Any code change to share ?

UPDATE

for iterating collection i did like shown below 

public static DataTable Exceltotable(this string path)
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                DataTable tbl = new DataTable();
                try
                {
                    using (var stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        pck.Load(stream);
                    }
                    var wss = pck.Workbook.Worksheets;
                    foreach (var ws in wss)
                    {
                        bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
                        string ErrorMessage = string.Empty;
                        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                        {

                                if ((firstRowCell.Text != "MAXIMO FIELD NAME") && (firstRowCell.Text != "Sr NO") && (firstRowCell.Text != "Sr NO"))
                                {
                                    tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                                }


                        }
                        var startRow = hasHeader ? 2 : 1;
                        for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                        {
                            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                            var row = tbl.NewRow();
                            foreach (var cell in wsRow)
                            {
                                row[cell.Start.Column - 1] = cell.Text;
                            }
                            tbl.Rows.Add(row);
                        }
                        Console.WriteLine();
                    }
                }

UPDATE

calling code is shown below

DataTable dt = Extensionexcel.Exceltotable(@"D:\sample.xlsx");

Upvotes: 0

Views: 154

Answers (3)

Sriram Sakthivel
Sriram Sakthivel

Reputation: 73442

You could return a DataSet containing list of tables instead of DataTable. Each table in your DataSet would represent a sheet in excel.

Your code will be something like this:

public static DataSet Exceltotable(this string path)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
            pck.Load(stream);
        }

        DataSet dataSet = new DataSet();
        var wss = pck.Workbook.Worksheets;
        foreach (var ws in wss)
        {
            DataTable tbl = new DataTable();
            //Populate the datatable here
            ...
           dataSet.Tables.Add(tbl);
        }
        return dataSet;
    }
}

Upvotes: 6

hynsey
hynsey

Reputation: 135

Instead of returning a DataTable, why not update the method to return a DataSet, consisting of a DataTable for each sheet in the Excel document.

Upvotes: 3

Nahum
Nahum

Reputation: 7197

Hi peter Why don't you just return IEnumerable< DataTable > ?

notice that .First is a method you use on a IEnumerable to get a single item. if you stop using first. then you have the whole collection.

Upvotes: 3

Related Questions