This project has moved. For the latest updates, please go here.

Support for QueryTable Part

May 24, 2012 at 4:40 PM

Is there any plan to add support for the QueryTablePart? 

I have a specific need for this functionality.  If you store numbers in Excel as text it will show little green arrows in the cell with validation errors showing "Number stored as Text".  Because the column in the spreadsheet contains both numbers and text I want all of the data stored as text. 

The way to get around this is to setup a named range and a querytable that references the named range.  That way Excel things the data is from an external source and doesn't validate the data and show the green arrows.  I can get the named range setup with no problem with the ClosedXml API, but I cannot create the QueryTable with the API.

Jun 1, 2012 at 4:38 PM

Is there any way to add this as a feature request?  I can submit the code on how I accomplish this if you want to use the code I use.

Coordinator
Jun 1, 2012 at 5:36 PM

How did you solve the problem?

Jun 1, 2012 at 7:27 PM

The code that I use is below.  The MemoryStream below (ms) contains the ClosedXml document that I've created first.  I also created a named range in my worksheet named ExternalData_1 which is what I'm referencing below.

// Add in the QueryTable (this is used to prevent the smart tags from showing that warn about numbers being displayed as text)
using (SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true))
{
// Create Connection Part
ConnectionsPart connectionsPart = document.WorkbookPart.AddNewPart<ConnectionsPart>();
Connections connections = new Connections();

Connection connection = new Connection()
			{
			    Id = 1,
			    Name = "Junk",
			    Type = 6,
			    RefreshedVersion = 1,
			    Background = true,
			    SaveData = true
			};

TextProperties textProperties = new TextProperties() { SourceFile = @"C:\Junk.htm" };

// The text fields are required or the document will be considered invalid by excel
TextFields textFields = new TextFields() { Count = 4};

for (int i = 0; i < 4; i++)
{
    textFields.Append(new TextField());
}

textProperties.Append(textFields);

connection.Append(textProperties);
connections.Append(connection);
connectionsPart.Connections = connections;

// Create Query Table Part
QueryTablePart queryTablePart = document.WorkbookPart.WorksheetParts.First().AddNewPart<QueryTablePart>();

queryTablePart.QueryTable = new QueryTable()
			    {
				Name = "ExternalData_1",
				ConnectionId = connection.Id,
				AutoFormatId = 16,
				ApplyFontFormats = true,
				ApplyPatternFormats = true
			    };

document.WorkbookPart.Workbook.Save();
}