none
To get cell value RRS feed

  • Question

  • Hi,
    By these
                                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                                    WorkbookStylesPart wstylePart = workbookPart.WorkbookStylesPart;
                                    Stylesheet ss = wstylePart.Stylesheet;
    
                                    foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
                                    {
                                        using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                        {
                                            sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.CellValue);
                                        }
                                        ...



    I get
    Cell reference is A2272; Cell value is DocumentFormat.OpenXml.Spreadsheet.CellValue



    How to get the real value of the cell?

    Many Thanks & Best Regards, Hua Min

    Wednesday, September 28, 2016 3:17 AM

Answers

  • Hi,

    Try the following:

        if (cell != null)
                                    {
                                        Console.WriteLine(cell.InnerText);
                                        if (cell.DataType != null)
                                        {
                                            switch (cell.DataType.Value)
                                            {
                                                case CellValues.SharedString:
     
                                                    var stringTable =
                                                        workbookPart.GetPartsOfType<SharedStringTablePart>()
                                                        .FirstOrDefault();
     
                                                    if (stringTable != null)
                                                    {
                                                        Console.WriteLine(stringTable.SharedStringTable
                                                            .ElementAt(int.Parse(cell.InnerText)).InnerText);
     
                                                    }
                                                    break;
                                            }
                                        }


    • Marked as answer by Jackson_1990 Wednesday, September 28, 2016 9:35 AM
    • Unmarked as answer by Jackson_1990 Wednesday, September 28, 2016 10:01 AM
    • Marked as answer by Jackson_1990 Thursday, September 29, 2016 1:53 AM
    Wednesday, September 28, 2016 7:34 AM
    Moderator

All replies

  • Hi,

    Use OpenXmlElement.InnerText property

     sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.CellValue.InnerText);

     

    Besides, for more information, you could visit How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)

    • Marked as answer by Jackson_1990 Wednesday, September 28, 2016 4:22 AM
    • Unmarked as answer by Jackson_1990 Wednesday, September 28, 2016 6:24 AM
    Wednesday, September 28, 2016 4:10 AM
    Moderator
  • Sorry, to 2 cells separately having values like

    2016/01
    Network & Communication, Mobile, HA-NC-MOB-18a



    but I've got Cell InnerText like

    19885
    19990



    while here is the line

    sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.InnerText);


    Why? How to get exactly the same value which I can see on Excel file?

    If I've changed the line to be

    sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.CellValue.InnerText);




    I then get this exception

    System.NullReferenceException was unhandled
      HResult=-2147467261
      Message=Object reference not set to an instance of an object.
      Source=WindowsFormsApplication2
      StackTrace:
           at WindowsFormsApplication2.Form1.menuItem5_Click(Object sender, EventArgs e) in c:\dp13\General Application\WindowsFormsApplication9_3_6_7\WindowsFormsApplication2\Form1.cs:line 132
           at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
           at System.Windows.Forms.MenuItem.MenuItemData.Execute()
           at System.Windows.Forms.Command.Invoke()
           at System.Windows.Forms.Command.DispatchID(Int32 id)
           at System.Windows.Forms.Control.WmCommand(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at WindowsFormsApplication2.Program.Main() in c:\dp13\General Application\WindowsFormsApplication9_3_6_7\WindowsFormsApplication2\Program.cs:line 19
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 






    Many Thanks & Best Regards, Hua Min


    • Edited by Jackson_1990 Wednesday, September 28, 2016 7:21 AM
    Wednesday, September 28, 2016 6:59 AM
  • Hi,

    Try the following:

        if (cell != null)
                                    {
                                        Console.WriteLine(cell.InnerText);
                                        if (cell.DataType != null)
                                        {
                                            switch (cell.DataType.Value)
                                            {
                                                case CellValues.SharedString:
     
                                                    var stringTable =
                                                        workbookPart.GetPartsOfType<SharedStringTablePart>()
                                                        .FirstOrDefault();
     
                                                    if (stringTable != null)
                                                    {
                                                        Console.WriteLine(stringTable.SharedStringTable
                                                            .ElementAt(int.Parse(cell.InnerText)).InnerText);
     
                                                    }
                                                    break;
                                            }
                                        }


    • Marked as answer by Jackson_1990 Wednesday, September 28, 2016 9:35 AM
    • Unmarked as answer by Jackson_1990 Wednesday, September 28, 2016 10:01 AM
    • Marked as answer by Jackson_1990 Thursday, September 29, 2016 1:53 AM
    Wednesday, September 28, 2016 7:34 AM
    Moderator
  •  

    Hi,

    Because you miss one line to print the value if they are numbers.

     

     if (cell != null)
                                        {
                                            using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                            {
         sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.InnerText);
                                                if (cell.DataType != null)
                                                {


    Wednesday, September 28, 2016 9:22 AM
    Moderator
  • Hi,

    Change into 

                                    if (cell != null)
                                    {
                                        using (StreamWriter sw = new StreamWriter(@File1, true, Encoding.Unicode))
                                        {
                                            if (cell.DataType == null)
                                        {
                                            //Console.WriteLine(cell.InnerText);
                                            sw.WriteLine("Cell reference is {0}; Cell value is {1}", cell.CellReference, cell.InnerText);
                                        }                               
                                        //if (cell.DataType != null)
                                        else
                                        {

    Wednesday, September 28, 2016 11:50 AM
    Moderator