Spire.XLS for Python 16.7.1 现已发布。该版本新增支持数据模拟分析（方案管理器）、Slicer 切片器以及 Excel 转 JSON 数据功能，进一步扩展了数据分析与处理能力。详情如下。
新功能:
- 支持数据模拟分析（方案管理器）功能，涵盖创建、编辑、删除、摘要生成及合并。
- 支持通过表格数据添加 Slicer 切片器。
- 支持通过透视表数据添加 Slicer 切片器。
- 支持移除 Slicer 切片器。
- 支持修改 Slicer 切片器。
- 支持获取 Slicer 切片器信息。
- 支持将 Excel 转换为 JSON 数据。
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() 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() 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()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)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)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()workbook = Workbook()
workbook.LoadFromFile(inputFile)
workbook.SaveToFile(outputFile, FileFormat.Json)
workbook.Dispose()获取 Spire.XLS for Python 16.7.1 请点击：
