Skip to content

Instantly share code, notes, and snippets.

@elcattivo
Last active August 29, 2015 13:56
Show Gist options
  • Save elcattivo/9196787 to your computer and use it in GitHub Desktop.
Save elcattivo/9196787 to your computer and use it in GitHub Desktop.
Converts an arbitrary Excel spreadsheet to a CSV file using MS Office Interop
using System;
using System.IO;
using System.Linq;
using Microsoft.Office.Interop.Excel;
namespace Excel2CSV
{
class Program
{
static void Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("Invalid number of arguments");
Console.WriteLine("Usage: excel2csv.exe source destination [columns]");
Console.WriteLine("Example: excel2csv.exe example.xls output.csv A B F");
return;
}
var source = args[0].Contains(":") ? args[0] : Path.Combine(Directory.GetCurrentDirectory(), args[0]);
var destination = args[1].Contains(":") ? args[1] : Path.Combine(Directory.GetCurrentDirectory(), args[1]);
var columnsToKeep = args.Skip(2).ToList();
var app = new Application { DisplayAlerts = false };
var workbook = app.Workbooks.Open(source);
if (columnsToKeep.Any())
workbook.RemoveAllColumnsExcept(columnsToKeep);
workbook.SaveAs(destination, XlFileFormat.xlCSVWindows);
workbook.Close(false);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Office.Interop.Excel;
namespace Excel2CSV
{
internal static class WorkbookExtensionMethods
{
public static void RemoveAllColumnsExcept(this _Workbook workbook, IEnumerable<string> columnsToKeep)
{
var columnCount = (int)workbook.ActiveSheet.UsedRange.Columns.Count;
var allColumns = Enumerable.Range(1, columnCount).Select(ConvertToLetters);
var columnsToRemove = allColumns.Except(columnsToKeep);
workbook.RemoveColumns(columnsToRemove.ToList());
}
public static void RemoveColumns(this _Workbook workbook, IEnumerable<string> columns)
{
var selection = string.Join(";", columns.Select(c => c + "1"));
workbook.RemoveColumns(selection);
}
public static void RemoveColumns(this _Workbook workbook, string selection)
{
var range = workbook.ActiveSheet.Range[selection];
range.EntireColumn.Delete();
}
private static string ConvertToLetters(int index)
{
const int alphabetSize = 26;
const int start = 'A';
if (index < 1)
return string.Empty;
var offset = (index - 1) % alphabetSize;
return ConvertToLetters(index - alphabetSize - offset) + (char)(start + offset);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment