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;
}