通过Open Xml链接特定的excel列

Question

使用Microsoft.Office.Interop.Excel.dll，我能够通过使用下面的代码从excel表中获得特定的行和特定的列数据

Excel.Workbook MyWorkBook = Excel_App.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet MyWorksheet = null;
MyWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Sheets[(1)];
Excel.Range Excel_Range;
Excel_Range = MyWorksheet.UsedRange;
SheetCount = MyWorkBook.Sheets.Count;
Lastrow = MyWorksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
LastColumn = MyWorksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
for (int i = 8; i <= Lastrow; i++)
{

        List_MAPPING_FILE_A429_PATHS.Add((string)(Excel_Range.Cells[i, 4] as Excel.Range).Value2.ToString());
        List_MAPPING_FILE_ASCB_PATHS.Add((string)(Excel_Range.Cells[i, 5] as Excel.Range).Value2.ToString());
}

现在我想通过使用OpenXml.dll获得相同的数据存储在列表中，我尝试下面的代码，但卡住了如何继续

public void AddtoLogFile( )
{
    string temp =@"C:PortedDATAEJETE2_A429RX_TIF_temp.xml";
    using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(temp, true))
    {
        WorkbookPart workbookPart = myDoc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData =
        worksheetPart.Worksheet.Elements<SheetData>().First();
        foreach (Row r in sheetData.Elements<Row>())
        {
            foreach (Cell c in r.Elements<Cell>())
            {
                string text = c.CellValue.Text;
            }
        }
    }
}

有人能帮我一下吗

Answer 1

单元格位置存储在单元格中。CellReference,

第一个单元格的单元格引用将是"A1"

使用此方法提取列:

private static readonly Regex ColumnNameRegex = new Regex("[A-Za-z]+");  
private static string GetColumnName(string cellReference)
    {
        if (ColumnNameRegex.IsMatch(cellReference))
            return ColumnNameRegex.Match(cellReference).Value;
        throw new ArgumentOutOfRangeException(cellReference);
    }

不确定你想从电子表格中得到什么，我猜你只想从特定行的单元格中获得信息:

 foreach (Row r in sheetData.Elements<Row>())
    {
        foreach (Cell c in r.Elements<Cell>())
        {
            if (GetColumnName(c.CellReference) == "A")
            {
            string text = c.CellValue.InnerText;
            }
        }
    }

cellvalue给出的数据。InnerText将是对sharedstringtable的引用，sharedstringtable保存了工作表的所有字符串。您需要从存储为

的SharedStringTable中获取数据。

WorkBookPart.SharedStringTablePart.SharedStringTable;

为此，我使用一个方法，该方法接受Cell和SharedStringTable返回值:

        public static string GetCellV (Cell cell, SharedStringTable ss)
    {
        string cellV = null;
        try
        {
            cellV = cell.CellValue.InnerText;
            if (cell.DataType != null
              && cell.DataType.Value == CellValues.SharedString)
            {
                cellV = ss.ElementAt(Int32.Parse(cellV)).InnerText;
            }
            else
            {
                cellV = cell.CellValue.InnerText;
            }
        }
        catch (Exception)
        {
            cellV = " ";
        }
        return cellV;
    }