C# Convert csv to excel 2007

Question

I need to export the data of a report that can escalate to 300.000 rows to excel.

For a high speed requirement, I used this library to create a csv file. The only problem I am having now, is that the column width is not adapted to the content of the widest cell, it gets cutted until I manually widen the column.

I thought that maybe an option was to convert my csv file to an excel file, but I couldn't find a solution anywere!

One option was using OpenXML, but at least what I know, is that it doesn't work for Excel 2007.

Another option was to work with Interop, but it also has its problems.. when I try to create the Application object, it throws an exception:

"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied." (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

And the solutions I found can't be implemented on the client I am working on, for security reasons.

I am running out of options..

Thank you for your help!!!

Answer 1

thanks to the suggestion of @mason I ended up using EPPlus.

Thank you!

Answer 2

You can use Microsoft.Office.Interop.Excel:

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            // input and output files
            string csv = @"c:\data\input.csv";
            string xls = @"c:\data\output.xlsx";

            // init the Appl obj
            Excel.Application xl = new Excel.Application();

            // get the worksheet
            Excel.Workbook wb = xl.Workbooks.Open(csv);
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);

            // select the used range
            Excel.Range used = ws.UsedRange;

            // autofit the columns
            used.EntireColumn.AutoFit();

            // save as xlsx
            wb.SaveAs(xls, 51);

            wb.Close();
            xl.Quit();
        }
    }
}

Alternatively, you can access the same methods from powershell

autofit.ps1

# define input and output files
$csv = Join-Path (Get-Location) "input.csv"
$xls = Join-Path (Get-Location) "myoutput.xlsx"

# initialize the COM object
$xl = New-Object -COM "Excel.Application"
$xl.Visible = $false

# open the CSV file
$wb = $xl.workbooks.open($csv)
$ws = $wb.Worksheets.Item(1)

# tell Excel to autofit the columns to the data
$range = $ws.UsedRange
[void] $range.EntireColumn.Autofit()

# save to xlsx format and close
$wb.SaveAs($xls, 51)
$wb.close()