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(varpck=newExcelPackage()){varsheet=pck.Workbook.Worksheets.Add("sheet");varfile=newFileInfo("c:\\temp\\my_file.txt");sheet.Cells["C1"].LoadFromText(file);}

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.

varformat=newExcelTextFormat();// change delimiter to ';'format.Delimiter=';';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.

varformat=newExcelTextFormat();// 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=neweDataTypes[]{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:

varformat=newExcelTextFormat();format.TextQualifier='\'';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:

varformat=newExcelTextFormat();// will ignore the first lineformat.SkipLinesBeginning=1;// will ignore the last two linesformat.SkipLinesEnd=2;sheet.Cells["C1"].LoadFromText(file,format);

Transpose

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

varformat=newExcelTextFormat();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.

varformat=newExcelTextFormat();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:

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

See also

• Sample 2.3-C# or Sample 2.3 VB
• LoadFromTextAsync
• Import data