Print lists in a list to excel, randomly stops printing after 0.5-6 lists. Comexpection 0x800AC472

Question

First of all I have have some codes which is filling some lists of strings, after that it puts all the lists in 1 big list. Now when I want to print those lists in my excel sheets, it stops after pint half of the first list, or stops after print 5 and a half list, and it says: Exception from HRESULT: 0x800AC472. There are already 30 tabs in the excel doc, so that is not the problem. Feel free to rename this title, Ihad no idea how to call this problem.

This is how I print my lists in excel:

for (int i = 0; i < ListofLists.Count; i++)
{
      for (int j = 1; j <= ListofLists[i].Count; j++)
      {
         excelDataHandler.excel_setValue("A" + j, ListofLists[i][j-1], "", (i+1));
         //A = cell, data of list, color of cell, sheetnumber
      }
}

excel_setValue method:

public void excel_setValue(string cellname, string value, string color, int workSheet)
        {
            ((Microsoft.Office.Interop.Excel._Worksheet)newWorkbook_First.Sheets[workSheet]).get_Range(cellname).set_Value(Type.Missing, value);
            if (color == "red")
            {
                newSheets.get_Range(cellname).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            }
        }

I would appriciate any help with this problem. Thanks in asvance!

Answer 1

Here is my reflection code.

   public static DataTable ClassToDataTable<T>() where T : class
    {
        Type classType = typeof(T);

        List<PropertyInfo> propertyList = classType.GetProperties().ToList();
        if (propertyList.Count < 1)
        {
            return new DataTable();
        }

        string className = classType.UnderlyingSystemType.Name;
        DataTable result = new DataTable(className);

        foreach (PropertyInfo property in propertyList)
        {
            DataColumn col = new DataColumn();
            col.ColumnName = property.Name;

            Type dataType = property.PropertyType;

            if (IsNullable(dataType))
            {
                if (dataType.IsGenericType)
                {
                    dataType = dataType.GenericTypeArguments.FirstOrDefault();
                }
            }
            else
            {   // True by default
                col.AllowDBNull = false;
            }

            col.DataType = dataType;

            result.Columns.Add(col);
        }

        return result;
    }

    public static DataTable ClassListToDataTable<T>(List<T> ClassList) where T : class
    {
        DataTable result = ClassToDataTable<T>();

        if (result.Columns.Count < 1)
        {
            return new DataTable();
        }
        if (ClassList.Count < 1)
        {
            return result;
        }

        foreach (T item in ClassList)
        {
            ClassToDataRow(ref result, item);
        }

        return result;
    }

    public static void ClassToDataRow<T>(ref DataTable Table, T Data) where T : class
    {
        Type classType = typeof(T);
        string className = classType.UnderlyingSystemType.Name;

        // Checks that the table name matches the name of the class. 
        // There is not required, and it may be desirable to disable this check.
        // Comment this out or add a boolean to the parameters to disable this check.
        if (!Table.TableName.Equals(className))
        {
            return;
        }

        DataRow row = Table.NewRow();
        List<PropertyInfo> propertyList = classType.GetProperties().ToList();

        foreach (PropertyInfo prop in propertyList)
        {
            if (Table.Columns.Contains(prop.Name))
            {
                if (Table.Columns[prop.Name] != null)
                {
                    row[prop.Name] = prop.GetValue(Data, null);
                }
            }
        }
        Table.Rows.Add(row);
    }

    public static bool IsNullable(Type Input)
    {
        if (!Input.IsValueType) return true; // Is a ref-type, such as a class
        if (Nullable.GetUnderlyingType(Input) != null) return true; // Nullable
        return false; // Must be a value-type
    }