How to get specific rows count and value from an excel sheet tab 3

Question

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 14-Dec-21 1:50am

Manidas Payyoli

Updated 14-Dec-21 3:17am

v2

Add a Solution

Comments

CHill60 14-Dec-21 8:09am

What does your Createtextfile function do?

[no name] 14-Dec-21 8:43am

question updated

[no name] 14-Dec-21 8:31am

just creating txt file with clm data

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2021-12-14T03:17:00

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[^]