Aspose.cell - Text File read trailing spaces issue

Aspose.Cells Product Family
1

Here is a code snippet that used to read a text file using an Aspose.Cell license key.
The problem is with trailing spaces in all columns that create an issue when reading the data table.

Please let me know if any properties need to set at “TxtLoadOptions” to avoid trailing spaces when they are exported to the data table.

Code Snippet ==>

switch (fileFormat)
{
case FileFormat.TXT:
var txtLoadOptions = new TxtLoadOptions()
{
Separator = Convert.ToChar(file.ColumnDelimiter),
CheckExcelRestriction = false,
ConvertNumericData = false,
ConvertDateTimeData = false,
};

     var workbook = new Workbook(localFilePath + "/"+ objFile.LocalFile, txtLoadOptions);
     var sheet = workbook.Worksheets[secondaryFile.SheetNumber - 1 ?? 0];
     var cells = sheet.Cells;

     return cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, options);
2

@dshetty25,

There is no such built-in method or property to remove leading or trailing spaces when importing/loading a text file. The reason is in text format, a space is also a character.

For your needs, you may travers through the cells data and remove your desired leading and trailing spaces for your needs and before exporting data to DataTable. For example, you may use Trim() method of string type that will remove all leading and trailing white-space characters from the current string/text. See the sample code segment for your reference.

foreach (Aspose.Cells.Cell cell in sheet.Cells)
{
bool chk = cell.StringValue.Contains(" ");
if(chk)
{
string tVal = cell.StringValue.Trim();
cell.PutValue(tVal, true);
}
}

Hope, this helps a bit.

3

@dshetty25
And some other solutions for your requirement:

  1. If you need to remove spaces from string value when loading the text file, you may implement your own handler for LightCellsDataHandler. The code example:
        class RemoveSpaceHandler : LightCellsDataHandler
        {
            public bool StartSheet(Worksheet sheet)
            {
                return true;
            }

            public bool StartRow(int rowIndex)
            {
                return true;
            }

            public bool ProcessRow(Row row)
            {
                return true;
            }

            public bool StartCell(int column)
            {
                return true;
            }

            public bool ProcessCell(Cell cell)
            {
                if (cell.Type == CellValueType.IsString)
                {
                    string v = cell.StringValue;
                    string n = v.Trim();
                    if (n != v)
                    {
                        cell.PutValue(n);
                    }
                }
                return true;//preserve the cell in Cells model
            }
        }
...
        Workbook workbook = new Workbook(..., new TxtLoadOptions() { LightCellsDataHandler = new RemoveSpaceHandler()});
        ...
  1. If you only need to remove spaces for the exported table, you may implement your own ExportTableOptions. The code example:
        class RemoveSpaceExportOptions : ExportTableOptions
        {
            public override bool PreprocessExportedValue(int cellRow, int cellColumn, CellValue value)
            {
                if (value.Type == CellValueType.IsString)
                {
                    string v = (string)value.Value;
                    string n = v.Trim();
                    if (n != v)
                    {
                        value.Value = n;
                        return true;
                    }
                }
                return false;
            }
        }
...
        return cells.ExportDataTable(..., new RemoveSpaceExportOptions());