LoadFromText (Fixed width text files)

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

Basic Usage

In these examples we will specify input data like below. Data can be a string or content of a text file.

Name            Date      Amount          Percent Category
David           2024/03/02          130000     2% A
Meryl           2024/02/15             999    10% B
Hal             2005/11/24               0     0% A
Frank           1988/10/12           40,00    59% C
Naomi           2015/09/03       245000,99   100% C

Reading Width of columns

Now let's create a workbook with a worksheet and add this data into it using the LoadFromText method. We will use the length of each column in this example.

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

• The value "David" will be written to address A2.
• The third value 130000 will be converted to a number since it is numeric, and will be written into D1.
• In this example, the header row will be included and since the last column has a longer header name than data, it will be truncated.

Reading Positions of columns

Now we will create a workbook with a worksheet and add data into it using LoadFromText 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"].LoadFromText(myFile, format);
}

Just as before we will have the same contents of each cell as the previous example. Somethings to note:

• We need to provide the length of a line in the fixed width file.
• If setting the lineLength (first parameter for the SetColumnPositions method) to 0 or a negative number we will read until the end of the line (Which in this case would write Category in full instead of just the first letter in the column header).

ExcelTextFormatFixedWidth

You can configure more behaviors by overriding the properties in the ExcelTextFormatFixedWidth class.

Columns

A list containing each column. You can use this to access parameters for each column.

FormatErrorStrategy

There are two ways to handle errors when reading and writing fixed width text files, ThrowError or Truncate. ThrowError will throw an error if data read has a length greater than the length specified for example. Truncate will ignore errors and write data anyway. This can however lead to loss of some data.

PaddingCharacter

The character which is used for padding the column when saving, or the character to trim when loading. Default is a space character.

PaddingCharacterNumeric

The character which is used for padding numbered in the column when saving, or the character to trim when loading a number. Default is null and numerics will use PaddingCharacter instead.

Name

You can supply a name for the column when loading a text. You can use SetColumnsNames providing names for each column to set them or you can use the ColumnFormats list and set them individually.

format.SetColumnNames("Name", "Date", "Amount", "Percent", "Category");

DataType

You can override how EPPlus converts data by specifying what data types should be used for each item in a row

format.SetColumnDataTypes(eDataTypes.String, eDataTypes.DateTime, eDataTypes.Number, eDataTypes.Percent, eDataTypes.String);

PaddingType

You can override how EPPlus aligns numbers and text by specifying a padding type. when writing a fixed width text file. By default it is set to auto and strings will be aligned to the left, and numbers to the right.

format.SetColumnPaddingAlignmentType(PaddingAlignmentType.Left, PaddingAlignmentType.Auto, PaddingAlignmentType.Right, PaddingAlignmentType.Right, PaddingAlignmentType.Auto);

UseColumn

You can specify to exclude certain columns when reading or writing a fixed width text.

format.SetUseColumns(true, false, true, false, false);

ShouldUseRow

Check if row should be included based on user provided functionality. This check is done on the whole row.

format.ShouldUseRow = row =>
{
    if (row.Contains("Hal") || string.IsNullOrEmpty(row))
    {
        return false;
    }
    return true;
};

This will result in that all rows containing the word Hal will be skipped when reading.

Skipping lines

You can make LoadFromText skip a number of lines in the beginning and/or in the end:

// will ignore the first line
format.SkipLinesBeginning = 1;
// will ignore the last two lines
format.SkipLinesEnd = 2;

Transpose

You can transpose the data when loading by setting the Transpose property

format.Transpose = true;

Table style

The third argument - TableStyle - gives you the possibility to style the range as a table.

sheet.Cells["C1"].LoadFromText(file, format, TableStyles.Dark1);

See Also

• Sample 2.3.2-C#