none
How can I send a date from C# to Excel using Open XML - and make the cell containing the date in Excel be a date cell instead of a general cell? RRS feed

  • Question

  • Hi

    I'm using c# and openxml to create an Excel workbook. This works fine, however my dates are just string representations of dates when I open the sheet. I find that the cell datatype is set to general, however I really need it to be date so that I can use the filtering functions in Excel properly. (I need to filter by January, not the string "01.01" or "02.01"). 

    I found a forum reply from 2009 stating that cells can either be strings or numbers, one can only format the string to look like a date. (https://social.msdn.microsoft.com/Forums/en-US/3143212a-c798-4a93-ab2b-f08625c5cbe5/how-to-get-cell-datatype-in-spreadsheet?forum=oxmlsdk)

    Is this still the case, or is there any way I can turn my cell into a cell with a datatype of Date from my c# code? I tried changing the template, but as soon as I filled the cells with data, they switched back to "general". The emtpy cells in that column were still date-cells. 

    Thanks

    Sunday, September 6, 2015 4:48 PM

Answers

  • Hi Minnie85,

    Do you change the style of the cell? In my option, before you pass the value, you need to apply the format style on the cell. Here is a simple code.

            private void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = worksheetPart1.Worksheet;
    
                SheetViews sheetViews1 = worksheet1.GetFirstChild<SheetViews>();
                SheetData sheetData1 = worksheet1.GetFirstChild<SheetData>();
    
                SheetView sheetView1 = sheetViews1.GetFirstChild<SheetView>();
    
                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.15D };
    
                Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
                CellValue cellValue1 = new CellValue();
                //cellValue1.Text = "42097";
                //cellValue1.Text = DateTime.Now.ToOADate().ToString();
                cellValue1.Text = "04.01.2013";
                cell1.Append(cellValue1);
    
                row1.Append(cell1);
                sheetData1.Append(row1);
            }
    
            private void ChangeWorkbookStylesPart1(WorkbookStylesPart workbookStylesPart1)
            {
                Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;
    
                Fonts fonts1 = stylesheet1.GetFirstChild<Fonts>();
                CellFormats cellFormats1 = stylesheet1.GetFirstChild<CellFormats>();
    
                NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)1U };
                NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)176U, FormatCode = "dd\\.mm\\.yyyy;@" };
    
                numberingFormats1.Append(numberingFormat1);
                stylesheet1.InsertBefore(numberingFormats1, fonts1);
                cellFormats1.Count = (UInt32Value)2U;
    
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)176U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
                cellFormats1.Append(cellFormat1);
            }
    

    For a general suggestion, I suggest you do as below:
    1. Create an empty excel file and excel file with date format you want;
    2. Use Open XML SDK 2.5 productivity Tool for Microsoft Office
    3. Click the Compare Files, generate the code, and then you could get the code
    For more information about Open XML SDK 2.5 productivity Tool for Microsoft Office, you could refer the link below:
    # Open XML SDK 2.5 for Microsoft Office
    https://www.microsoft.com/en-us/download/details.aspx?id=30425

    With Open XML SDK productivity Toll for Microsoft Office, you could create two files, one is original file and another it the result file, click the Compare Files, generate the code, and then you could get the code what you want.

    Best Regards,

    Edward


    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.


    Wednesday, September 9, 2015 8:14 AM
  • Hi Minnie85,

    >> I understand that this is related to UTF-8 BOM, but could this be what's causing the issue? Does Excel not cooperate with UTF-8 BOM?

    Have you used UTF8Encoding in your code? I did not get the same issue like yours. If you used UTF8Encoding, I suggest you use parameter to specify whether to provider a Unicode byte order mark.
    # UTF8Encoding Constructor
    https://msdn.microsoft.com/en-us/library/system.text.utf8encoding.utf8encoding(v=vs.110).aspx

    I suggest you use Open XML SDK 2.5 for Microsoft Office to generate code by comparing the two file to check whether this issue could be resolved by converting with code.

    Best Regards,

    Edward


    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.


    Wednesday, September 16, 2015 7:14 AM

All replies

  • Hi Minnie85,

    >>Is this still the case
    Yes, a cell in SpreadsheetML can be text or number. It could not be a date.

    >>is there any way I can turn my cell into a cell with a datatype of Date from my c# code?
    Yes, we could use serial dates. A serial date is simply a date represented as a number, and we could add the date style of the cell to format the cell value.

    Here is a simple code.

    private static Cell CreateDateCell(string header, int index, DateTime time)
            {
                //Create new inline string cell, format the cell style with style index
                var c = new Cell {CellReference = header + index, StyleIndex = 1};
    
                //Add text to text cell, convert datetime to serial date
                var value = new CellValue {Text = time.ToOADate().ToString()};
    
                c.AppendChild(value);
    
                return c;
            }
    

    Best Regards,

    Edward


    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.


    Monday, September 7, 2015 2:23 AM
  • Hi

    Thanks for your reply, I appreciate it. Unfortunately the date still isn't presented correctly. The date sent to the function is 04.01.2013 (dd.mm.yyyy), the outcome in Excel is 41 278. On the bright side, the format has changed to Custom, and the type is _ * # ##0_ ;_ * -# ##0_ ;_ * "-"??_ ;_ @_ 

    I tried setting the date to DateTime.Now, however it didn't change the outcome in Excel. My regional settings are set to Norwegian. Any input on more things to try? 
    Tuesday, September 8, 2015 10:02 AM
  • Hi Minnie85,

    Do you change the style of the cell? In my option, before you pass the value, you need to apply the format style on the cell. Here is a simple code.

            private void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = worksheetPart1.Worksheet;
    
                SheetViews sheetViews1 = worksheet1.GetFirstChild<SheetViews>();
                SheetData sheetData1 = worksheet1.GetFirstChild<SheetData>();
    
                SheetView sheetView1 = sheetViews1.GetFirstChild<SheetView>();
    
                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.15D };
    
                Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
                CellValue cellValue1 = new CellValue();
                //cellValue1.Text = "42097";
                //cellValue1.Text = DateTime.Now.ToOADate().ToString();
                cellValue1.Text = "04.01.2013";
                cell1.Append(cellValue1);
    
                row1.Append(cell1);
                sheetData1.Append(row1);
            }
    
            private void ChangeWorkbookStylesPart1(WorkbookStylesPart workbookStylesPart1)
            {
                Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;
    
                Fonts fonts1 = stylesheet1.GetFirstChild<Fonts>();
                CellFormats cellFormats1 = stylesheet1.GetFirstChild<CellFormats>();
    
                NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)1U };
                NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)176U, FormatCode = "dd\\.mm\\.yyyy;@" };
    
                numberingFormats1.Append(numberingFormat1);
                stylesheet1.InsertBefore(numberingFormats1, fonts1);
                cellFormats1.Count = (UInt32Value)2U;
    
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)176U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
                cellFormats1.Append(cellFormat1);
            }
    

    For a general suggestion, I suggest you do as below:
    1. Create an empty excel file and excel file with date format you want;
    2. Use Open XML SDK 2.5 productivity Tool for Microsoft Office
    3. Click the Compare Files, generate the code, and then you could get the code
    For more information about Open XML SDK 2.5 productivity Tool for Microsoft Office, you could refer the link below:
    # Open XML SDK 2.5 for Microsoft Office
    https://www.microsoft.com/en-us/download/details.aspx?id=30425

    With Open XML SDK productivity Toll for Microsoft Office, you could create two files, one is original file and another it the result file, click the Compare Files, generate the code, and then you could get the code what you want.

    Best Regards,

    Edward


    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.


    Wednesday, September 9, 2015 8:14 AM
  • Hi

    Thanks again, this solved the date issue - and I'm very thankful for all your help. Unfortunately it introduced another issue, the "we found a problem with some content in 'myworkbook.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes". 

    When I click yes, the corrected parts are styles.xml, and sheet.xml. The format and content of the woorkbook is however just the way I want it after the fix. 

    I followed your tip about opening the xlsx as an xml for comparison. The one with dates not generated from Excel differs quite a bit. I then opened the sheet that was fixed by Excel (agreed to fixing, saved it and made an xml out of it), and a copy of the woorkbook that was not fixed. The last two (fixed.xml and unfixed.xml) is visually exactly the same, but if you binary-compare them using ExamDiff, I find that unfixed.xml has four characters to begin with that fixed.xml doesn't have. 

    fixed.xml starts with: <?xml version="1.0" 
    unfixed.xml starts with: ï»¿<?xml version="1.0"

    I understand that this is related to UTF-8 BOM, but could this be what's causing the issue? Does Excel not cooperate with UTF-8 BOM? 


    • Edited by Minnie85 Tuesday, September 15, 2015 11:19 AM edited last sentence
    Tuesday, September 15, 2015 11:17 AM
  • Hi Minnie85,

    >> I understand that this is related to UTF-8 BOM, but could this be what's causing the issue? Does Excel not cooperate with UTF-8 BOM?

    Have you used UTF8Encoding in your code? I did not get the same issue like yours. If you used UTF8Encoding, I suggest you use parameter to specify whether to provider a Unicode byte order mark.
    # UTF8Encoding Constructor
    https://msdn.microsoft.com/en-us/library/system.text.utf8encoding.utf8encoding(v=vs.110).aspx

    I suggest you use Open XML SDK 2.5 for Microsoft Office to generate code by comparing the two file to check whether this issue could be resolved by converting with code.

    Best Regards,

    Edward


    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.


    Wednesday, September 16, 2015 7:14 AM