Best way to write a DataTable in Worksheet one column at time and preserve NULLS

My current issue relates to performing a SUBTOTAL(3, …) which is COUNTA of the column where I am writing in data. The formula should not counting any column cell with NULL value but the formula is returning counts when there is nothing in the cell. I think that this is related to how I am putting column data into the worksheet.


If anyone is interested in why I am doing things this way, it is because I have a certain order I need the datatable columns written into the spreadsheet.

Here is my method to write data into the worksheet:

private static void PutColumnData(DataTable dataTable, MasterProblem_Fields field, Worksheet worksheet, int column)
{
string[] stringArray = dataTable.Rows.Cast().Select(s => s[field.DBName].ToString()).ToArray();
if (field.ColumnNumberFormat.HasValue)
{
var arrayList = new ArrayList();
foreach (string value in stringArray)
{
if (string.IsNullOrEmpty(value))
{
arrayList.Add(null);
}
else
{
try
{
arrayList.Add((AsposeNumberFormats) field.ColumnNumberFormat.Value == AsposeNumberFormats.General ? int.Parse(value) : decimal.Parse(value));
}
catch (FormatException formatException)
{
throw new FormatException(formatException.Message + string.Format(" Failed for Field: {0} and value: {1}", field.DBName, value));
}
}
}
worksheet.Cells.ImportArrayList(arrayList, FirstDataRow, column, true);
}
else
{
worksheet.Cells.ImportArray(stringArray, FirstDataRow, column, true);
}
SetColumnFormula(field, worksheet, column, dataTable.Rows.Count);
}

My problem is here:

string[] stringArray = dataTable.Rows.Cast().Select(s => s[field.DBName].ToString()).ToArray();

That produces empty string for null entries.

My final code to fix the problem:

private static void PutColumnData(DataTable dataTable, MasterProblem_Fields field, Worksheet worksheet, int column)
{
object[] objectsArray = dataTable.Rows.Cast().Select(s => s[field.DBName]).ToArray();
var arrayList = new ArrayList(objectsArray.Count());
for (int i = 0; i < objectsArray.Count(); i++)
{
if (objectsArray[i] == null || string.IsNullOrEmpty(objectsArray[i].ToString()))
{
arrayList.Add(null);
}
else if (field.ColumnNumberFormat.HasValue)
{
try
{
arrayList.Add((AsposeNumberFormats) field.ColumnNumberFormat.Value == AsposeNumberFormats.General ? int.Parse(objectsArray[i].ToString()) : decimal.Parse(objectsArray[i].ToString()));
}
catch (FormatException formatException)
{
throw new FormatException(formatException.Message + string.Format(" Failed for {0} item in object array for Field: {1} and value: {2}", i, field.DBName, objectsArray[i]));
}
}
else
{
arrayList.Add(objectsArray[i].ToString());
}
}
worksheet.Cells.ImportArrayList(arrayList, FirstDataRow, column, true);
SetColumnFormula(field, worksheet, column, dataTable.Rows.Count);
}

Hi Pouya,

Thanks for your posting and using Aspose.Cells.

It is good to know that you were able to sort out this issue. Hopefully, your sample code will be helpful for other users too. Thanks for sharing it. Let us know if you encounter any other issue, we will be glad to look into it and help you further.