我需要将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;
}
}
