# 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/com/grapecity/documents/excel/CellPadding.html) and [Margin](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/Margin.html) class, [ILabelOptions](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/ILabelOptions.html) interface, [LabelAlignment](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/ILabelOptions.html) and [LabelVisibility](/document-solutions/java-excel-api/api/online/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/com/grapecity/documents/excel/IRange.html#setDecoration) method of [IRange](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IRange.html) interface, which uses the instances of [ICornerFold](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/ICornerFold.html) and [ICellDecorationIcon](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/ICellDecorationIcon.html) interfaces. DsExcel also provides [CornerPosition](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/CornerPosition.html) and [IconPosition](/document-solutions/java-excel-api/api/online/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/com/grapecity/documents/excel/ICornerFold.html) and [ICellDecorationIcon](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/ICellDecorationIcon.html) interfaces using [CornerFold](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/CornerFold.html) and [CellDecorationIcon](/document-solutions/java-excel-api/api/online/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");
```

![](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/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/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** |
| --------------------- | ------------------------- |
| ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/numbersfitmode=0.png) | ![](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 | ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/right.png) |
| Left alignment | ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/left.png) |
| Center Alignment | ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/center.png) |
| Orientation greater than zero | ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/morethanzero.png) |
| Orientation less than zero | ![](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/com/grapecity/documents/excel/IWorksheet.html#getbackgroundpicture) method in [IWorksheet](/document-solutions/java-excel-api/api/online/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/ManageFileOperations/ExporttoaPDFFile/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");
```

**Limitations**

* While importing from JSON, the background image is placed at the (left : 0, top: 0) location of each worksheet.
* After exporting to PDF, all pages of PDF document will have the same background image as was imported from ssjson

## 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/com/grapecity/documents/excel/IWorkbookView.html#setbackcolor) and [setGrayAreaBackColor](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IWorkbookView.html#setgrayareabackcolor) methods in [IWorkbookView](/document-solutions/java-excel-api/api/online/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/com/grapecity/documents/excel/drawing/IPictureFormat.html#geturl) method in the [IPictureFormat](/document-solutions/java-excel-api/api/online/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/com/grapecity/documents/excel/drawing/IPictureFormat.html#setfill) method of the [IPictureFormat](/document-solutions/java-excel-api/api/online/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();
}
```