locked
Create Excel Columns Dynamically in C# RRS feed

  • Question

  • User1655374113 posted

    Hello,

    I am creating a excel using namespace "Microsoft.Office.Interop.Excel"

    I have list of users that I am getting from Database. That users list will change frequently.

    How can I create column for each user ?

    Thursday, June 19, 2014 12:51 PM

Answers

  • User103196646 posted

    Hello! Thanks for your post! Here's how to create a column for each user:

    var i = 1;
    foreach (DataRow dr in dt.Rows)
    {
      oSheet.Cells[1, i] = dr[i - 1].ToString();
      i++;
    }

    using Excel = Microsoft.Office.Interop.Excel; 
    using System.Reflection;
    
    Excel.Application oXL; 
    Excel.Workbook oWB; 
    Excel.Worksheet oSheet; 
    Excel.Range oRange; 
    
    // Start Excel and get Application object. 
    oXL = new Excel.Application(); 
    
    // Set some properties 
    oXL.Visible = true; 
    oXL.DisplayAlerts = false; 
    
    // Get a new workbook. 
    oWB = oXL.Workbooks.Add(Missing.Value); 
    
    // Get the active sheet 
    oSheet = (Excel.Worksheet)oWB.ActiveSheet ; 
    oSheet.Name = "Users"; 
    
    // Process the DataTable 
    // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE 
    DataTable dt = Customers.RetrieveAsDataTable(); 
    
    var i = 1;
    foreach (DataRow dr in dt.Rows)
    {
      oSheet.Cells[1, i] = dr[i - 1].ToString();
      i++;
    } // Resize the columns oRange = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[rowCount, dt.Columns.Count]); oRange.EntireColumn.AutoFit(); // Save the sheet and close oSheet = null; oRange = null; oWB.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Close(Missing.Value, Missing.Value, Missing.Value); oWB = null; oXL.Quit(); // Clean up // NOTE: When in release mode, this does the trick GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

    Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 19, 2014 1:04 PM