none
How to read the excel sheet column value without formula applied for that column using spreadsheetdocument in c#? RRS feed

  • Question

  • Hello Sir/Mam,

    I am just applying the formula to one column of the Excel sheet. After that i read the data of Excel sheet using SpreadsheetDocument in c#.

    exceldocument = SpreadsheetDocument.Open(UploadControl.PostedFile.InputStream, false);

    IEnumerable<Sheet> sheets = exceldocument.WorkbookPart.Workbook.Descendants<Sheet>();

    foreach (Sheet sheet in sheets)

    {

    sheetId = sheet.Id;

    sheetdata = (WorksheetPart) exceldocument.WorkbookPart.GetPartById(sheetId);

    Worksheet worksheet = sheetdata.sheet;

    Cell cell1 = GetCell(worksheet, "C", 1);

    // here I getting the sheet column value with applied formula

    }

    Can any one please help me to solve this problem?

    • Moved by LeoTang Friday, September 28, 2012 1:36 AM (From:Windows Workflow Foundation 3)
    Wednesday, September 26, 2012 9:39 AM

Answers

  • Hi Nandhini.M,

    I hope following snippet can help you. Omit the component which comes form name space "com.mksword.Net.OpenXmlTools". That only a customized assembly which I have create to help me make the code shorter.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using com.mksword.Net.OpenXmlTools;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ReadValue
    {
        public class TestClass : OpenXmlUtil
        {
            public event PrintHandler Print;
    
            public void Exceute()
            {
                OpenFile(OXUFileFilter.EXCEL);
                using (SpreadsheetDocument SSD = SpreadsheetDocument
                    .Open(OriginalPath, false))
                {
                    WorkbookPart WBP = SSD.WorkbookPart;
                    Workbook WB = WBP.Workbook;
                    Sheet sheet = WB.Descendants<Sheet>()
                        .Where(ST => ST.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart WSP = WBP.GetPartById(sheet.Id) as WorksheetPart;
                    if (WSP != null)
                    {
                        Worksheet WS = WSP.Worksheet;
                        SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                        List<Cell> cells = SD.Descendants<Cell>()
                            .Where(CL => CL.CellReference.Value.StartsWith("C"))
                            .ToList();
                        foreach (Cell cs in cells)
                        {
                            CellValue CV = cs.Descendants<CellValue>()
                                .FirstOrDefault();
                            Print(CV.Text);
                        }
                    }
                    else
                    {
                        SetLog("WSP is NULL", OXULogType.FATAL);
                    }
                }
            }
        }
    
        public delegate void PrintHandler(string Value);
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 4, 2012 8:51 AM
    Moderator

All replies

  • Hi Nandhini.M,

    Thanks for posting in the MSDN Forum.

    Would you please share your snippet of method GetCell. And I still not very sure about your goal would you please show me detailed scenario?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, September 28, 2012 2:21 AM
    Moderator
  • Hello Tom,

    Thanks for your reply...

    I need to read the data from Excel and display it using devex gridview...

    The data of the Excel sheet column comes with formula when it have the formula applied for that cell...     

     public static List<List<INVTemplate>> GetINVList(HtmlInputFile UploadControl, int EliminateHeaderCount, ref bool TemplateMatch, ref List<String> SheetNames)
            {
                SpreadsheetDocument INVDocument = null;
                List<List<INVTemplate>> INVList = new List<List<INVTemplate>>();
                try
                {
                    if (UploadControl.PostedFile.FileName.Length > 0)
                    {
                        // Open excel document.
                        INVDocument = SpreadsheetDocument.Open(UploadControl.PostedFile.InputStream, false);
                        INVList = GetINVListFromSpeedSheetDocument(INVDocument, EliminateHeaderCount, ref TemplateMatch, ref SheetNames);
                    }
                    else
                    {
                        TemplateMatch = false;
                    }
                }
                catch { TemplateMatch = false; }
    
                return INVList;
            }

    public static List<List<INVTemplate>> GetINVListFromSpeedSheetDocument(SpreadsheetDocument INVDocument, int EliminateHeaderCount, ref bool TemplateMatch, ref List<String> SheetNames)
            {
                INVTemplate itFormat = new INVTemplate();
                List<List<INVTemplate>> ResultINVList = new List<List<INVTemplate>>();
                try
                {
                    List<INVTemplate> INVList = new List<INVTemplate>();
                    IEnumerable<Sheet> workSheets = INVDocument.WorkbookPart.Workbook.Descendants<Sheet>();
                    WorksheetPart INVSheet = null;
                    string INVSheetId = string.Empty;
                  
                    // Get the first sheet using id.
                    foreach (Sheet worksheet in workSheets)
                    {
                      
                        if (!worksheet.Name.ToString().Contains("Print Label"))
                        {
                           
                            INVSheetId = worksheet.Id;
                            INVSheet = (WorksheetPart)INVDocument.WorkbookPart.GetPartById(INVSheetId);
                            Worksheet worksheet1 = INVSheet.Worksheet;
                            SharedStringTable sharedStrings = INVDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
                            XElement worksheetElement = XElement.Load(XmlReader.Create(INVSheet.GetStream()));
    
                            // Get the xml namespace of the xelement.
                            XNamespace xns = worksheetElement.Name.Namespace;
                            // Assign xml valus to the INVTemplate object 
                            INVList = (from list in worksheetElement.Descendants(xns + "row")
                                       select new INVTemplate
                                       {
                                           Height = list.Elements().Where(l => Regex.IsMatch(l.Attribute("r").Value, INVTemplate.HeightPosition + "[0-9]*")).Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.HeightPosition)).FirstOrDefault() != null ? list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.HeightPosition)).FirstOrDefault().Attribute("t") != null && list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.HeightPosition)).FirstOrDefault().Attribute("t").Value.Equals("s") ? sharedStrings.ChildElements[int.Parse(list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.HeightPosition)).FirstOrDefault().Value)].InnerText : list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.HeightPosition)).FirstOrDefault().Value : null,
                                           Width = list.Elements().Where(l => Regex.IsMatch(l.Attribute("r").Value, INVTemplate.WidthPosition + "[0-9]*")).Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.WidthPosition)).FirstOrDefault() != null ? list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.WidthPosition)).FirstOrDefault().Attribute("t") != null && list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.WidthPosition)).FirstOrDefault().Attribute("t").Value.Equals("s") ? sharedStrings.ChildElements[int.Parse(list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.WidthPosition)).FirstOrDefault().Value)].InnerText : list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.WidthPosition)).FirstOrDefault().Value : null,
                                           Depth = list.Elements().Where(l => Regex.IsMatch(l.Attribute("r").Value, INVTemplate.DepthPosition + "[0-9]*")).Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.DepthPosition)).FirstOrDefault() != null ? list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.DepthPosition)).FirstOrDefault().Attribute("t") != null && list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.DepthPosition)).FirstOrDefault().Attribute("t").Value.Equals("s") ? sharedStrings.ChildElements[int.Parse(list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.DepthPosition)).FirstOrDefault().Value)].InnerText : list.Elements().Where(l => l.Attribute("r").Value.StartsWith(INVTemplate.DepthPosition)).FirstOrDefault().Value : null,
                                       }).ToList<INVTemplate>();
                            bool isValidINVTemplate = INVList.Where(z => z.Height.Equals(INVTemplate.HeightHeader)
                                                                        && z.Width.Equals(INVTemplate.WidthHeader)
                                                                        && z.Depth.Equals(INVTemplate.DepthHeader)
                                                                        ).Count() > 0;
    
                            if (isValidINVTemplate)
                            {
                                // Remove the header record from the list. + Number of headers should remove
                                for (int HeaderIndex = 0; HeaderIndex < EliminateHeaderCount + 1; HeaderIndex++)
                                    INVList.RemoveAt(0);
    
                                TemplateMatch = true;
                                SheetNames.Add(worksheet.Name);
    
                                // Add Inventory List to cache(Session).
                                ResultINVList.Add(INVList);
                            }
                            else
                            {
                                TemplateMatch = false;
                                break;
                            }
                            INVSheet = null;
                        }
    
                    }
                }
                catch
                {
                    TemplateMatch = false;
                }
    
                return ResultINVList;
            }


    The above code gives the data with formula applied for that cell...


    • Edited by Nandhini.M Monday, October 1, 2012 10:47 AM
    Monday, October 1, 2012 10:43 AM
  • Hello Tom,

    How to avoid the formula while reading data from excel?

    Please help me to find the solution...

    Thanks,

    Nandhini.M

    Monday, October 1, 2012 10:53 AM
  • Hi Nandhini.M,

    I would recommend you iterator the Cellvalue node of the Cell. And retrieve the value forum that side. As usual, it will have the correct data which your want to get if your spreadsheet isn't a document generate via OpenXml program.

    If you don't know how to approach it, please free feel to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 3, 2012 8:42 AM
    Moderator
  • Hi Tom,

    I don't know how to approach this... can u explain?

    Thanks,

    Nandhini.M

    Wednesday, October 3, 2012 9:23 AM
  • Hi Nandhini.M,

    I hope following snippet can help you. Omit the component which comes form name space "com.mksword.Net.OpenXmlTools". That only a customized assembly which I have create to help me make the code shorter.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using com.mksword.Net.OpenXmlTools;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ReadValue
    {
        public class TestClass : OpenXmlUtil
        {
            public event PrintHandler Print;
    
            public void Exceute()
            {
                OpenFile(OXUFileFilter.EXCEL);
                using (SpreadsheetDocument SSD = SpreadsheetDocument
                    .Open(OriginalPath, false))
                {
                    WorkbookPart WBP = SSD.WorkbookPart;
                    Workbook WB = WBP.Workbook;
                    Sheet sheet = WB.Descendants<Sheet>()
                        .Where(ST => ST.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart WSP = WBP.GetPartById(sheet.Id) as WorksheetPart;
                    if (WSP != null)
                    {
                        Worksheet WS = WSP.Worksheet;
                        SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                        List<Cell> cells = SD.Descendants<Cell>()
                            .Where(CL => CL.CellReference.Value.StartsWith("C"))
                            .ToList();
                        foreach (Cell cs in cells)
                        {
                            CellValue CV = cs.Descendants<CellValue>()
                                .FirstOrDefault();
                            Print(CV.Text);
                        }
                    }
                    else
                    {
                        SetLog("WSP is NULL", OXULogType.FATAL);
                    }
                }
            }
        }
    
        public delegate void PrintHandler(string Value);
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 4, 2012 8:51 AM
    Moderator