Format cells with JavaScript via C++
Contents
[
Hide
]
Introduction
Aspose.Cells provides the style and style methods of the Cell class, used to get/set the formatting style of a cell. Aspose.Cells also provides a Style class.
How to Format Cells using style Methods
Apply different kinds of formatting styles on cells to set background or foreground colors, borders, fonts, horizontal and vertical alignments, indentation level, text direction, rotation angle and much more.
How to Use the style Methods
If developers need to apply different formatting styles to different cells then it’s better to get the Style of the cell using Cell.style method, specify the style attributes and then apply the formatting using Cell.style method. An example is given below to demonstrate this approach to apply various formatting on a cell.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Styling Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// Instantiate or load workbook
let workbook;
if (fileInput.files && fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Obtaining the reference of the first worksheet
const worksheet = workbook.worksheets.get(0);
// Accessing the "A1" cell from the worksheet
const cell = worksheet.cells.get("A1");
// Adding some value to the "A1" cell
cell.value = "Hello Aspose!";
// Defining a Style object
let style;
// Get the style from A1 cell
style = cell.style;
// Setting the vertical alignment
style.verticalAlignment = AsposeCells.TextAlignmentType.Center;
// Setting the horizontal alignment
style.horizontalAlignment = AsposeCells.TextAlignmentType.Center;
// Setting the font color of the text
style.font.color = AsposeCells.Color.Green;
// Setting to shrink according to the text contained in it
style.shrinkToFit = true;
// Setting the bottom border color to red
style.borders.get(AsposeCells.BorderType.BottomBorder).color = AsposeCells.Color.Red;
// Setting the bottom border type to medium
style.borders.get(AsposeCells.BorderType.BottomBorder).lineStyle = AsposeCells.CellBorderType.Medium;
// Applying the style to A1 cell
cell.style = style;
// Saving the Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Use Style Object to Format Different Cells
If developers need to apply the same formatting style to different cells then they can use Style object. Please follow the steps below to use the Style object:
- Add a Style object by calling the createStyle() method of the Workbook class
- Access the newly added Style object
- Set the desired properties/attributes of the Style object to apply desired formatting settings
- Assign the configured Style object to your desired cells
This approach can greatly improve the efficiency of your applications and save memory too.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example - Create Workbook and Style Cells</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, TextAlignmentType, Color, BorderType, CellBorderType } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Instantiate a new Workbook
const workbook = new Workbook();
// Add a new worksheet to the workbook
const i = workbook.worksheets.add();
// Obtain the newly added worksheet by index
const worksheet = workbook.worksheets.get(i);
// Access cell A1
const cell = worksheet.cells.get("A1");
// Set value of A1
cell.value = "Hello Aspose!";
// Create a new style
const style = workbook.createStyle();
// Set vertical and horizontal alignment
style.verticalAlignment = TextAlignmentType.Center;
style.horizontalAlignment = TextAlignmentType.Center;
// Set font color
style.font.color = Color.Green;
// Shrink to fit
style.shrinkToFit = true;
// Set bottom border color and style
style.borders.get(BorderType.BottomBorder).color = Color.Red;
style.borders.get(BorderType.BottomBorder).lineStyle = CellBorderType.Medium;
// Apply style to A1
cell.style = style;
// Apply same style to B1, C1, D1
worksheet.cells.get("B1").style = style;
worksheet.cells.get("C1").style = style;
worksheet.cells.get("D1").style = style;
// Save workbook to XLS format and provide download link
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created and styled successfully! Click the download link to get the file.</p>';
});
</script>
</html>
How to Use Microsoft Excel 2007 Predefined Styles
If you need to apply different formatting styles for Microsoft Excel 2007, apply styles using the Aspose.Cells API. An example is given below to demonstrate this approach to apply a predefined style on a cell.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Create Workbook and Set Cell Style Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Instantiate a new Workbook.
const workbook = new Workbook();
// Create a style object.
const style = workbook.createStyle();
// Access the first worksheet in the workbook
const worksheet = workbook.worksheets.get(0);
// Access cell A1
const cell = worksheet.cells.get("A1");
// Input a value to A1 cell.
cell.putValue("Test");
// Apply the style to the cell.
cell.style = style;
// Saving the Excel 2007 file.
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created successfully! Click the download link to get the file.</p>';
});
</script>
</html>
How to Format Selected Characters in a Cell
Dealing with Font Settings explains how to format text in cells, but it only explains how to format all of the cell content. What if you want to format only selected characters?
Aspose.Cells supports this feature too. This topic explains how to use this feature effectively.
How to Format Selected Characters
Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains the worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection. Each item in the cells collection represents an object of the Cell class.
The Cell class provides the characters(number, number) method that takes the following parameters to select a range of characters inside a cell:
- Start Index, the index of the character that the selection starts from.
- Number of Characters, the number of characters to select.
The characters(number, number) method returns an instance of the FontSetting class that allows developers to format the characters in the same way as they would a cell as shown below in the code example. In the output file, in the A1 cell, the word ‘Visit’ will be formatted with the default font but ‘Aspose!’ is bold and blue.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
<meta charset="utf-8" />
</head>
<body>
<h1>Aspose.Cells Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// If a file is provided, open it; otherwise create a new workbook
let workbook;
if (fileInput.files && fileInput.files.length > 0) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Obtaining the reference of the first (default) worksheet by passing its sheet index
const worksheet = workbook.worksheets.get(0);
// Accessing the "A1" cell from the worksheet
const cell = worksheet.cells.get("A1");
// Adding some value to the "A1" cell
cell.value = "Visit Aspose!";
// Setting the font of selected characters to bold and color to blue
const charRange = cell.characters(6, 7);
charRange.font.isBold = true;
charRange.font.color = AsposeCells.Color.Blue;
// Saving the Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
If you are interested in formatting a portion of Rich Text in a cell, consider using the Cell.characters & Cell.characters methods. The Cell.characters method is to be used to access the portions of the text and then amendments can be done using the Cell.characters method whereas the Get method returns an array of FontSetting objects which can be manipulated to set various properties such as font name, font color, boldness, etc. and Set method can be used to apply the changes.
How to Format Rows and Columns
Sometimes, developers need to apply the same formatting on rows or columns. Applying formatting on cells one by one often takes longer and is not a good solution. To address this issue, Aspose.Cells provides a simple, fast way discussed in detail in this article.
Formatting Rows & Columns
Aspose.Cells provides a class, the Workbook that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection. The cells collection provides a rows collection.
How to Format a Row
Each item in the rows collection represents a Row object. The Row object offers the applyStyle(Style, StyleFlag) method used to set the row’s formatting. To apply the same formatting to a row, use the Style object. The steps below show how to use it.
- Add a Style object to the Workbook class by calling its createStyle() method.
- Set the Style object’s properties to apply formatting settings.
- Make the relevant attributes ON for the StyleFlag object.
- Assign the configured Style object to the Row object.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Instantiating a Workbook object
const workbook = new Workbook();
// Obtaining the reference of the first (default) worksheet by passing its sheet index
const worksheet = workbook.worksheets.get(0);
// Adding a new Style to the styles
const style = workbook.createStyle();
// Setting the vertical alignment of the text in the "A1" cell
style.verticalAlignment = AsposeCells.TextAlignmentType.Center;
// Setting the horizontal alignment of the text in the "A1" cell
style.horizontalAlignment = AsposeCells.TextAlignmentType.Center;
// Setting the font color of the text in the "A1" cell
style.font.color = AsposeCells.Color.Green;
// Shrinking the text to fit in the cell
style.shrinkToFit = true;
// Setting the bottom border color of the cell to red
style.borders.get(AsposeCells.BorderType.BottomBorder).color = AsposeCells.Color.Red;
// Setting the bottom border type of the cell to medium
style.borders.get(AsposeCells.BorderType.BottomBorder).lineStyle = AsposeCells.CellBorderType.Medium;
// Creating StyleFlag
const styleFlag = new AsposeCells.StyleFlag();
styleFlag.horizontalAlignment = true;
styleFlag.verticalAlignment = true;
styleFlag.shrinkToFit = true;
styleFlag.borders = true;
styleFlag.fontColor = true;
// Accessing a row from the Rows collection
const row = worksheet.cells.rows.get(0);
// Assigning the Style object to the Style property of the row
row.applyStyle(style, styleFlag);
// Saving the Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Format a Column
The cells collection also provides a columns collection. Each item in the columns collection represents a Column object. Similar to a Row object, the Column object also offers the applyStyle(Style, StyleFlag) method for formatting a column.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Style and Column Apply Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Instantiate a new Workbook
const workbook = new Workbook();
// Obtain the first worksheet
const worksheet = workbook.worksheets.get(0);
// Add a new Style to the styles
const style = workbook.createStyle();
// Set vertical and horizontal alignment
style.verticalAlignment = AsposeCells.TextAlignmentType.Center;
style.horizontalAlignment = AsposeCells.TextAlignmentType.Center;
// Set font color
style.font.color = AsposeCells.Color.Green;
// Shrink to fit
style.shrinkToFit = true;
// Set bottom border color and style
style.borders.get(AsposeCells.BorderType.BottomBorder).color = AsposeCells.Color.Red;
style.borders.get(AsposeCells.BorderType.BottomBorder).lineStyle = AsposeCells.CellBorderType.Medium;
// Create and configure StyleFlag
const styleFlag = new AsposeCells.StyleFlag();
styleFlag.horizontalAlignment = true;
styleFlag.verticalAlignment = true;
styleFlag.shrinkToFit = true;
styleFlag.borders = true;
styleFlag.fontColor = true;
// Access first column and apply style
const column = worksheet.cells.getColumns().get(0);
column.applyStyle(style, styleFlag);
// Save the workbook and provide download link
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'book1.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Style applied to column successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>