none
Generating dynamic excel spreadsheet with WebQuery using OpenXML SDK RRS feed

  • Question

  • Hi,

    I have generated an excel workbook using OpenXML. This uses data from DB for sheet data and column validations.

    I want to make this excel document a dynamic one so the user can refresh any time to get latest values for those columns.

    I am aware this can be done using Web Queries (Connection part) which needs inputs like URL and POST data string. I have created a webpage which will return data to excel document on refresh.

    Can I return my excel document or sheet data as is so that I wont need to look at validations again?

    How can I reuse the excel generation code for dynamically pulling the data?

    Thanks,

    Tushar

    Tuesday, November 4, 2014 4:47 AM

Answers

  • Hi Tushar,

    You could use OpenXML to  create the workbook with QueryTable and a WebQuery connection inside it. In this way, when the user opens the workbook, right click the QueryTable and choose Refresh, it'll automatically download the newest data from the web page. To format the data, you could create <table> elements in the web page. See this sample code:

    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\test.xlsx", SpreadsheetDocumentType.Workbook))
                {
                    // Add a WorkbookPart to the document.
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
    
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
    
                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    
                    ConnectionsPart connPart = workbookpart.AddNewPart<ConnectionsPart>();
                    connPart.Connections = new Connections();
    
                    Connection c = new Connection()
                    {
                        Id = 1,
                        Name = "Connection",
                        Type = 4, //Web Query
                        RefreshedVersion = 5,
                        MinRefreshableVersion = 1,
                        Background = true,
                        SaveData = true,
                        RefreshOnLoad = true,
                        WebQueryProperties = new WebQueryProperties()
                        {
                            XmlSource = true,
                            SourceData = true,
                            ParsePreTag = true,
                            Consecutive = true,
                            RefreshedInExcel2000 = true,
                            Url = "http://localhost:7925/"
                        }
                    };
                    connPart.Connections.Append(c);
    
                    QueryTablePart qt = worksheetPart.AddNewPart<QueryTablePart>();
                    qt.QueryTable = new QueryTable()
                    {
                        Name = "Connection",
                        ConnectionId = c.Id,
                        AutoFormatId = 16,
                        ApplyNumberFormats = true,
                        ApplyBorderFormats = true,
                        ApplyFontFormats = true,
                        ApplyPatternFormats = true,
                        ApplyAlignmentFormats = false,
                        ApplyWidthHeightFormats = false
                    };
    
                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "mySheet"
                    };
                    sheets.Append(sheet);
                    //sheets.Append(qt.QueryTable);
    
    
                    DefinedNames definedNames = new DefinedNames();
                    // Create a new range (name matching the QueryTable name) 
                    DefinedName definedName = new DefinedName() { Name = "Connection", Text = "mysheet!$A$1:$A$1" };
                    definedNames.Append(definedName);
                    workbookpart.Workbook.Append(definedNames);
    
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    spreadsheetDocument.Close();
                }

    This code creates a new workbook with a QueryTable, the QueryTable uses a new Connection whose URL is the web page.


    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.

    • Marked as answer by deva_d Wednesday, November 12, 2014 1:19 PM
    Wednesday, November 5, 2014 8:16 AM
    Moderator

All replies

  • Hi Tushar,

    You could use OpenXML to  create the workbook with QueryTable and a WebQuery connection inside it. In this way, when the user opens the workbook, right click the QueryTable and choose Refresh, it'll automatically download the newest data from the web page. To format the data, you could create <table> elements in the web page. See this sample code:

    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(@"C:\test.xlsx", SpreadsheetDocumentType.Workbook))
                {
                    // Add a WorkbookPart to the document.
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
    
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
    
                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    
                    ConnectionsPart connPart = workbookpart.AddNewPart<ConnectionsPart>();
                    connPart.Connections = new Connections();
    
                    Connection c = new Connection()
                    {
                        Id = 1,
                        Name = "Connection",
                        Type = 4, //Web Query
                        RefreshedVersion = 5,
                        MinRefreshableVersion = 1,
                        Background = true,
                        SaveData = true,
                        RefreshOnLoad = true,
                        WebQueryProperties = new WebQueryProperties()
                        {
                            XmlSource = true,
                            SourceData = true,
                            ParsePreTag = true,
                            Consecutive = true,
                            RefreshedInExcel2000 = true,
                            Url = "http://localhost:7925/"
                        }
                    };
                    connPart.Connections.Append(c);
    
                    QueryTablePart qt = worksheetPart.AddNewPart<QueryTablePart>();
                    qt.QueryTable = new QueryTable()
                    {
                        Name = "Connection",
                        ConnectionId = c.Id,
                        AutoFormatId = 16,
                        ApplyNumberFormats = true,
                        ApplyBorderFormats = true,
                        ApplyFontFormats = true,
                        ApplyPatternFormats = true,
                        ApplyAlignmentFormats = false,
                        ApplyWidthHeightFormats = false
                    };
    
                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "mySheet"
                    };
                    sheets.Append(sheet);
                    //sheets.Append(qt.QueryTable);
    
    
                    DefinedNames definedNames = new DefinedNames();
                    // Create a new range (name matching the QueryTable name) 
                    DefinedName definedName = new DefinedName() { Name = "Connection", Text = "mysheet!$A$1:$A$1" };
                    definedNames.Append(definedName);
                    workbookpart.Workbook.Append(definedNames);
    
                    workbookpart.Workbook.Save();
    
                    // Close the document.
                    spreadsheetDocument.Close();
                }

    This code creates a new workbook with a QueryTable, the QueryTable uses a new Connection whose URL is the web page.


    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.

    • Marked as answer by deva_d Wednesday, November 12, 2014 1:19 PM
    Wednesday, November 5, 2014 8:16 AM
    Moderator
  • Thank you for your reply.

    I could create the connection part and get the data on refresh from webpage which has HTML table.

    I was wondering if there is any way by which I can send entire datasheet without formatting it into HTML. Is this possible? It will save my work creating HTML tables.

    Thanks again.

    Tushar

    Wednesday, November 5, 2014 12:36 PM
  • You said the data is from DB, it's easy to retrieve the data from the DB from a web application. But why you want to send a datasheet from a webpage to the user?  Can you make it clear what exactly you want?

    If you mean you want to do all the things on the server side(including generating the Excel file, creating Connection part to connect to the DB), let the user download the file from the webpage, you can change the Connection type to 5(OLE DB) to connect to the Database.


    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.

    Thursday, November 6, 2014 5:16 AM
    Moderator
  • Let me clarify.

    - User can download static or dynamic excel from web.

    - I have written code for creating static as well as dynamic sheets

    My problem is, if I already have code for generating static sheet, why should I create HTML table/ XML for web query? How can I reuse the code I have for generating static sheets? Can I update the dynamic excel on refresh with the sheet I create for static because it has the same data but live.

    Note: I can use only web query.

    Thanks again!

    Tushar


    • Edited by deva_d Thursday, November 6, 2014 6:52 AM
    Thursday, November 6, 2014 6:51 AM
  • Your saying is contradictory. The data in the "static sheet" is static, how could the data in the "dynamic sheet" update based on static data?

    If you can only use web query, the only way I can think of is to create a web page which could provide dynamic data for the worksheet. Or else you need to use the worksheet to connect to the database directly.


    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.

    Thursday, November 6, 2014 10:30 AM
    Moderator