Hi,
Is there a way to read from one Excel with Infragistics and output the same data to another Excel, while keeping the exact same formatting as the original? For example, if part of the text has strikethrough (see above, circle added by me) or some of the text is black and some is red, how do I end up with the same visual effect in the new spreadsheet I'm writing to? Thanks!
Alex
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.
Hi Mike, thanks for your reply. I tried that before posting. In the sample text at the top of my original posting, the strikethrough does not get preserved. In other cases, when part of the text is red and part of the text is black, the red does not get preserved.
Alex,
I created a simple console app to test the saving of the formatting and the strike through format was preserved in my test. I have attached this sample for your reference.
Please test this sample to see if you have the same behavior. If you see that the formatting isn't preserved, then maybe there is a difference in the versions of the toolset that we are using or another environment detail that is a factor. If this sample works as expected but your source excel file behaves differently, we will need an excel file that does reproduce the behavior that we can use for debugging to see what happens.
Let us know if you have any questions.
ConsoleApp1.zip
Alan,
That makes so much sense when you explain it but I'd never think of that (a string not being a FormattedString) on my own. My code now works with our spreadsheet and it's a beautiful thing. It's amazing what your Excel engine can do, and I'm probably only scratching the surface.
Thank you so much for all the help!
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 been away on vacation so I haven’t taken you up on your offer until now. Thank you for volunteering to help us. There is a Book2.xlsx copied from our original formatted spreadsheet, with fewer columns and fewer rows. Ideally we would process the rows in a loop like the one commented out in the attached solution, but for this spreadsheet issue it’s not pertinent. With this Book2.xlsx, I get a null for formattedString on the very first row it processes. With another version of the workbook, with only 2 rows, I do not get a null. Go figure. It must be something in our formatting.
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!
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.
That is exactly the type of solution I was looking for. Using your suggested code, I wrote my own program that processes a given spreadsheet and writes to another one. The code works great on your spreadsheet but unfortunately it doesn't work on ours. Our spreadsheet has some bells and whistles like filtering, which has caused other issues in the past when using the Infragistics Excel engine. The formatted string came up as null when I ran my code.I took filtering off but it still came up as null. I took your spreadsheet and added a couple of more columns and it worked just fine with that 1 line. Then I copied and pasted a few lines from our spreadsheet and again the formatted strings that I pasted in came up as null when I ran my code. Something in our spreadsheet is confusing Infragistics. Is the filtering a known incompatibility issue? Sorry to keep bothering you with questions and thanks for the help!