want to make a window form which gets two cell values from a range in every excel sheet and print values to text file.below is my code so far

Question

1.00/5 (2 votes)

See more:

, +

C#

private void btnTest_Click(object sender, EventArgs e)
        {
            try
            {
                Excel.Application excelApp = new Excel.Application();
            // excelApp.Visible = true;
            string workbookPath = "D:\\Input.xlsx";

            
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                     true, false, 0, true, false, false);

            // This selectes the used range of the excel workbook and enters it in
            // a two dimentional array
            // Get a reference to the first sheet of the workbook.
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            // string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[1];
            //Excel.Worksheet excelWorksheet = excelWorkbook.ActiveSheet;

            Excel.Range range = excelWorksheet.get_Range("A2", "B300");
            int j = range.Columns.Count;
            j = 3;
            int i = range.Rows.Count;
   
           string text = range.Cells[i, j].ToString();
            string[,] list = new string[i, j];

            foreach (Excel.Worksheet excelWorksheet1 in excelWorkbook.Sheets)
            {
                 for (int a = 1; a <= i; a++)
                {
                    for (int b = 1; b <= j; b++)
                    {

                        if (!text.Equals(null))
                        {
                                list[a - 1, b - 1] = text;
           
                        }
                    }
                }
            }
            string fileLoc = System.IO.Path.GetTempPath() + "Update Excel.txt";

            if (File.Exists(fileLoc))
            {
                File.Delete(fileLoc);
            }

            System.IO.StreamWriter objWriter = new System.IO.StreamWriter(fileLoc, false);

            for (int x = 0; x < list.GetLength(0); x++)
            {
                for (int y = 0; y < list.GetLength(1); y++)
                
                {
                    objWriter.Write(list[x, y]);
                   
                }

            }
           
            objWriter.Close();
            System.Diagnostics.Process.Start("Notepad.Exe", fileLoc);

            }

            catch (Exception ex)
            {
                MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Posted 30-Jun-15 0:48am

chetan1193

Updated 30-Jun-15 2:52am

v3

Add a Solution

Comments

Richard Deeming 30-Jun-15 8:06am

Was there supposed to be a question hiding in there somewhere?

ZurdoDev 30-Jun-15 9:53am

Where are you stuck?

chetan1193 1-Jul-15 1:32am

On button click, an empty text file opens without the cell values, which is not what I want..I dont seem to get where i should alter my code

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)

Mark Miller · Answer 1 · 2015-07-01T10:46:00

Solution 1

C#

Excel.Range range = excelWorksheet.get_Range("A2", "B300"); // Establish an Excel Range
int j = range.Columns.Count; // This line is meaningless, because you set the
j = 3;                       // number of columns to 3 here.
int i = range.Rows.Count;    // This is the number of rows in the Range.
   
string text = range.Cells[i, j].ToString(); // This copies the value of the
                                            // 3rd cell in the last row of the
                                            // Range into the variable "text"
string[,] list = new string[i, j]; // This creates a new empty string array
                                   // equal in size to the Range.

In your foreach, you cycle through all worksheets, but inside the nested for loops you copy the variable text into every element of the array.

Your code is not copying the cell contents to the array, and that is why you are getting an empty text file at the end.

Posted 1-Jul-15 10:46am

Mark Miller

Updated 1-Jul-15 10:48am

v2

Comments

chetan1193 2-Jul-15 4:58am

is there any other way/logic for storing the values within excel range to the array??

Mark Miller 2-Jul-15 10:23am

Yes, change the foreach as follows:
string text = range.Cells[i, j].ToString();
string[,] list = new string[i, j];

foreach (Excel.Worksheet excelWorksheet1 in excelWorkbook.Sheets)
{
for (int a = 1; a <= i; a++)
{
for (int b = 1; b <= j; b++)
{
text = range.Cells[i, j].ToString();
if (!text.Equals(null))
{
list[a - 1, b - 1] = text;

}
}
}
}

chetan1193 3-Jul-15 1:50am

I tried your code but now the text file gets filled with 'System.String[,]'
Also now, my requirement is to get only those cells having string values starting with these letters: 'A3','MSG','PNL' (the range contains these cell values).I know I have to include this condition inside the foreach loop.but i am confused how to do it...

Mark Miller 9-Jul-15 14:41pm

Try changing this line:
text = range.Cells[i, j].ToString();
to this:
text = range.Cells[i, j].Value2.ToString();

See also:
http://csharp.net-informations.com/excel/csharp-read-excel.htm
http://www.codeproject.com/Articles/252962/Excel-Automation-With-Clean-Exit-Quit