How do I copy an excel range to Text (.txt) file, unformatted so that all cells form one single string and are not separate items? C#

Question

I get a range of all "Good" cells in Column B of my excel sheet, the find the corresponding cells in the "D" Column and create a range of those cells. I want to convert all those cells to one single string and paste that to my notepad file, so that there are no spaces between each cell's strings and they are displayed on a single line.

Right now my code reads each cell item as its own entity and prints them on separate lines. I want to be able to iterate over one single string, so I would like them to all form one whole string.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(comboBox2.Text);
            Excel.Worksheet xlWorkSheet = 

(Excel.Worksheet)excelWorkbook.Sheets[sheetSpaces];
            excelApp.Visible = false;
            excelApp.ScreenUpdating = false;
            excelApp.DisplayAlerts = false;
            Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            int lastUsedRow = last.Row;
            string lastCell = "B" + lastUsedRow.ToString();
            Excel.Range range = xlWorkSheet.get_Range("B1", lastCell);


            foreach (Excel.Range item in range.Cells)
            {

                string text = (string)item.Text;
                if (text == "Good")
                {
                    //get address of all Good items 
                    string textx = (string)item.Address;

   //change address of Good items to corresponing address in D column
                string textxcorrect = textx.Replace("B", "D");

                //get rid of "$" for address
                var cellAddress = textxcorrect.Replace("$", "");

                //create range for addresses with the new D column addresses
                Excel.Range xlRng = xlWorkSheet.get_Range(cellAddress, Type.Missing);
                    string fileLocation = @"C:\\Users\\npinto\\Desktop\\hopethisworks.txt";


               foreach (Excel.Range item2 in xlRng)
                    {
                        xlRng.Copy();

                        File.WriteAllText(fileLocation, Clipboard.GetText());

                    }




                    string readText = System.IO.File.ReadAllText(fileLocation);
                    Console.WriteLine(readText);

Try using this on the clipboard text before writing to file - https://stackoverflow.com/questions/5203607/fastest-way-to-remove-white-spaces-in-string — Joe C, Sep 12 '17 at 15:11
It removes the white spaces from each individual cell item, but does not concatenate all the cell items together to form one string. They are still on separate lines. — Nicole Pinto, Sep 12 '17 at 15:27
Exactly what are you trying to achieve here - it looks like you are iterating through a list of cells from column B - for each cell that contains the string "Good" - you recreate the cell address & then extract the contents (which you already know is "Good") & copy it to the clipboard & then write it to a file (instead of appending). It seems what you would achieve if the code worked as you explained just a long strong of repeated "Good". — PaulF, Sep 12 '17 at 15:59
I edited it to make it simpler, but basically in my original code, I convert the Good cells addresses to another address (ie. original address is B1 and I convert it to D1 and great a range of the D cells that correspond with the B addresses, I will change my script to reflect this to avoid confusion. Basically I want a long string of all the D cells' contents. — Nicole Pinto, Sep 12 '17 at 16:02
So identical to the question you asked on the 7th Sept : https://stackoverflow.com/questions/46102778/how-can-i-take-a-range-of-excel-cells-and-make-the-contents-a-single-string-to-i — PaulF, Sep 12 '17 at 16:03
Basically, I've been doing this on the side trying to figure it out, everyone is giving me solutions to iterate and clear whitespaces from the individual cells, but not how to concatenate every cell together — Nicole Pinto, Sep 12 '17 at 16:05

PaulF · Accepted Answer · 2017-09-12T16:39:26.163

I have updated my answer based on your original question - if I now understand correctly the cell in row B will contain the word "Good" - the cell in the same row in column D will contain a single Cell reference - e.g A4 & you want to append that data.

NOTE - if the column D cell contains "+A4" - then the text returned will be what you require to be appended - so just concatenate nextAddress rather than get xlRng2.

How about this - depending on the size of the text you may want to use a StringBuilder rather than string - but with small amounts of data there wont be any significant difference.

string RequiredOutputString = String.Empty;
foreach (Excel.Range item in range.Cells)
{
  string text = (string)item.Text;
  if (text == "Good")
  {
    //get address of all Good items 
    string textx = (string)item.Address;

    //change address of Good items to corresponing address in D column
    var cellAddress = textx.Replace("$B", "D");
    // get a reference to cell in column D
    Range xlRng = curWorkSheet.get_Range(cellAddress, Type.Missing);
    // get the cell address in row D cell
    string nextAddr = xlRng.Text;
    // get a reference to the cell point to from Row D
    Range xlRng2 = curWorkSheet.get_Range(nextAddr, Type.Missing);
    // append that cell contents
    RequiredOutputString += xlRng2.Text.Trim();
  }
}

string fileLocation = @"C:\\Users\\npinto\\Desktop\\hopethisworks.txt";
File.WriteAllText(fileLocation, RequiredOutputString);

Finally figured it out using your answer, thanks!!! :D The "RequiredOutputString" did the trick. — Nicole Pinto, Sep 12 '17 at 17:46

How do I copy an excel range to Text (.txt) file, unformatted so that all cells form one single string and are not separate items? C#

1 Answers1