locked
Create dynamic excel worksheet RRS feed

  • Question

  • Hi

    I have more than twenty tables in Sql Database and each table has different metadata. for example

    Select E_id, E_name from emp;
    Select stud_Add, stud_zip from stud;
    Select PinNo, state from address;

    I want to execute all the tables and load the data to single excel file with three different sheet and the sheet name is table name.

    Please help me to solve this. Its too urgent.

    Thanks in advance ...

    • Moved by Amanda Zhu Monday, December 29, 2014 2:02 AM
    Friday, December 26, 2014 2:15 PM

Answers

  • Hi Hussain1977,

    Based on the description, you want to create a dynamic workbook based on the data.

    If you are not working on the server environment, you can consider using Excel PIAs to automate Excel application to achieve the goals. Here are some helpful links for your reference about this solution:
    How to automate Microsoft Excel from Microsoft Visual C#.NET

    Add worksheets via Worksheets.Add method, loop the values get from SQL server and set the value cell by cell via Range.Value. And here is a sample for your reference:

     class InsertRecord
        {
            public void Main()
            {
    
              
                string tagertFilePath = @"C:\Users\UserName\Desktop\workbook_B.xlsx";
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook targetWorkBook = excelApp.Workbooks.Add();
                Excel.Worksheet targetWorkSheet = targetWorkBook.Sheets.Add();
                targetWorkSheet.Name = "tableName";
    
                // Create a new DataTable.
                System.Data.DataTable table = new DataTable("ParentTable");
                MakeParentTable(table);
                int rowIndex = 1;
                int columnIndex = 0;
                //copy table header
                for (columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
                {
                    targetWorkSheet.Cells[rowIndex, columnIndex+1].Value = table.Columns[columnIndex].ColumnName;
                }
                //copy data
                rowIndex = 2;
                columnIndex = 0;
                foreach (DataRow dr in table.Rows)
                { 
                   for(columnIndex=0;columnIndex<table.Columns.Count;columnIndex++)
                   {
                       targetWorkSheet.Cells[rowIndex, columnIndex+1].Value = dr[columnIndex];
                   }
                   rowIndex++;
                }
              
                targetWorkBook.SaveAs(tagertFilePath);
                targetWorkBook.Save();
                targetWorkBook.Close();
                excelApp.Quit();
            }
    
            private void MakeParentTable(DataTable table)
            {
              
                // Declare variables for DataColumn and DataRow objects.
                DataColumn column;
                DataRow row;
    
                // Create new DataColumn, set DataType, 
                // ColumnName and add to DataTable.    
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.Int32");
                column.ColumnName = "id";
                column.ReadOnly = true;
                column.Unique = true;
                // Add the Column to the DataColumnCollection.
                table.Columns.Add(column);
    
                // Create second column.
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "ParentItem";
                column.AutoIncrement = false;
                column.Caption = "ParentItem";
                column.ReadOnly = false;
                column.Unique = false;
                // Add the column to the table.
                table.Columns.Add(column);
    
                // Make the ID column the primary key column.
                DataColumn[] PrimaryKeyColumns = new DataColumn[1];
                PrimaryKeyColumns[0] = table.Columns["id"];
                table.PrimaryKey = PrimaryKeyColumns;
    
    
                // Create three new DataRow objects and add 
                // them to the DataTable
                for (int i = 0; i <= 2; i++)
                {
                    row = table.NewRow();
                    row["id"] = i;
                    row["ParentItem"] = "ParentItem " + i;
                    table.Rows.Add(row);
                }
            }
    
        }

    You also can get more samples from link below:
    How do I... (Excel 2013 developer reference)

    If you are working on the server environment, you can use Open XML to achieve the goal. Here are some helpful links for your reference:
    Getting started with the Open XML SDK 2.5 for Office

    How to: Insert a new worksheet into a spreadsheet document (Open XML SDK)

    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 29, 2014 8:38 AM

All replies

  • Hi Hussain1977,

    Based on your scenario, I moved this thread to Excel for Developers forum which is a better forum for your question. You will get better solutions there.

    Thanks,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 29, 2014 2:03 AM
  • Hi Hussain1977,

    Based on the description, you want to create a dynamic workbook based on the data.

    If you are not working on the server environment, you can consider using Excel PIAs to automate Excel application to achieve the goals. Here are some helpful links for your reference about this solution:
    How to automate Microsoft Excel from Microsoft Visual C#.NET

    Add worksheets via Worksheets.Add method, loop the values get from SQL server and set the value cell by cell via Range.Value. And here is a sample for your reference:

     class InsertRecord
        {
            public void Main()
            {
    
              
                string tagertFilePath = @"C:\Users\UserName\Desktop\workbook_B.xlsx";
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                Excel.Workbook targetWorkBook = excelApp.Workbooks.Add();
                Excel.Worksheet targetWorkSheet = targetWorkBook.Sheets.Add();
                targetWorkSheet.Name = "tableName";
    
                // Create a new DataTable.
                System.Data.DataTable table = new DataTable("ParentTable");
                MakeParentTable(table);
                int rowIndex = 1;
                int columnIndex = 0;
                //copy table header
                for (columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
                {
                    targetWorkSheet.Cells[rowIndex, columnIndex+1].Value = table.Columns[columnIndex].ColumnName;
                }
                //copy data
                rowIndex = 2;
                columnIndex = 0;
                foreach (DataRow dr in table.Rows)
                { 
                   for(columnIndex=0;columnIndex<table.Columns.Count;columnIndex++)
                   {
                       targetWorkSheet.Cells[rowIndex, columnIndex+1].Value = dr[columnIndex];
                   }
                   rowIndex++;
                }
              
                targetWorkBook.SaveAs(tagertFilePath);
                targetWorkBook.Save();
                targetWorkBook.Close();
                excelApp.Quit();
            }
    
            private void MakeParentTable(DataTable table)
            {
              
                // Declare variables for DataColumn and DataRow objects.
                DataColumn column;
                DataRow row;
    
                // Create new DataColumn, set DataType, 
                // ColumnName and add to DataTable.    
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.Int32");
                column.ColumnName = "id";
                column.ReadOnly = true;
                column.Unique = true;
                // Add the Column to the DataColumnCollection.
                table.Columns.Add(column);
    
                // Create second column.
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.String");
                column.ColumnName = "ParentItem";
                column.AutoIncrement = false;
                column.Caption = "ParentItem";
                column.ReadOnly = false;
                column.Unique = false;
                // Add the column to the table.
                table.Columns.Add(column);
    
                // Make the ID column the primary key column.
                DataColumn[] PrimaryKeyColumns = new DataColumn[1];
                PrimaryKeyColumns[0] = table.Columns["id"];
                table.PrimaryKey = PrimaryKeyColumns;
    
    
                // Create three new DataRow objects and add 
                // them to the DataTable
                for (int i = 0; i <= 2; i++)
                {
                    row = table.NewRow();
                    row["id"] = i;
                    row["ParentItem"] = "ParentItem " + i;
                    table.Rows.Add(row);
                }
            }
    
        }

    You also can get more samples from link below:
    How do I... (Excel 2013 developer reference)

    If you are working on the server environment, you can use Open XML to achieve the goal. Here are some helpful links for your reference:
    Getting started with the Open XML SDK 2.5 for Office

    How to: Insert a new worksheet into a spreadsheet document (Open XML SDK)

    How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 29, 2014 8:38 AM
  • Please see this.

    http://datapigtechnologies.com/ExcelExplosion.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, December 29, 2014 8:43 PM