Last active
August 29, 2015 13:56
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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