Skip to content

ToText (Fixed-width text files)

As of EPPlus 7.2 it can read and write fixed width text files. It supports two ways of doing it using either start positions of columns or the length of each column.

Basic Usage

Saving Width of columns

Now let's create a workbook with a worksheet and add this data into it using the ToText method. In this case ToText will need an ExcelOutputTextFormatFixedWidth where we enter the lengths of each column. This is the default behavior.

using (var p = new ExcelPackage())
{
    var ws = p.Workbook.Worksheets.Add("Sheet1");
    ExcelOutputTextFormatFixedWidth format = new ExcelOutputTextFormatFixedWidth();
    format.SetColumnLengths(16, 10, 16, 8, 1);
    ws.Cells["A1:E6"].ToText(myFile, format);
}

Using p As New ExcelPackage()
    Dim ws = p.Workbook.Worksheets.Add("Sheet1")
    Dim format As New ExcelOutputTextFormatFixedWidth()
    format.SetColumnLengths(16, 10, 16, 8, 1)
    ws.Cells("A1:E6").ToText(myFile, format)
End Using
  • This will write the contents from cell A1 to E6
  • Numbers will be right aligned. Text will be left aligned
  • By default the space character will be the padding character.

Saving Positions of columns

Now we will create a workbook with a worksheet and add data into it using ToText method, but this time we will use positions.

using (var p = new ExcelPackage())
{
    var ws = p.Workbook.Worksheets.Add("Sheet1");
    ExcelTextFormatFixedWidth format = new ExcelTextFormatFixedWidth();
    format.SetColumnPositions(51, 0, 16, 26, 42, 50);
    format.ReadType = FixedWidthReadType.Positions;
    ws.Cells["A1:E6"].LoadFromText(myFile, format);
}

Using p As New ExcelPackage()
    Dim ws = p.Workbook.Worksheets.Add("Sheet1")
    Dim format As New ExcelTextFormatFixedWidth()
    format.SetColumnPositions(51, 0, 16, 26, 42, 50)
    format.ReadType = FixedWidthReadType.Positions
    ws.Cells("A1:E6").LoadFromText(myFile, format)
End Using
  • This will write the contents from cell A1 to E6
  • Numbers will be right aligned. Text will be left aligned
  • By default the space character will be the padding character.

ExcelOutputTextFormatFixedWidth

You have the same properties when loading a fixed width text file as when saving, but you also have some extra properties as documented under the ToText article.

UseTrailingMinus

You can write fixed width file with trailing minus sign for numerics by setting the property. This will put the minus sign after the number in the column.

See Also