Method to split string and add to different rows in excel sheet

Question

I have a method that creates an excel sheet from a list of strings. The strings are details that are like:

Name: Obi Wan Kenobi

Location: Tattooine

So far, when creating an excel sheet in c#, I'm able to add it to all to individual rows. However, I would like to split each string at the colon and add them to separate columns. I want it to look something like this:

A	B
Name	Obi Wan Kenobi
Location	Tattooine

So far I've tried this:

public static void CreateExcelSheet(List<string> list)
{
    var wb = new XLWorkbook();
    var ws = wb.Worksheets.Add("Data_Test_Worksheet");

    foreach (var item in list)
    {
        for (int i = 0; i < list.Count; i++)
        {
            var detail = item.Split(':');
            ws.Cell(i + 1, 1).InsertData(detail[0]);
            ws.Cell(i + 1, 2).InsertData(detail[1]);
        }              
    }

    wb.SaveAs(@"c:\temp\Data_Text.xlsx");
}

This is how I though the logic would play out but when I execute it, it errors.

Can someone please point me in the right direction? Thanks

You should only need one for loop. You are looping through the 2 items here twice. I would have thought you would get "Location Tattooine" twice by reading the code rather than an error — Kristian Fitzgerald
– Kristian Fitzgerald, Commented Aug 11, 2021 at 17:43
"when I execute it, it errors." It would be helpful if you posted the error message. — littlerufe
– littlerufe, Commented Aug 11, 2021 at 17:53
@KristianFitzgerald Ok I've removed the foreach and left the for loop. It now saves fine but the excel sheet is turning out blank. — h3x3n95
– h3x3n95, Commented Aug 11, 2021 at 17:58
From example code online, it looks like InsertData is for adding a list of data rather than one value. ws.Cell(i + 1, 1).Value = detail[0]; should work. Or the Answer posted below — Kristian Fitzgerald
– Kristian Fitzgerald, Commented Aug 11, 2021 at 18:18

Gilvan Júnior · Accepted Answer · 2021-08-11 18:06:06Z

1

I guess the problem is the methods you are using, its kinda not finding the right place to write your data.

Try adding the var row = ws.Row(i + 1); to get the current row and then just set the values like this row.Cell(1).Value = detail[0];

using ClosedXML.Excel;
using System.Collections.Generic;

namespace ConsoleApp12
{
    class Program
    {
        static void Main(string[] args)
        {
            CreateExcelSheet(new List<string> {
                "s:1",
                "s:2"
            });
        }

        public static void CreateExcelSheet(List<string> list)
        {
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Data_Test_Worksheet");

            for (int i = 0; i < list.Count; i++)
            {
                var detail = list[i].Split(':');
                var row = ws.Row(i + 1);
                row.Cell(1).Value = detail[0];
                row.Cell(2).Value = detail[1];
            }

            wb.SaveAs(@"Data_Text.xlsx");
        }
    }
}

answered Aug 11, 2021 at 18:06

Gilvan Júnior

994 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

h3x3n95 Over a year ago

Ah, that's what I was missing. Thank you!

Collectives™ on Stack Overflow

Method to split string and add to different rows in excel sheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related