PivotTableがExcel2003に互換性があるかどうかを指定する

Aspose.Cells for Python via .NETは、PivotTableを更新する際にExcel2003用にPivotTableが互換性があるかどうかを指定するための PivotTable.is_excel_2003_compatible プロパティを提供し、これを使用します。trueの場合、文字列は255文字以下でなければならず、文字列が255文字を超える場合は切り捨てられます。falseの場合、文字列に前述の制限はありません。デフォルト値はtrueです。

ピボットテーブルを更新する際にExcel2003との互換性があるかどうかを指定する方法

次のサンプルコードは、PivotTable.is_excel_2003_compatibleプロパティの使用方法を説明しています。元の文字列は383文字ですが、PivotTable.is_excel_2003_compatibleプロパティをtrueに設定してピボットテーブルを更新すると、ピボットテーブルのセルB5のデータが切り捨てられて255文字になります。ただし、PivotTable.is_excel_2003_compatibleプロパティをfalseに設定してピボットテーブルを再度更新すると、ピボットテーブルのセルB5のデータが切り捨てられず、383文字のままです。このプロパティの理解のためにコード内のコメントをお読みください。

from aspose.cells import SaveFormat, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Load source excel file containing sample pivot table
wb = Workbook(dataDir + "sample-pivot-table.xlsx")
#  Access first worksheet that contains pivot table data
dataSheet = wb.worksheets[0]
#  Access cell A3 and sets its data
cells = dataSheet.cells
cell = cells.get("A3")
cell.put_value("FooBar")
#  Access cell B3, sets its data. We set B3 a very long string which has more than 255 characters
longStr = "Very long text 1. very long text 2. very long text 3. very long text 4. very long text 5. very long text 6. very long text 7. very long text 8. very long text 9. very long text 10. very long text 11. very long text 12. very long text 13. very long text 14. very long text 15. very long text 16. very long text 17. very long text 18. very long text 19. very long text 20. End of text."
cell = cells.get("B3")
cell.put_value(longStr)
#  Print the length of cell B3 string
print("Length of original data string: "  + str(len(cell.string_value)))
#  Access cell C3 and sets its data
cell = cells.get("C3")
cell.put_value("closed")
#  Access cell D3 and sets its data
cell = cells.get("D3")
cell.put_value("2016/07/21")
#  Access the second worksheet that contains pivot table
pivotSheet = wb.worksheets[1]
#  Access the pivot table
pivotTable = pivotSheet.pivot_tables[0]
#  IsExcel2003Compatible property tells if PivotTable is compatible for Excel2003 while refreshing PivotTable.
#  If it is true, a string must be less than or equal to 255 characters, so if the string is greater than 255 characters,
#  it will be truncated. If false, a string will not have the aforementioned restriction. The default value is true.
pivotTable.is_excel_2003_compatible = True
pivotTable.refresh_data()
pivotTable.calculate_data()
#  Check the value of cell B5 of pivot sheet.
#  It will be 255 because we have set IsExcel2003Compatible property to true. All the data after 255 characters has been truncated
b5 = pivotSheet.cells.get("B5")
print("Length of cell B5 after setting IsExcel2003Compatible property to True: "  + str(len(b5.string_value)))
#  Now set IsExcel2003Compatible property to false and again refresh
pivotTable.is_excel_2003_compatible = False
pivotTable.refresh_data()
pivotTable.calculate_data()
#  Now it will print 383 the original length of cell data. The data has not been truncated now.
b5 = pivotSheet.cells.get("B5")
print("Length of cell B5 after setting IsExcel2003Compatible property to False: "  + str(len(b5.string_value)))
#  Set the row height and column width of cell B5 and also wrap its text
pivotSheet.cells.set_row_height(b5.row, 100)
pivotSheet.cells.set_column_width(b5.column, 65)
st = b5.get_style()
st.is_text_wrapped = True
b5.set_style(st)
#  Save workbook in xlsx format
wb.save(dataDir + "SpecifyCompatibility_out_.xlsx", SaveFormat.XLSX)