Generate unique name for multiple worksheets in a for loop

Question

I am using for loop to generate some worksheets, and I want to give each one a unique name. All I get is Sheet1, Sheet2, Sheet3, and so on.

Below is my code:

var package = new ExcelPackage();

for (var i = 0; i < ds.Tables.Count; i++)
{
    var ws = package.Workbook.Worksheets.Add(String.Format("Sheet{0}", i));

    ws.Cells["A1"].LoadFromDataTable(i == 0 
        ? Transpose(ds.Tables[i].Copy()).DefaultView.ToTable() 
        : ds.Tables[i], true, TableStyles.Medium1);

    ws.Cells[ws.Dimension.Address].AutoFitColumns();
    ws.Cells["A:J"].Style.Numberformat.Format = "#,##0";                          
}

@AndyG That would give me SomethingElse1, SomethingElse2, SomethingElse3.. wouldn't it? I want to give a different name to each sheet. like abc, xyz, pqr..etc — J. Raj, Feb 20 '18 at 14:31
Where do these names come from? Are they, for example, random choices of three letters? You can only write code when you've clearly stated, and understood, the task. (Other options are temp file naming, or GUIDs, but you'll end up with nonsense names, and probably have to remove non-permissible characters.) — Andy G, Feb 20 '18 at 14:37
(And if the actual names are not important, then SomethingElse1, SomethingElse2 are as good as anything (though not as meaningful as MySheet1, MySheet2).) — Andy G, Feb 20 '18 at 14:40
@AndyG So, the sheets have various details, so I want to name the sheets as Summary, EmployeeData, Benefits etc.. so, every sheet has unique data and want to name each sheet accordingly which I think is not possible in this case as I am using for loop with "Sheet{0}" — J. Raj, Feb 20 '18 at 15:05
Do the names you want relate to the table names, or can they be tied in some way to the table? — d219, Feb 20 '18 at 15:15
Why not create an array of the names you want to use, in the order the worksheet creation & access the required name from that array. — PaulF, Feb 20 '18 at 15:34
Please do not append [Solved] to your title. By simply marking an answer with the checkmark shows the community that your question was solved. Thank you. — K.Dᴀᴠɪs, Mar 05 '18 at 21:30

Rufus L · Accepted Answer · 2018-02-20T16:00:58.563

You haven't shown where the names are coming from, but probably the best way would be to use the name from some field in the same data source that you're using to populate the sheet.

Here's one one way to give them each unique names would be to have them stored in a list, which you can access using the same index that you're using currently. Of course you have to somehow ensure that the names are in the correct order in your list:

var sheetNames = new List<string> { "Summary", "EmployeeData", "Benefits" };

for (var i = 0; i < ds.Tables.Count; i++)
{
    // Choose a name from the list or use 'Sheet1, 2, 3' if we don't have enough names
    var sheetName = i < sheetNames.Count 
        ? sheetNames[i] 
        : String.Format("Sheet{0}", sheetNames.Count - i);

    var ws = package.Workbook.Worksheets.Add(sheetName);

score 0 · Answer 2 · answered Feb 20 '18 at 14:35

0

In this line of code here:

var ws = package.Workbook.Worksheets.Add(String.Format("Sheet{0}", i));

You're setting the worksheet name, that's why you end up with 'Sheet1', 'Sheet2', etc. Changing that to something else, you get your worksheets named differently.

Now, I'm not sure, if your problem is where you can change the name or how you can do it, to make it unique. Depending on what you're aiming for, you could use Guid's.

answered Feb 20 '18 at 14:35

Ann

21
1
5

I agree that the above line of code is generating sheet1, sheet2..etc. But I want to give every sheet a different name like Summary, EmployeeData, Benefits etc.. which I am not sure, how to achieve with sheets being generated by for loop. The only option I can think of is hard code, adding every sheet. – J. Raj Feb 20 '18 at 15:09
@J.Raj Sorry, I didn't see the comments when I posted my answer. Do you know specifically which sheet should be Summary, EmpleyeeData and so now? Like, 1st is Summary, 2nd is Benefits, for instance... – Ann Feb 20 '18 at 15:14
Yes, I know the order in which they should be named. – J. Raj Feb 20 '18 at 15:18
@J.Raj Not the best approach, maybe someone can provide something better, but the first thing that came to mind, would be a predefined dictionary (or something else of that sort) that you can lookup inside the for loop. You can check for the key (1, 2, 3, etc, stored in 'i') and get its value (string - would contain 'Summary', etc). It would be hardcoded, tho. To avoid it, you could have it stored somewhere else. Idk your environment, but either database, or registry level. Hope this is enough for you to go on. – Ann Feb 20 '18 at 15:24
@Ann: not sure if it is better, but simpler would be an array of strings. – PaulF Feb 20 '18 at 15:36
@PaulF yeah, following the same train of thought, there are a few ways it can be achieved. I'ts a matter of (OP's) preference, I think. – Ann Feb 20 '18 at 15:42

Generate unique name for multiple worksheets in a for loop

2 Answers2