Skip to content

LoadFromText

Loads data from a text file or string content into the worksheet. The method supports various formats as specified below.

Basic usage

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

a,2,3%
d,3,5%

Now let's create a workbook with a worksheet and add this data into it using the LoadFromText method. In this case LoadFromText will use the default delimiters - comma for separating items in a row and "\r\n" for newline.

using (var pck = new ExcelPackage())
{
    var sheet = pck.Workbook.Worksheets.Add("sheet");
    var file = new FileInfo("c:\\temp\\my_file.txt");
    sheet.Cells["C1"].LoadFromText(file);
}

Using pck As New ExcelPackage()
    Dim sheet = pck.Workbook.Worksheets.Add("sheet")
    Dim file = New FileInfo("c:\temp\my_file.txt")
    sheet.Cells("C1").LoadFromText(file)
End Using

With the default behaviour the first row will be stored like this:

  • The value "a" will be written to address C1.
  • The second value 2 will be converted to a number, since it is numeric and written into D1.
  • The third value will be written into E1 as 0.03 since it ends with a '%'.

ExcelTextFormat

By supplying an instance of the ExcelTextFormat class you can configure the behaviour of the LoadFromText function.

var format = new ExcelTextFormat();
// change delimiter to ';'
format.Delimiter = ';';
sheet.Cells["C1"].LoadFromText(file, format);

Dim format As New ExcelTextFormat()
' change delimiter to ';'
format.Delimiter = ";"c
sheet.Cells("C1").LoadFromText(file, format)

Data types

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

var format = new ExcelTextFormat();
// specify the data type for each corresponding column position in a row
// with the configuration below all three items will be written into the worksheet as strings.
format.DataTypes = new eDataTypes[] { eDataTypes.String, eDataTypes.String, eDataTypes.String};
sheet.Cells["C1"].LoadFromText(file, format);

Dim format As New ExcelTextFormat()
' specify the data type for each corresponding column position in a row
' with the configuration below all three items will be written into the worksheet as strings.
format.DataTypes = New eDataTypes() {eDataTypes.String, eDataTypes.String, eDataTypes.String}
sheet.Cells("C1").LoadFromText(file, format)

NOTE: Date's and number's data types will be parsed according to the culture set in the ExcelTextFormat.CultureInfo property.

Culture/Encoding

You can configure these via properties on the ExcelTextFormat class. Default Culture is InvariantCulture and default encoding is ASCII.

TextQualifier

The TextQualifier is a character used for defining a string/block of text in a row. The default value is null which means that you must specify this if you need it.

Input:

'One apple, one orange',2,3%
'Look, a bird!',3,5%

Set the TextQualifier to parse this input correctly:

var format = new ExcelTextFormat();
format.TextQualifier = '\'';
sheet.Cells["C1"].LoadFromText(file, format);

Dim format As New ExcelTextFormat()
format.TextQualifier = "'"c
sheet.Cells("C1").LoadFromText(file, format)

Skipping lines

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

var format = new ExcelTextFormat();
// will ignore the first line
format.SkipLinesBeginning = 1;
// will ignore the last two lines
format.SkipLinesEnd = 2;
sheet.Cells["C1"].LoadFromText(file, format);

Dim format As New ExcelTextFormat()
' will ignore the first line
format.SkipLinesBeginning = 1
' will ignore the last two lines
format.SkipLinesEnd = 2
sheet.Cells("C1").LoadFromText(file, format)

Transpose

You can load the data in a transposed format by setting the Transpose property:

var format = new ExcelTextFormat();
format.Transpose = true;
sheet.Cells["C1"].LoadFromText(file, format);

Dim format As New ExcelTextFormat()
format.Transpose = True
sheet.Cells("C1").LoadFromText(file, format)

Table style

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

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

Dim format As New ExcelTextFormat()
sheet.Cells("C1").LoadFromText(file, format, TableStyles.Dark1)

The TableStyles enum gives you over 60 different table styles to pick from!

Headers

If the first row of the content should be used as headers in the range, set the bool argument FirstRowIsHeader to true.

Given this input:

Header 1, Header 2, Header 3
a,2,3%
d,3,5%

...you can call LoadFromText like below to get headers for each column in the range from the first row:

var format = new ExcelTextFormat();
sheet.Cells["C1"].LoadFromText(file, format, TableStyles.Dark1, true);

Dim format As New ExcelTextFormat()
sheet.Cells("C1").LoadFromText(file, format, TableStyles.Dark1, True)

See also