.txt file content need to be copied to existing excel sheet without deleting other sheet information

Question

I already have the excel-sheet named test.xls which has 3 worksheets (Files1, Files2 and Results). Now I need to copy the text file content and write it to the 'Results' worksheet.

Below is the C# code that I worked on.

System.IO.StreamReader file = new System.IO.StreamReader("D:\\test.txt");
            string line = "";
            int counter = 0;
            while ((line = file.ReadLine()) != null)
            {
                if (counter == 0)
                {
                    lstColumnNames.AddRange(line.Split(' '));
                }
                else
                {
                    List<string> tempRowData = new List<string>();
                    tempRowData.AddRange(line.Split(' '));
                    lstRowData.Add(tempRowData);
                }
                counter++;
            }
            System.IO.TextWriter tw = new System.IO.StreamWriter("D:\\test.xlsx");


            if (lstColumnNames.Count != 0)
            {
                string temp = "";
                foreach (string str in lstColumnNames)
                {
                    if (temp != "")
                    {
                        temp += ";";
                    }
                    temp += str;
                }
                tw.WriteLine(temp);


                foreach (List<string> lstRow in lstRowData)
                {
                    temp = "";

                    foreach (string str in lstRow)
                    {
                        if (temp != "")
                        {
                            temp += ";";
                        }
                        temp += str;
                    }
                    tw.WriteLine(temp);
                }
                tw.Close();

The above code deleted all the worksheets and write the text file content into new worksheet.

score 0 · Answer 1 · edited May 23 '17 at 12:21

It overwrites the current content because you're missing an overload for the StreamWriter. If you pass true then you will put StreamWriter into Append mode.

... new StreamWriter(@"C:\MyFile.xlsx", true);

I wouldn't recommend trying to manipulate Excel this way. There's a proper tool for the job called Excel Interop which will let you do what you want. There are lots of tutorials available on read/writing data this way, here are a few I've found:

MSDN - Use COM Interop to Create an Excel Spreadsheet
DotNetPerls Excel Interop
CSharp.NET - How to open an Excel file in C#
CodeProject - Faster Excel Reading using Interop

Some good reading about how to tidy up after yourself when using Excel Interop: How do I properly clean up Excel interop objects?

.txt file content need to be copied to existing excel sheet without deleting other sheet information

1 Answers1