c# WPF - better way to ingest data into program?

[Dravinian]

Hey all,

 

I have been working on a project and the project works - to a degree.

 

But, and there is always a but, I do not think that it will work without user input in all the cases that it will encounter and that will prove difficult.

 

The data is coming from an excel spreadsheet in windows, the output will be put into a different excel spreadsheet in windows.

 

I cannot have the program access or interact with either of the spreadsheets (they are not always available on the users machine 365 etc and there are security concerns). So I can't use any of the automated functions, it must be copy paste into the program, copy paste out of the program.

 

Current method:

 

            string phrase = SpreadsheetValue.Text;
            string[] words = phrase.Split(" ");
            
            string eFN1 = words[2];
            string eFN2 = words[5];
            string eFN3 = words[4];
            string eCN1 = words[3];
            string eCN2 = words[7];
         

 

Where SpreadsheetValue.Text comes from a textbox into which the user copies the original spreadsheet data which is spread over several cells (the cells are ignored by TextBox and it creates just a space)

 

The program does the following:

 

Takes ordered data copied from a spreadsheet;

Reorders some of the data and adds data also entered into the program elsewhere;

Provides an output that can be copied into a pre-ordered spreadsheet in the correct order necessary to simply paste the data.

 

Method used to output data:

 

            this.OutPutValue.Text += eFN1 + eFN2 + eFN3;
            this.OutPutValue.Text += " ";
            this.OutPutValue.Text += NameValue.Text
            this.OutPutValue.Text += " ";
            this.OutPutValue.Text += eCN1 + eCN2;
            this.OutPutValue.Text += " ";

NameValue.text is a text box into which the user manually enters data.

 

I have three problems:

 

1. The data output needs to go to a spreadsheet and I am currently using "  " as a means to split the data into cells when pasted (either automatically or through 'text-to-colums').

 

2. Some of the cells have a " " within the data (ex. "Hello World") and I am losing that spacing (eFN1 + eFN2) so that it goes into a single cell when pasted, looking like this: HelloWorld

 

3. Some cells will have a " " sometimes, but not always.  Which means that when I delimiter the String with Split, I will get a different location in the array for particular cells of data. (this one is breaking me - I can live with no spacing in the words)

 

To explain 3, because I am not sure I entirely understand that sentence and I wrote it....

 

The data could look like this - where \ indicates a cell wall in excel:

 

Monday \ Tuesday \ Not Today \ Wednesday

 

But sometimes, "Not Today" will say "Today".

 

Currently, when I break this into an array, using " ", I get:

 

0 = Monday

1 = Tuesday

2 = Not

3 = Today

4 = Wednesday

 

However, on other occasions it will look like this:

 

0 = Monday

1 = Tuesday

2 = Today

3 = Wednesday

 

So my eFN1 + eFN2 will say TuesdayNot or TuesdayToday depending on the data.  Which is not good enough for this to work.

 

These words will change every time - the days are just examples to assist explanation, and I cannot delimit by a specific character, a word or a number of characters into the string - so can't use str.Length as the words are not always the same length (some are but those always have spaces in the same place).

 

My thought process is that there must be a way in which the data can be 'ingested' into the program so that it is in a grid fashion, based on the way in which excel copies cells?

 

However, looking for that online is a nightmare, as every single answer out there appears to talk about automation or data or being able to copy data out of a DataGrid and I just can't find an answer due to the keywords I need to use.

 

I was thinking that I could copy paste the data into the program in such a way that the data is automatically split into boxes, not by a space but by the cell walls of Excel as the clipboard carries that data in it - as it will copy paste to cells within Excel.

 

I just can't find whether that is possible, and if it is, how I would do it.

Bit long winded this I know, but it was difficult to explain the problem.

[Dravinian]

Also, if anyone actually reads through that, and can think of a better way of doing this, happy to take on that idea.

 

My idea of grid pasting is only a way I thought about doing it to make it work.

[HarryNyquist]

So if I'm understanding your problem correctly, you might want to use a tab character. Excel will import tab delimited values directly.

 

Try it yourself: open up an excel sheet and copy-paste some data into a text editor that can show whitespace. You'll see (if you show whitespace) that the values are separated by tab characters. Copying that and pasting into a new excel sheet will import it into the cells as you intend.

[Dravinian]

21 minutes ago, HarryNyquist said:

So if I'm understanding your problem correctly, you might want to use a tab character. Excel will import tab delimited values directly.

 

Try it yourself: open up an excel sheet and copy-paste some data into a text editor that can show whitespace. You'll see (if you show whitespace) that the values are separated by tab characters. Copying that and pasting into a new excel sheet will import it into the cells as you intend.

Absolutely.

 

My only issue is that a text box within WPF appears to wipe that and replace the content with just spaces.  So when I then .Split the string, the only choice to split by is spaces.

Which then screws me over because i have other spaces in the data which I would like to keep.

 

And thank you for taking the time to read that, I realise it was a lot of information.

[Dravinian]

32 minutes ago, HarryNyquist said:

So if I'm understanding your problem correctly, you might want to use a tab character. Excel will import tab delimited values directly.

 

Try it yourself: open up an excel sheet and copy-paste some data into a text editor that can show whitespace. You'll see (if you show whitespace) that the values are separated by tab characters. Copying that and pasting into a new excel sheet will import it into the cells as you intend.

Just to test it because while I believed that was what was happening, due to the way it acted, I was not 100% certain.  So I got notepad++ which I had been meaning to get for awhile.

Copied a row of cells into it, and did show symbols and saw -> for tab markings.

Pasted it into a text box, then immediately copied it all and pased it back to Notepad++ .....all -> markings gone, and replaced with a space. 

 

This is what lead me to thinking about a grid paste set-up, someway in which those original tab markings can have an impact in the way that the data is pasted.

[Franck]

Not an answer on your problem but know that you are not using WPF the way it's meant to be used. You are using it like it's winforms. WPF is all about databinding so you never set directly the values into the controls.

[HarryNyquist]

11 minutes ago, Franck said:

Not an answer on your problem but know that you are not using WPF the way it's meant to be used. You are using it like it's winforms. WPF is all about databinding so you never set directly the values into the controls.

It's a real pain in the butt to do that, though, especially for an application that sounds rather simple like this. Technically WinForms can do data binding and MVVM too, just as technically WPF can behave like this.

 

23 minutes ago, Dravinian said:

Pasted it into a text box, then immediately copied it all and pased it back to Notepad++ .....all -> markings gone, and replaced with a space.

Try adding "AcceptsTab" to your Textbox. https://docs.microsoft.com/en-us/dotnet/api/system.windows.controls.primitives.textboxbase.acceptstab?redirectedfrom=MSDN&view=netcore-3.1#System_Windows_Controls_Primitives_TextBoxBase_AcceptsTab

[Dravinian]

5 minutes ago, HarryNyquist said:

It's a real pain in the butt to do that, though, especially for an application that sounds rather simple like this. Technically WinForms can do data binding and MVVM too, just as technically WPF can behave like this.

 

Try adding "AcceptsTab" to your Textbox. https://docs.microsoft.com/en-us/dotnet/api/system.windows.controls.primitives.textboxbase.acceptstab?redirectedfrom=MSDN&view=netcore-3.1#System_Windows_Controls_Primitives_TextBoxBase_AcceptsTab

That sounds promising, I will look into it now.

 

I did consider whether doing this in WinForms was the better option, if the truth be told, I just found a really good tutorial on using WPF so used that if there had been a better WinForm tutorial that I had found I would likely to have done it in that.

There was no programming reason for this decision.

[Dravinian]

15 minutes ago, HarryNyquist said:

Try adding "AcceptsTab" to your Textbox.

I tried this and it did not work unfortunately.

 

Been a real head scratcher for me this one.

[Dravinian]

8 minutes ago, Dravinian said:

I tried this and it did not work unfortunately.

 

Been a real head scratcher for me this one.

Ignore me, I have not implemented it properly yet, let me get it right...

[Dravinian]

42 minutes ago, HarryNyquist said:

It's a real pain in the butt to do that, though, especially for an application that sounds rather simple like this. Technically WinForms can do data binding and MVVM too, just as technically WPF can behave like this.

 

Try adding "AcceptsTab" to your Textbox. https://docs.microsoft.com/en-us/dotnet/api/system.windows.controls.primitives.textboxbase.acceptstab?redirectedfrom=MSDN&view=netcore-3.1#System_Windows_Controls_Primitives_TextBoxBase_AcceptsTab

Nice one fella, tabs are now in the TextBox and I am therefore able to delimit by that and keep my spaces!

Thank you!

 

I will do the same to the output box now and the user can simply paste into one cell and then text to columns by tab to get the result they need.

[Dravinian]

Oh and as a crowning bonus, by parsing out the information by '\t' Excel recognises that tabs should be the delimiter and parses the information by Tab and not by Space, so it automatically all goes into the spreadsheet without the user doing anything.

Glorious!!!

[Dravinian]

I gotta say this somewhere, I feel awesome.

 

I have written a program that takes userinputs, data, then re-orders data and inputs and spits out data in a specified pattern.

 

Ok maybe not that hard.

 

BUT....

 

I also made it so that it double checks your inputs and the dates you entered, so that you haven't made a mistake!!!  And if you do, it pops up and says....you sure about that matey?....

 

That is a thing of beauty...lol very easy for some of you, but for me, this is like baby's first proper program and it feels good.

 

I appreciate the help of this forum, and stack overflow, that got me where I need to be!

[HarryNyquist]

35 minutes ago, Dravinian said:

I gotta say this somewhere, I feel awesome.

 

I have written a program that takes userinputs, data, then re-orders data and inputs and spits out data in a specified pattern.

 

Ok maybe not that hard.

 

BUT....

 

I also made it so that it double checks your inputs and the dates you entered, so that you haven't made a mistake!!!  And if you do, it pops up and says....you sure about that matey?....

 

That is a thing of beauty...lol very easy for some of you, but for me, this is like baby's first proper program and it feels good.

 

I appreciate the help of this forum, and stack overflow, that got me where I need to be!

 

I love that feeling, that's why I code