Alignment Settings
Contents
[
Hide
]
Configuring Alignment Settings
Alignment settings in Microsoft Excel
Anyone who has used Microsoft Excel to format cells will be familiar with the alignment settings in Microsoft Excel.
As you can see from the above figure, there are different kinds of alignment options:
- Text alignment(horizontal & vertical)
- Indentation.
- Orientation.
- Text control.
- Text direction.
All of these alignment settings are fully supported by Aspose.Cells and are discussed in more detail below.
Alignment settings in Aspose.Cells
Aspose.Cells provides a class, Workbook, that represents an 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. Each item in the Cells collection represents an object of the Cell class.
Aspose.Cells provides style and style methods for the Cell class that are used to get and set a cell’s formatting. The Style class provides useful properties for configuring alignment settings.
Select any text alignment type using the TextAlignmentType enumeration. The pre-defined text alignment types in the TextAlignmentType enumeration are:
| Text Alignment Types | Description |
|---|---|
| Bottom | Represents bottom text alignment |
| Center | Represents center text alignment |
| CenterAcross | Represents center across text alignment |
| Distributed | Represents distributed text alignment |
| Fill | Represents fill text alignment |
| General | Represents general text alignment |
| Justify | Represents justify text alignment |
| Left | Represents left text alignment |
| Right | Represents right text alignment |
| Top | Represents top text alignment |
| JustifiedLow | Aligns the text with an adjusted kashida length for Arabic text. |
| ThaiDistributed | Distributes Thai text especially, because each character is treated as a word. |
You can also apply the justify distributed setting using the Style.isJustifyDistributed(boolean) method.
Horizontal Alignment
Use the Style object’s horizontalAlignment method to align the text horizontally.
<!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 () => {
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) {
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 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.putValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
const style = cell.style;
style.horizontalAlignment = AsposeCells.TextAlignmentType.Center;
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>
Vertical Alignment
Similar to horizontal alignment, use the Style object’s verticalAlignment method to align the text vertically.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Create Workbook 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 Workbook object
const workbook = new Workbook();
// Clearing all the worksheets
workbook.worksheets.clear();
// Adding a new worksheet to the Excel object
const i = workbook.worksheets.add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
const worksheet = workbook.worksheets.get(i);
// 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 horizontal/vertical alignment of the text in the "A1" cell via style
const style = cell.style;
style.verticalAlignment = AsposeCells.TextAlignmentType.Center;
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;">Workbook created successfully! Click the download link to get the file.</p>';
});
</script>
</html>
Indentation
It is possible to set the indentation level of the text in a cell with the Style object’s indentLevel method.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Set Cell Indent Level 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>
<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) {
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 = "Visit Aspose!";
// Setting the horizontal alignment of the text in the "A1" cell
const style = cell.style;
// Setting the indentation level of the text (inside the cell) to 2
style.indentLevel = 2;
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 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>
</body>
</html>
Orientation
Set the orientation (rotation) of the text in a cell with the Style object’s rotationAngle method.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example - Create Workbook and Modify Cell</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 () => {
// This example creates a new workbook, updates A1, sets rotation, and saves the file.
const fileInput = document.getElementById('fileInput');
// Instantiate a new Workbook (blank)
const workbook = new Workbook();
// Obtain reference to the first worksheet
const worksheet = workbook.worksheets.get(0);
// Access the "A1" cell from the worksheet
const cell = worksheet.cells.get("A1");
// Adding some value to the "A1" cell
cell.value = "Visit Aspose!";
// Getting the style of the cell
const style = cell.style;
// Setting the rotation of the text (inside the cell) to 25
style.rotationAngle = 25;
// Assign the modified style back to the cell
cell.style = style;
// Save the Excel file in Excel 97-2003 format and provide download link
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData], { type: "application/vnd.ms-excel" });
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;">Workbook created and modified successfully! Click the download link to get the file.</p>';
});
</script>
</html>
Text Control
The following section discusses how to control text by setting text wrapping, shrink to fit and other formatting options.
Wrapping Text
Wrapping text in a cell makes it easier to read: the height of the cell adjusts to fit all the text, instead of cutting it off or spilling over into adjacent cells. Set text wrapping on or off with the Style object’s isTextWrapped(boolean) method.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Wrapping Text Example</title>
</head>
<body>
<h1>Wrapping Text 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');
// Create or load workbook
let workbook;
if (fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Open first Worksheet in the workbook
const ws = workbook.worksheets.get(0);
// Get Worksheet Cells Collection
const cells = ws.cells;
// Increase the width of First Column Width
cells.columns.get(0).width = 35;
// Increase the height of first row
cells.rows.get(0).height = 36;
// Add Text to the First Cell
const cellRef = cells.checkCell(0, 0);
cellRef.value = "I am using the latest version of Aspose.Cells to test this functionality";
// Make Cell's Text wrap
const style = cellRef.style;
style.isTextWrapped = true;
cellRef.style = style;
// Save Excel File
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'WrappingText.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created. Click the download link to get the modified file.</p>';
});
</script>
</html>
Shrinking to Fit
An option to wrapping text in a field is to shrink the text size to fit a cell’s dimensions. This is done by setting the Style object’s shrinkToFit(boolean) method to true.
<!DOCTYPE html>
<html>
<head>
<title>Set Cell Style Example</title>
</head>
<body>
<h1>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, Worksheet, Cell } = 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);
// Access the "A1" cell from the worksheet
const cell = worksheet.cells.get("A1");
// Adding some value to the "A1" cell
cell.value = "Visit Aspose!";
// Getting the style of the cell
const style = cell.style;
// Shrinking the text to fit according to the dimensions of the cell
style.shrinkToFit = true;
// Applying the style back to the cell
cell.style = style;
// Saving the Excel file in Excel97To2003 format
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>
Merging Cells
Like Microsoft Excel, Aspose.Cells supports merging several cells into one. Aspose.Cells provides two approaches to this task. One way is to call the Cells collection’s merge method. The merge method takes the following parameters to merge the cells:
- First row: the first row from where to start merging.
- First column: the first column from where to start merging.
- Number of rows: the number of rows to merge.
- Number of columns: the number of columns to merge.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Merge Cells and Style Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx" />
<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 (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Create a Workbook.
const wbk = new Workbook(new Uint8Array(arrayBuffer));
// Create a Worksheet and get the first sheet.
const worksheet = wbk.worksheets.get(0);
// Create a Cells object to fetch all the cells.
const cells = worksheet.cells;
// Merge some Cells (C6:E7) into a single C6 Cell.
cells.merge(5, 2, 2, 3);
// Input data into C6 Cell.
const cell = cells.get(5, 2);
cell.value = "This is my value";
// Create a Style object to fetch the Style of C6 Cell.
const style = cell.style;
// Create a Font object
const font = style.font;
// Set the name.
font.name = "Times New Roman";
// Set the font size.
font.size = 18;
// Set the font color
font.color = AsposeCells.Color.Blue;
// Bold the text
font.isBold = true;
// Make it italic
font.isItalic = true;
// Set the background color of C6 Cell to Red
style.foregroundColor = AsposeCells.Color.Red;
style.pattern = AsposeCells.BackgroundType.Solid;
// Apply the Style to C6 Cell.
cell.style = style;
// Save the Workbook.
const outputData = wbk.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'mergingcells.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>
The other way is to first call the Cells collection’s createRange method to create a range of the cells to be merged. The createRange method takes the same set of parameters as that of the merge method discussed above and returns a Range object. The Range object also provides a merge method that merges the range specified in the Range object.
Text Direction
It is possible to set the reading order of text in cells. The reading order is the visual order in which characters, words, etc. are displayed. For example, English is a left to right language while Arabic is a right to left language.
The reading order is set with the Style object’s textDirection property. Aspose.Cells provides pre-defined text direction types in the TextDirectionType enumeration.
| Text Direction Types | Description |
|---|---|
| Context | The reading order consistent with the language of the first entered character |
| LeftToRight | Left to right reading order |
| RightToLeft | Right to left reading order |
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example - Modify A1 and Save</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx" />
<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, TextDirectionType } = 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');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Obtaining the reference of 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 = "I am using the latest version of Aspose.Cells to test this functionality.";
// Gets style in the "A1" cell
const style = cell.style;
// Shrinking the text to fit according to the dimensions of the cell
style.textDirection = TextDirectionType.LeftToRight;
// Apply the style back to the cell
cell.style = style;
// Saving the Excel 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.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>