none
Cell Strikethough - Check For RRS feed

  • Question

  • This seems such a simple problem, but I have Googled myself to a standstill.

    I wish to return the contents of an Excel cell, and check whether the font has a Strikethrough property. This is my flag to ignore that row. I am using ClosedXML and the following code: 

    XLWorkbook wb = new XLWorkbook(fs);
    IXLWorksheet ws = wb.Worksheet(pstrXLSWorksheetName);
    if (ws.Cell(lstrCellAddressPriKey).Style.Font.Strikethrough == true)
    {
    pintTotalStrikethroughsIgnored++;
    lblnProceed = false;
    }

    This works but the performance is appalling. Over severall hundred rows it turns a minute into an hour.

    Can anyone provide a sample not using ClosedXML?
    bool IsStrikethrough(cell) - or (cellAddress)

    Thanks

    Dale

    Wednesday, May 6, 2015 2:28 PM

Answers

  • Hi Dale,

    After my further investigation, I found the part about format for the text and numbers is incorrect. If we striktrough all the text of cells, Excel also add the cell format for it.

    So we always need to detect two location for it. First is the run property under the share string item, second is font under style sheet.

    I also wrote a simple sample for your reference:

     public void Main()
            {
                Console.WriteLine(GetCellValue(@"C:\Book1.xlsx", "Sheet1", "A4"));
            }
    
            // Retrieve the value of a cell, given a file name, sheet name, 
            // and address name.
            public bool GetCellValue(string fileName,
                string sheetName,
                string addressName)
            {
                string value = null;
    
                // Open the spreadsheet document for read-only access.
                using (SpreadsheetDocument document =
                    SpreadsheetDocument.Open(fileName, false))
                {
                    // Retrieve a reference to the workbook part.
                    WorkbookPart wbPart = document.WorkbookPart;
    
                    // Find the sheet with the supplied name, and then use that 
                    // Sheet object to retrieve a reference to the first worksheet.
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                      Where(s => s.Name == sheetName).FirstOrDefault();
    
                    // Throw an exception if there is no sheet.
                    if (theSheet == null)
                    {
                        throw new ArgumentException("sheetName");
                    }
    
                    // Retrieve a reference to the worksheet part.
                    WorksheetPart wsPart =
                        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
                    // Use its Worksheet property to get a reference to the cell 
                    // whose address matches the address you supplied.
                    Cell theCell = wsPart.Worksheet.Descendants<Cell>().
                      Where(c => c.CellReference == addressName).FirstOrDefault();
    
                    // If the cell does not exist, return an empty string.
                    if (theCell != null)
                    {
                        value = theCell.InnerText;
    
                        // If the cell represents an integer number, you are done. 
                        // For dates, this code returns the serialized value that 
                        // represents the date. The code handles strings and 
                        // Booleans individually. For shared strings, the code 
                        // looks up the corresponding value in the shared string 
                        // table. For Booleans, the code converts the value into 
                        // the words TRUE or FALSE.
                        if (theCell.DataType != null)
                        {
                            switch (theCell.DataType.Value)
                            {
                                case CellValues.SharedString:
    
                                    // For shared strings, look up the value in the
                                    // shared strings table.
                                    var stringTable =
                                        wbPart.GetPartsOfType<SharedStringTablePart>()
                                        .FirstOrDefault();
    
                                  
                                    if (stringTable != null)
                                    {
                                        //detect whther the text was part of striked from string item
                                        foreach (Strike strike in stringTable.SharedStringTable
                                           .ElementAt(int.Parse(value)).Descendants<Strike>())
                                           {
                                               if (strike.Val == null || strike.Val != false)
                                               {
                                                   return true;
                                               }
                                        }
    
    
                                        //detect whther the text was striked from cell style
                                        var cellFormat = (CellFormat)wbPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(int.Parse(theCell.StyleIndex));
                                        var font = wbPart.WorkbookStylesPart.Stylesheet.Fonts.ElementAt(int.Parse(cellFormat.FontId));
                                        foreach (Strike strike in font.Descendants<Strike>())
                                        {
                                            if (strike.Val == null || strike.Val != false)
                                            {
                                                return true;
                                            }
                                        }
                                        return false;
                                           
                                    }
                                    break;
                                 
                            }
                        }
                    }
                }
                return false;
            }

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 8, 2015 10:12 AM
    Moderator

All replies

  • Hi Dale,

    According to the description, you wanted to check whether the text in cell was strikethroughed.

    As far as I know, we can complete it using Open XML. There are several secnario for values strikethroughed.

    The first secnario is for the text in the cell. In this secnario, we can detect the whether the text is strikethroughed by the run property under the sharedStringItem.

    This second secnario is the numbers. For this secnario, we can check whether the font propery for the strkethrough format.

    Here is the fingure for this two secnario for your reference:

    I recommed that strikethrouhg the values in cells manually then use open XML SDK 2.5 Productivity Tool to compare the spreadsheet with the orginal one to see the difference. And then we can refrect the code to use Open XML to achieve the goal.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 7, 2015 5:17 AM
    Moderator
  • Hi Fei,

    Thank you VERY much for your reply. Whilst you information is relevant, I am afraid I am at a complete loss as to how to return whether a Spreadsheet Cell contains text with a strikethrough. I am loathe to ask anyone to code for me, but I really need help with this one. The ClosedXML function is as I would expect:

    if (Cell(CellAddress).Style.Font.Strikethrough == true) 

    but I am unable to achieve this with plain OpenXML. Additionally, I only need to consider text, not numbers. Regards, Dale

    Thursday, May 7, 2015 8:19 AM
  • Hi Dale,

    After my further investigation, I found the part about format for the text and numbers is incorrect. If we striktrough all the text of cells, Excel also add the cell format for it.

    So we always need to detect two location for it. First is the run property under the share string item, second is font under style sheet.

    I also wrote a simple sample for your reference:

     public void Main()
            {
                Console.WriteLine(GetCellValue(@"C:\Book1.xlsx", "Sheet1", "A4"));
            }
    
            // Retrieve the value of a cell, given a file name, sheet name, 
            // and address name.
            public bool GetCellValue(string fileName,
                string sheetName,
                string addressName)
            {
                string value = null;
    
                // Open the spreadsheet document for read-only access.
                using (SpreadsheetDocument document =
                    SpreadsheetDocument.Open(fileName, false))
                {
                    // Retrieve a reference to the workbook part.
                    WorkbookPart wbPart = document.WorkbookPart;
    
                    // Find the sheet with the supplied name, and then use that 
                    // Sheet object to retrieve a reference to the first worksheet.
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                      Where(s => s.Name == sheetName).FirstOrDefault();
    
                    // Throw an exception if there is no sheet.
                    if (theSheet == null)
                    {
                        throw new ArgumentException("sheetName");
                    }
    
                    // Retrieve a reference to the worksheet part.
                    WorksheetPart wsPart =
                        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
                    // Use its Worksheet property to get a reference to the cell 
                    // whose address matches the address you supplied.
                    Cell theCell = wsPart.Worksheet.Descendants<Cell>().
                      Where(c => c.CellReference == addressName).FirstOrDefault();
    
                    // If the cell does not exist, return an empty string.
                    if (theCell != null)
                    {
                        value = theCell.InnerText;
    
                        // If the cell represents an integer number, you are done. 
                        // For dates, this code returns the serialized value that 
                        // represents the date. The code handles strings and 
                        // Booleans individually. For shared strings, the code 
                        // looks up the corresponding value in the shared string 
                        // table. For Booleans, the code converts the value into 
                        // the words TRUE or FALSE.
                        if (theCell.DataType != null)
                        {
                            switch (theCell.DataType.Value)
                            {
                                case CellValues.SharedString:
    
                                    // For shared strings, look up the value in the
                                    // shared strings table.
                                    var stringTable =
                                        wbPart.GetPartsOfType<SharedStringTablePart>()
                                        .FirstOrDefault();
    
                                  
                                    if (stringTable != null)
                                    {
                                        //detect whther the text was part of striked from string item
                                        foreach (Strike strike in stringTable.SharedStringTable
                                           .ElementAt(int.Parse(value)).Descendants<Strike>())
                                           {
                                               if (strike.Val == null || strike.Val != false)
                                               {
                                                   return true;
                                               }
                                        }
    
    
                                        //detect whther the text was striked from cell style
                                        var cellFormat = (CellFormat)wbPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(int.Parse(theCell.StyleIndex));
                                        var font = wbPart.WorkbookStylesPart.Stylesheet.Fonts.ElementAt(int.Parse(cellFormat.FontId));
                                        foreach (Strike strike in font.Descendants<Strike>())
                                        {
                                            if (strike.Val == null || strike.Val != false)
                                            {
                                                return true;
                                            }
                                        }
                                        return false;
                                           
                                    }
                                    break;
                                 
                            }
                        }
                    }
                }
                return false;
            }

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 8, 2015 10:12 AM
    Moderator