# Style and Formatting Settings

Using DsExcel, you can perform the json I/O of SpreadJS files. Various supported SpreadJS features include shapes, barcodes, cell buttons, background image and color, validation styles etc.

## Content

DsExcel Java supports the JSON I/O of [SpreadJS](https://developer.mescius.com/spreadjs/docs/overview) files. You can also import an ssjson file created with SpreadJS Designer and save it back after modifying it as per your preferences.

## Text Ellipsis

When text in a cell is longer than the column width, SpreadJS allows you to show ellipsis instead of overflowing text in the other cell. The SpreadJS files containing text ellipsis are supported for JSON I/O and PDF exporting in DsExcel. You can also download the JSON file containing text ellipsis from [here](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/jsonfiles/textellipsis.rar).

```auto
// Create a new workbook
Workbook workbook = new Workbook();
InputStream fileStream = this.getResourceStream("json/TextEllipsis.json");

workbook.fromJson(fileStream);    

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

**Limitation**
SpreadJS allows different types of text alignment composed with text ellipsis but DsExcel does not. Hence, text ellipsis is only shown at the end of text in exported PDF.

## Cell Padding and Labels

DsExcel allows you to perform JSON I/O and PDF exporting for SpreadJS files containing cell padding and labels. You can also download the JSON file containing cell padding and labels from [here](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/jsonfiles/cellpaddingandlabel.rar).
In addition to this, DsExcel also provides [CellPadding](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CellPadding.html) and [Margin](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/Margin.html) class, [ILabelOptions](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ILabelOptions.html) interface, [LabelAlignment](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ILabelOptions.html) and [LabelVisibility](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/LabelVisibility.html) enumerations to support cell padding and labels in DsExcel.
The following example code adds cell padding and labels in a DsExcel worksheet.

```Java
// Create a new workbook
Workbook workbook = new Workbook();
// Get the sheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
// Set row height
worksheet.getRange("A:A").setRowHeight(40);
// Set column width
worksheet.getRange("A:A").setColumnWidth(25);
// Set watermark
worksheet.getRange("A1").setWatermark("JAVA");
// Set cell padding
worksheet.getRange("A1").setCellPadding(new CellPadding(50, 0, 0, 0));
// Set label options
worksheet.getRange("A1").getLabelOptions().setVisibility(LabelVisibility.visible);
worksheet.getRange("A1").getLabelOptions().setForeColor(Color.GetGreen());
worksheet.getRange("A1").getLabelOptions().setMargin(new Margin(15, 0, 0, 0));
worksheet.getRange("A1").getLabelOptions().getFont().setSize(14);
worksheet.getRange("A1").getLabelOptions().getFont().setName("Calibri");
worksheet.getRange("A1").getBorders().setLineStyle(BorderLineStyle.Thin);

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

## Cell Decoration

DsExcel allows you to add decorations to cells or cell ranges in the form of corner folds or icons using [setDecoration](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IRange.html#setDecoration) method of [IRange](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IRange.html) interface, which uses the instances of [ICornerFold](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICornerFold.html) and [ICellDecorationIcon](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICellDecorationIcon.html) interfaces. DsExcel also provides [CornerPosition](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CornerPosition.html) and [IconPosition](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IconPosition.html) enumerations to set the position of the corner fold and icon.
You must create instances of [ICornerFold](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICornerFold.html) and [ICellDecorationIcon](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/ICellDecorationIcon.html) interfaces using [CornerFold](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CornerFold.html) and [CellDecorationIcon](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/CellDecorationIcon.html) constructors and configure the corner fold and icon before setting the decoration of a cell or cell range.
Refer to the following example code to add cell decoration to the cells:

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

// Access first worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add values to cell range.
worksheet.getRange("C4").setValue("FY 2019");
worksheet.getRange("C5").setValue("Sales");
worksheet.getRange("C6").setValue("Monthly");

String[] months = { "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar" };
int[] monthlySales = { 188897, 208146, 226196, 277318, 263273, 259845, 241047, 256306, 195845, 204934, 257852, 227779 };

for (int i = 0; i < months.length; i++) {
    worksheet.getRange("B" + (7 + i)).setValue(months[i]);
    worksheet.getRange("C" + (7 + i)).setValue(monthlySales[i]);
}

// Set color using string.
worksheet.getRange("B4:C6").getInterior().setColor(Color.GetLightBlue());
worksheet.getRange("C4:C6").getBorders().setColor(Color.GetBlack());
worksheet.getRange("B7:B18").getInterior().setColor(Color.GetLightGray());
worksheet.getRange("B7:C18").getBorders().setColor(Color.GetBlack());

// Set cell range style.
worksheet.getRange("B4:B6").getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.Thin);
worksheet.getRange("B4:B6").getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.Thin);
worksheet.getRange("B4:B6").merge();
worksheet.getRange("C2:D18").setColumnWidth(15);
worksheet.getRange("B2:D18").setHorizontalAlignment(HorizontalAlignment.Center);
worksheet.getRange("B2:C18").setVerticalAlignment(VerticalAlignment.Center);
worksheet.getRange("B4:C6").getFont().setBold(true);
worksheet.getRange("C7:C18").setNumberFormat("#,##0");
worksheet.getRange("C2").setHorizontalAlignment(HorizontalAlignment.Right);

// Hightlight highest sales using cell decoration.
ICornerFold cornerFold1 = new CornerFold(Color.GetRed(), EnumSet.of(CornerPosition.LeftTop), 8);
ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon(
        "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZ"
        + "pZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3"
        + "cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyI"
        + "iBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiI"
        + "GNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3"
        + "IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi4"
        + "1NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40N"
        + "Dc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K",
        12,
        12,
        IconPosition.OutsideRight);
worksheet.getRange("C10").setDecoration(new com.grapecity.documents.excel.CellDecoration(cornerFold1, Collections.singletonList(cellDecorationIcon1)));
worksheet.getRange("D10").setValue("Highest");

// Hightlight lowest sales using cell decoration.
ICornerFold cornerFold2 = new CornerFold(Color.GetGreen(), EnumSet.of(CornerPosition.LeftTop), 8);
ICellDecorationIcon cellDecorationIcon2 = new CellDecorationIcon(
        "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWd"
        + "odD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIge"
        + "G1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2"
        + "lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8"
        + "cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZ"
        + "CIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NT"
        + "IyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMi"
        + "A2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3I"
        + "DUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=",
        12,
        12,
        IconPosition.OutsideRight);
worksheet.getRange("C7").setDecoration(new com.grapecity.documents.excel.CellDecoration(cornerFold2, Collections.singletonList(cellDecorationIcon2)));
worksheet.getRange("D7").setValue("Lowest");

// Save the workbook to .sjs document.
workbook.save("CellDecoration.sjs");
```

![DsExcel Java exports CellDecoration icons to an SJS file, displaying corner folds and outside-right indicators for highlighted sales values.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/cell-decoration.png)
Refer to the following example code to remove the cell decoration completely:

```Java
// Remove cell decoration.
worksheet.getRange("C7").setDecoration(null);
```

Refer to the following example code to remove only cell icon decoration:

```Java
// Remove icon decoration.
worksheet.getRange("C7").setDecoration(new CellDecoration(cornerFold2, null));

// Or

worksheet.getRange("C7").setDecoration(new CellDecoration(cornerFold2));
```

Refer to the following example code to remove only cell corner fold decoration:

```Java
// Remove corner fold decoration.
worksheet.getRange("C7").setDecoration(new CellDecoration(null, Arrays.asList(cellDecorationIcon2)));

// Or

worksheet.getRange("C7").setDecoration(new CellDecoration(Arrays.asList(cellDecorationIcon2)));
```

>type=note
> **Note**: Cell decoration is a SpreadJS feature, and DsExcel only supports exporting it to .sjs and .ssjson formats.

## Numbers Fit Mode

In MS Excel, when a number or date does not fit in the available cell width, it masks the cell value and displays "####" in the cell. To overcome this, DsExcel provides [NumbersFitMode](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/NumbersFitMode.html) enumeration so that you can choose to either mask or show entire number or date value when cell is not wide enough to accommodate the entire value. The enumeration can be set using the [setNumbersFitMode](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorkbookView.html#setNumbersFitMode) method and can have "Mask" or "Overflow" values. To avoid displaying "####", you can set the enumeration option to "Overflow" so that overflowing value occupies the space of blank neighboring cell. No overflow happens and only partial value is displayed in case the cell itself or the neighboring cell is a merged cell or has value in it.

| **NumbersFitMode = Mask** | **NumbersFitMode = Overflow** |
| --------------------- | ------------------------- |
| ![DsExcel Java uses setNumbersFitMode with NumbersFitMode.Mask so an oversized numeric value displays as hash characters inside the cell.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/numbersfitmode=0.png) | ![DsExcel Java uses setNumbersFitMode with NumbersFitMode.Overflow so the complete numeric value extends into adjacent blank cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/numbersfitmode=1.png) |

```Java
// Set numbersFitMode is overflow.
workbook.getBookView().setNumbersFitMode(NumbersFitMode.Overflow);
```

This overflow behavior and direction vary according to the horizontal alignment and orientation of the cell values. The following table displays a value longer than the available width and its overflow behavior with different horizontal alignment and orientation.

| **Horizontal Alignment/Orientation** | **Overflow Behavior** |
| -------------------------------- | ----------------- |
| General or right alignment | ![DsExcel Java NumbersFitMode.Overflow extends a general or right-aligned numeric value leftward across available neighboring cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/right.png) |
| Left alignment | ![DsExcel Java NumbersFitMode.Overflow extends a left-aligned numeric value rightward across available neighboring blank cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/left.png) |
| Center Alignment | ![DsExcel Java NumbersFitMode.Overflow extends a center-aligned numeric value in both directions across available neighboring cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/center.png) |
| Orientation greater than zero | ![DsExcel Java NumbersFitMode.Overflow displays a positively rotated numeric value diagonally across neighboring blank worksheet cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/morethanzero.png) |
| Orientation less than zero | ![DsExcel Java NumbersFitMode.Overflow displays a negatively rotated numeric value diagonally across neighboring blank worksheet cells.](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/lessthanzero.png) |

>type=note
> **Note**: As MS Excel does not support the NumbersFitMode, the NumbersFitMode.Overflow option is not effective on exporting the worksheet to MS Excel.

## Background Image

DsExcel supports JSON I/O and PDF exporting of SpreadJS files containing background images. You can also download the JSON file containing background image from [here](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/jsonfiles/backgroundimage.rar).
DsExcel also provides [getBackgroundPictures](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorksheet.html#getbackgroundpicture) method in [IWorksheet](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorksheet.html) interface to add background pictures in DsExcel. For more information, refer [Support Sheet Background Image](/document-solutions/java-excel-api/docs/online/v9.2/ManageFileOperations/ExporttoaPDFFile/pdf-export-options#support-sheet-background-image).
The following example code sets background image in DsExcel worksheet.

```Java
// Create a new workbook
Workbook workbook = new Workbook();
// Get the sheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
// Load an image from a specific file in input stream
InputStream stream = new FileInputStream("image.png");
// Add background picture
IBackgroundPicture picture = worksheet.getBackgroundPictures().addPictureInPixel(stream, ImageType.PNG, 10, 10,
        500, 370);
// Set image layout
picture.setBackgroundImageLayout(ImageLayout.Zoom);
// Set options
workbook.getActiveSheet().getPageSetup().setPrintGridlines(true);
// Save to a pdf file
workbook.save("BackgroundImage.pdf");
```

The following example code imports background image from JSON and exports to PDF document.

```Java
// Create a new workbook
Workbook workbook = new Workbook();
// Load JSON file
FileInputStream stream = new FileInputStream("BackgroundImage.json");
workbook.fromJson(stream);
// Save file
workbook.save("BackgroundImage.pdf");
```

## Background Color

DsExcel supports JSON I/O and PDF exporting of SpreadJS files containing background color. You can also download the JSON file containing background color from [here](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/jsonfiles/backgroundcolor.rar).
DsExcel also provides [setBackColor](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorkbookView.html#setbackcolor) and [setGrayAreaBackColor](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorkbookView.html#setgrayareabackcolor) methods in [IWorkbookView](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/IWorkbookView.html) interface to set background color in DsExcel.
The following code example sets background color for all the worksheets in DsExcel.

```Java
// Create a new workbook
Workbook workbook = new Workbook();
// Get the sheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Set background color
workbook.getBookView().setBackColor(Color.GetLightSkyBlue());
workbook.getBookView().setGrayAreaBackColor(Color.GetGray());

worksheet.getRange("H20").setValue("The text");

// Set page options
worksheet.getPageSetup().setPrintGridlines(true);
worksheet.getPageSetup().setPrintHeadings(true);

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

**Limitation**
In SpreadJS, background image always overrides the background color. Thus, the background image needs to be removed for the background color to take effect while exporting to PDF documents.

## Get Picture URL

DsExcel allows you to get the URL of a picture from a json file using [getUrl](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/drawing/IPictureFormat.html#geturl) method in the [IPictureFormat](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/drawing/IPictureFormat.html) interface. This URL is then converted to byte array and set to the picture by using [setFill](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/drawing/IPictureFormat.html#setfill) method of the [IPictureFormat](/document-solutions/java-excel-api/api/online/v9.2/com/grapecity/documents/excel/drawing/IPictureFormat.html) interface. This allows you to export the json file containing picture URL to an Excel or PDF file.
Refer to the following example code which gets the URL of a picture from JSON file and exports it to Excel and PDF formats.

```Java
private static byte[] GetPicFromUrl(String urlString) throws MalformedURLException, UnsupportedEncodingException {

    URL url = new URL(encode(urlString));
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    try (InputStream inputStream = url.openStream()) {
        int n = 0;
        byte[] buffer = new byte[1024];
        while (-1 != (n = inputStream.read(buffer))) {
            baos.write(buffer, 0, n);
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    return baos.toByteArray();
}

private static String encode(String url) throws UnsupportedEncodingException {
    char[] charArray = url.toCharArray();
    StringBuilder sb = new StringBuilder();
    for (char c : charArray) {
        if (c >= 0 && c < 255) {
            sb.append(c);
        } else {
            sb.append(URLEncoder.encode(String.valueOf(c), "UTF-8"));
        }
    }
    return sb.toString();
}
```