// ignore style and formula when serialize workbook to json
string jsonWithOption = workbook.ToJson(new SerializationOptions() { IgnoreFormula = true, IgnoreStyle = true });

workbook.FromJson(jsonWithOption);

//save to an excel file
workbook.Save("tojsonwithoptions.xlsx");

// ignore style and formula when deserialize workbook from json.
workbook.FromJson(json, new DeserializationOptions() { IgnoreFormula = true, IgnoreStyle = true });

//save to an excel file
workbook.Save("fromjsonwithoptions.xlsx");

var book = new Workbook();
IWorksheet worksheet = book.Worksheets[0];
//Add custom name style
IStyle style = book.Styles.Add("testStyle1");
 
style.Font.ThemeColor = ThemeColor.Accent1;
style.Font.TintAndShade = 0.8;
style.Font.Italic = true;
style.Font.Bold = true;
style.Font.Name = "LiSu";
style.Font.Size = 28;
style.Font.Strikethrough = true;
style.Font.Subscript = true;
style.Font.Superscript = false;
style.Font.Underline = 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.Range["B2:E6"].Value = data;
worksheet.Range["A:XFD"].Style = book.Styles["testStyle1"];
worksheet.Range["A:XFD"].ColumnWidth = 20;
    
//Export sizes/styles of only used range to json
using FileStream jsonFile = new FileStream("TestJson_true.json", FileMode.Create);
book.ToJson(jsonFile, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = true }); // Size of output file is 9KB
    
//Export all sizes/styles to json
using FileStream jsonFile2 = new FileStream("TestJson_false.json", FileMode.Create);
book.ToJson(jsonFile2, new SerializationOptions() { IgnoreColumnRowInfoOutOfUsedRange = false }); // Size of output file is 793KB
    
//Default behavior (same as true option)
book.ToJson(new FileStream("TestJson_default.json", FileMode.Create)); // Size of output file is 9KB

// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Set options for iterative calculation.
workbook.Options.Formulas.EnableIterativeCalculation = true;
workbook.Options.Formulas.MaximumIterations = 20;
var worksheet = workbook.Worksheets[0];

// Set values and formulas.
worksheet.Range["B2"].Value = "Initial Cash";
worksheet.Range["C2"].Value = 10000;
worksheet.Range["B3"].Value = "Interest";
worksheet.Range["C3"].Value = 0.0125;

worksheet.Range["B5"].Value = "Month";
worksheet.Range["C5"].Value = "Total Cash";

worksheet.Range["B6:B17"].Value = new double[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };

worksheet.Range["C6"].Formula = "=C2*(1+$C$3)";
worksheet.Range["C7:C17"].Formula = "=C6*(1+$C$3)";

// Initialize SerializationOptions and set ExportSharedFormula to true.
SerializationOptions options = new SerializationOptions();
options.ExportSharedFormula = true;

// Save the JSON file.
workbook.ToJson(File.Create("ExportSharedFormulas.json"), options);

// Initialize SerializationOptions and set ExportSharedFormula to false.
SerializationOptions options2 = new SerializationOptions();
options2.ExportSharedFormula = false;

// Save the JSON file.
workbook.ToJson(File.Create("ExportIndividualFormulas.json"), options2);

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

CheckBoxListCellType cellType = new CheckBoxListCellType
{
    Direction = CellTypeDirection.Horizontal,
    TextAlign = CellTypeTextAlign.Right,
    IsFlowLayout = false,
    MaxColumnCount = 2,
    MaxRowCount = 1,
    HorizontalSpacing = 20,
    VerticalSpacing = 5,
};
cellType.Items.Add(new SelectFieldItem("sample1", "1"));
cellType.Items.Add(new SelectFieldItem("sample2", "2"));
cellType.Items.Add(new SelectFieldItem("sample3", "3"));
cellType.Items.Add(new SelectFieldItem("sample4", "4"));
cellType.Items.Add(new SelectFieldItem("sample5", "5"));
worksheet.Range["A1"].RowHeight = 60;
worksheet.Range["A1"].ColumnWidth = 25;
worksheet.Range["A1"].CellType = cellType;
        
//check multiple options in the check box list
 worksheet.Range["A1"].Value = new object[,]
{
    {new object[]{"1", "3", "5" } }
};

//save to a pdf file
workbook.Save("addcheckboxlistcelltype.pdf");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

GrapeCity.Documents.Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer();
IWorksheet worksheet = workbook.Worksheets[0];

CheckBoxListCellType cellType = new CheckBoxListCellType
{
    Direction = CellTypeDirection.Horizontal,
    TextAlign = CellTypeTextAlign.Right,
    IsFlowLayout = false,
    MaxColumnCount = 2,
    MaxRowCount = 1,
    HorizontalSpacing = 20,
    VerticalSpacing = 5,
};
cellType.Items.Add(new SelectFieldItem("player1", new People { Name = "Tom", Age = 5 }));
cellType.Items.Add(new SelectFieldItem("player2", new People { Name = "Jerry", Age = 3 }));
cellType.Items.Add(new SelectFieldItem("player3", new People { Name = "Mario", Age = 6 }));
cellType.Items.Add(new SelectFieldItem("player4", new People { Name = "Luigi", Age = 4 }));
worksheet.Range["A1"].RowHeight = 60;
worksheet.Range["A1"].ColumnWidth = 25;
worksheet.Range["A1"].CellType = cellType;

worksheet.Range["A1"].Value = new object[,]
{
{new object[]{ new People { Name = "Tom", Age = 5 }, new People { Name = "Mario", Age = 6 }} }
};

//save to a pdf file
workbook.Save("addcheckboxlistcelltypecustomobject.pdf");
}
class CustomObjectJsonSerializer : IJsonSerializer
{
    public object Deserialize(string json)
    {
        return Newtonsoft.Json.JsonConvert.DeserializeObject<People>(json);
    }

    public string Serialize(object value)
    {
        if (value is People)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(value);
        }
        return null;
    }
}
class People
{
    private int age;
    private string name;

    public int Age { get => age; set => age = value; }
    public string Name { get => name; set => name = value; }

    public override bool Equals(object obj)
    {
        return obj is People people &&
               age == people.age &&
               name == people.name;
    }

    public override int GetHashCode()
    {
        return age.GetHashCode() ^ name.GetHashCode();
    }
}

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

RadioButtonListCellType cellType = new RadioButtonListCellType
{
    Direction = CellTypeDirection.Horizontal,
    TextAlign = CellTypeTextAlign.Right,
    IsFlowLayout = false,
    MaxColumnCount = 2,
    MaxRowCount = 1,
    HorizontalSpacing = 20,
    VerticalSpacing = 5,
};
cellType.Items.Add(new SelectFieldItem("sample1", "1"));
cellType.Items.Add(new SelectFieldItem("sample2", "2"));
cellType.Items.Add(new SelectFieldItem("sample3", "3"));
cellType.Items.Add(new SelectFieldItem("sample4", "4"));
cellType.Items.Add(new SelectFieldItem("sample5", "5"));
worksheet.Range["A1"].RowHeight = 60;
worksheet.Range["A1"].ColumnWidth = 25;
worksheet.Range["A1"].CellType = cellType;
worksheet.Range["A1"].Value = 1;
        
//check multiple options in the radio button list
 worksheet.Range["A1"].Value = new object[,]
{
    {new object[]{"1", "3", "5" } }
};

//save to a pdf file
workbook.Save("addradiobuttonlistcelltype.pdf");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();


GrapeCity.Documents.Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer();
IWorksheet worksheet = workbook.Worksheets[0];

RadioButtonListCellType cellType = new RadioButtonListCellType
{
    Direction = CellTypeDirection.Horizontal,
    TextAlign = CellTypeTextAlign.Right,
    IsFlowLayout = false,
    MaxColumnCount = 2,
    MaxRowCount = 1,
    HorizontalSpacing = 20,
    VerticalSpacing = 5,
};
cellType.Items.Add(new SelectFieldItem("player1", new People { Name = "Tom", Age = 5 }));
cellType.Items.Add(new SelectFieldItem("player2", new People { Name = "Jerry", Age = 3 }));
cellType.Items.Add(new SelectFieldItem("player3", new People { Name = "Mario", Age = 6 }));
cellType.Items.Add(new SelectFieldItem("player4", new People { Name = "Luigi", Age = 4 }));
worksheet.Range["A1"].RowHeight = 60;
worksheet.Range["A1"].ColumnWidth = 25;
worksheet.Range["A1"].CellType = cellType;

worksheet.Range["A1"].Value = new object[,]
{
{new People { Name = "Tom", Age = 5 } }
};

//save to a pdf file
workbook.Save("addradiobuttoncelltypecustomobject.pdf");
}

class CustomObjectJsonSerializer : IJsonSerializer
{
    public object Deserialize(string json)
    {
        return Newtonsoft.Json.JsonConvert.DeserializeObject<People>(json);
    }

    public string Serialize(object value)
    {
        if (value is People)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(value);
        }
        return null;
    }
}
class People
{
    private int age;
    private string name;

    public int Age { get => age; set => age = value; }
    public string Name { get => name; set => name = value; }

    public override bool Equals(object obj)
    {
        return obj is People people &&
               age == people.age &&
               name == people.name;
    }

    public override int GetHashCode()
    {
        return age.GetHashCode() ^ name.GetHashCode();
    }
}

// create a new workbook
Workbook workbook = new Workbook();
// get the sheet
IWorksheet worksheet = workbook.Worksheets[0];
// set row height
worksheet.Range["A:A"].RowHeight=40;
// set column width
worksheet.Range["A:A"].ColumnWidth=25;
// set watermark
worksheet.Range["A1"].Watermark="JAVA";
// set cell padding
worksheet.Range["A1"].CellPadding=new CellPadding(50, 0, 0, 0);
// set label options
worksheet.Range["A1"].LabelOptions.Visibility = LabelVisibility.visible;
worksheet.Range["A1"].LabelOptions.ForeColor = Color.Green;
worksheet.Range["A1"].LabelOptions.Margin=new Margin(15, 0, 0, 0);
worksheet.Range["A1"].LabelOptions.Font.Size=14;
worksheet.Range["A1"].LabelOptions.Font.Name="Calibri";
worksheet.Range["A1"].Borders.LineStyle=BorderLineStyle.Thin;

// save to a pdf file
workbook.Save("CellPaddingAndLabels.pdf");

// Set numbersFitMode is overflow.
workbook.BookView.NumbersFitMode = GrapeCity.Documents.Excel.NumbersFitMode.Overflow;

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

FileStream stream = File.Open(@"Logo.png", FileMode.Open, FileAccess.Read);

//Add background picture
IBackgroundPicture picture = worksheet.BackgroundPictures.AddPictureInPixel(stream, ImageType.PNG, 10, 10, 500, 370);
//Set image layout
picture.BackgroundImageLayout = ImageLayout.Zoom;

//Set options
workbook.ActiveSheet.PageSetup.PrintGridlines = true;

//save to a pdf file
workbook.Save("backgroundimage.pdf");

Workbook workbook = new Workbook();

string ssjson = string.Empty;
try
{
    var jsonFile = @"D:\bgimage.ssjson";

    using (StreamReader sr = System.IO.File.OpenText(jsonFile))
    {
        ssjson = sr.ReadToEnd();
    }
}
catch (Exception e)
{
    Console.WriteLine(e);
}

//Importing from ssjson
workbook.FromJson(ssjson);

//Set options
workbook.ActiveSheet.PageSetup.PrintGridlines = true;

//Exporting to PDF
workbook.Save("bgimage.pdf");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

//Set background color
workbook.BookView.BackColor = Color.LightSkyBlue;
workbook.BookView.GrayAreaBackColor = Color.Gray;

//Set value to a cell
IWorksheet worksheet = workbook.ActiveSheet;
worksheet.Range["H20"].Value = "The text";

//Set page options
worksheet.PageSetup.PrintGridlines = true;
worksheet.PageSetup.PrintHeadings = true;

//save to a pdf file
workbook.Save("backgroundcolor.pdf");

IWorkbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 1;
worksheet.Range["A11"].Value = 2;

// Modify the row count and column count of the worksheet.
worksheet.RowCount = 10;
worksheet.ColumnCount = 10;

// Save to a json file.
// Open this json file with spreadjs, you will find that the row count is 10, and the column count is 10.
FileStream file = new FileStream("RowColumnCount.json", FileMode.Create);
workbook.ToJson(file, new SerializationOptions { IgnoreRangeOutOfRowColumnCount = true});

// Initialize WorkbookOptions.
WorkbookOptions workbookOptions = new WorkbookOptions();

// Enable pixel-based column width for the workbook.
workbookOptions.PixelBasedColumnWidth = true;
var workbook = new Workbook(workbookOptions);

// Open SSJSON file.
workbook.Open("Event budget.ssjson");
IWorksheet worksheet = workbook.Worksheets[0];

// Save to a PDF and PNG file.
workbook.Save("SavePDFWithPixelBasedColumnWidth.pdf");
worksheet.ToImage("SavePDFWithPixelBasedColumnWidth.png");

//create a new workbook
var workbook = new Workbook();
workbook.Worksheets.Add();

workbook.BookView.AllowSheetReorder = false;
workbook.BookView.TabEditable = false;
workbook.BookView.TabNavigationVisible = false;
workbook.BookView.TabStripPosition = SpreadJSTabStripPosition.Left;
workbook.BookView.TabStripWidth = 150;
workbook.BookView.NewTabVisible = false;
using var file = new FileStream("sheettabposition.json", FileMode.Create);
workbook.ToJson(file);

//create a new workbook
var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

CheckBoxListCellType cellType = new CheckBoxListCellType
{
    Direction = CellTypeDirection.Horizontal,
    TextAlign = CellTypeTextAlign.Right,
    IsFlowLayout = false,
    MaxColumnCount = 2,
    MaxRowCount = 1,
    HorizontalSpacing = 20,
    VerticalSpacing = 5,
    BoxSize = 40,
    AutoBoxSize = true
};
cellType.Items.Add(new SelectFieldItem("sample1", "1"));
cellType.Items.Add(new SelectFieldItem("sample2", "2"));
cellType.Items.Add(new SelectFieldItem("sample3", "3"));
cellType.Items.Add(new SelectFieldItem("sample4", "4"));
cellType.Items.Add(new SelectFieldItem("sample5", "5"));
worksheet.Range["A1:C3"].ColumnWidth = 25;
worksheet.Range["A1:C3"].CellType = cellType;
worksheet.Range["A1:C3"].Value = new object[,]
{
{new object[]{"1", "3", "5" } }
};
using var file = new FileStream("checkboxlistsize.json", FileMode.Create);
workbook.ToJson(file);
}

static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.Open("Picture.json");
    var pic = workbook.ActiveSheet.Shapes[0];
    //Get URL of picture from json file
    string url = pic.PictureFormat.Url;
    byte[] picByte = GetPictureFromUrl(url);
    //Set byte array of picture
    pic.PictureFormat.Fill = picByte;
    //Save to PDF and Excel 
    workbook.Save("PicturePDF.pdf");
    workbook.Save("PictureExcel.xlsx");
}

private static byte[] GetPictureFromUrl(string url)
{
    WebClient wc = new WebClient();
    byte[] imageBytes = wc.DownloadData(url);
    return imageBytes;
}

// Create a new workbook.
var workbook = new Workbook();

// Define a JSON data source.
var 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" +
                 " ]" +
                 "}";

// Add data source to worksheet.
var dataSourceSheet = workbook.Worksheets.Add();
dataSourceSheet.Name = "DataSource";
var table = dataSourceSheet.Tables.Add(dataSourceSheet.Range["A1:E4"], true);

// Set binding path.
table.BindingPath = "ds";
table.Columns[0].DataField = "Area";
table.Columns[1].DataField = "City";
table.Columns[2].DataField = "Category";
table.Columns[3].DataField = "Name";
table.Columns[4].DataField = "Revenue";

// Set data source.
dataSourceSheet.DataSource = new JsonDataSource(dataSource);

// Create pivot table sheet.
var pivotSheet = workbook.Worksheets[0];
pivotSheet.Name = "PivotSheet";

// Create pivot table.
var pivotCache = workbook.PivotCaches.Create(table);
var pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range["A1"], "pivottable1");

// Configure pivot table fields.
var fieldArea = pivotTable.PivotFields["Area"];
fieldArea.Orientation = PivotFieldOrientation.RowField;
var fieldCity = pivotTable.PivotFields["City"];
fieldCity.Orientation = PivotFieldOrientation.RowField;
var fieldName = pivotTable.PivotFields["Name"];
fieldName.Orientation = PivotFieldOrientation.ColumnField;
var fieldRevenue = pivotTable.PivotFields["Revenue"];
fieldRevenue.Orientation = PivotFieldOrientation.DataField;
pivotSheet.UsedRange.AutoFit();
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.Refresh();

var saveOptions = new SerializationOptions();

// Set IncludeBindingSource property to false to exclude the binding source from being exported.
saveOptions.IncludeBindingSource = false;

// Save the workbook.
workbook.ToJson(new FileStream("IncludeBindingSourceOption.ssjson", FileMode.Create), saveOptions);