从Excel数据表来在C＃中使用的Open XML [英] From Excel to DataTable in C# with Open XML

问题描述

我使用Visual Studio 2008和我需要使用Open XML SDK 2.0中的Excel工作表创建一个数据表。我需要用片材的第一行数据表列创建它，并与其余值完成它。

I'm using Visual Studio 2008 and I need create a DataTable from a Excel Sheet using the Open XML SDK 2.0. I need to create it with the DataTable columns with the first row of the sheet and complete it with the rest of values.

有没有人有一个示例代码或链接可以帮我做到这一点？

Does anyone have a example code or a link that can help me to do this?

推荐答案

我想这应该做你在问什么。另一个功能是那里只是为了对付如果你有共同的字符串，我以为你在列标题做。不知道这是完美的，但我希望它能帮助。

I think this should do what you're asking. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. Not sure this is perfect, but I hope it helps.

static void Main(string[] args)
{
    DataTable dt = new DataTable();

    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"..\..\example.xlsx", false))
    {

        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();

        foreach (Cell cell in rows.ElementAt(0))
        {
            dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }

        foreach (Row row in rows) //this will also include your header row...
        {
            DataRow tempRow = dt.NewRow();

            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
            }

            dt.Rows.Add(tempRow);
        }

    }
    dt.Rows.RemoveAt(0); //...so i'm taking it out here.

}


public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.CellValue.InnerXml;

    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

这篇关于从Excel数据表来在C＃中使用的Open XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！