1

I have an application that uses excel. The user needs to write an excel format in my application and I need to place it excel to some cells. However I need to provide an example to the user to know if the format will be correct or not.

I know in VBA there is the function FORMAT. Is there an equivalent in C# for this function?

It needs to work for example with the format "[$-409]d.m.yy h:mm AM/PM;@" but it has to support anything that the Excel supports.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Dan
  • 683
  • 2
  • 8
  • 24
  • The [Office Interop Assemblies](http://msdn.microsoft.com/en-us/library/15s06t57.aspx) will have these functions. You can create, format and save office documents using the COM objects. – crthompson Jun 06 '14 at 05:49
  • There is a class WorksheetFunctions but it does not have the Format function in it. Do you know where else might it be? – Dan Jun 06 '14 at 06:03
  • I never said the interop libraries were intuitive. ;) There is lots on google however. I found [this](http://social.msdn.microsoft.com/Forums/vstudio/en-US/ebec8732-4a5f-4780-b10c-eb1c4aff74e7/how-to-reformatting-cell-style-before-export-officeinteropexcel-library-v12) first try. – crthompson Jun 06 '14 at 06:06
  • Not really helping. I know about this solution: change the format of a cell, write the value in it and than take the text. I need a solution that does not required the changing of a cell because the workbook might have track changes on it and I don't want to have the change in the track – Dan Jun 06 '14 at 08:05
  • so you want to take the value from the cell and format it in C#? – Siddharth Rout Jun 06 '14 at 11:22
  • Also have you seen [THIS](http://stackoverflow.com/questions/3296645/equivalent-of-format-of-vb-in-c-sharp)? – Siddharth Rout Jun 06 '14 at 11:24
  • I need a function to work with the example I gave in C#. Something like xxx(myValue, "[$-409]d.m.yy h:mm AM/PM;@") and will return exactly the same thing as excel would – Dan Jun 10 '14 at 08:36

1 Answers1

1

This was bothering me too. I didn't want to have to apply NumberFormat to the cells, paste the values and then retrieve the text.

After some investigation, I found the function that does this.

WorksheetFunction.Text: https://msdn.microsoft.com/en-us/library/office/ff841121.aspx

The first argument is the value that you want to format, this could be a String, DateTime, Double, Integer etc, the second argument is the Excel NumberFormat string.

MessageBox.Show(ExcelApp.WorksheetFunction.Text(DateTime.Now, "[$-409]d.m.yy h:mm AM/PM;@"));
tjsmith
  • 729
  • 7
  • 21