[tsotne1990]

Hello! I have a problem here, when I try to export the string into excel sheet it puts the whole string into A1 cell. I need it to be split into multiple cells. The string is the stock quotes like following:

Date,Open,High,Low,Close,Volume
1980-12-12,0.0000,3.6094,3.5938,3.5938,16751200
1980-12-15,0.0000,3.4219,3.4063,3.4063,6281600
1980-12-16,0.0000,3.1719,3.1563,3.1563,3776000
1980-12-17,0.0000,3.25,3.2344,3.2344,3087200
1980-12-18,0.0000,3.3438,3.3282,3.3282,2623200
1980-12-19,0.0000,3.5469,3.5313,3.5313,1736800 this is the mmeDloadedData.Text

I need them not to be all in one cell. But as you guess in separate cells. I cannot use Data=>Text To Columns menu option here because the string is in A1 cell. What can I do?
Thanks in advance!

 private void bbtExport_ItemClick(object sender, ItemClickEventArgs e)
        {
            //Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            //xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            xlWorkSheet.Cells[1,1] = mmeDloadedData.Text;

            xlWorkBook.SaveAs("StockQuotes.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            XtraMessageBox.Show("Excel file created, you can find the file c:\\StockQuotes.xls", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

This post has been edited by tsotne1990: 22 November 2011 - 03:14 PM

[tlhIn`toq]

Quote

Date,Open,High,Low,Close,Volume
1980-12-12,0.0000,3.6094,3.5938,3.5938,16751200
1980-12-15,0.0000,3.4219,3.4063,3.4063,6281600
1980-12-16,0.0000,3.1719,3.1563,3.1563,3776000
1980-12-17,0.0000,3.25,3.2344,3.2344,3087200
1980-12-18,0.0000,3.3438,3.3282,3.3282,2623200
1980-12-19,0.0000,3.5469,3.5313,3.5313,1736800

So in line 13 you are just taking the entire text file and dumping it into cell[1,1] which is A1. You weren't expecting there to be a single command that would break all that apart for you, were you?

Before you ever put anything into a cell you're going to have to break the massive text into individual lines.
Then break each line into elements at the commas.
Then put each element into a new cell.

Personally I'd
<loop>

• read the text file one line at a time
  
• Take the line and separate it using String.split
  
• Then put each element of the returned array into a cell
  
• Increment the row counter
  
• Reset the column counter to 1

<end loop>

This post has been edited by tlhIn`toq: 22 November 2011 - 03:43 PM

[tsotne1990]

Thanks tlhIn`toq Ive tried such. Youve exactly pin down the problem. I thought I didn't explain well. The thing is that I dont actually have any text file. I have only one string named downloadedData and I artificially tried to put it in MemoEditBox to follow the steps you said above (like breaking down into rows, creating and allocating array, etc). Any better ideas for raw string?

Huge thanks again

[tlhIn`toq]

I don't see anyplace where you've tried to break down the string.
Did you delete those lines? In the future never delete lines until you have something working. Instead comment them out. That way you see the 10 different approaches you've tried and don't repeat them. And we see your approaches so we have something to actually help you fix.

Show me what you tried to code to break down the string.

[tsotne1990]

Im doing it as follows:

List<string[]> writter = new List<string[]>();
int counter = memoEdit1.Lines.Length;
            List<string[]> writter = new List<string[]>();

            for (int i = 0; i < counter; i++)
            {
                string lines = memoEdit1.Lines[i];
                writter.Add(lines.Split(','));
            }

Then its clear. Thanks I think I have the path to follow now.

I have a final question - now Im putting the string into memoBox in order to catch string[] array type. Is there any other way to do the same without using Memo at all?

This post has been edited by tsotne1990: 22 November 2011 - 04:14 PM

[tsotne1990]

What's wrong with this:

for (int i = 0; i < writter.Count; i++)
            {
                for (int j = 0; j < 6; j++)
                {
                    xlWorkSheet.Cells[i, j] = (writter[i])[j];
                }
            }

It throws COM exception

[tlhIn`toq]

Each line ends in a carriage return.
SO break it into individual strings at each one.

tsotne1990, on 22 November 2011 - 05:37 PM, said:

What's wrong with this:

for (int i = 0; i < writter.Count; i++)
            {
                for (int j = 0; j < 6; j++)
                {
                    xlWorkSheet.Cells[i, j] = (writter[i])[j];
                }
            }

It throws COM exception

If j is 0 and i is 0 then you are trying to access cells[0,0]. Does that exist? cells(1,1) is A1... so what would cells(0,0) be?

This post has been edited by tlhIn`toq: 22 November 2011 - 04:44 PM

[tsotne1990]

And about the above question?

Quote

I have a final question - now Im putting the string into memoBox in order to catch string[] array type. Is there any other way to do the same without using Memo at all?

[tsotne1990]

The same problem I have still, anyone?

[tlhIn`toq]

Did the earlier advice not work for you? Post #7

tlhIn`toq, on 22 November 2011 - 05:43 PM, said:

Each line ends in a carriage return.
SO break it into individual strings at each one.

Did you even try doing this? Show us the code where you tried along with any information about how it didn't work and we'll try to clean up your attempt.

[tsotne1990]

tlhIn`toq, on 23 November 2011 - 02:39 PM, said:

Did the earlier advice not work for you? Post #7

I have another question. Thanks that advice was helpful I quickly did that. Now it starts loop at 1,1 cell. The problem now is that the only way I can think of to extract the string characters from downloaded data is to put it in memoedit and then by the property Lines[i] read each line as string[]. But when I have List<string[]> someArray and want to do the same for all the someArray[i] items, I have to put each downloaded data into memoedit and then read them as strings[] which takes enormous time. Is there any other way? Again, my string looks like this:

Date,Open,High,Low,Close,Volume
2009-11-23,203,206,202.95,205.88,16970533
2009-11-24,205.33,205.88,202.9,204.44,11372742
2009-11-25,205.4,205.65,203.76,204.19,10237473
2009-11-26,204.19,204.19,204.19,204.19,0
2009-11-27,199.22,202.96,198.37,200.59,10544892
2009-11-30,201.11,201.68,198.77,199.91,15173418
2009-12-01,202.24,202.77,196.83,196.97,16634369
2009-12-02,198.96,201.42,195.75,196.23,25544958
2009-12-03,197.42,198.98,196.27,196.48,16025654
2009-12-04,199.6995,199.88,190.28,193.32,29539524
2009-12-07,193.32,193.77,188.68,188.95,25527094
2009-12-08,189.36,192.35,188.7,189.87,24657040
.
.
.
and so on...

Thank you

[tlhIn`toq]

I keep telling you to separate the string at the carriage return at the end of the line - I keep asking you if you have even tried - you keep coming back with "how do I avoid using a memobox?"

Do you understand that there is a char(13) carriage return (newLine) (\n) at the end of each of these lines?

Each line really looks like this:

Date,Open,High,Low,Close,Volume\n
2009-11-23,203,206,202.95,205.88,16970533\n
2009-11-24,205.33,205.88,202.9,204.44,11372742\n
2009-11-25,205.4,205.65,203.76,204.19,10237473\n
2009-11-26,204.19,204.19,204.19,204.19,0\n
2009-11-27,199.22,202.96,198.37,200.59,10544892\n
2009-11-30,201.11,201.68,198.77,199.91,15173418\n
2009-12-01,202.24,202.77,196.83,196.97,16634369\n
2009-12-02,198.96,201.42,195.75,196.23,25544958\n
2009-12-03,197.42,198.98,196.27,196.48,16025654\n
2009-12-04,199.6995,199.88,190.28,193.32,29539524\n
2009-12-07,193.32,193.77,188.68,188.95,25527094\n
2009-12-08,189.36,192.35,188.7,189.87,24657040\n

The exact same way you break a single line on the commas into an array of strings is how you would break the big string on the newline into an array of strings (lines)

I refuse to write the code for you. You are already doing this with the commas to break up a single line. Do the same thing with newline to break up the single string into lines.

tsotne1990, on 22 November 2011 - 05:08 PM, said:

Im doing it as follows:

List<string[]> writter = new List<string[]>();
int counter = memoEdit1.Lines.Length;
            List<string[]> writter = new List<string[]>();

            for (int i = 0; i < counter; i++)
            {
                string lines = memoEdit1.Lines[i];
                writter.Add(lines.Split(','));
            }

Then its clear. Thanks I think I have the path to follow now.

This post has been edited by tlhIn`toq: 23 November 2011 - 03:26 PM