导入和导出SpreadJS文件

// 初始化工作簿
Workbook workbook = new Workbook();

// 打开 .sjs 文件
workbook.open("ProjectPlan.sjs", OpenFileFormat.Sjs);

// 保存 .sjs 文件
workbook.save("SaveProjectPlan.sjs", SaveFileFormat.Sjs);

// 创建新工作簿
var streamworkbook = new Workbook();
// 创建新文件流以打开文件
InputStream openFile;
try {
    openFile = new FileInputStream("ProjectPlan.sjs");
    // 打开 .sjs 文件
    streamworkbook.open(openFile, OpenFileFormat.Sjs);
} catch (FileNotFoundException e1) {
    // TODO 自动生成的 catch 块
    e1.printStackTrace();
}
  
// 创建新文件流以保存文件
OutputStream out;
try {
    out = new FileOutputStream("SaveProjectPlan.sjs");
    // 将工作簿保存为 .sjs 文件
    streamworkbook.save(out, SaveFileFormat.Sjs);
} catch (FileNotFoundException e) {
    // TODO 自动生成的 catch 块
    e.printStackTrace();
}

// 初始化工作簿
Workbook workbook = new Workbook();

// 使用公式打开 .sjs 文件
SjsOpenOptions openOptions = new SjsOpenOptions();
openOptions.setIncludeFormulas(false);
openOptions.setIncludeStyles(false);
workbook.open("ProjectPlan.sjs", openOptions);

// 保存包含样式的 .sjs 文件
SjsSaveOptions saveOptions = new SjsSaveOptions();
saveOptions.setIncludeStyles(false);
saveOptions.setIncludeFormulas(true);
saveOptions.setIncludeUnusedNames(false);
saveOptions.setIncludeEmptyRegionCells(false);
workbook.save("SaveProjectPlan.sjs", saveOptions);

// 初始化工作簿
Workbook workbook = new Workbook();

// 打开工作簿
workbook.open("12-month cash flow statement1.sjs");

// 为 .sjs 文件生成 JSON 字符串并保存到流中
OutputStream outputStream;
try {
    outputStream = new FileOutputStream("CashFlow.json");
    workbook.toSjsJson(outputStream);
    outputStream.close();
} catch (FileNotFoundException e) {
    e.printStackTrace();        
}

// 从流中导入 JSON 字符串并保存为 Excel 文件
InputStream inputStream;
try {
    inputStream = new FileInputStream("CashFlow.json");
    workbook.fromSjsJson(inputStream);
} catch (FileNotFoundException e) {
    e.printStackTrace();        
}

// 保存工作簿
workbook.save("CashFlow.xlsx");

// 从 JSON 反序列化工作簿时忽略样式和公式
DeserializationOptions deserializationOptions = new DeserializationOptions();
deserializationOptions.setIgnoreStyle(true);
deserializationOptions.setIgnoreFormula(true);
workbook.fromJson(json, deserializationOptions);

// 保存为 Excel 文件
workbook.save("FromJsonWithOptions.xlsx");

// 将工作簿序列化为 JSON 时忽略样式和公式
SerializationOptions serializationOptions = new SerializationOptions();
serializationOptions.setIgnoreStyle(true);
serializationOptions.setIgnoreFormula(true);

String jsonWithOption = workbook.toJson(serializationOptions);

workbook.fromJson(jsonWithOption);

// 保存为 Excel 文件
workbook.save("ToJsonWithOptions.xlsx");

Workbook book = new Workbook();
 
IWorksheet worksheet = book.getWorksheets().get(0);
// 添加自定义名称样式
IStyle style = book.getStyles().add("testStyle1");
     
style.getFont().setThemeColor(ThemeColor.Accent1);
style.getFont().setTintAndShade(0.8);
style.getFont().setItalic(true);
style.getFont().setBold(true);
style.getFont().setName("LiSu");
style.getFont().setSize(28);
style.getFont().setStrikethrough(true);
style.getFont().setSubscript(true);
style.getFont().setSuperscript(false);
style.getFont().setUnderline(UnderlineType.Double);
     
Object data = new Object[][]{
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
    {"test", "test", "test", "test" },
};
     
worksheet.getRange("B2:E6").setValue(data);
worksheet.getRange("A:XFD").setStyle(style);
worksheet.getRange("A:XFD").setColumnWidthInPixel(20);
        
// 仅将已用范围的大小/样式导出到 JSON
SerializationOptions options = new SerializationOptions();
options.setIgnoreColumnRowInfoOutOfUsedRange(true);
        
try {
    book.toJson(new FileOutputStream("TestJson_true.json"), options);    // 输出文件大小为 9KB
} catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
        
// 导出所有大小/样式到 JSON
SerializationOptions options2 = new SerializationOptions();
options2.setIgnoreColumnRowInfoOutOfUsedRange(false);
        
try {
    book.toJson(new FileOutputStream("TestJson_false.json"), options2);    // 输出文件大小为 809KB
} catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
        
// 默认行为（与设置为 true 时相同）
try {
    book.toJson(new FileOutputStream("TestJson_default.json"));    // 输出文件大小为 9KB
} catch (FileNotFoundException e) {
    e.printStackTrace();
}

// 创建新工作簿
 var workbook = new Workbook();

 // 设置迭代计算选项
 workbook.getOptions().getFormulas().setEnableIterativeCalculation(true);
 workbook.getOptions().getFormulas().setMaximumIterations(20);
 var worksheet = workbook.getWorksheets().get(0);

 // 设置值和公式
 worksheet.getRange("B2").setValue("Initial Cash");
 worksheet.getRange("C2").setValue(10000);
 worksheet.getRange("B3").setValue("Interest");
 worksheet.getRange("C3").setValue(0.0125);

 worksheet.getRange("B5").setValue("Month");
 worksheet.getRange("C5").setValue("Total Cash");  

 worksheet.getRange("B6:B17").setValue(new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 });  

 worksheet.getRange("C6").setFormula("=C2*(1+$C$3)");  
 worksheet.getRange("C7:C17").setFormula("=C6*(1+$C$3)");  

 // 初始化 SerializationOptions 并将 ExportSharedFormula 设置为 true
 SerializationOptions options = new SerializationOptions();
 options.setExportSharedFormula(true);

 // 保存 JSON 文件
 PrintWriter out1;
try {
    out1 = new PrintWriter("ExportSharedFormulas.json");
    out1.println(workbook.toJson(options));
} catch (FileNotFoundException e1) {
    // TODO 自动生成的 catch 块
    e1.printStackTrace();
}     

 // 初始化 SerializationOptions 并将 ExportSharedFormula 设置为 false
 SerializationOptions options2 = new SerializationOptions();
 options2.setExportSharedFormula(false);

 // 保存 JSON 文件
 PrintWriter out2;
try {
    out2 = new PrintWriter("ExportIndividualFormulas.json");
    out2.println(workbook.toJson(options));
} catch (FileNotFoundException e) {
    // TODO 自动生成的 catch 块
    e.printStackTrace();
}

// 创建新工作簿
Workbook workbook = new Workbook();

// 旧工作簿
IWorkbook oldWorkbook = new com.grapecity.documents.excel.Workbook();

InputStream fileStream = getResourceStream("xlsx\\12-month cash flow statement1.xlsx");
oldWorkbook.open(fileStream);

FileOutputStream workbookOutputJson = null;
FileInputStream workbookInputJson = null;
FileOutputStream worksheetOutputJson = null;
FileInputStream worksheetInputJson = null;

try {
    SerializationOptions serializationOptions = new SerializationOptions();
    serializationOptions.setIgnoreSheets(true);
    
    // 将不含工作表的工作簿框架导出到 JSON 流
    workbookOutputJson = new FileOutputStream("workbookJava.json");
    oldWorkbook.toJson(workbookOutputJson, serializationOptions);
    
    // 导入工作簿流
    workbookInputJson = new FileInputStream("workbookJava.json");
    workbook.fromJson(workbookInputJson);

    // 将工作表导出到 JSON 流
    worksheetOutputJson = new FileOutputStream("worksheetJava.json");
    oldWorkbook.getActiveSheet().toJson(worksheetOutputJson);

    // 导入工作表流
    worksheetInputJson = new FileInputStream("worksheetJava.json");
    workbook.getActiveSheet().fromJson(worksheetInputJson);
} catch (FileNotFoundException e) {
    // TODO 自动生成的 catch 块
    e.printStackTrace();
} finally {
    // 关闭流
    try {
        if (workbookOutputJson != null) {
            workbookOutputJson.close();
        }
        
        if (workbookInputJson != null) {
            workbookInputJson.close();
        }
        
        if (worksheetOutputJson != null) {
            worksheetOutputJson.close();
        }
        
        if (worksheetInputJson != null) {
            worksheetInputJson.close();
        }
    } catch (Exception e2) {
        // TODO: 处理异常
    }
}
    
// 保存为 Excel 文件
workbook.save("WorkbookToJsonWithoutSheets.xlsx");

// 创建新工作簿
var workbook = new Workbook();

// 定义 JSON 数据源
String dataSource = "{ \"ds\":" +
        "[\n" +
        "   {\"Area\": \"North America\",\"City\": \"Chicago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
        "   {\"Area\": \"North America\",\"City\": \"New York\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
        "   {\"Area\": \"South America\",\"City\": \"Santiago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 19550},\n" +
        "   {\"Area\": \"Europe\",\"City\": \"Berlin\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 30000},\n" +
        "   {\"Area\": \"Asia\",\"City\": \"Tokyo\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 45000},\n" +
        "   {\"Area\": \"North America\",\"City\": \"Los Angeles\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 60000},\n" +
        "   {\"Area\": \"Europe\",\"City\": \"Paris\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 55000},\n" +
        "   {\"Area\": \"Asia\",\"City\": \"Seoul\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 40000},\n" +
        "   {\"Area\": \"South America\",\"City\": \"Buenos Aires\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 35000},\n" +
        "   {\"Area\": \"North America\",\"City\": \"Toronto\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 50000}\n" +
        " ]" +
        "}";

// 向工作表添加数据源
IWorksheet dataSourceSheet = workbook.getWorksheets().add();
dataSourceSheet.setName("DataSource");
ITable table = dataSourceSheet.getTables().add(dataSourceSheet.getRange("A1:E4"), true);

// 设置绑定路径
table.setBindingPath("ds");
table.getColumns().get(0).setDataField("Area");
table.getColumns().get(1).setDataField("City");
table.getColumns().get(2).setDataField("Category");
table.getColumns().get(3).setDataField("Name");
table.getColumns().get(4).setDataField("Revenue");

// 设置数据源
dataSourceSheet.setDataSource(new JsonDataSource(dataSource));

// 创建数据透视表工作表
IWorksheet pivotSheet = workbook.getWorksheets().get(0);
pivotSheet.setName("PivotSheet");

// 创建数据透视表
IPivotCache pivotcache = workbook.getPivotCaches().create(table);
IPivotTable pivottable = pivotSheet.getPivotTables().add(pivotcache, pivotSheet.getRange("A1"), "pivottable1");

// 配置数据透视表字段
IPivotField fieldArea = pivottable.getPivotFields().get("Area");
fieldArea.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldCity = pivottable.getPivotFields().get("City");
fieldCity.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldName = pivottable.getPivotFields().get("Name");
fieldName.setOrientation(PivotFieldOrientation.ColumnField);
IPivotField fieldRevenue = pivottable.getPivotFields().get("Revenue");
fieldRevenue.setOrientation(PivotFieldOrientation.DataField);
pivotSheet.getUsedRange().autoFit();
pivottable.setColumnGrand(false);
pivottable.setRowGrand(false);
pivottable.refresh();

SjsSaveOptions saveOptions = new SjsSaveOptions();

// 如果导出为 SSJSON 格式，请取消以下注释
//SerializationOptions saveOptions = new SerializationOptions();

// 将 IncludeBindingSource 属性设置为 false，以排除绑定源的导出
saveOptions.setIncludeBindingSource(false);

// 保存工作簿
workbook.save("IncludeBindingSourceOption.sjs", saveOptions);

// 如果要导出 SSJSON，请取消以下代码注释
/*
try 
{
    // 保存工作簿
    workbook.toJson(new FileOutputStream("IncludeBindingSourceOption.ssjson"), saveOptions);
} 
catch (FileNotFoundException e1) {
    e1.printStackTrace();
}
*/

// 创建新工作簿
Workbook workbook = new Workbook();

// --------------- 导出为 .sjs 文件 ---------------

// 打开 .sjs 文件
workbook.open("LoanDetails.sjs", OpenFileFormat.Sjs);

// 访问第一个工作表
IWorksheet worksheet = workbook.getWorksheets().get(0);

// 将行和列计数设置为 4
worksheet.setRowCount(4);
worksheet.setColumnCount(4);

// 配置导出以忽略行/列计数范围外的数据
SjsSaveOptions sjsSaveOptions = new SjsSaveOptions();
sjsSaveOptions.setIgnoreRangeOutOfRowColumnCount(true);

// 保存为 .sjs 文件
workbook.save("IgnoreDataOption.sjs", sjsSaveOptions);

// --------------- 导出为 SSJSON 文件 ---------------
// 如果要导出为 SSJSON，请取消以下代码注释

/*
IWorksheet worksheet2 = workbook.getWorksheets().get(0);

// 设置单元格数据示例
worksheet2.getRange("A1").setValue(1);
worksheet2.getRange("A11").setValue(2);

// 将行和列计数设置为 10
worksheet2.setRowCount(10);
worksheet2.setColumnCount(10);

// 配置导出以忽略行/列计数范围外的数据
SerializationOptions ssjsonOptions = new SerializationOptions();
ssjsonOptions.setIgnoreRangeOutOfRowColumnCount(true);

// 保存为 SSJSON 文件（JSON 格式）
// 使用 SpreadJS 打开此 JSON 文件，可看到行/列计数为 10
try {
    workbook.toJson(new FileOutputStream("RowColumnCount.json"), ssjsonOptions);
} catch (FileNotFoundException e) {
    e.printStackTrace();
}
*/

// 创建新工作簿
Workbook workbook = new Workbook();
// 打开包含 JSON 错误的 JSON 文件
InputStream stream = new FileInputStream("ErrorJson.json");

List<JsonError> errors = workbook.fromJson(stream);
for (JsonError item : errors) {
    if (item instanceof FormulaJsonError) {
        FormulaJsonError fError = (FormulaJsonError) item;
        System.out
                .println(fError.getErrorMessage() + " "
                        + workbook.getWorksheets().get(fError.getWorksheetName())
                                .getRange(fError.getRow(), fError.getColumn()).toString()
                        + " " + fError.getFormula());
    }

    if (item instanceof DataValidationJsonError) {
        DataValidationJsonError dError = (DataValidationJsonError) item;
        System.out.println(dError.getErrorMessage() + " "
                + workbook.getWorksheets().get(dError.getWorksheetName()).getRange(dError.getRange().toString())
                + " " + dError.getErrorContent());
    }