Find System.Double and convert it to System.Decimal

[paam]

Hello,

I have a spreadsheet which I use ExportDataTable method to export to a data table. Then, I try to bulkcopy my data to the database but having trouble because of the datatype mismatch. SQL db does not have a “double” datatype. How can I check if a cell is of type double and convert it to decimal?

A couple things I tried…

- apply a custom style (style.custom = ‘0.00’) before I export to datatable but it still ends up as double

- attempt to loop through each cell and set the style:

Cells cells = worksheet.Cells;

int mRow = cells.MaxDataRow;

int mCol = cells.MaxDataColumn;

for (int i = 0; i <= mRow; i++)

{

for (int j = 0; j <= mCol; j++)

{

Aspose.Cells.Cell cell = cells[i, j];

if (cell.Value != null)

{

System.Type type = cell.GetType(); //this does not give me data type for the number

//not sure what is the proper thing to do here to get the number data type to convert it to decimal

}

}

}

[babar.raza]

Hi Paam,

Thank you for contacting Aspose support.

Unfortunately, I was unable to find a solution for your presented scenario while using Aspose.Cells API. Although you can workaround this situation by changing the data type of the DataTable column. Please check the below provided code snippet for your reference, and let us know if this helps a bit.

C#

var book = new Workbook(myDir + “book1.xlsx”);
var sheet = book.Worksheets[0];
var options = new ExportTableOptions();
options.ExportColumnName = true;
options.SkipErrorValue = true;
var table = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow+1, sheet.Cells.MaxDataColumn+1, options);

//You cannot change the Data Type of a DataTable column if it contains any data
//therefore we have to first clone the original DataTable, Change the Column Data Type
//and re-import all the rows into the new DataTable
var dtCloned = table.Clone();
dtCloned.Columns[0].DataType = typeof(Decimal);
foreach (DataRow row in table.Rows)
{
dtCloned.ImportRow(row);
}
Console.WriteLine(dtCloned.Columns[0].DataType);

Please feel free to write back in case you have further questions or face any difficulty.

[paam]

Hi Babar,

Thank you for looking into this issue and trying to help. While the suggestion solves the problem, it requires looping to each row to copy the data. I end up decide to change the database datatype to float instead of decimal since I can afford to lose some precision. It eventually allows double type.

Thanks again for your time!

[babar.raza]

Hi again,

Thank you for writing back.

Good to know you have found another solution for your problematic scenario. Please feel free to write back in case you need our further assistance or have more questions for us.