Date Format in Worksheet Cell from DataTable

Jun 16, 2011 at 10:07 AM

Hi When using the epplus library like this:

worksheet.Cells["A1"].LoadFromDataTable(dt, true);

If the Datatable contains a date e.g. 05/05/2011 it converts it to a numeric value of 40668. 

Looking in the worksheet xml, this renders (as an example):

 

<c r="J14" s="0">
    <v>40668</v>
</c>

 

When I open the worksheet in Excel and Modify the cell to Date, it changes the above XML to

<c r="J14" s="1">
    <v>40668</v>
</c>

I was just wondering if I can set this flag to 1 based on the datatype from the datatable, and if so - what the syntax would be, as I cant find it in the documentation

Thanks, Mark

 

Jun 16, 2011 at 10:10 AM

Apologies, I have just searched and found your other discussion: http://epplus.codeplex.com/discussions/259085

Thanks, mark

Jun 16, 2011 at 11:08 AM

I still cant work out how to get it to apply the style to a set of cells (only for certain columns) so any code advice would be greatly received :-)

Coordinator
Jun 16, 2011 at 3:43 PM

Have a look at the sample project, you have several samples of how to do it there. Heres a simple example...

worksheet.Cells[1,1,15,2].Style.NumberFormat.Format="#,##0";
//or
worksheet.Cells["A1:B15"].Style.NumberFormat.Format="#,##0";
Jun 16, 2011 at 3:56 PM

Hi Jan

Thanks I have written some additional code in order to do it in a generic fashion for any column in the DataTable (without putting smelly patches in the ExcelRangeBase class I downloaded:

var rowCount = dt.Rows.Count;
var worksheet = package.Workbook.Worksheets.Add(repName + "_" + (i + 1));
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
var dateColumns = from DataColumn d in dt.Columns
				  where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date")
				  select d.Ordinal + 1;

foreach (var dc in dateColumns)
{
	worksheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format = "dd/mm/yyyy";
}

Thanks for your help, Mark