Style and Formatting Settings

DsExcel .NET support the JSON I/O of SpreadJS files. You can also import a .sjs file or a .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
var workbook = new GrapeCity.Documents.Excel.Workbook();

workbook.Open("TextEllipsis.json");

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

Cell Decoration

DsExcel allows you to add decorations to cells or cell ranges in the form of corner folds or icons using Decoration property 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.Worksheets[0];

// Add values to cell range.
worksheet.Range["C4"].Value = "FY 2019";
worksheet.Range["C5"].Value = "Sales";
worksheet.Range["C6"].Value = "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.Range[$"B{7 + i}"].Value = months[i];
    worksheet.Range[$"C{7 + i}"].Value = monthlySales[i];
}

// Set color using string.
worksheet.Range["B4:C6"].Interior.Color = Color.FromArgb(173, 216, 230);
worksheet.Range["C4:C6"].Borders.Color = Color.FromArgb(0, 0, 0);
worksheet.Range["B7:B18"].Interior.Color = Color.FromArgb(211, 211, 211);
worksheet.Range["B7:C18"].Borders.Color = Color.FromArgb(0, 0, 0);

// Set cell range style.
worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
worksheet.Range["B4:B6"].Merge();
worksheet.Range["C2:D18"].ColumnWidth = 15;
worksheet.Range["B2:D18"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["B2:C18"].VerticalAlignment = VerticalAlignment.Center;
worksheet.Range["B4:C6"].Font.Bold = true;
worksheet.Range["C7:C18"].NumberFormat = "#,##0";
worksheet.Range["C2"].HorizontalAlignment = HorizontalAlignment.Right;

// Hightlight highest sales using cell decoration.
ICornerFold cornerFold1 = new CornerFold(Color.Red, CornerPosition.LeftTop, 8);
ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon(
    "data:image/svg+xml;base64" +
    ",PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxM" +
    "iAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMD" +
    "Avc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWl" + 
    "naHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVu" +
    "dCIvPgo8cGF0aCBmaWxsLXJ1bGU9I" +
    "mV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTT" +
    "cgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi" +
    "41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM"
      + "3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTI" +
      "gNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIg" +
      "MTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K",
    12,
    12,
    IconPosition.OutsideRight);
worksheet.Range["C10"].Decoration = new CellDecoration(cornerFold1, new List<ICellDecorationIcon>() { cellDecorationIcon1 });
worksheet.Range["D10"].Value = "Highest";

// Hightlight lowest sales using cell decoration.
ICornerFold cornerFold2 = new CornerFold(Color.Green, CornerPosition.LeftTop, 8);
ICellDecorationIcon cellDecorationIcon2 = new CellDecorationIcon(
    "data:image/svg+xml;base64," +
    "PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdC" +
    "b3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh" +
    "0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnI" +
    "j4KPHJlY3Qgd2lkdGg9IjEyIiBoZW" + 
    "lnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvP" +
    "go8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXA" +
    "tcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0" +
    "NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA" +
    "3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyO" +
    "CAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMT" +
    "FDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxM" +
    "FoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=",
    12,
    12,
    IconPosition.OutsideRight);
worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2, new List<ICellDecorationIcon>() { cellDecorationIcon2 });
worksheet.Range["D7"].Value = "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.Range["C7"].Decoration = null;

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

// Remove icon decoration.
worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2, null);
            
// Or

worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2);

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

// Remove corner fold decoration.
worksheet.Range["C7"].Decoration = new CellDecoration(null, new List<ICellDecorationIcon>() { cellDecorationIcon2 });

// Or

worksheet.Range["C7"].Decoration = new CellDecoration(new List<ICellDecorationIcon>() { cellDecorationIcon2 });

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 accessed through IWorkbookView.NumbersFitMode property and can have "Mask" or "Overflow" values. To avoid displaying "####", you can set the property 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.BookView.NumbersFitMode = GrapeCity.Documents.Excel.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

Note: As MS Excel does not support the NumbersFitMode, IWorkbookView.NumbersFitMode = Overflow 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 BackgroundPictures property 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
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");

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

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

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 BackColor and GrayAreaBackColor properties 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
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");

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 Url property in the IPictureFormat interface. This URL is then converted to a byte array and set to the picture by using Fill property of the IPictureFormat interface. This allows you to export the json file containing picture URL to an Excel or a 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.

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