This module uses the DevExpress Spreadsheet API to work with rows and columns in Excel documents. You can process the predefined sample file or supply your own document. To do the latter, select Upload a File in the file selection drop-down menu.
Select whether you want to manage rows or columns. Specify the operation type and related settings.
Use the Process and Save as ... dropdown button to select the output format, execute the selected operation, and download the result.
Select a Document
InvestmentPortfolio.xlsx
Modify Rows
using DevExpress.Office;
using DevExpress.Spreadsheet;
using Microsoft.AspNetCore.Mvc;
// Rows
Stream AddRows(Stream inputStream, int rowCount, int startRowIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Insert rows into the worksheet at the specified position.
firstWorksheet.Rows.Insert(startRowIndex, rowCount);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream RemoveRows(Stream inputStream, int rowCount, int startRowIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Remove rows from the worksheet starting with the specified position.
firstWorksheet.Rows.Remove(startRowIndex, rowCount);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream SetSizeRows(Stream inputStream, int rowCount, int startRowIndex, DocumentUnit unit, double rowHeight, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
workbook.Unit = unit;
// Set rows height
for (int i = startRowIndex; i < startRowIndex + rowCount; i++) {
firstWorksheet.Rows[i].Height = rowHeight;
}
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream HideRows(Stream inputStream, int rowCount, int startRowIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Hide rows starting with the specified position.
firstWorksheet.Rows.Hide(startRowIndex, startRowIndex + rowCount - 1);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream AutoFitRows(Stream inputStream, int rowCount, int startRowIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Auto Fit rows starting with the specified position
firstWorksheet.Rows.AutoFit(startRowIndex, startRowIndex + rowCount - 1);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
// Columns
Stream AddColumns(Stream inputStream, int columnCount, int startColumnIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Insert columns into the worksheet at the specified position
firstWorksheet.Columns.Insert(startColumnIndex, columnCount);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream RemoveColumns(Stream inputStream, int columnCount, int startColumnIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Remove columns from the worksheet starting with the specified position
firstWorksheet.Columns.Remove(startColumnIndex, columnCount);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream SetSizeColumns(Stream inputStream, int columnCount, int startColumnIndex, DocumentUnit unit, double columnWidth, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
workbook.Unit = unit;
// Set columns height
for (int i = startColumnIndex; i < startColumnIndex + columnCount; i++) {
firstWorksheet.Columns[i].Width = columnWidth;
}
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream HideColumns(Stream inputStream, int columnCount, int startColumnIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Hide columns starting with the specified position
firstWorksheet.Columns.Hide(startColumnIndex, startColumnIndex + columnCount - 1);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}
Stream AutoFitColumns(Stream inputStream, int columnCount, int startColumnIndex, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
Worksheet firstWorksheet = workbook.Worksheets.First();
// Auto Fit columns starting with the specified position
firstWorksheet.Columns.AutoFit(startColumnIndex, startColumnIndex + columnCount - 1);
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}