Can I save an EXCEL table in CSV format via ClosedXML?

Question

Can I save a worksheet as CSV through ClosedXML?

For instance:

var workbook = new XLWorkbook(fileName); IXLWorksheet worksheet; workbook.Worksheets.TryGetWorksheet(sheetName, out worksheet); worksheet. /How to Save it as CSV? 

Answer 1

No, this is not possible directly in ClosedXML. You must use loops or LINQ to create your own CSV file.

For instance:

 System.IO.File.WriteAllLines(csvFileName, worksheet.RowsUsed().Select(row => string.Join(";", row.Cells(1, row.LastCellUsed(false).Address.ColumnNumber) .Select(cell => cell.GetValue<string>())) )); 

Answer 2

Other answers will not generate a valid CSV if the cells have a separator, so here is the best way

 var lastCellAddress = worksheet.RangeUsed().LastCell().Address; File.WriteAllLines(csvFileName, worksheet.Rows(1, lastCellAddress.RowNumber) .Select(r => string.Join(",", r.Cells(1, lastCellAddress.ColumnNumber) .Select(cell => { var cellValue = cell.GetValue<string>(); return cellValue.Contains(",") ? $"\"{cellValue}\"" : cellValue; })))); 

This is based on @Extragorey's answer

Answer 3

Wrong in

 row.LastCellUsed(false) 

This is not the right format for csv. The last columns will be empty, but will not receive a relative separator.

Answer 4

As @Emanuele points out, @Raidri's answer does not generate the correct CSV format, and also completely eliminates blank lines. To fix this:

 var lastCellAddress = worksheet.RangeUsed().LastCell().Address; System.IO.File.WriteAllLines(csvFileName, worksheet.Rows(1, lastCellAddress.RowNumber) .Select(row => String.Join(",", row.Cells(1, lastCellAddress.ColumnNumber) .Select(cell => cell.GetValue<string>())) ));