We’re pleased to announce the release of Spire.XLS for Python 16.7.1. This version adds support for Data Simulation Analysis (Scenario Manager), Slicers, and Excel to JSON conversion, expanding data analysis and processing capabilities. More details are listed below.
Here is a list of changes made in this release
| Category | ID | Description |
| New Feature | SPIREXLS-6020 | Added support for Data Simulation Analysis (Scenario Manager), including creation, editing, deletion, summary generation, and merging.
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
scenarios = worksheet.Scenarios
currentChangePercentage_Values = [0.23, 0.8, 1.1, 0.5, 0.35, 0.2]
increasedChangePercentage_Values = [0.45, 0.56, 0.9, 0.5, 0.58, 0.43]
decreasedChangePercentage_Values = [0.3, 0.2, 0.5, 0.3, 0.5, 0.23]
currentQuantity_Values = [1500, 3000, 5000, 4000, 500, 4000]
increasedQuantity_Values = [1000, 5000, 4500, 3900, 10000, 8900]
decreasedQuantity_Values = [1000, 2000, 3000, 3000, 300, 4000]
scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values)
scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values)
scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values)
scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values)
scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values)
scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values)
wb.SaveToFile(outputFile, ExcelVersion.Version2013)
wb.Dispose()
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
scenarios = worksheet.Scenarios
currentChangePercentage_Values = [0.23, 0.8, 1.1, 0.5, 0.35, 0.2]
increasedChangePercentage_Values = [0.45, 0.56, 0.9, 0.5, 0.58, 0.43]
decreasedChangePercentage_Values = [0.3, 0.2, 0.5, 0.3, 0.5, 0.23]
currentQuantity_Values = [1500, 3000, 5000, 4000, 500, 4000]
increasedQuantity_Values = [1000, 5000, 4500, 3900, 10000, 8900]
decreasedQuantity_Values = [1000, 2000, 3000, 3000, 300, 4000]
scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values)
scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values)
scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values)
scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values)
scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values)
scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values)
worksheet.Scenarios.Summary(worksheet.Range["L7"])
wb.SaveToFile(outputFile, ExcelVersion.Version2013)
wb.Dispose()
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
scenarios = worksheet.Scenarios
scenario1 = scenarios[0]
scenario2 = scenarios[1]
scenario1.SetVariableCells(worksheet.Range["A1:A5"], scenario2.Values)
sourceCell = worksheet.Range["B1:B5"]
scenario2.SetVariableCells(sourceCell, scenario2.Values)
scenario1.Show()
scenario2.Show()
self.get_content(scenario1, outputFile_TXT)
wb.SaveToFile(outputFile, ExcelVersion.Version2013)
wb.Dispose()
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
scenarios = worksheet.Scenarios
scenarios.RemoveScenarioAt(0)
scenarios.RemoveScenarioByName("two")
content = ""
content += "Count:" + str(scenarios.Count) + "\n"
content += "ContainsScenario:" + str(scenarios.ContainsScenario("two")) + "\n"
content += "ContainsScenario:" + str(scenarios.ContainsScenario("one")) + "\n"
with open(outputFile, 'w') as f:
f.write(content)
wb.Dispose()
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
scenarios = worksheet.Scenarios
scenarios.RemoveScenarioAt(0)
scenarios.RemoveScenarioByName("two")
content = ""
content += "Count:" + str(scenarios.Count) + "\n"
content += "ContainsScenario:" + str(scenarios.ContainsScenario("two")) + "\n"
content += "ContainsScenario:" + str(scenarios.ContainsScenario("one")) + "\n"
with open(outputFile, 'w') as f:
f.write(content)
wb.Dispose()
|
| New Feature | - | Added support for adding Slicers using table data.
wb = Workbook()
wb.LoadFromFile(inputFile)
worksheet = wb.Worksheets[0]
slicers = worksheet.Slicers
table = worksheet.ListObjects.Create("Super Table", worksheet.Range["A1:C9"])
count = 3
for type in SlicerStyleType.dict.values():
if isinstance(type, SlicerStyleType):
count += 5
rangeStr = "E" + str(count)
index = slicers.Add(table, rangeStr, 0)
xlsSlicer = slicers[index]
xlsSlicer.Name = "slicers_" + str(count)
xlsSlicer.StyleType = type
wb.SaveToFile(outputFile_xlsx, ExcelVersion.Version2013)
wb.Dispose()
|
| New Feature | - | Added support for adding Slicers using PivotTable data.
wb = Workbook() wb.LoadFromFile(inputFile) worksheet = wb.Worksheets[0] pt = sheet.PivotTables[0] slicers = worksheet.Slicers index = slicers.Add(pt, "E12", 0) xlsSlicer = slicers[index] xlsSlicer.Name = "test_xlsSlicer" xlsSlicer.Width = 100 xlsSlicer.Height = 120 xlsSlicer.StyleType = SlicerStyleType.SlicerStyleLight2 xlsSlicer.PositionLocked = True slicerCache = xlsSlicer.SlicerCache slicerCache.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithNoData slicerCacheItems = xlsSlicer.SlicerCache.SlicerCacheItems xlsSlicerCacheItem = slicerCacheItems[0] xlsSlicerCacheItem.Selected = False slicers_2 = worksheet.Slicers r1 = pt.PivotFields["year"] index_2 = slicers_2.Add(pt, "I12", r1) xlsSlicer_2 = slicers[index_2] xlsSlicer_2.RowHeight = 40 xlsSlicer_2.StyleType = SlicerStyleType.SlicerStyleLight3 xlsSlicer_2.PositionLocked = False slicerCache_2 = xlsSlicer_2.SlicerCache slicerCache_2.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithDataAtTop slicerCacheItems_2 = xlsSlicer_2.SlicerCache.SlicerCacheItems xlsSlicerCacheItem_2 = slicerCacheItems_2[1] xlsSlicerCacheItem_2.Selected = False pt.CalculateData() wb.SaveToFile(outputFile_xlsx, ExcelVersion.Version2013) wb.Dispose() |
| New Feature | - | Added support for removing Slicers.
wb = Workbook() wb.LoadFromFile(inputFile) worksheet_1 = wb.Worksheets[0] slicers = worksheet_1.Slicers slicers.RemoveAt(0) slicer = worksheet_1.Slicers[1] worksheet_1.Slicers.Remove(slicer) worksheet_2 = wb.Worksheets[2] worksheet_2.Slicers.Clear() wb.SaveToFile(outputFile_xlsx, ExcelVersion.Version2013) |
| New Feature | - | Added support for modifying Slicers.
wb = Workbook() wb.LoadFromFile(inputFile) worksheet = wb.Worksheets[0] slicers = worksheet.Slicers xlsSlicer = slicers[0] xlsSlicer.StyleType = SlicerStyleType.SlicerStyleDark4 xlsSlicer.Caption = "Slicer" xlsSlicer.PositionLocked = True slicerCacheItems = xlsSlicer.SlicerCache.SlicerCacheItems xlsSlicerCacheItem = slicerCacheItems[0] xlsSlicerCacheItem.Selected = False displayValue = xlsSlicerCacheItem.DisplayValue slicerCache = xlsSlicer.SlicerCache slicerCache.CrossFilterType = SlicerCacheCrossFilterType.ShowItemsWithNoData wb.SaveToFile(outputFile_xlsx, ExcelVersion.Version2013) |
| New Feature | - | Added support for retrieving Slicer information.
wb = Workbook() wb.LoadFromFile(inputFile) worksheet = wb.Worksheets[0] slicers = worksheet.Slicers content = "" content += "slicers.Count：" + str(slicers.Count) + "\n" xlsSlicer = slicers[1] content += "xlsSlicer.Name：" + xlsSlicer.Name + "\n" content += "xlsSlicer.Caption：" + xlsSlicer.Caption + "\n" content += "xlsSlicer.NumberOfColumns：" + str(xlsSlicer.NumberOfColumns) + "\n" content += "xlsSlicer.ColumnWidth：" + str(xlsSlicer.ColumnWidth) + "\n" content += "xlsSlicer.RowHeight：" + str(xlsSlicer.RowHeight) + "\n" content += "xlsSlicer.ShowCaption：" + str(xlsSlicer.ShowCaption) + "\n" content += "xlsSlicer.PositionLocked：" + str(xlsSlicer.PositionLocked) + "\n" content += "xlsSlicer.Width：" + str(xlsSlicer.Width) + "\n" content += "xlsSlicer.Height：" + str(xlsSlicer.Height) + "\n" slicerCache = xlsSlicer.SlicerCache content += "slicerCache.SourceName：" + slicerCache.SourceName + "\n" content += "slicerCache.IsTabular：" + str(slicerCache.IsTabular) + "\n" content += "slicerCache.Name：" + slicerCache.Name + "\n" slicerCacheItems = slicerCache.SlicerCacheItems xlsSlicerCacheItem = slicerCacheItems[1] content += "xlsSlicerCacheItem.Selected：" + str(xlsSlicerCacheItem.Selected) + "\n" with open(outputFile_T, 'w') as f: f.write(content) wb.Dispose() |
| New Feature | SPIREXLS-6162 | Added support for converting Excel files to JSON data.
workbook = Workbook() workbook.LoadFromFile(inputFile) workbook.SaveToFile(outputFile, FileFormat.Json) workbook.Dispose() |
Click the link below to download Spire.XLS for Python 16.7.1:
