Search code examples
c#excellibrary

Issue generating an excel file from a SP with a column datetime using ExcelLibrary c#

I'm using the ExcelLibrary to export the result from a SP to Excel following Mike Webb's example at: Create Excel (.XLS and .XLSX) file from C#

But one of the columns of the SP returns a DateTime field, and every time I generate the excel File that column appears with a different value.

Example: 

In SP: 08/05/2013  5:27PM
In Excel: 40029.72778

Is there any way to fix this? I believe that this is because of the cell format.

Solution

  • You don't show any code but I believe the problem is you need to specify formatting for the ExcelLibrary.SpreadSheet.Cell object.

    The following code demonstrates the problem you're seeing and formats the Cell to correct it:

    // DateTime = 08/05/2013  5:27PM, from your SP.
var dateTime = new DateTime(2013, 8, 5, 17, 27, 00);

string file = "..\\..\\..\\..\\newdoc1.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");

// This reproduces your problem.
worksheet.Cells[0, 0] = new Cell(dateTime);
// This corrects the problem by specifying Cell formatting.
worksheet.Cells[0, 1] = new Cell(dateTime, @"YYYY\-MM\-DD");

workbook.Worksheets.Add(worksheet);

workbook.Save(file);

    Documentation for this project is pretty light, but various Cell formatting options are demonstrated on the project's Google Code home page.