Using EPPlus Library to Convert XLSX to CSV
The EPPlus library for Excel data is great, but it has no built-in functionality for converting .xlsx to .csv.
Here is a sample (posted here because I discovered a pitfall, and it's not a slam dunk) for doing just that.
using System;
using System.Collections.Generic;
using System.Linq;
using OfficeOpenXml;
using System.IO;
namespace KillMeConsole
{
public static class ListExtension
{
public static string ToDelimitedString( this List<string> list, string separator = ":", bool insertSpaces = false )
{
var result = string.Empty;
for ( int i = 0; i < list.Count; i++ )
{
var currentString = list[i];
if ( i < list.Count - 1 )
{
currentString += separator;
if ( insertSpaces )
{
currentString += ' ';
}
}
result += currentString;
}
return result;
}
}
class Program
{
static void Main( string[] args )
{
var inputFile = new FileInfo( @"C:\temp\killme\Book1.xlsx" );
var outputFile = @"C:\temp\killme\Book1.csv";
using ( var doc = new ExcelPackage( inputFile ) )
{
var workbook = doc.Workbook;
if ( workbook != null )
{
if ( workbook.Worksheets.Count > 0 )
{
var worksheet = workbook.Worksheets[1];
int maxColumnNumber = worksheet.Dimension.End.Column;
var convertedRecords = new List<List<string>>( worksheet.Dimension.End.Row );
var excelRows = worksheet.Cells.GroupBy( c => c.Start.Row ).ToList();
excelRows.ForEach( r =>
{
var currentRecord = new List<string>( maxColumnNumber );
var cells = r.OrderBy( cell => cell.Start.Column ).ToList();
for ( int i = 1; i <= maxColumnNumber; i++ )
{
var currentCell = cells.Where( c => c.Start.Column == i ).FirstOrDefault();
if ( currentCell == null )
{
/// Add a cell value for empty cells to keep data aligned.
AddCellValue( string.Empty, currentRecord );
}
else
{
/// Can't use .Text: http://epplus.codeplex.com/discussions/349696
AddCellValue( currentCell.Value == null ? string.Empty : currentCell.Value.ToString(), currentRecord );
}
}
convertedRecords.Add( currentRecord );
} );
WriteToFile( convertedRecords, outputFile );
}
}
}
Console.WriteLine( "chekkit: {0}", outputFile );
Console.ReadKey();
}
private static void AddCellValue( string s, List<string> record )
{
record.Add( string.Format( "{0}{1}{0}", '"', s ) );
}
/// <summary>
/// Assumes file isn't massive
/// </summary>
private static void WriteToFile( List<List<string>> records, string path )
{
var commaDelimited = new List<string>( records.Count );
records.ForEach( r => commaDelimited.Add( r.ToDelimitedString( "," ) ) );
File.WriteAllLines( path, commaDelimited );
}
}
}
using System.Collections.Generic;
using System.Linq;
using OfficeOpenXml;
using System.IO;
namespace KillMeConsole
{
public static class ListExtension
{
public static string ToDelimitedString( this List<string> list, string separator = ":", bool insertSpaces = false )
{
var result = string.Empty;
for ( int i = 0; i < list.Count; i++ )
{
var currentString = list[i];
if ( i < list.Count - 1 )
{
currentString += separator;
if ( insertSpaces )
{
currentString += ' ';
}
}
result += currentString;
}
return result;
}
}
class Program
{
static void Main( string[] args )
{
var inputFile = new FileInfo( @"C:\temp\killme\Book1.xlsx" );
var outputFile = @"C:\temp\killme\Book1.csv";
using ( var doc = new ExcelPackage( inputFile ) )
{
var workbook = doc.Workbook;
if ( workbook != null )
{
if ( workbook.Worksheets.Count > 0 )
{
var worksheet = workbook.Worksheets[1];
int maxColumnNumber = worksheet.Dimension.End.Column;
var convertedRecords = new List<List<string>>( worksheet.Dimension.End.Row );
var excelRows = worksheet.Cells.GroupBy( c => c.Start.Row ).ToList();
excelRows.ForEach( r =>
{
var currentRecord = new List<string>( maxColumnNumber );
var cells = r.OrderBy( cell => cell.Start.Column ).ToList();
for ( int i = 1; i <= maxColumnNumber; i++ )
{
var currentCell = cells.Where( c => c.Start.Column == i ).FirstOrDefault();
if ( currentCell == null )
{
/// Add a cell value for empty cells to keep data aligned.
AddCellValue( string.Empty, currentRecord );
}
else
{
/// Can't use .Text: http://epplus.codeplex.com/discussions/349696
AddCellValue( currentCell.Value == null ? string.Empty : currentCell.Value.ToString(), currentRecord );
}
}
convertedRecords.Add( currentRecord );
} );
WriteToFile( convertedRecords, outputFile );
}
}
}
Console.WriteLine( "chekkit: {0}", outputFile );
Console.ReadKey();
}
private static void AddCellValue( string s, List<string> record )
{
record.Add( string.Format( "{0}{1}{0}", '"', s ) );
}
/// <summary>
/// Assumes file isn't massive
/// </summary>
private static void WriteToFile( List<List<string>> records, string path )
{
var commaDelimited = new List<string>( records.Count );
records.ForEach( r => commaDelimited.Add( r.ToDelimitedString( "," ) ) );
File.WriteAllLines( path, commaDelimited );
}
}
}
posted by Brad @ 9:27 AM
0 Comments
