Click here to Skip to main content
15,441,020 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, i have to get datas from 3rd colom of 3rd tab in an excel sheet.For all 3 tabs i have same number of rows.So when i tried to copy 3rd colom data always copying first colom data.I need to save only colom 3 value and heading save in to .txt file.How can i get 3rd clm data with heading?

I need to use interop


What I have tried:

Excel._Worksheet xlWorksheetA = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[3];

               Excel.Range firstColumnA = (Excel.Range)xlWorksheetA.UsedRange.Columns[3];

               int RowsA = firstColumnA.Rows.Count;

                int ColumnsA = firstColumnA.Columns.Count;

                MessageBox.Show("RowsA"+RowsA); MessageBox.Show("ColumnsA"+ColumnsA);

               Createtextfile(RowsA, ColumnsA, xlWorksheetA);


//creating txt file from 3rd clm data


public static void Createtextfile(int lastUsedRow, int lastUsedColumn, Excel._Worksheet xlWorksheet)

        {
            string output = "";
            string ExportPath = mypath;


            for (int i = 1; i <= lastUsedRow; i++)
            {
                for (int j = 1; j <= lastUsedColumn; j++)

                {
                    object xVal = ((Excel.Range)xlWorksheet.Cells[i, j]).Value;

                    if (xVal != null)

                    {

                        output += xVal.ToString();

                    }


                    output += Environment.NewLine;

                }
            }


            FileStream fs = new FileStream(ExportPath, FileMode.Create, FileAccess.Write);
            StreamWriter writer = new StreamWriter(fs);
            writer.Write(output);
            writer.Close();

        }
Posted
Updated 14-Dec-21 3:17am
v2
Comments
CHill60 14-Dec-21 8:09am    
What does your Createtextfile function do?
Manidas Payyoli 14-Dec-21 8:43am    
question updated
Manidas Payyoli 14-Dec-21 8:31am    
just creating txt file with clm data

1 solution

The clue to your problem is in the definition of the Createtextfile method
C#
public static void Createtextfile(int lastUsedRow, int lastUsedColumn, Excel._Worksheet xlWorksheet)
You have a loop that starts from Column 1 and goes up to your columnsA value. If you only want to include a single column then pass the number of that column and just copy all the rows for it e.g.
C#
public static void CreatetextfileFromColumn(int lastUsedRow, int ColumnToExport, Excel._Worksheet xlWorksheet)
{
	string output = "";
	string ExportPath = mypath; //where is this coming from? Should be a parameter

	for (int i = 1; i <= lastUsedRow; i++)
	{
		object xVal = ((Excel.Range)xlWorksheet.Cells[i, ColumnToExport]).Value;

		if (xVal != null)
		{
			output += xVal.ToString();

		}
		output += Environment.NewLine;
	}

	FileStream fs = new FileStream(ExportPath, FileMode.Create, FileAccess.Write);
	StreamWriter writer = new StreamWriter(fs);
	writer.Write(output);
	writer.Close();
}


EDIT after OP Comment
To call this function pass the number of the column you want to export as the ColumnToExport

When you debug your code (always a good idea)
VB
Excel.Range firstColumnA = (Excel.Range)xlWorksheetA.UsedRange.Columns[3];
int RowsA = firstColumnA.Rows.Count;
int ColumnsA = firstColumnA.Columns.Count;//clm numb
you will notice that the variable RowsA = 1.
That is because the variable firstColumnAcontains a single column, the range "C:C".

So when you call my single-column function passing RowsA you are asking it to output Column 1 of the Worksheet (not the range firstColumnA)

When you call your original method with RowsA you are asking it to stop output after Column 1 of the Worksheet (not the range firstColumnA)

You might find this article usefule
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Debugging code for absolute beginners - Visual Studio (Windows) | Microsoft Docs[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900