[RESOLVED] [2.0] Text file to Excell Sheet

**shakti5385** · Nov 23rd, 2006, 09:59 PM

Hi all

How to transfer text file data to the Excell Sheet
Text file in the following format

Name NickName age
shakti singh 18

Name NickName age
are the cell heading and the other are the detail i have to put in the excell file.
Thanks

**BramVandenbon** · Nov 24th, 2006, 06:10 PM

Hi,

I think maybe 3 years ago somebody asked the same question. And several people told him it was impossible, because Excel does not have an open format.

I could solve his question though. And I will give you the answer I gave him!

Why don't you download and take a look at the following "excel file"

test.xls
I hope it looks like what you want it to look like.

Next, change the filename to test.txt and open it with notepad. I think you will be pleasently surprised.

Of course that's only a quick and dirty way to do it. Because it has tons of disadvantages and limits as well. But I hope it is sufficient for what you want to do.

I hope that solves your question.

Greetings
BramGo

**shakti5385** · Nov 24th, 2006, 09:36 PM

Thanks for help, but i have a new format like this

person1
22
india
person2
23
Uk
person3
21
Us
person4
25
Aus.

Now i wants to exportthis data in excel file person(1) is the name of any person, 23 is the age and country in next row, now i want to create excel file of three columns name age and country, First line place in first column sec. in sec. col. third line in third col.
Excel format like this

Name age country
person1 22 India
person2 23 Uk
person3 21 US
person4 25 AUS.

And so on. How do this ?

**BramVandenbon** · Nov 25th, 2006, 08:19 AM

In my earlier reply I just wrote the information in HTML-code (a language used for websites), next I changed the extention of the filename to ".xls". Excel is capable of recognizing HTML and displaying it as a normal Excel-worksheet.

HTML is not such a hard language. For creating tables you start like this:

HTML Code:

<HTML>
    <BODY>
        <TABLE> //creates the table
            <TR>  //creates a row
                <TD> //creates a cell
                    Text of the cell goes here
                </TD>  //closes a cell
                <TD> //creates a second cell
                    The text of the 2nd cell, still on the same row
                </TD>  //closes the second cell
            </TR> //closes the 1st row
            <TR>  //creates a 2nd row
                <TD> //creates a cell
                    This is the text of the first cell on the second row
                </TD>  //closes a cell
                <TD> //creates a second cell
                    Second cell on the 2nd row.
                </TD>  //closes the second cell
            </TR> //closes the 2nd row
        </TABLE> //closes the table
    </BODY>
</HTML>

I hope that explains it a bit. Now how to creates such a file from C#.

I would first try to convert it to a 2D array directly like this:

Code:

string[,] table = new string[5,3]; 
//5 rows (including the header) 
//and 3 columns

table[0,0] = @"<B>Name</B>"; //the B puts the text in bold.
table[0,1] = @"<B>Age</B>";
table[0,2] = @"<B>Country</B>";
table[1,0] = "person1";
table[1,1] = "22";
table[1,2] = "India";
table[2,0] = "person2";
table[2,1] = "23";
table[2,2] = "Uk";
table[3,0] = "person3";
table[3,1] = "21";
table[3,2] = "US";
table[4,0] = "person4";
table[4,1] = "25";
table[4,2] = "AUS";

Or if you get the data in a 1D array like you showed then you can convert it like this. (Something I wrote from the top of my head and I did not test it, so there could be some small bugs in it):

Code:

public string[,] ConvertArrayTo_NameAgeCountry(string[] input)
{
    int length = input.Length; //12
    int columns = 3; // 3 columns (Name, Age, Country)
    int rows = (length/columns) +1; // 4 rows + 1 header_row = 5 rows
    
    string[,] output = new string[rows, columns];
    output[0,0] = "Name";
    output[0,1] = "Age";
    output[0,2] = "Country";

    for(int i=0;i<length;i++){
        int current_row = i/columns;
        int current_column = i%columns;
        output[current_row + 1, current_column] = input[i];
    }
    return output;
}

How to use the function? I think that's pretty clear:

Code:

string[] output = ConvertArrayTo_NameAgeCountry(input);

Now the big question: How to export it to the HTML code I showed earlier.

Code:

using System;
using System.Xml; //you need to place this at the top

public void SaveToHtml(string[,] input, string filename){
    XmlDocument doc = new XmlDocument();

    //creating the HTML tags
    XmlNode html = doc.CreateElement("HTML");
    doc.AppendChild(html);

    //creating the Body tags
    XmlNode body = doc.CreateElement("BODY");
    html.AppendChild(body);

    //creating the Table tags
    XmlNode table = doc.CreateElement("TABLE");
    body.AppendChild(table);

    for(int i=0; i<input.GetLength(0); i++){
        XmlNode row = doc.CreateElement("TR");
        table.AppendChild(row);
        for(int j=0; j<input.GetLength(1); j++){
            XmlNode cell = doc.CreateElement("TD");
            row.AppendChild(cell);
            cell.Value = input[i,j];
        }
    }
    
    doc.Save(filename);
}

How to put everything together now?

Code:

string[,] output = ConvertArrayTo_NameAgeCountry(input);
SaveToHtml(output, @"c:\test.xls");

I think that should do the trick

Succes with it, and please let me know if it works

Thank you for rating me by the way

**shakti5385** · Nov 29th, 2006, 05:47 AM

Thanks for your help I save the data in the Ms Access database then get them into the Excel sheet.

Thread: [RESOLVED] [2.0] Text file to Excell Sheet

Thread Tools

Display

[RESOLVED] [2.0] Text file to Excell Sheet

Re: [2.0] Text file to Excell Sheet

Re: [2.0] Text file to Excell Sheet

Re: [2.0] Text file to Excell Sheet

Re: [2.0] Text file to Excell Sheet

Posting Permissions