如何使用OpenXML SDK将Excel转换为CSV？ [英] How do you convert Excel to CSV using OpenXML SDK?

问题描述

我要求将Excel(2010)文件转换为CSV。目前我正在使用Excel Interop打开并另存为CSV，它工作得很好。但是，Interop在我们使用它的环境中有一些问题，所以我正在寻找另一个解决方案。

我发现在没有互操作的情况下处理Excel文件的方法是使用OpenXML SDK。我收集了一些代码来遍历每个工作表中的所有单元格，并简单地将它们写入CSV中的另一个文件。

我遇到的一个问题是处理空白行和单元格。看起来，在这个代码中，空白行和单元格是完全不存在的，所以我无法了解它们。是否可以遍历所有行和单元格，包括空格？

string filename = @"D:	est.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,two,three
last,row

推荐答案

//Xlsx to Csv
ConvertXlsxToCsv(@"D:	est.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;
    }
}

这篇关于如何使用OpenXML SDK将Excel转换为CSV？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！