Convert Excel to CSV without Excel installed in ASP.NET

Question

I have the task of creating a class that can take an XLS file and convert it to a CSV file, but there's a catch, I cannot install anything on the server such as Excel or Jet drivers, and it has to be .NET 2.0 for the time being. Given the constraints I thought about doing it with a FileStream and StreamReader.

The code gives me no errors, it uploads the Excel file and even creates a CSV file for it, but when I open the file there's no rows in it (and the Excel file has rows). From what I'm seeing all the data is always on the 2nd worksheet and I'm wondering if thats where my issue lies. Here's the code I have thus far, any help would be greatly appreciated:

''' <summary>
''' Method for converting an XLS or XLSX file to CSV format without requiring any 3rd party
''' installs like Excel or the ACE/JET drivers
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function DoConvert(ByVal delimiter As String, Optional ByVal retainHeaders As Boolean = True) As Boolean
    Try
        Dim stream As New FileStream(String.Format("{0}", ExcelFile.SourceFile), FileMode.Open, FileAccess.Read, FileShare.None)

        Dim table As DataTable = GenerateDataTableFromXls(stream, ",", True)

        If table.Rows.Count > 0 Then
            Using writer = New StreamWriter(ExcelFile.TargetFile)
                For Each row As DataRow In table.Rows
                    Dim first As Boolean = True
                    For Each column As DataColumn In table.Columns
                        If Not first Then
                            writer.Write(",")
                        Else
                            first = False
                        End If
                        Dim data = row(column.ColumnName).ToString().Replace("""", """""")
                        writer.Write(String.Format("""{0}""", data))
                    Next
                    writer.WriteLine()
                Next

            End Using
        Else
            Throw New Exception(Me.ReturnMessage)
        End If
        ' write out CSV data

        Return True
    Catch ex As Exception
        Me.ReturnMessage = ex.Message()
        Return False
    End Try
End Function

''' <summary>
''' Import file with delimited rows, columns to datatable 
''' </summary>
''' <param name="stream">The strem to load</param>
''' <param name="delimiter">string for delimited imported row items</param>
''' <param name="importHeaders">Including columns header with importing , (if true, the first row will be added as DataColumns) , (if false, DataColumns will be numbers)</param>
Private Function GenerateDataTableFromXls(stream As FileStream, delimiter As String, importHeaders As Boolean) As DataTable
    Dim file As New StreamReader(stream, Encoding.Default)
    Dim table As New DataTable
    Try
        If importHeaders Then
            Dim columns As String() = file.ReadLine().Split(delimiter.ToCharArray())
            For Each column As String In columns
                table.Columns.Add(column)
            Next
        Else
            Dim count As Integer = file.ReadLine().Split(delimiter.ToCharArray()).Length()
            For i As Integer = 1 To count
                table.Columns.Add(i.ToString())
            Next
        End If

        While file.Peek() > 0
            table.Rows.Add(file.ReadLine().Split(delimiter.ToCharArray()))
        End While

    Catch ex As Exception
        Me.ReturnMessage = ex.Message()
    Finally
        file.Close()
        file.Dispose()
    End Try

    Return table
End Function

Any ideas?

Answer 1

I work as a social media developer at Aspose and our Aspose.Cells for .NET can be a suitable solution for you. It can easily convert xls, xlsx to CSV without the requirement of Excel or JET installation. Also, you can use it with .NET 2.0. Following is a sample code to convert workbook to a CSV file:

//Load your source workbook
Workbook workbook = new Workbook(@"c:\data\data.xls");

//0-byte array
byte[] workbookData = new byte[0];

//Text save options. You can use any type of separator
TxtSaveOptions opts = new TxtSaveOptions();
opts.Separator = ',';

//Copy each worksheet data in text format inside workbook data array
for (int idx = 0; idx < workbook.Worksheets.Count; idx++)
{
    //Save the active worksheet into text format
    MemoryStream ms = new MemoryStream();
    workbook.Worksheets.ActiveSheetIndex = idx;
    workbook.Save(ms, opts);

    //Save the worksheet data into sheet data array
    ms.Position = 0;
    byte[] sheetData = ms.ToArray();

    //Combine this worksheet data into workbook data array
    byte[] combinedArray = new byte[workbookData.Length + sheetData.Length];
    Array.Copy(workbookData, 0, combinedArray, 0, workbookData.Length);
    Array.Copy(sheetData, 0, combinedArray, workbookData.Length, sheetData.Length);

    workbookData = combinedArray;
}

//Save entire workbook data into file
File.WriteAllBytes("c:\\data\\output.csv", workbookData);

Answer 2

There are a number of open source solutions that can read (and in some cases write) Excel files. My favorite is EPPlus.

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

EPPlus does not have any external dependencies like Excel or JET. It also does not support the legacy .xls format. If you must support legacy files, you may need to look to one of the commercial solutions out there. I'm not aware of a maintained open source solution that handles the older format.