0

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.

Community
  • 1
  • 1
Somebody
  • 2,667
  • 14
  • 60
  • 100
  • this sounds like a formatting issue.. also when you export it can you change the Column Type or are you needing to do this via the code / Export Library – MethodMan Aug 05 '13 at 21:45
  • I did both in SQL, first a datetime column, and then I tried converting the column to varchar, same result :( – Somebody Aug 05 '13 at 21:48

1 Answers1

2

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.

Jay Riggs
  • 53,046
  • 9
  • 139
  • 151