Export worksheet to CSV

The following example shows you how to export the whole workbook to CSV format with a single call. The GetCsv method writes every worksheet, each preceded by its name, and quotes the fields that contain a comma, a quotation mark or a line break, as specified by RFC 4180.

C# example

using System;
using System.IO;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            string csv = book.GetCsv();

            File.WriteAllText("c:\\test\\output.csv", csv);

            Console.WriteLine(csv);
        }
    }
}

VB example

Imports System
Imports System.IO
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim book As New Workbook("c:\test\input.xlsx")

        Dim csv As String = book.GetCsv()

        File.WriteAllText("c:\test\output.csv", csv)

        Console.WriteLine(csv)

    End Sub
End Module

The following example exports a single worksheet by iterating over all populated cells, which is what you need when you want to control the layout of the output yourself.

C# example

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");
            Worksheet ws = (Worksheet)book.Sheets[0];

            IList<Cell> cells = ws.GetCells();
            StringBuilder sb = new StringBuilder();
            int currentRow = 0;

            foreach (Cell cell in cells)
            {
                string r = cell.Reference;
                int i = 0;
                while (i < r.Length && char.IsLetter(r[i])) i++;
                int row = int.Parse(r.Substring(i));

                if (row > currentRow)
                {
                    if (currentRow > 0) sb.AppendLine();
                    currentRow = row;
                }
                else
                {
                    sb.Append(',');
                }

                string val = cell.Value != null ? cell.Value.ToString() : "";
                if (val.Contains(',') || val.Contains('"'))
                    val = "\"" + val.Replace("\"", "\"\"") + "\"";
                sb.Append(val);
            }
            if (currentRow > 0) sb.AppendLine();

            File.WriteAllText("c:\\test\\output.csv", sb.ToString(), Encoding.UTF8);
            Console.WriteLine("Exported " + cells.Count + " cells to CSV.");
        }
    }
}

VB example

Imports System
Imports System.Collections.Generic
Imports System.IO
Imports System.Text
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim book As New Workbook("c:\test\input.xlsx")
        Dim ws As Worksheet = DirectCast(book.Sheets(0), Worksheet)

        Dim cells As IList(Of Cell) = ws.GetCells()
        Dim sb As New StringBuilder()
        Dim currentRow As Integer = 0

        For Each cell As Cell In cells
            Dim r As String = cell.Reference
            Dim i As Integer = 0
            While i < r.Length AndAlso Char.IsLetter(r(i))
                i += 1
            End While
            Dim row As Integer = Integer.Parse(r.Substring(i))

            If row > currentRow Then
                If currentRow > 0 Then sb.AppendLine()
                currentRow = row
            Else
                sb.Append(",")
            End If

            Dim val As String = If(cell.Value IsNot Nothing, cell.Value.ToString(), "")
            If val.Contains(",") OrElse val.Contains("""") Then
                val = """" & val.Replace("""", """""") & """"
            End If
            sb.Append(val)
        Next
        If currentRow > 0 Then sb.AppendLine()

        File.WriteAllText("c:\test\output.csv", sb.ToString(), Encoding.UTF8)
        Console.WriteLine("Exported " & cells.Count & " cells to CSV.")

    End Sub
End Module

Spreadsheet .NET > Tutorial > Export worksheet to CSV

Need help? Ask our developers: