Hi all :wave:
How to transfer text file data to the Excell Sheet
Text file in the following format
Name NickName ageQuote:
Name NickName age
shakti singh 18
are the cell heading and the other are the detail i have to put in the excell file.
Thanks
Printable View
Hi all :wave:
How to transfer text file data to the Excell Sheet
Text file in the following format
Name NickName ageQuote:
Name NickName age
shakti singh 18
are the cell heading and the other are the detail i have to put in the excell file.
Thanks
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
Thanks for help, but i have a new format like this
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.Quote:
person1
22
india
person2
23
Uk
person3
21
Us
person4
25
Aus.
Excel format like this
And so on. How do this ?Quote:
Name age country
person1 22 India
person2 23 Uk
person3 21 US
person4 25 AUS.
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:
I hope that explains it a bit. Now how to creates such a file from C#.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 would first try to convert it to a 2D array directly like this:
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: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";
How to use the function? I think that's pretty clear: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;
}
Now the big question: How to export it to the HTML code I showed earlier.Code:string[] output = ConvertArrayTo_NameAgeCountry(input);
How to put everything together now?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);
}
I think that should do the trick :)Code:string[,] output = ConvertArrayTo_NameAgeCountry(input);
SaveToHtml(output, @"c:\test.xls");
Succes with it, and please let me know if it works ;)
Thank you for rating me by the way :)
Thanks for your help I save the data in the Ms Access database then get them into the Excel sheet.