Export Windows forms datagridview to excel power pivot table with C#

Question

i have a Windows form that generates reports by a series of filters, and it can be exported to multiple formats, one of them Excel, but now we want to have predefined Excel power view templates that requires data from a table on Excel, when i export my data to Excel it is like a new one, but i need it to clear and fill the existing table, and them when the user go to the power view sheet, he can see the dynamic tables, maps, and everything that is predifined, with his new data

i though on exporting my datagrid to an XML file and the excel file would have a load event that will clear the current data and them fill the table with the XML file that will have a persistent directory

i wanted to know if there is a better way to do this, cause i cant find any solutions, thanks

Answer 1

im using a XML generated by the windows form, linked to my Excel table as source

using this method

public static void SerializeObject(this List<SPSR_ReporteSabanasxOrdenes_Todos_Result> list, string fileName)
    {
        var serializer = new XmlSerializer(typeof(List<SPSR_ReporteSabanasxOrdenes_Todos_Result>));
        using (var stream = File.OpenWrite(fileName))
        {
            serializer.Serialize(stream, list);
        }
    }

Answer 2

OP: Your task description is a bit unclear and lacks details, so I am giving this example of DataGrid content export to Microsoft Excel procedure:

public bool Export2Excel(DataTable dataTable)
{
    object misValue = System.Reflection.Missing.Value;

    Microsoft.Office.Interop.Excel.Application _appExcel = null;
    Microsoft.Office.Interop.Excel.Workbook _excelWorkbook = null;
    Microsoft.Office.Interop.Excel.Worksheet _excelWorksheet = null;
    try
    {

        if (dataTable.Rows.Count <= 0) { throw new ArgumentNullException("Table is Empty"); }

        // excel app object
        _appExcel = new Microsoft.Office.Interop.Excel.Application();

        // excel workbook object added to app
        _excelWorkbook = _appExcel.Workbooks.Add(misValue);
        _excelWorksheet = _appExcel.ActiveWorkbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

        // column names row (range obj)
        Microsoft.Office.Interop.Excel.Range _columnsNameRange;
        _columnsNameRange = _excelWorksheet.get_Range("A1", misValue).get_Resize(1, dataTable.Columns.Count);

        // column names array to be assigned to _columnNameRange
        string[] _arrColumnNames = new string[dataTable.Columns.Count];

        // set Excel columns NumberFormat property
        // note; most important for decimal-currency, DateTime
        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            // array of column names
            _arrColumnNames[i] = dataTable.Columns[i].ColumnName;

            string _strType = dataTable.Columns[i].DataType.FullName.ToString();
            switch (_strType)
            {
                case "System.DateTime":
                {
                    _excelWorksheet.Range["A1"].Offset[misValue, i].EntireColumn.NumberFormat = "MM/DD/YY";
                    break;
                }
                case "System.Decimal":
                {
                    _excelWorksheet.Columns["A"].Offset[misValue, i].EntireColumn.NumberFormat = "$ #,###.00";
                    break;
                }
                case "System.Double":
                {
                    _excelWorksheet.Columns["A"].Offset[misValue, i].EntireColumn.NumberFormat = "#.#";
                    break;
                }
                case "System.Int8":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                {
                    // use general format for int
                    //_excelWorksheet.Columns["A"].Offset[misValue, i].EntireColumn.NumberFormat = "####";
                    break;
                }
                default: break;
            }
        }

        // assign array to column headers range, make 'em bold
        _columnsNameRange.set_Value(misValue, _arrColumnNames);
        _columnsNameRange.Font.Bold = true;

        // populate data content row by row
        for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)
        {
            _excelWorksheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =
            dataTable.Rows[Idx].ItemArray;
        }

        // Autofit all Columns in the range
        _columnsNameRange.Columns.EntireColumn.AutoFit();

        // quit excel app process
        if (_appExcel != null)
        {
            _appExcel.UserControl = false;
            _appExcel.Quit();
        }
        return true;
    }
    catch {  throw; }
    finally
    {
        _excelWorksheet = null;
        _excelWorkbook = null;
        _appExcel = null;
        misValue = null;
    }
}
#endregion

}

You can customize it for your purpose.

Also, you can see how DataGrid Export to MS Excel works in actual demo application (link: http://www.shopdigit.com/PaydayNY-2014P-Pro-for-Win-P14-2-02P.htm).

Hope this may help. Best regards,