我需要将Excel(2010)文件转换为csv.目前我正在使用Excel Interop打开和SaveAs csv,这很好用.然而,Interop在我们使用它的environemt中存在一些问题,所以我正在寻找另一种解决方案.
我发现没有互操作的Excel文件的使用方法是使用OpenXML SDK.我一起编写了一些代码来遍历每个工作表中的所有单元格,然后将它们简单地写入CSV中的另一个文件.
我遇到的一个问题是处理空行和单元格.看来,使用此代码,空白行和单元格完全不存在,因此我无法了解它们.有没有通过所有行和单元格,包括空白?
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(',')); } } } }
如果我有以下Excel文件数据:
one,two,three,last,row
我将得到以下CSV(这是错误的):
one,three last,row
解决方法
//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; } }