Convert .txt File into Excel

GemBox.Spreadsheet
1

Hi,

currently, my program saves data written into multiple textboxes in this format into a “.txt” file:

XXX1 | XXX2 | XXX3 | XXX4 | XXX5

also, I can add multiple lines so I run like a logging and looks like this:

XXX1 | XXX2 | XXX3 | XXX4 | XXX5
XXX1 | XXX2 | XXX3 | XXX4 | XXX5
XXX1 | XXX2 | XXX3 | XXX4 | XXX5
…

now I want that all rows to get written into an Excel row like:

all XXX1 into A3:AX
all XXX2 into B3:BX
all XXX3 into C3:CX

without knowing how many entries I have in the end.
Is that understandable?
Hope someone can help me :slight_smile:

actual code of writing to the “.txt” file:

try
{
    var selectedItems = checkedListBox1.CheckedItems.Cast<FileItem>();
    var PathofFile = string.Join(Environment.NewLine, selectedItems.Select(x => x.Path)); // "Get" Path of selected Files
    System.IO.File.AppendAllText(PathofFile, "\n" + dateTimePicker1.Value.Day + "." + dateTimePicker1.Value.Month + "." + dateTimePicker1.Value.Year + " | " + comboBox1.Text + " | " + textBox1.Text + " | " + comboBox3.Text + " | " + textBox2.Text + " | " + comboBox2.Text + " h");
    richTextBox1.Text = System.IO.File.ReadAllText(PathofFile);
}
catch (IOException ex)
{
    MessageBox.Show("Error", ex.Message);
}
2

Hi Andreas,

Try this:

var workbook = ExcelFile.Load(PathofFile , new CsvLoadOptions('|'));
workbook.Save(Path.ChangeExtension(PathofFile, ".xlsx"));

I hope this helps.

Regards,
Mario

3

Sadly i do not completely understand what you are suggesting.

so i have my Text.txt file and my Final.xlsx file and i want the rows (XXX1 | XXX2 |…)
from the Text.txt file in the .xlsx file.

XXX1 = A3: AX
XXX2 = B3: BX
…

4

Here is a screenshot of the resulting input TXT and output XLSX:

image
image1010×625 33.6 KB

If you want that data to start from row 3, then you could do this:

var workbook = ExcelFile.Load("input.txt", new CsvLoadOptions('|'));
var worksheet = workbook.Worksheets[0];
worksheet.Rows.InsertEmpty(0, 2);
workbook.Save("output.xlsx");
1 Like
5

okay thanks that helps a lot.
but i have a prepared Excel file so i would like to load the .txt file in it and not create a new one.

6

I see, in that case, try this:

// The "input.xlsx" is your prepared Excel file.
var destination = ExcelFile.Load("input.xlsx");
var destinationWorksheet = destination.Worksheets.ActiveWorksheet;

var source = ExcelFile.Load("input.txt", new CsvLoadOptions('|'));
var sourceRange = source.Worksheets.ActiveWorksheet.GetUsedCellRange(true);

sourceRange.CopyTo(destinationWorksheet, destinationWorksheet.Rows.Count, 0);

destination.Save("output.xlsx");
7

thanks, in the meantime i just created the template in the code as well.
But thanks a lot it´s really helpfull for the future :slight_smile: