C#でセル範囲をトリムする方法 Interopなし

using IronXL;

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet workSheet = workBook.DefaultWorkSheet;

workSheet["A2"].Value = "A2";
workSheet["A3"].Value = "A3";

workSheet["B1"].Value = "B1";
workSheet["B2"].Value = "B2";
workSheet["B3"].Value = "B3";
workSheet["B4"].Value = "B4";

// Retrieve column
RangeColumn column = workSheet.GetColumn(0);

// Apply trimming
Range trimmedColumn = workSheet.GetColumn(0).Trim();

Imports IronXL

Private workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

Private workSheet("A2").Value = "A2"
Private workSheet("A3").Value = "A3"

Private workSheet("B1").Value = "B1"
Private workSheet("B2").Value = "B2"
Private workSheet("B3").Value = "B3"
Private workSheet("B4").Value = "B4"

' Retrieve column
Private column As RangeColumn = workSheet.GetColumn(0)

' Apply trimming
Private trimmedColumn As Range = workSheet.GetColumn(0).Trim()

// Trimming different range types
using IronXL;

WorkBook workBook = WorkBook.Load("DataWithEmptyBorders.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Trim a specific column
Range trimmedColumn = workSheet.GetColumn(2).Trim();

// Trim a specific row
Range trimmedRow = workSheet.GetRow(5).Trim();

// Trim a rectangular range
Range dataRange = workSheet.GetRange("A1:Z100");
Range trimmedRange = dataRange.Trim();

// Save the cleaned data
workBook.SaveAs("CleanedData.xlsx");

Imports IronXL

' Trimming different range types
Dim workBook As WorkBook = WorkBook.Load("DataWithEmptyBorders.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Trim a specific column
Dim trimmedColumn As Range = workSheet.GetColumn(2).Trim()

' Trim a specific row
Dim trimmedRow As Range = workSheet.GetRow(5).Trim()

' Trim a rectangular range
Dim dataRange As Range = workSheet.GetRange("A1:Z100")
Dim trimmedRange As Range = dataRange.Trim()

' Save the cleaned data
workBook.SaveAs("CleanedData.xlsx")

// Example: Combining sort and trim for comprehensive cleaning
using IronXL;

WorkBook workBook = WorkBook.Load("MessyData.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// First, get the data range
Range dataRange = workSheet.GetRange("A1:E50");

// Sort to push empty cells to bottom
dataRange.SortByColumn(0, SortOrder.Ascending);

// Now trim to remove the empty cells that were pushed to edges
Range cleanedRange = dataRange.Trim();

// The result is a compacted data range
Console.WriteLine($"Original range: {dataRange.RowCount} rows");
Console.WriteLine($"Trimmed range: {cleanedRange.RowCount} rows");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Load("MessyData.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' First, get the data range
Dim dataRange As Range = workSheet.GetRange("A1:E50")

' Sort to push empty cells to bottom
dataRange.SortByColumn(0, SortOrder.Ascending)

' Now trim to remove the empty cells that were pushed to edges
Dim cleanedRange As Range = dataRange.Trim()

' The result is a compacted data range
Console.WriteLine($"Original range: {dataRange.RowCount} rows")
Console.WriteLine($"Trimmed range: {cleanedRange.RowCount} rows")