Write Range Workbook: Unable to set cell value to 12/29/1899 00:00:00

Help Activities
, ,
1

Hi guys,
while writing data into excel, i getting this error as “Write Range Workbook: Unable to set cell value to 12/29/1899 00:00:00”. some of answers saying convert to string. i have huge data approximately 9k rows, i have lot of Date columns, they keeping -1 where date is not sure in one of column. this -1 is showing in studio above error date.
input is like this
image

i dont want entire data so i am using Linq for just filtering with single column. how can i resolve my issue.

1 Like
2

Hi,

Use below linq to Convert date column to string and update values in the same column:-

dataTable.AsEnumerable().Select(Sub(row) row.SetField(“DateColumn”, row.Field(Of DateTime)(“DateColumn”).ToString(“yyyy-MM-dd”))).ToList()

Thanks

3

Thanks for reply,
its showing below error.

image
image753×390 28 KB

however finally it convert to list, how can i merge into those 9k rows data.

4

Hi,

If possible please share input excel file. I will share xaml file.

Thanks

5

Sorry bro, not possible.
my problem is if any row contains -1(we can see first image) it will throw error only in write range. otherwise its working fine.

6

Hi,

We can replace -1 with null values from datatable.

Try below linq:-

yourDataTableVariable =
(From row In yourDataTableVariable.AsEnumerable()
Let newRow = row.ItemArray.Select(Function(item) If(Convert.ToInt32(item) = -1, DBNull.Value, item)).ToArray()
Select yourDataTableVariable.Rows.Add(newRow)).CopyToDataTable()

Thanks