Having trouble creating data validation items in XLS/XLSX- but not Spreadsheet ML

RMTTeam · August 31, 2009, 12:03pm

We are evaluating ASPOSE along with 3 other tools, and I created a test that each one needs to pass. Here is the code we are using... we are using the adventureworks database. What we expect to be able to do is to go A2, Choose "Have State" and go to F2 and choose "Green", "Yellow" or "Red Light".

XLS/XLSX do not work properly, but spreadsheet ML does. I've attached all 3 as files.

default.aspx.cs

protected void Page_Load(object sender, EventArgs e)

{

OurWorkbook wb = new OurWorkbook();

//this works fine, except that you literally

//have to go into data validation and click ok for the dropdown to come in the F column

//(Choose "Has Entry" in A first.)

wb.ReturnWorkbook().Save(@"c:\ASPOSE.xls", FileFormatType.Excel2003);

//this saves fine, but cannot open in Excel 2007 because of the data validation because you get the

// message

wb.ReturnWorkbook().Save(@"c:\ASPOSE.xlsx", FileFormatType.Excel2007Xlsx);

wb.ReturnWorkbook().Save(@"c:\ASPOSE.csv", FileFormatType.CSV);

//this works EXACTLY right.

//(Choose "Has Entry" in A first.)

wb.ReturnWorkbook().Save(@"c:\ASPOSE.xml", FileFormatType.SpreadsheetML);

this.Dispose();

}

Workbook.cs

public class OurWorkbook : Aspose.Cells.Workbook

{

private Aspose.Cells.Workbook _this = new Aspose.Cells.Workbook();

public Aspose.Cells.Workbook ReturnWorkbook()

{

return _this;

}

public OurWorkbook()

{

//

// TODO: Add constructor logic here

//

_this.Worksheets.Clear(); //Necessary, it puts a sheet1 in by default.

_this.Worksheets.Add("DataEntry");

_this.Worksheets.Add("Lookup");

_this.Worksheets.Add("Errors");

_this.Worksheets.Add("Instructions");

_this.Worksheets.Add("PivotChart");

_this.Worksheets.Add("PivotTable");

_this.Worksheets.Add("PivotData");

//Hide pivotdata and lookup

_this.Worksheets[6].IsVisible = false;

//_this.Worksheets["Lookup"].IsVisible = false;

//default font arial 10

_this.DefaultStyle.Font.Name = "Arial";

_this.DefaultStyle.Font.Size = 10;

_this.Worksheets[5].CustomProperties.Add("_MyRenderer2", "ASPOSE.Cells");//this did not work

_this.Worksheets.CustomDocumentProperties.Add("_MyRenderer", "ASPOSE.Cells");

_this.Worksheets.BuiltInDocumentProperties.Company = "Mr. Intel Corporation";

_this.Worksheets["Instructions"].Cells["A2"].PutValue("The quick brown fox is to jump over the lazy dogs.");

//Add the hyperlink style

int styleIndex = _this.Styles.Add();

_this.Styles[styleIndex].Name = "Hyperlink";

//this didn't work

//_this.Styles["Hyperlink"].ForegroundColor = System.Drawing.Color.Red;

//but this did

_this.Styles["Hyperlink"].Font.Color = System.Drawing.Color.Red;

_this.Styles[styleIndex].Font.Underline = FontUnderlineType.Double;

_this.Worksheets["Instructions"].Hyperlinks.Add("B7", 1, 1, "DataEntry!B2");

_this.Worksheets["Instructions"].Cells["B7"].PutValue("Go Enter Data!!");

_this.Worksheets["Instructions"].Cells["B7"].SetStyle(_this.Styles["Hyperlink"]);

//Load stuff from dataset

Cells cells = _this.Worksheets["DataEntry"].Cells;

DataSet ds = GetAdventureWorksBase();

cells.ImportDataTable(ds.Tables[0], false, "B2");

string[] strArray = new string[] { "Type", "Territory", "Country", "State", "Sales YTD", "Mock Entry" };

cells.ImportObjectArray(strArray, 0, 0, false);

//Style headers and add comment

styleIndex = _this.Styles.Add();

_this.Styles[styleIndex].Name = "ColumnHeaders";

_this.Styles["ColumnHeaders"].Font.Underline = FontUnderlineType.Single;

_this.Styles["ColumnHeaders"].Font.Name = "Century Gothic";

_this.Styles["ColumnHeaders"].Font.Size = 11;

_this.Styles["ColumnHeaders"].HorizontalAlignment = TextAlignmentType.Right;

//Apparently needed when you are doing a range

StyleFlag flag = new StyleFlag();

flag.All = true;

cells.CreateRange("A1", "F1").ApplyStyle(_this.Styles["ColumnHeaders"], flag);

cells["A1"].Style.HorizontalAlignment = TextAlignmentType.Center;

cells["F1"].Style.HorizontalAlignment = TextAlignmentType.Left;

int commentIndex = _this.Worksheets["DataEntry"].Comments.Add("A5");

_this.Worksheets["DataEntry"].Comments[commentIndex].Note = "Kilroy was here!";

//NOTE: No properties to set comment owner

//Decimal points, Autofilter, AutoFit, Freeze Panes

styleIndex = _this.Styles.Add();

_this.Styles[styleIndex].Name = "DecimalStyle";

_this.Styles["ColumnHeaders"].Custom = "$#,##0.000";

flag = new StyleFlag();

flag.All = false;

flag.NumberFormat = true;

cells.CreateRange("A1", "F1").ApplyStyle(_this.Styles[styleIndex], flag);

_this.Worksheets["DataEntry"].AutoFilter.SetRange(0, 0, ds.Tables[0].Columns.Count + 1);

_this.Worksheets["DataEntry"].AutoFitColumns();

_this.Worksheets["DataEntry"].FreezePanes("A2", 1, 0);

//Set lookup tab ranges

ds = GetAdventureWorksValidation();

Worksheet lookup = _this.Worksheets["Lookup"];

strArray = new string[] { "Have State", "No State" };

lookup.Cells.ImportObjectArray(strArray, 0, 0, true);

strArray = new string[] { "Green", "Yellow", "Red Light" };

lookup.Cells.ImportObjectArray(strArray, 1, 1, true);

strArray = new string[] { "HAVESTATE", "HAVESTATE", "HAVESTATE" };

lookup.Cells.ImportObjectArray(strArray, 1, 2, true);

strArray = new string[] { "HAVESTATEGREEN", "HAVESTATEYELLOW", "HAVESTATEREDLIGHT" };

lookup.Cells.ImportObjectArray(strArray, 1, 3, true);

strArray = new string[] { "State", "StateValidator", "StateFullValidator" };

lookup.Cells.ImportObjectArray(strArray, 0, 1, false);

Range range;

range = lookup.Cells.CreateRange("A1", "A2");

range.Name = "HasStateDropDown";

range = lookup.Cells.CreateRange("B2", "B4");

range.Name = "StateListDropDown";

range = lookup.Cells.CreateRange("C2", "C4");

range.Name = "StateValidator";

range = lookup.Cells.CreateRange("D2", "D4");

range.Name = "StateFullValidator";

//Load lookup datasets

lookup.Cells.ImportDataColumn(ds.Tables[0], true, 0, 4, ds.Tables[0].Columns["Territory"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 4, ds.Tables[1].Rows.Count + 1, 1);

range.Name = "Territories";

lookup.Cells.ImportDataColumn(ds.Tables[1], true, 0, 5, ds.Tables[1].Columns["Country"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 5, ds.Tables[1].Rows.Count + 1, 1);

range.Name = "Country";

lookup.Cells.ImportDataColumn(ds.Tables[1], true, 0, 6, ds.Tables[1].Columns["CountryFullValidator"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 6, ds.Tables[1].Rows.Count + 1, 1);

range.Name = "CountryFullValidator";

lookup.Cells[0, 6].PutValue("CountryFullValidator");

lookup.Cells.ImportDataColumn(ds.Tables[1], true, 0, 7, ds.Tables[1].Columns["CountryValidator"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 7, ds.Tables[1].Rows.Count + 1, 1);

range.Name = "CountryValidator";

lookup.Cells[0, 7].PutValue("CountryValidator");

lookup.Cells.ImportDataColumn(ds.Tables[2], true, 0, 8, ds.Tables[2].Columns["State"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 8, ds.Tables[2].Rows.Count + 1, 1);

range.Name = "Province";

lookup.Cells[0, 8].PutValue("Province");

lookup.Cells.ImportDataColumn(ds.Tables[2], true, 0, 9, ds.Tables[2].Columns["StateFullValidator"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 9, ds.Tables[2].Rows.Count + 1, 1);

range.Name = "ProvinceValidator";

lookup.Cells[0, 9].PutValue("ProvinceValidator");

lookup.Cells.ImportDataColumn(ds.Tables[2], true, 0, 10, ds.Tables[2].Columns["StateValidator"].Ordinal, false);

range = lookup.Cells.CreateRange(0, 10, ds.Tables[2].Rows.Count + 1, 1);

range.Name = "ProvinceFullValidator";

lookup.Cells[0, 10].PutValue("ProvinceFullValidator");

//Fix 'state' headers

strArray = new string[] { "Province", "ProvinceValidator", "ProvinceFullValidator" };

lookup.Cells.ImportObjectArray(strArray, 0, 8, false);

//Set data entry conditional formats

int formatIndex = _this.Worksheets["DataEntry"].ConditionalFormattings.Add();

CellArea formatRange = new CellArea();

formatRange.StartRow = 1;

formatRange.StartColumn = 5;

formatRange.EndRow = 65535;

formatRange.EndColumn = 5;

int styleIndexYellowAndBlack = _this.Styles.Add();

Style yellowAndBlack = _this.Styles[styleIndexYellowAndBlack];

yellowAndBlack.Name = "yellowandblack";

yellowAndBlack.BackgroundColor = System.Drawing.Color.Yellow;

yellowAndBlack.ForegroundColor = System.Drawing.Color.Black;

yellowAndBlack.Pattern = BackgroundType.DiagonalStripe;

int styleIndexBlackStripes = _this.Styles.Add();

Style blackStripes = _this.Styles[styleIndexBlackStripes];

blackStripes.Name = "blackstripes";

blackStripes.BackgroundColor = System.Drawing.Color.Gray;

blackStripes.ForegroundColor = System.Drawing.Color.Black;

blackStripes.Pattern = BackgroundType.DiagonalStripe;

int styleIndexYellowSolid = _this.Styles.Add();

Style yellowSolid = _this.Styles[styleIndexYellowSolid];

yellowSolid.Name = "yellowSolid";

yellowSolid.BackgroundColor = System.Drawing.Color.Yellow;

yellowSolid.ForegroundColor = System.Drawing.Color.Yellow;

yellowSolid.Pattern = BackgroundType.Solid;

//yellow and black first

//The formula should be based on the start column below and will be replicated like you were copying

// relative cell formulas

//RC[-4] = A2 because starting point if F2 (Start Column 5, Start Row 1- 0-based) -- go left 4 columns, you reach a.

//string formula = "=IF(UPPER(SUBSTITUTE(RC[-4],\" \",\"\"))=\"HAVESTATE\",0,IF(SUBSTITUTE(RC,\" \",\"\")=\"\",0,1))";

string formula = "=IF(UPPER(SUBSTITUTE(A2,\" \",\"\"))=\"HAVESTATE\",0,IF(SUBSTITUTE(F2,\" \",\"\")=\"\",0,1))";

int conditionIndex = _this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddCondition(FormatConditionType.Expression, OperatorType.None, formula, String.Empty);

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex][conditionIndex].Style = _this.Styles["yellowandblack"];

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddArea(formatRange);

//then black

formula = "=IF(UPPER(SUBSTITUTE(A2,\" \",\"\"))=\"NOSTATE\",1, 0)";

conditionIndex = _this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddCondition(FormatConditionType.Expression, OperatorType.None, formula, String.Empty);

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex][conditionIndex].Style = _this.Styles["blackstripes"];

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddArea(formatRange);

//then yellow solid

formula = "=IF(COUNTIF(StateFullValidator,UPPER(SUBSTITUTE(A2&F2,\" \",\"\"))),FALSE,IF(TRIM(F2)=\"\",FALSE,TRUE))";

conditionIndex = _this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddCondition(FormatConditionType.Expression, OperatorType.None, formula, String.Empty);

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex][conditionIndex].Style = _this.Styles["yellowSolid"];

_this.Worksheets["DataEntry"].ConditionalFormattings[formatIndex].AddArea(formatRange);

//Set data entry validation

CellArea dvRange = new CellArea();

dvRange.StartRow = 1;

dvRange.EndRow = 65535;

dvRange.StartColumn = 0;

dvRange.EndColumn = 0;

int dvCurrentIndex = _this.Worksheets["DataEntry"].Validations.Add();

Validation v = _this.Worksheets["DataEntry"].Validations[dvCurrentIndex];

v.AlertStyle = ValidationAlertType.Information;

v.Type = ValidationType.List;

v.AreaList.Add(dvRange);

v.Formula1 = "=HasStateDropdown";

v.InCellDropDown = true;

v.ShowInput = false;

v.ShowError = false;

dvRange = new CellArea();

dvRange.StartRow = 1;

dvRange.EndRow = 65535;

dvRange.StartColumn = 5;

dvRange.EndColumn = 5;

dvCurrentIndex = _this.Worksheets["DataEntry"].Validations.Add();

v = _this.Worksheets["DataEntry"].Validations[dvCurrentIndex];

v.AlertStyle = ValidationAlertType.Information;

v.Type = ValidationType.List;

v.AreaList.Add(dvRange);

v.Formula1 = "=OFFSET(StateListDropdown,MATCH(SUBSTITUTE(UPPER(A2),\" \",\"\"),StateValidator,0)-1,0,COUNTIF(StateValidator, SUBSTITUTE(UPPER(A2),\" \",\"\")),1)";

v.InCellDropDown = true;

v.IgnoreBlank = false;

v.ShowInput = false;

v.InputTitle = "Mock Entry";

v.InputMessage = "Enter or choose a color";

v.ShowError = false;

v.Operator = OperatorType.None;

}

private DataSet GetAdventureWorksBase()

{

SqlConnection conn = new SqlConnection("Data Source=**PUT YOUR SERVER HERE**;Initial Catalog=AdventureWorks;Integrated Security=True");

SqlCommand comm = conn.CreateCommand();

comm.CommandText = "SELECT [Name] as Terriotry, CountryRegionName as [Country], StateProvinceName as [State], SalesYTD as [TerritorySales] FROM Person.vStateProvinceCountryRegion v JOIN Sales.SalesTerritory st on v.TerritoryID = st.TerritoryID";

comm.CommandType = CommandType.Text;

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

conn.Open();

da.Fill(ds);

conn.Close();

comm.Dispose();

conn.Dispose();

return ds;

}

private DataSet GetAdventureWorksValidation()

{

SqlConnection conn = new SqlConnection("Data Source=***YOUR SERVER HERE***;Initial Catalog=AdventureWorks;Integrated Security=True");

SqlCommand comm = conn.CreateCommand();

comm.CommandText = "zTempSpreadsheetTest";

comm.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

conn.Open();

da.Fill(ds);

conn.Close();

comm.Dispose();

conn.Dispose();

return ds;

}

RMTTeam · August 31, 2009, 12:54pm

Oops, forgot to include the stored proc I am using so you can duplicate the problem on your end.

CREATE PROCEDURE zTempSpreadsheetTest

AS

BEGIN

SELECT DISTINCT [Name] as Territory

FROM Sales.SalesTerritory

order by [Name]

SELECT DISTINCT [Name],[CountryRegionName] as Country,

UPPER(REPLACE([Name],' ','')) as CountryValidator,

UPPER(REPLACE([Name]+[CountryRegionName],' ','')) as CountryFullValidator

FROM Person.vStateProvinceCountryRegion v

JOIN Sales.SalesTerritory st

on v.TerritoryID = st.TerritoryID

order by [Name],[CountryRegionName]

SELECT DISTINCT [Name],[CountryRegionName],[StateProvinceName] as [State],

UPPER(REPLACE([Name]+[CountryRegionName],' ','')) as StateValidator,

UPPER(REPLACE([Name]+[CountryRegionName]+[StateProvinceName],' ','')) as StateFullValidator

FROM Person.vStateProvinceCountryRegion v

JOIN Sales.SalesTerritory st

on v.TerritoryID = st.TerritoryID

order by [Name],[CountryRegionName],[StateProvinceName] END

amjad.sahi · August 31, 2009, 1:41pm

Hi,

Could you download and try our latest official release of Aspose.Cells for .NET (v4.8.0): http://www.aspose.com/community/files/51/file-format-components/aspose.cells-for-.net-and-java/entry195573.aspx

Please let us know if you still find the issue, we will check it soon.

Note: Just for your knowledge. In the new release v4.8.0, we have merged Aspose.Grid suite to Aspose.Cells for .NET msi installer as Aspose.Grid would be no longer offered as a separate product now. You need to install the installer (uninstall older one first if you have) and use only Aspose.Cells.dll library in your project for your need. You may also take advantage using Aspose.Cells Grid suite though.

Thank you.

RMTTeam · August 31, 2009, 2:15pm

4.8 seems to solve it for XLS only, but not XLSX . Note opening up the XLS in Excel 2003/2007 works fine.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error045760_01.xml</logFileName>

<summary>Errors were detected in file 'C:\ASPOSE.xlsx'</summary>

- <removedFeatures summary="Following is a list of removed features:">

<removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet1.xml part</removedFeature>

</removedFeatures>

</recoveryLog>

Attaching the XLSX file

RMTTeam · August 31, 2009, 2:29pm

I saved my working XLS file in Excel 2007 as XLSX and did a comparison of the Sheet1.xml files and found the difference. Formula1 is quoted in the version that fails. It is NOT quoted in the version that succeeds. I validated this by renaming XLSX as .ZIP editing book1.xml and renaming back to .XLSX

i.e. "This is the formula" (will not work)

This is the formula (will work)

Sounds like an easy bug fix for you guys ;)

amjad.sahi · September 1, 2009, 1:42am

Hi,

Thanks for providing us further details.

We have found an issue regarding data validation, we will figure it out soon.

Thank you.

amjad.sahi · September 2, 2009, 4:31am

Hi,
Please try the attached version, we have fixed your issue.
Thank you.

aspose.notifier · November 16, 2009, 5:16am

The issues you have found earlier (filed as 10318) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.