Search code examples
c#asp.netasp.net-mvcepplus

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

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 headersenter image description here

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.

Solution

  • 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