none
When i export a file(some column have date type).But it is not coming in my excel file

    Question

  • I have an excel file with date column i  write a date content in excel file.but when i open a excel file in excel 2007 the date content is not display but when i open a file in excel 2010 the date content is display as a number.I am share my sample code for write the cell value

    public DateCell(string header, DateTime dateTime, int index)        {       

        DataType = CellValues.Date;           

    CellReference = header + index;

     StyleIndex = 3;

    if (dateTime != DateTime.MinValue)

          CellValue = new CellValue { Text = dateTime.ToOADate().ToString(CultureInfo.CurrentCulture) };

    }

    and style of date cell is

      NumberingFormat nfDateTime = new NumberingFormat();            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(excelIndex++);            nfDateTime.FormatCode = StringValue.FromString(Configuration.DateFormat);            numberingFormats.Append(nfDateTime);

    Tuesday, March 12, 2013 6:29 AM

Answers

  • This bolg shows you how to create a stylesheet with date using OpenXML sdk.

    Code

    private static Stylesheet CreateStylesheet()
    {
    	Stylesheet ss = new Stylesheet();
    
    	Fonts fts = new Fonts();
    	DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
    	FontName ftn = new FontName();
    	ftn.Val = "Calibri";
    	FontSize ftsz = new FontSize();
    	ftsz.Val = 11;
    	ft.FontName = ftn;
    	ft.FontSize = ftsz;
    	fts.Append(ft);
    	fts.Count = (uint)fts.ChildElements.Count;
    
    	Fills fills = new Fills();
    	Fill fill;
    	PatternFill patternFill;
    	fill = new Fill();
    	patternFill = new PatternFill();
    	patternFill.PatternType = PatternValues.None;
    	fill.PatternFill = patternFill;
    	fills.Append(fill);
    	fill = new Fill();
    	patternFill = new PatternFill();
    	patternFill.PatternType = PatternValues.Gray125;
    	fill.PatternFill = patternFill;
    	fills.Append(fill);
    	fills.Count = (uint)fills.ChildElements.Count;
    
    	Borders borders = new Borders();
    	Border border = new Border();
    	border.LeftBorder = new LeftBorder();
    	border.RightBorder = new RightBorder();
    	border.TopBorder = new TopBorder();
    	border.BottomBorder = new BottomBorder();
    	border.DiagonalBorder = new DiagonalBorder();
    	borders.Append(border);
    	borders.Count = (uint)borders.ChildElements.Count;
    
    	CellStyleFormats csfs = new CellStyleFormats();
    	CellFormat cf = new CellFormat();
    	cf.NumberFormatId = 0;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	csfs.Append(cf);
    	csfs.Count = (uint)csfs.ChildElements.Count;
    
    	uint iExcelIndex = 164;
    	NumberFormats nfs = new NumberFormats();
    	CellFormats cfs = new CellFormats();
    
    	cf = new CellFormat();
    	cf.NumberFormatId = 0;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cfs.Append(cf);
    
    	NumberFormat nf;
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "#,##0.0000";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	// #,##0.00 is also Excel style index 4
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "#,##0.00";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	// @ is also Excel style index 49
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "@";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	nfs.Count = (uint)nfs.ChildElements.Count;
    	cfs.Count = (uint)cfs.ChildElements.Count;
    
    	ss.Append(nfs);
    	ss.Append(fts);
    	ss.Append(fills);
    	ss.Append(borders);
    	ss.Append(csfs);
    	ss.Append(cfs);
    
    	CellStyles css = new CellStyles();
    	CellStyle cs = new CellStyle();
    	cs.Name = "Normal";
    	cs.FormatId = 0;
    	cs.BuiltinId = 0;
    	css.Append(cs);
    	css.Count = (uint)css.ChildElements.Count;
    	ss.Append(css);
    
    	DifferentialFormats dfs = new DifferentialFormats();
    	dfs.Count = 0;
    	ss.Append(dfs);
    
    	TableStyles tss = new TableStyles();
    	tss.Count = 0;
    	tss.DefaultTableStyle = "TableStyleMedium9";
    	tss.DefaultPivotStyle = "PivotStyleLight16";
    	ss.Append(tss);
    
    	return ss;
    }
    

    Friday, March 15, 2013 7:38 AM

All replies

  • This bolg shows you how to create a stylesheet with date using OpenXML sdk.

    Code

    private static Stylesheet CreateStylesheet()
    {
    	Stylesheet ss = new Stylesheet();
    
    	Fonts fts = new Fonts();
    	DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
    	FontName ftn = new FontName();
    	ftn.Val = "Calibri";
    	FontSize ftsz = new FontSize();
    	ftsz.Val = 11;
    	ft.FontName = ftn;
    	ft.FontSize = ftsz;
    	fts.Append(ft);
    	fts.Count = (uint)fts.ChildElements.Count;
    
    	Fills fills = new Fills();
    	Fill fill;
    	PatternFill patternFill;
    	fill = new Fill();
    	patternFill = new PatternFill();
    	patternFill.PatternType = PatternValues.None;
    	fill.PatternFill = patternFill;
    	fills.Append(fill);
    	fill = new Fill();
    	patternFill = new PatternFill();
    	patternFill.PatternType = PatternValues.Gray125;
    	fill.PatternFill = patternFill;
    	fills.Append(fill);
    	fills.Count = (uint)fills.ChildElements.Count;
    
    	Borders borders = new Borders();
    	Border border = new Border();
    	border.LeftBorder = new LeftBorder();
    	border.RightBorder = new RightBorder();
    	border.TopBorder = new TopBorder();
    	border.BottomBorder = new BottomBorder();
    	border.DiagonalBorder = new DiagonalBorder();
    	borders.Append(border);
    	borders.Count = (uint)borders.ChildElements.Count;
    
    	CellStyleFormats csfs = new CellStyleFormats();
    	CellFormat cf = new CellFormat();
    	cf.NumberFormatId = 0;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	csfs.Append(cf);
    	csfs.Count = (uint)csfs.ChildElements.Count;
    
    	uint iExcelIndex = 164;
    	NumberFormats nfs = new NumberFormats();
    	CellFormats cfs = new CellFormats();
    
    	cf = new CellFormat();
    	cf.NumberFormatId = 0;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cfs.Append(cf);
    
    	NumberFormat nf;
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "#,##0.0000";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	// #,##0.00 is also Excel style index 4
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "#,##0.00";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	// @ is also Excel style index 49
    	nf = new NumberFormat();
    	nf.NumberFormatId = iExcelIndex++;
    	nf.FormatCode = "@";
    	nfs.Append(nf);
    	cf = new CellFormat();
    	cf.NumberFormatId = nf.NumberFormatId;
    	cf.FontId = 0;
    	cf.FillId = 0;
    	cf.BorderId = 0;
    	cf.FormatId = 0;
    	cf.ApplyNumberFormat = true;
    	cfs.Append(cf);
    
    	nfs.Count = (uint)nfs.ChildElements.Count;
    	cfs.Count = (uint)cfs.ChildElements.Count;
    
    	ss.Append(nfs);
    	ss.Append(fts);
    	ss.Append(fills);
    	ss.Append(borders);
    	ss.Append(csfs);
    	ss.Append(cfs);
    
    	CellStyles css = new CellStyles();
    	CellStyle cs = new CellStyle();
    	cs.Name = "Normal";
    	cs.FormatId = 0;
    	cs.BuiltinId = 0;
    	css.Append(cs);
    	css.Count = (uint)css.ChildElements.Count;
    	ss.Append(css);
    
    	DifferentialFormats dfs = new DifferentialFormats();
    	dfs.Count = 0;
    	ss.Append(dfs);
    
    	TableStyles tss = new TableStyles();
    	tss.Count = 0;
    	tss.DefaultTableStyle = "TableStyleMedium9";
    	tss.DefaultPivotStyle = "PivotStyleLight16";
    	ss.Append(tss);
    
    	return ss;
    }
    

    Friday, March 15, 2013 7:38 AM
  • Hi Naveen,

    I temporarily marked the reply as answer. You can unmark it if it provides no help.

    Please feel free to let us know if you need any help.

    Have a nice day.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 28, 2013 7:25 AM