loop through Data for one or more UserIDs and create new worksheet for each User

Question

Right now my code returns data from the database by userId(s) and specific date ranges. The results are then exported to a Excel spreadsheet. if more than one UserId is searched for then I need to have each UserId have there own worksheet in the workbook. Right now all the results are displayed on "worksheet1". Would I loop through the results and use if statements to check for more than one unique id? and for every id a new worksheet would be created and populated with data? I am very new to programming and any help would be great.

Controller

 [HttpPost]
    public FileResult Export(ReportPhoneSupportVM model)
    {
        ReportPhoneSupportResultTypedView results = new ReportPhoneSupportResultTypedView();
        string[] userIds = model.UserId.Split(',');
        foreach (string userId in userIds)
        {
            int iUserId = 0;
            if (Int32.TryParse(userId, out iUserId))
            {

                RetrievalProcedures.FetchReportPhoneSupportResultTypedView(results, model.FromDate, model.ToDate, iUserId);
            }
        }



var ExcelResults = results;  

    //Create new Excel workbook
    var workbook = new HSSFWorkbook();

    //Create new Excel sheet
    var sheet = workbook.CreateSheet();

    //(Optional) set the width of the columns
    sheet.SetColumnWidth(0, 10 * 256);
    sheet.SetColumnWidth(1, 50 * 256);
    sheet.SetColumnWidth(2, 50 * 256);
    sheet.SetColumnWidth(3, 50 * 256);

    //Create a header row
    var headerRow = sheet.CreateRow(0);

    //Set the column names in the header row
    headerRow.CreateCell(0).SetCellValue("ActivityDate");
    headerRow.CreateCell(1).SetCellValue("Assignment");
    headerRow.CreateCell(2).SetCellValue("Action");
    headerRow.CreateCell(3).SetCellValue("ToFrom");
    headerRow.CreateCell(2).SetCellValue("Result");
    headerRow.CreateCell(3).SetCellValue("Description");

    //(Optional) freeze the header row so it is not scrolled
    sheet.CreateFreezePane(0, 1, 0, 1);

    int rowNumber = 1;

    //Populate the sheet with values from the grid data
    foreach (ReportPhoneSupportResultRow ER in ExcelResults)
    {
        //Create a new row
        var row = sheet.CreateRow(rowNumber++);

        //Set values for the cells
        row.CreateCell(0).SetCellValue(ER.ActivityDate);
        row.CreateCell(1).SetCellValue(ER.Assignment);
        row.CreateCell(2).SetCellValue(ER.Action);
        row.CreateCell(3).SetCellValue(ER.ToFrom);
        row.CreateCell(2).SetCellValue(ER.Result);
        row.CreateCell(3).SetCellValue(ER.Description);
    }

    //Write the workbook to a memory stream
    MemoryStream output = new MemoryStream();
    workbook.Write(output);

    //Return the result to the end user

    return File(output.ToArray(),   //The binary data of the XLS file
        "application/vnd.ms-excel", //MIME type of Excel files
        "GridExcelExport.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user

}

Hamid Reza · Accepted Answer · 2014-06-23T05:52:32.773

Use the following code:

var ExcelResults = results; 
string userID = "";
int rowNumber = 1;

//Create new Excel workbook
var workbook = new HSSFWorkbook();
var? sheet = null;

//Populate the sheet with values from the grid data
foreach (ReportPhoneSupportResultRow ER in ExcelResults)
{
    if (ER.UserID != userID)
    {
        //Create new Excel sheet
        sheet = workbook.CreateSheet();

        //(Optional) set the width of the columns
        sheet.SetColumnWidth(0, 10 * 256);
        sheet.SetColumnWidth(1, 50 * 256);
        sheet.SetColumnWidth(2, 50 * 256);
        sheet.SetColumnWidth(3, 50 * 256);

        //Create a header row
        var headerRow = sheet.CreateRow(0);

        //Set the column names in the header row
        headerRow.CreateCell(0).SetCellValue("ActivityDate");
        headerRow.CreateCell(1).SetCellValue("Assignment");
        headerRow.CreateCell(2).SetCellValue("Action");
        headerRow.CreateCell(3).SetCellValue("ToFrom");
        headerRow.CreateCell(2).SetCellValue("Result");
        headerRow.CreateCell(3).SetCellValue("Description");

        //(Optional) freeze the header row so it is not scrolled
        sheet.CreateFreezePane(0, 1, 0, 1);

        userID = ER.UserID; 
        rowNumber = 1;
    }

    //Create a new row
    var row = sheet.CreateRow(rowNumber++);

    //Set values for the cells
    row.CreateCell(0).SetCellValue(ER.ActivityDate);
    row.CreateCell(1).SetCellValue(ER.Assignment);
    row.CreateCell(2).SetCellValue(ER.Action);
    row.CreateCell(3).SetCellValue(ER.ToFrom);
    row.CreateCell(2).SetCellValue(ER.Result);
    row.CreateCell(3).SetCellValue(ER.Description);
}

//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);

//Return the result to the end user

return File(output.ToArray(),   //The binary data of the XLS file
    "application/vnd.ms-excel", //MIME type of Excel files
    "GridExcelExport.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user

}

I am getting a error message of Cannot assign to an implicitly-typed local variable at var sheet = null; — user3750212, Jun 22 '14 at 14:21
also another problem, the userID is a string, not a int. how would that work? thanks — user3750212, Jun 22 '14 at 14:40
I added the entire controller. I should have done that in the first place — user3750212, Jun 22 '14 at 15:34
I changed my code. So it will cover your problems mentioned above. — Hamid Reza, Jun 23 '14 at 05:54

loop through Data for one or more UserIDs and create new worksheet for each User

1 Answers1