Hi Alex,

When you Read an Excel File into a Workbook and then Write that Workbook to an Excel File, any formatting should be preserved.

Please try this and let me know if it works for you.

ConsoleApp1.zip

The cell's value in this scenario is a FormattedString and to apply that to another cell you would first need to clone the FormattedString.  For example the following will duplicate the FormattedString in the third row of the file in the sample I provided:

static void Main(string[] args)
{
    Workbook wb = Workbook.Load("Book1.xlsx");
    FormattedString formattedString = wb.Worksheets[0].Rows[0].Cells[0].Value as FormattedString;
    wb.Worksheets[0].Rows[2].Cells[0].Value = formattedString.Clone();
    wb.Save("output.xlsx");
}

If you want to dynamically create the Formatted String, you you would pass the string to format into the constructor for FormattedString and use GetFont to get the FormattedStringFont object where you can set the Strikeout to true:

wb.Worksheets[0].Rows[4].Cells[0].Value = newFormattedString;
FormattedStringFont formattedFont = newFormattedString.GetFont(35, 6);
formattedFont.Strikeout = ExcelDefaultableBoolean.True;

Let us know if you have any questions.

The Value property of a cell can return various types depending on what is in the cell and in the case of Book2.xlsx, when the value isn't a formatted string it is expected that you would get a NullReferenceException in the code provided.  The following screen shot shows that the cell in the second row returns a string while the cell in the fifth row returns a FormattedString:

Note that in the code example provided the values were being read from and written to the same WorkBook.  You will need to check if the value in the source WorkBook is a FormattedString and if so Clone it otherwise just set the value to the same Value.  For example:

static void Main(string[] args)
{
    Workbook wb = Workbook.Load("Book2.xlsx");
    Workbook wb2 = new Workbook(WorkbookFormat.Excel2007);
    wb2.Worksheets.Add("sheet");
    for (int rowIndex = 1; rowIndex <= wb.Worksheets[0].Rows.Count() - 1; rowIndex++)
    {
        object value = wb.Worksheets[0].Rows[rowIndex].Cells[2].Value;
        if (value is FormattedString)
        {
            value = ((FormattedString) value).Clone();
        }

        wb2.Worksheets[0].Rows[rowIndex + 10].Cells[2].Value = value;
    }
    wb2.Save(@"Book3.xlsx");
}

Let me know if you have any questions.

I've tried to attach the zip file to this post but keep getting an error. I do Insert->Insert image/video/file->From file upload with and without dimensions but it gives me an error every time even though it looks like it actually pulls it in. If you'd like to email me at abratu@tauck.com, I can reply and attach it to that. Or you can tell me the trick to uploading a zip file correctly in a post.

Thanks!

 Alex

I don't expect worksheet filtering or sorting to be an issue though if you are using an older volume of our toolset, you might want to upgrade to 2018 Volume 1 as we just added support for worksheet level filtering and sorting in the latest release.  If you update the example that I provided with an excel file and code that doesn't work as expected, I can take a closer look at what is happening.