How to convert csv string data into excel sheet using C#-CodePudding

I have a string which contains the data in the form of csv. Now, I want to convert it to excel file. How could i do that ? Can anyone help me.

String csvItem = "";

var sb = new StringBuilder();

//logic that appending the data

csvItem = sb.toString();

Now data is like , example

Employee, Id

Varsha,12

John,13

CodePudding user response：

First of all, Excel can open CSV files natively so you really don't have to convert the file to Excel. But if you really need to, what I use to create Excel Sheets is EP Plus. There's no need to build the StringBuilder string if you're going to do this. If you already have it in one string, you'll have to first split by line breaks (possibly using csvItem.Split(Environment.NewLine)). I don't know how you're building sb so I can't really help with that.

using (var package = new ExcelPackage())
{
    var worksheet = package.Workbook.Worksheets.Add("Sheet 1");

    int rowCount = 1; // cols and rows in EP plus start at one not zero
    foreach (string line in XXX) { // foreach loop that goes through every line in your CSV data and sets each line to string "line"
        int colCount = 1;
        string[] lineData = line.Split(','); // split your line by comma
        foreach (string colValue in lineData) {
            worksheet.Cells[rowCount,colCount  ].Value = colValue;
        }
        
        rowCount  ;
    }

    // when you're done save your file
    var fi = new FileInfo(@"filename.xlsx");
    package.SaveAs(fi);
}

CodePudding user response：

This example use Epplus. (You don't need to create a string with StringBuilder for a CSV anyway, you could directly write to a CSV.)

void Main()
{
  var Employee = new List<Employee> {
   new Employee { Id=1, Name="Ali", Surname="Veli"},
   new Employee { Id=2, Name="Ahmet", Surname="Sari"},
   new Employee { Id=3, Name="Mehmet", Surname="Lacivert"},
   new Employee { Id=4, Name="Huseyin", Surname="En"},
   new Employee { Id=5, Name="Veli", Surname="Buyuk"},
   new Employee { Id=6, Name="Zeki", Surname="Fener"},
  };


  ExcelPackage pck = new ExcelPackage();

  var wsEnum = pck.Workbook.Worksheets.Add("Employees");
  wsEnum.Cells["A1"].LoadFromCollection(Employee, true, TableStyles.Medium9);
  wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();

  var fi = new FileInfo(@"c:\temp\ExcelEmployee.xlsx");
  if (fi.Exists)
  {
    fi.Delete();
  }
  pck.SaveAs(fi);
}

public class Employee
{ 
  public int Id { get; set; }
  public string Name { get; set; }
  public string Surname { get; set; }
}