Use Placeholders to generate string from uploaded excel File in asp.net mvc using EPPlus Library

Question

I'm writing an application where users can upload an excel file with contact information which the number of columns is unknown to me. A sheet could have as much column and row as possible. What i'm trying to achieve is that users can send message to each contact in the sheet by building the messages using a zero based index as placeholders for the columns.Take for example in the sheet below assuming that the first row represents the headers

A user will build his message like this Dear {0} {1}, your age is {2} and your email is {3} where {0} is the title of the first column, {1} is the title of the second column,on and on like that. This should output the message as Dear Jon Snow, your age is 27 and your email is jsnow@winterfell.com. I want to do this for every row in the sheet. I'm using EPplus to parse the excel sheet.This is what I've tried.

static void Main(string[] args)
{
    var path = "C:\\Users\\RIDWAN\\Desktop\\ExcelFile.xlsx";
    FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);            
    using (var package = new ExcelPackage(fs))
    {
        var currentSheet = package.Workbook.Worksheets;//get the work sheet
        var workSheet = currentSheet.First();//get the first sheet
        var noOfCol = workSheet.Dimension.End.Column; //get the no of col in the active sheet
        var noOfRow = workSheet.Dimension.End.Row; /get the no of row in the active sheet
        var placeholder = "{FirstName}";
        for (int i = 2; i <= noOfRow; i++)
        {
            for (int j = 1; j <= noOfCol; j++)
            {
                var message = String.Format("Dear {0} {1}, your age is {2} and your email is {3}", workSheet.GetValue(i, j));
                Console.Write(message);
            }
        }
        Console.ReadLine();
    }

}

I would like to output a well formatted message using the placeholders to generate the message with their corresponding placeholder values.

A suggestion to make your life easier - use named ranges in your spreadsheet. It will be easier than hard coding row and column numbers. It will allow you to modify the spreadsheet and move ranges around without having to go back and change the numbers in your code. [http://stackoverflow.com/questions/36956802/how-to-read-excel-file-in-asp-net](http://stackoverflow.com/questions/36956802/how-to-read-excel-file-in-asp-net) — Scott Hannen, Jul 18 '16 at 02:28
I'm concerned as to how this will help me achieve my goal since I'm not the one presenting a template to them they will be uploading a file with an unknown number of columns or rows using any column title they like. — ibnhamza, Jul 18 '16 at 11:18
In that case my suggestion won't be much help. But how will you know which values to insert into which columns? — Scott Hannen, Jul 18 '16 at 11:26
With reference to my question. All they need do is compose a message by making reference to their column name using a zero based index as placeholders and i'll enforce that the first row must be column names and as such i will loop through the sheet starting from the second row to the last available row and the first column to the last available column. See the accepted answer. — ibnhamza, Jul 18 '16 at 11:29

score 1 · Accepted Answer · answered Jul 17 '16 at 23:55

Seems like you only need one for loop since the number of cols is known - its the rows that is the variant here. Simply do something like this:

using (var package = new ExcelPackage(fs))
{
    var currentSheet = package.Workbook.Worksheets;//get the work sheet
    var workSheet = currentSheet.First();//get the first sheet
    var noOfCol = workSheet.Dimension.End.Column; //get the no of col in the active sheet
    var noOfRow = workSheet.Dimension.End.Row; // get the no of row in the active sheet
    var placeholder = "{FirstName}";
    for (int i = 2; i <= noOfRow; i++)
    {
        var vals = workSheet.Cells[i, 1, i, noOfCol].Select(c => c.Value).ToArray();
        var message = String.Format("Dear {0} {1}, your age is {2} and your email is {3}", vals);
        Console.WriteLine(message);
    }
}

gives this int the output:

Dear Jon Snow, your age is 27 and your email is jsnow@winderfell.com
Dear Arya Stark, your age is 18 and your email is aryastark@winterfell.com
Dear Eddard Stark, your age is 50 and your email is lordeddard@winterfell.com

Dear Ernie I appreciate the answet. However, thr number of columns is uknown and thats why i used the two "for loops". How can i incorporate that into the answer. — ibnhamza, Jul 18 '16 at 06:12
@ibnhamza Your code already handle that part actually with the line `var noOfCol = workSheet.Dimension.End.Column;`. Epplus will determine how many columns for you based on which columns in the sheet have actual content (as opposed to being empty). — Ernie S, Jul 18 '16 at 10:47

Use Placeholders to generate string from uploaded excel File in asp.net mvc using EPPlus Library

1 Answers1