使用OpenXML读取Excel文件 [英] Reading Excel files using OpenXML

问题描述

我正在尝试通过OpenXML读取Excel文件，并希望将其输出为CSV.当前，它在单独的行中(由于写行)或在一行中(使用write时)读取每个单元格.像在Excel中那样以表格格式阅读和输出的最佳方法是什么?我可以利用OpenXML中的内置功能吗?

I am trying to read Excel files via OpenXML and looking to output into a CSV. Currently it reads each cell in a separate line (due to writeline) or a single line (when using write). What is the best way to read and outputing in a tabular format like in Excel? Is there a built in feature in OpenXML which I can leverage for this?

static void Main(string[] args)
{
        String xlDocName = @"C:\Users\xlp111\source.xlsx";

        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(xlDocName, false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            string cellValue = string.Empty;
            foreach(WorksheetPart worksheetPart in workbookPart.WorksheetParts)
            {
                OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

                while (reader.Read())
                {
                if (reader.ElementType == typeof(Row))
                {
                    reader.ReadFirstChild();

                    do
                    {
                    if (reader.ElementType == typeof(Cell))
                    {
                        Cell c = (Cell)reader.LoadCurrentElement();

                        if (c.DataType != null && c.DataType == CellValues.SharedString)
                        {
                        SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
                        cellValue = ssi.Text.Text;
                        Console.WriteLine(cellValue);
                        }
                    }
                    }                      
                    while (reader.ReadNextSibling());
                }
                }
            }
            Console.ReadLine();
        }
    }
}

推荐答案

使用Microsoft Office的Open XML SDK

Using Open XML SDK for Microsoft Office

从以下位置安装V2: https: //www.microsoft.com/zh-cn/download/details.aspx?id=5124&wa=wsignin1.0

install V2 from :https://www.microsoft.com/en-eg/download/details.aspx?id=5124&wa=wsignin1.0

(或V2.5)

以下类将Excel工作表转换为带分隔符的CSV文件

The following class convert excel sheet to CSV file with delimeter

 //reference library
 using DocumentFormat.OpenXml.Packaging;
 using DocumentFormat.OpenXml.Spreadsheet;


public class OpenXmlExcel
{
public void ExcelToCsv(string source, string target, string delimiter = ";", bool firstRowIsHeade = true)
{
    var dt = ReadExcelSheet(source, firstRowIsHeade);
    DatatableToCsv(dt, target, delimiter);

}

private void DatatableToCsv(DataTable dt, string fname, string delimiter = ";")
{

    using (StreamWriter writer = new StreamWriter(fname))
    {
        foreach (DataRow row in dt.AsEnumerable())
        {
            writer.WriteLine(string.Join(delimiter, row.ItemArray.Select(x => x.ToString())) + delimiter);
        }
    }

}

List<string> Headers = new List<string>();


private DataTable ReadExcelSheet(string fname, bool firstRowIsHeade)
{

    DataTable dt = new DataTable();
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fname, false))
    {
        //Read the first Sheets 
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();

        foreach (Row row in rows)
        {
            //Read the first row as header
            if (row.RowIndex.Value == 1)
            {
                var j = 1;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    var colunmName = firstRowIsHeade ? GetCellValue(doc, cell) : "Field" + j++;
                    Console.WriteLine(colunmName);
                    Headers.Add(colunmName);
                    dt.Columns.Add(colunmName);
                }
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = GetCellValue(doc, cell);
                    i++;
                }
            }
        }

    }
    return dt;
}

private string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
 }
}

使用方法:

new OpenXmlExcel().ExcelToCsv("f1.xlsx","f1.csv",";",true);
or
//use default: separator=";" ,first row is header
new OpenXmlExcel().ExcelToCsv("f1.xlsx","f1.csv");

这篇关于使用OpenXML读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！