TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
hadoop hadoop
NA
159
50.8k
importing excel date value to DataTable
Dec 9 2018 1:14 AM
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
public
static
class
ExcelPackageExtenstions
{
//public ExcelPackageExtenstions() { }
public
static
DataTable ToDataTable(
this
ExcelPackage package)
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
DataTable table =
new
DataTable();
foreach
(var firstRowCell
in
workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
{
table.Columns.Add(firstRowCell.Text);
}
for
(var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
{
var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
var newRow = table.NewRow();
foreach
(var cell
in
row)
{
newRow[cell.Start.Column - 1] = cell.Text;
}
table.Rows.Add(newRow);
}
return
table;
}
}
aspx code
protected
void
Page_Load(
object
sender, EventArgs e)
{
if
(IsPostBack && Upload.HasFile)
{
if
(Path.GetExtension(Upload.FileName).Equals(
".xlsx"
))
{
var excel =
new
ExcelPackage(Upload.FileContent);
var dt = excel.ToDataTable();
var table =
"Test"
;
using
(var conn =
new
SqlConnection(
"Server=.;Database=Test;Integrated Security=SSPI"
))
{
//bulk copy code
}
}
}
}
while debugging the class ExcelPackageExtenstions, I found that
newRow[cell.Start.Column - 1] = cell.Text;
here I am getting the wrong value for date cells. How can I solve this problem ?
Reply
Answers (
2
)
telecommuting work
Print array values correctly