How to convert Excel to CSV using the OpenXML SDK?

Question

I have a requirement to convert Excel (2010) files to csv. I am currently using Excel Interop to open and SaveAs csv, which works well. However, Interop has some problems in the environment where we use it, so I'm looking for another solution.

I found a way to work with Excel files without interaction, this is to use the OpenXML SDK. I got the code together to repeat all the cells on each sheet and just writes them to another file in CSV.

One of the problems that I have encountered is handling empty rows and cells. It seems that with this code the empty rows and cells completely do not exist, so I can not find out about them. Is it possible to go through all rows and cells, including spaces?

string filename = @"D:\test.xlsx"; string outputDir = Path.GetDirectoryName(filename); //-------------------------------------------------------- using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false)) { foreach (Sheet sheet in document.WorkbookPart.Workbook.Descendants<Sheet>()) { WorksheetPart worksheetPart = (WorksheetPart) document.WorkbookPart.GetPartById(sheet.Id); Worksheet worksheet = worksheetPart.Worksheet; SharedStringTablePart shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First(); SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray(); // Create a new filename and save this file out. if (string.IsNullOrWhiteSpace(outputDir)) outputDir = Path.GetDirectoryName(filename); string newFilename = string.Format("{0}_{1}.csv", Path.GetFileNameWithoutExtension(filename), sheet.Name); newFilename = Path.Combine(outputDir, newFilename); using (var outputFile = File.CreateText(newFilename)) { foreach (var row in worksheet.Descendants<Row>()) { StringBuilder sb = new StringBuilder(); foreach (Cell cell in row) { string value = string.Empty; if (cell.CellValue != null) { // If the content of the first cell is stored as a shared string, get the text // from the SharedStringTablePart. Otherwise, use the string value of the cell. if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) value = items[int.Parse(cell.CellValue.Text)].InnerText; else value = cell.CellValue.Text; } // to be safe, always use double quotes. sb.Append(string.Format("\"{0}\",", value.Trim())); } outputFile.WriteLine(sb.ToString().TrimEnd(',')); } } } } 

If I have the following excel file data:

 one,two,three ,, last,,row 

I will get the following CSV (which is wrong):

 one,two,three last,row 

Answer 1

I do not think OpenXml is the right tool for this problem. I would recommend getting data from a sheet with OleDbConnection , and then to a csv file with this method.

Once you get the data in the data format in memory, you have much more control over the situation.

Answer 2

you can use oledb connection and request excel file, convert strings to csv format and save the results in file

here is a simple example that i tested for this it creates another csv unicode file, encoded, tab limited for each sheet in excel file

 using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Text; namespace XlsTests { class Program { static void Main(string[] args) { string _XlsConnectionStringFormat = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\""; string xlsFilename = @"C:\test.xlsx"; using (OleDbConnection conn = new OleDbConnection(string.Format(_XlsConnectionStringFormat, xlsFilename))) { try { conn.Open(); string outputFilenameHeade = Path.GetFileNameWithoutExtension(xlsFilename); string dir = Path.GetDirectoryName(xlsFilename); string[] sheetNames = conn.GetSchema("Tables") .AsEnumerable() .Select(a => a["TABLE_NAME"].ToString()) .ToArray(); foreach (string sheetName in sheetNames) { string outputFilename = Path.Combine(dir, string.Format("{0}_{1}.csv", outputFilenameHeade, sheetName)); using (StreamWriter sw = new StreamWriter(File.Create(outputFilename), Encoding.Unicode)) { using (DataSet ds = new DataSet()) { using (OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), conn)) { adapter.Fill(ds); foreach (DataRow dr in ds.Tables[0].Rows) { string[] cells = dr.ItemArray.Select(a => a.ToString()).ToArray(); sw.WriteLine("\"{0}\"", string.Join("\"\t\"", cells)); } } } } } } catch (Exception exp) { // handle exception } finally { if (conn.State != ConnectionState.Open) { try { conn.Close(); } catch (Exception ex) { // handle exception } } } } } } } 

Answer 3

 //Xlsx to Csv ConvertXlsxToCsv(@"D:\test.xlsx", @"C:\"); internal static void ConvertXlsxToCsv(string SourceXlsxName, string DestinationCsvDirectory) { try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(SourceXlsxName, false)) { foreach (Sheet _Sheet in document.WorkbookPart.Workbook.Descendants<Sheet>()) { WorksheetPart _WorksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(_Sheet.Id); Worksheet _Worksheet = _WorksheetPart.Worksheet; SharedStringTablePart _SharedStringTablePart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First(); SharedStringItem[] _SharedStringItem = _SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray(); if (string.IsNullOrEmpty(DestinationCsvDirectory)) DestinationCsvDirectory = Path.GetDirectoryName(SourceXlsxName); string newFilename = string.Format("{0}_{1}.csv", Path.GetFileNameWithoutExtension(SourceXlsxName), _Sheet.Name); newFilename = Path.Combine(DestinationCsvDirectory, newFilename); using (var outputFile = File.CreateText(newFilename)) { foreach (var row in _Worksheet.Descendants<Row>()) { StringBuilder _StringBuilder = new StringBuilder(); foreach (Cell _Cell in row) { string Value = string.Empty; if (_Cell.CellValue != null) { if (_Cell.DataType != null && _Cell.DataType.Value == CellValues.SharedString) Value = _SharedStringItem[int.Parse(_Cell.CellValue.Text)].InnerText; else Value = _Cell.CellValue.Text; } _StringBuilder.Append(string.Format("{0},", Value.Trim())); } outputFile.WriteLine(_StringBuilder.ToString().TrimEnd(',')); } } } } } catch (Exception Ex) { throw Ex; } }