2
Answers

importing excel date value to DataTable

hadoop hadoop

hadoop hadoop
6y
2.1k
1
I have a excel sheet where 4th column has date value sample values (1979-12-15 , 1975-05-04) when I import this excel sheet to DataTable, I am getting only the last part of these values like 15 , 04.
 
In the excel sheet, the 4 th column will only have date value but the number of rows is dynamic.
 
My code is::
class ExcelPackageExtenstions
  1. public static class ExcelPackageExtenstions  
  2.     {  
  3.         //public ExcelPackageExtenstions() { }  
  4.   
  5.         public static DataTable ToDataTable(this ExcelPackage package)  
  6.         {  
  7.             ExcelWorksheet workSheet = package.Workbook.Worksheets.First();  
  8.             DataTable table = new DataTable();  
  9.             foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])  
  10.             {  
  11.                 table.Columns.Add(firstRowCell.Text);  
  12.             }  
  13.             for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)  
  14.             {  
  15.                 var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];  
  16.                 var newRow = table.NewRow();  
  17.                 foreach (var cell in row)  
  18.                 {  
  19.                     newRow[cell.Start.Column - 1] = cell.Text;  
  20.                 }  
  21.                 table.Rows.Add(newRow);  
  22.             }  
  23.             return table;  
  24.         }  
  25.     }  
 aspx code
  1. protected void Page_Load(object sender, EventArgs e)  
  2.         {  
  3.             if (IsPostBack && Upload.HasFile)  
  4.             {  
  5.                 if(Path.GetExtension(Upload.FileName).Equals(".xlsx"))  
  6.                 {  
  7.                     var excel = new ExcelPackage(Upload.FileContent);  
  8.                     var dt = excel.ToDataTable();  
  9.                     var table = "Test";  
  10.                     using (var conn = new SqlConnection("Server=.;Database=Test;Integrated Security=SSPI"))  
  11.                     {  
  12.                         //bulk copy code  
  13.                     }  
  14.                 }  
  15.             }  
  16.         }  
while debugging the class ExcelPackageExtenstions, I found that 
  1. newRow[cell.Start.Column - 1] = cell.Text;   
here I am getting the wrong value for date cells. How can I solve this problem ?
 
Answers (2)
Next Recommended Forum