Insertion et suppression de lignes et colonnes dans un fichier Excel
Contents
[
Hide
]
Introduction
Que vous créiez une nouvelle feuille de calcul à partir de zéro ou travailliez sur une feuille de calcul existante, vous pouvez avoir besoin d’ajouter des lignes ou des colonnes supplémentaires pour accueillir plus de données. Inversement, vous pouvez également avoir besoin de supprimer des lignes ou des colonnes à partir de positions spécifiées dans la feuille de calcul. Pour répondre à ces besoins, Aspose.Cells for JavaScript via C++ fournit un ensemble très simple de classes et méthodes, expliquées ci-dessous.
Gérer les lignes et les colonnes
Aspose.Cells for JavaScript via C++ fournit une classe Workbook, qui représente un fichier Microsoft Excel. La classe Workbook contient une collection Worksheets qui permet d’accéder à chaque feuille de calcul d’un fichier Excel. Une feuille de calcul est représentée par la classe Worksheet. La classe Worksheet fournit une collection cells qui représente toutes les cellules de la feuille de calcul.
La collection cells offre plusieurs méthodes pour gérer les lignes et colonnes dans une feuille. Certaines sont expliquées ci-dessous.
Lorsque des lignes ou des colonnes sont ajoutées, le contenu de la feuille est décalé vers le bas ou vers la droite, et si des lignes ou colonnes sont supprimées, le contenu est décalé vers le haut ou vers la gauche.
Insérer des lignes et des colonnes
Comment Insérer une Ligne
Insérez une ligne dans la feuille à n’importe quelle position en appelant la méthode insertRow(number) de la collection cells. La méthode insertRow(number) prend l’indice de la ligne où la nouvelle ligne sera insérée.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Insert Row 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 (!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();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Inserting a row into the worksheet at 3rd position (index 2)
worksheet.cells.insertRow(2);
// Saving the modified Excel file as Excel97To2003 (.xls)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Row inserted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Comment Insérer Plusieurs Lignes
Pour insérer plusieurs lignes dans une feuille, appelez la méthode insertRows(number, number, boolean) de la collection cells. La méthode insertRows(number, number, boolean) prend deux paramètres :
- Index de la ligne, l’index de la ligne à partir de laquelle les nouvelles lignes seront insérées.
- Nombre de lignes, le nombre total de lignes à insérer.
<!DOCTYPE html>
<html>
<head>
<title>Insert Rows Example</title>
</head>
<body>
<h1>Insert Rows 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 (!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();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Inserting 10 rows into the worksheet starting at index 2
worksheet.cells.insertRows(2, 10);
// Saving the modified Excel file (Excel 97-2003 format)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Rows inserted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Comment insérer une ligne avec mise en forme
Pour insérer une ligne avec des options de mise en forme, utilisez la surcharge insertRows(number, number, boolean) qui prend InsertOptions en paramètre. Configurez la propriété CopyFormatType de la classe InsertOptions avec l’énumération CopyFormatType. L’énumération CopyFormatType possède trois membres listés ci-dessous.
- SameAsAbove : Met en forme la ligne comme la ligne ci-dessus.
- SameAsBelow : Formate la ligne de la même manière que la ligne ci-dessous.
- Clear: Efface la mise en forme.
<!DOCTYPE html>
<html>
<head>
<title>Inserting a Row With Formatting Example</title>
</head>
<body>
<h1>Inserting a Row With Formatting 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, InsertOptions, CopyFormatType, Utils } = 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();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Setting Formatting options
const insertOptions = new InsertOptions();
insertOptions.copyFormatType = CopyFormatType.SameAsAbove;
// Inserting a row into the worksheet at 3rd position
worksheet.cells.insertRows(2, 1, insertOptions);
// Saving the modified 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 = 'InsertingARowWithFormatting.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Row inserted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Comment insérer une colonne
Les développeurs peuvent également insérer une colonne dans la feuille à n’importe quelle position en appelant la méthode insertColumn(number, boolean) de la collection cells. La méthode insertColumn(number, boolean) prend l’indice de la colonne où la nouvelle colonne sera insérée.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Insert Column Example</title>
</head>
<body>
<h1>Insert Column 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, Utils } = 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();
// Instantiating a Workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Inserting a column into the worksheet at 2nd position
worksheet.cells.insertColumn(1);
// Saving the modified Excel file (Excel97-2003 format for .xls)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Column inserted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Supprimer des lignes et des colonnes
Comment supprimer plusieurs lignes
Pour supprimer plusieurs lignes d’une feuille, appelez la méthode deleteRows(number, number) de la collection cells. La méthode deleteRows(number, number) prend deux paramètres :
- Index de la ligne, l’index de la ligne à partir de laquelle les lignes seront supprimées.
- Nombre de lignes, le nombre total de lignes à supprimer.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Delete Rows Example</title>
</head>
<body>
<h1>Delete Rows 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, Utils } = 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();
// Instantiating a Workbook object with file buffer
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Deleting 10 rows from the worksheet starting from 3rd row (index 2)
worksheet.cells.deleteRows(2, 10);
// Saving the modified 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 = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Rows deleted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Comment supprimer une colonne
Pour supprimer une colonne de la feuille à n’importe quelle position, appelez la méthode deleteColumn(number, boolean) de la collection cells. La méthode deleteColumn(number, boolean) prend l’indice de la colonne à supprimer.
<!DOCTYPE html>
<html>
<head>
<title>Delete Column Example</title>
</head>
<body>
<h1>Delete Column 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, Utils } = 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();
// Instantiating a Workbook object by opening the Excel file from the file input
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Deleting a column from the worksheet at 5th position (zero-based index 4)
worksheet.cells.deleteColumn(4);
// Saving the modified 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 = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Column deleted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>