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);
}