[]
        
(Showing Draft Content)

Style and Formatting Settings

DsExcel Java supports the JSON I/O of SpreadJS 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.

// 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.

In addition to this, DsExcel also provides CellPadding and Margin class, ILabelOptions interface, LabelAlignment and LabelVisibility enumerations to support cell padding and labels in DsExcel.

The following example code adds cell padding and labels in a DsExcel worksheet.

// 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 method of IRange interface, which uses the instances of ICornerFold and ICellDecorationIcon interfaces. DsExcel also provides CornerPosition and IconPosition enumerations to set the position of the corner fold and icon.

You must create instances of ICornerFold and ICellDecorationIcon interfaces using CornerFold and CellDecorationIcon 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:

// 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");


Refer to the following example code to remove the cell decoration completely:

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

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

// 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:

// 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 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 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



// 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


Left alignment


Center Alignment


Orientation greater than zero


Orientation less than zero


!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.

DsExcel also provides getBackgroundPictures method in IWorksheet interface to add background pictures in DsExcel. For more information, refer Support Sheet Background Image.

The following example code sets background image in DsExcel worksheet.

// 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.

// 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.

DsExcel also provides setBackColor and setGrayAreaBackColor methods in IWorkbookView interface to set background color in DsExcel.

The following code example sets background color for all the worksheets in DsExcel.

// 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 method in the IPictureFormat interface. This URL is then converted to byte array and set to the picture by using setFill method of the IPictureFormat 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.

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();
}