62403

Using OpenXML, how can I associate a list for data validation

Question:

I am processing an .xlsm file and need to know how to use a list on another sheet for data validation using openXML and C#.

To start, I have a .xlsm file with two empty sheets and macros in it. In my program I open the file, Create the column header on Sheet1 then create the validation list on sheet2. So, after I run my program Sheet1 "A1" contains the text "Color" and Sheet2 "A1:A4" contains "Blue","Green","Red","Yellow". I get this far just fine.

I would like to make it so there is a dropdown list in all cells of column "A" on sheet1 that contains each of the 4 colors and enforces them as the only input. In Microsoft Excel this is done by going to the "Data" tab, selecting "Data Validation" selecting "List" and highlighting the cells you want to use. I need to make this association programmatically.

The (Desired) XML that Microsoft Excel creates if I do it manually is this:

<extLst> <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"> <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"> <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1"> <x14:formula1> <xm:f>'Validation Data'!$A$1:$A$4</xm:f> </x14:formula1> <xm:sqref>A1:A1048576</xm:sqref> </x14:dataValidation> </x14:dataValidations> </ext> </extLst>

The following method and results is something I tried. It may give a better Idea of what I'm trying to do.

Here, I pass in "'Sheet2'!$A$1:$A$4" as the "validationListCells" parameter. This represents the cells in "Sheet2" that, in this example, would contain the color names "Red", "Green"...etc.

I pass in "A2:A1048576" as the "cellsToValidate" parameter. This represents all cells of Sheet1 column "A", on which I want to enforce validation.

I pass "Sheet1" as the worksheetName parameter.

private void InsertValidation(String worksheetName, String validationListCells, String cellsToValidate) { DataValidations dataValidations1 = new DataValidations() { Count = (UInt32Value)1U }; DataValidation dataValidation1 = new DataValidation() { Formula1 = new Formula1(validationListCells), Type = DataValidationValues.List, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = cellsToValidate } }; dataValidations1.Append(dataValidation1); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(_documentPath, true)) { WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, worksheetName); worksheetPart.Worksheet.Append(dataValidations1); worksheetPart.Worksheet.Save(); } }

It results in this XML in Sheet1.xml. Which causes an error in Excel.

<x:dataValidations count="1"> <x:dataValidation type="list" showInputMessage="1" showErrorMessage="1" sqref="A2: A1048576"> <x:formula1>'Sheet2'!$A$1:$A$5</x:formula1> </x:dataValidation> </x:dataValidations>

It looks like I may be on the right track since it is beginning to resemble the xml created by Excel, but I'm completely new to openXML and I'm finding little about this topic on the net.

Thanks in advance!

Answer1:

For anyone else in need of this..the code below worked for me. I put in there user3251089's variable names.

In general, when I try to programmatically create an excel "feature" I manually make a really basic excel that has in it that feature (delete extra sheets too). Then I reflect the code and try to make it prettier.

hope it serves to someone!

using Excel = DocumentFormat.OpenXml.Office.Excel; using X14 = DocumentFormat.OpenXml.Office2010.Excel;

.....

Worksheet worksheet = worksheetPart.Worksheet; WorksheetExtensionList worksheetExtensionList = new WorksheetExtensionList(); WorksheetExtension worksheetExtension = new WorksheetExtension() { Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" }; worksheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.DataValidations dataValidations = new X14.DataValidations() { Count = (UInt32Value)3U }; dataValidations.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); //sites validation dataValidations.Append(new X14.DataValidation() { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, DataValidationForumla1 = new X14.DataValidationForumla1() { Formula = new Excel.Formula(validationListCells) }, ReferenceSequence = new Excel.ReferenceSequence(cellsToValidate) }); worksheetExtension.Append(dataValidations); worksheetExtensionList.Append(worksheetExtension); worksheet.Append(worksheetExtensionList); worksheet.Save();

Recommend

  • Using OpenXML, how can I associate a list for data validation
  • VBA Range 255 Character Limit [duplicate]
  • string to byte then byte[] 0xformat
  • Understanding bomb lab
  • ASCII 7x5 side-feeding characters for led modules
  • ftp import zip file with csv data, getting “string contains null byte”
  • rownames and colnames with specific value
  • How to download ms word docx file in python with raw data from http url
  • Python Sqlite3 Database table isn't being updated
  • Impossible to use this SDK is it possible that its unstable SDK?
  • While loop in Matlab to increment a number
  • Scheme getting error is not a function
  • Dual RC522 on Orange PI
  • Angular: Binding to a observable property of service -> value doesn't update
  • R grep regular expression using elements in a vector
  • Getting States and Provinces in .NET
  • Flask-Restful error: “as_view” method not inherited
  • Visual Studio 2008 and JavaScript brackets formatting
  • composer dependency stating in doesn't have php-xsl
  • Splash Screen will not display
  • Is it possible to run clang with llc flags
  • Android Google Maps API v2 start navigation
  • JBoss External Properties Files in Classpath
  • System.InvalidCastException: Specified cast is not valid
  • How to use JavaScript to determine whether a file exists in a directory?
  • Android - Material Design - NavigationView - How to put vertical scroll?
  • Dialing with Intent.ACTION_CALL stopps at # in phone number
  • Pass value from viewmodel to script in zk
  • SetUp method failed while running tests from teamcity
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?