Can I save an EXCEL worksheet as CSV via ClosedXML?

Question

Is it possible to save a worksheet of a workbook as CSV via ClosedXML?

For example:

var workbook = new XLWorkbook(fileName);
IXLWorksheet worksheet;
workbook.Worksheets.TryGetWorksheet(sheetName, out worksheet);

How to Save it as CSV?

score 22 · Answer 1 · answered May 23 '17 at 18:32

Other answers will fail to generate a valid CSV if cells have the separator so here is a better 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

Bear in mind Excel cells can also contain the newline/return character, in which case they would also need quotes. There may be a couple of other trigger characters I'm not thinking of. — Hambone, Feb 22 '23 at 21:16

score 8 · Accepted Answer · answered Nov 24 '14 at 12:26

8

No, it is not possible directly in ClosedXML. You have to use loops or LINQ to build your own CSV file.

For example:

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

answered Nov 24 '14 at 12:26

Raidri

17,258
9
62
65

All columns are write as single column. – Andi AR Jul 04 '17 at 04:59
Did you change the ";" to a "," It works for me when you do. – Jon Oct 30 '18 at 01:06

score 1 · Answer 3 · answered Mar 06 '17 at 00:17

As @Emanuele points out, @Raidri's answer won't generate a proper CSV format, and it also omits blank rows entirely. 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>()))
));

score 0 · Answer 4 · edited Nov 03 '15 at 10:46

0

Wrong in

row.LastCellUsed(false)

it's not a correct format for csv. Last columns will be empty instead of get relative separator.

edited Nov 03 '15 at 10:46

Andrei

42,814
35
154
218

answered Jul 02 '15 at 07:21

Emanuele

648
12
33

Can I save an EXCEL worksheet as CSV via ClosedXML?

4 Answers4

Linked