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. ```text 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. ```csharp 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); } ``` ```vbnet Using p As New ExcelPackage() Dim ws = p.Workbook.Worksheets.Add("Sheet1") Dim format As New ExcelTextFormatFixedWidth() format.SetColumnLengths(16, 10, 16, 8, 1) ws.Cells("A1").LoadFromText(myFile, format) End Using ``` - 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. ```csharp 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); } ``` ```vbnet 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").LoadFromText(myFile, format) End Using ``` 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. ```csharp format.SetColumnNames("Name", "Date", "Amount", "Percent", "Category"); ``` ```vbnet 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 ```csharp format.SetColumnDataTypes(eDataTypes.String, eDataTypes.DateTime, eDataTypes.Number, eDataTypes.Percent, eDataTypes.String); ``` ```vbnet 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. ```csharp format.SetColumnPaddingAlignmentType(PaddingAlignmentType.Left, PaddingAlignmentType.Auto, PaddingAlignmentType.Right, PaddingAlignmentType.Right, PaddingAlignmentType.Auto); ``` ```vbnet 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. ```csharp format.SetUseColumns(true, false, true, false, false); ``` ```vbnet 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. ```csharp format.ShouldUseRow = row => { if (row.Contains("Hal") || string.IsNullOrEmpty(row)) { return false; } return true; }; ``` ```vbnet format.ShouldUseRow = Function(row) If row.Contains("Hal") OrElse String.IsNullOrEmpty(row) Then Return False End If Return True End Function ``` 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: ```csharp // will ignore the first line format.SkipLinesBeginning = 1; // will ignore the last two lines format.SkipLinesEnd = 2; ``` ```vbnet ' 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 ```csharp format.Transpose = true; ``` ```vbnet format.Transpose = True ``` ## Table style The third argument - TableStyle - gives you the possibility to style the range as a table. ```csharp sheet.Cells["C1"].LoadFromText(file, format, TableStyles.Dark1); ``` ```vbnet sheet.Cells("C1").LoadFromText(file, format, TableStyles.Dark1) ``` ## See Also - [Sample 2.3.2-C\#]()