Importing values into Certain Cells in Excel

[sundog1]

Hi Guys.. Can any experts in Excel/C# Help me with this one? I think I'm nearly there.

Currently I have a DGV on a windows form which pulls data from a Datasource. I want to export the findings to and Excel report template.

I've managed to export the data into Excel (with some help from the web and of course Dani). However, I'm trying to format it so that the data in question is populated into selective cells of my choosing i.e. They fit the template.

My 'Export' Code currently looks like this...

    private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["Sheet1"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "Exported from gridview";

            // storing Each row and column value to excel sheet
            for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView2.Columns.Count; j++)
                {
                    ws.Cells[i + 2, j + 1] = dataGridView2.Rows[i].Cells[j].Value.ToString();
                }
            }
        }

I have attached 3 Screenshots also. 1 showing the DGV, 1 of the Template Report and the final one showing how data it currently being pulled across.

My FOR Statements really need some work and this is one area I'm not great at.

Any Advice/Help to get this data formatted correctly would be great.

Regards
Mark.

[Ketsuekiame]

I think the easiest way to approach this is to give your application knowledge of what columns contain which data. Simply keep track of what row you're dealing with and increment through each iteration of your export.

[sundog1]

Good morning Ketsuekiame,

Many thanks for your response on this. In regards to doing this what would be the most logicals first steps to make on the first row for example? (My Iteration statements really need some work)

Regards
Mark

[Ketsuekiame]

It becomes easier to conceptualise the outcome if you visualise the data segregation. Rather than seeing row numbers and column numbers, think about them in name format.

Your DGV already knows columns such as Name, Address, Account Reference etc.
You know where they will be going on your Excel report.

So in order for your code to know where they're going, setup some basic steps. This is hardcoding and therefore "bad" but in time you could always rip it out and make it configurable.

But let's say you output Account Ref, Name, Postcode;

public int AccRefColumn = 0; // Column A
public int NameColumn = 1; // Column B
public int PostCodeColumn = 2; // Column C

public int CurrentRow = 6; // Row 6

// semi-pseudo code incoming...
foreach(var dataRow in dgv.DataRows)
{
    excelSheet.Row[CurrentRow].Column[accRefColum] = dataRow["AccountRef"].Value;
    excelSheet.Row[CurrentRow].Column[NameColumn] = dataRow["Name"].Value;
    ...
    CurrentRow++;
}

excelSheet.Save();

Although it's good to strive for completely generic ways of doing things, sometimes, you just need to make it specific for the task. Without a lot more work outside of this particular function, I think this is the best approach.

Just heading off for lunch, I'll be back in about an hour if you need more assistance.

[sundog1]

Hi Ketsuekiame,

Once again many thanks for your help on this.. I think I understand the logic in this.

I have used the code below:

      private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["Sheet1"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            // storing Each row and column value to excel sheet
            foreach(var dataRow in dataGridView1.DataRows)
            {
                excelsheet.Row[CurrentRow].Column[AccRefColumn] = dataRow["CashAccountRef"].Value;
                excelSheet.Row[CurrentRow].Column[NameColumn] = dataRow["CashName"].Value;
                excelSheet.Row[CurrentRow].Column[TownColumn] = dataRow["CashTown"].Value;

                CurrentRow++;
            }


        }

But for some reason it fails on this line...
foreach(var dataRow in dataGridView1.DataRows)

I've also Attached a screenshot for you to see.

When debugging the form even with this error the spreadsheet loads but does not pass any information across (I'm guessing due to the error).

I look forward to your advice.

Regards
Mark.

[Ketsuekiame]

Sorry I didn't give you actually working code, it was more pseudo code for logic.

The dgv.DataRows should just be dgv.Rows, this will give you a type of DataGridViewRow, which will have the property Cells, which you access via array to pull the value.

So more like;

foreach(DataGridViewRow row in datagridView1.Rows)
{
    Object column1Value = row.Cells["AccountRef"].Value;
    ...
}

[sundog1]

Hi Ketsuekiame,

I'm still having a few issues. even though I think i'm moving forward. Does this look right to you?

           foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                ws.Rows[CurrentRow].Column[0] = row.Cells["cashAccRef"].Value;

I do get this error though when debugging:

"Cannot apply indexing with [] to an expression of type 'int'"

[Ketsuekiame]

Right I see, it's not letting you call a cell index by name. Sorry, I'm not a regular user of straight up ADO, we have layers covering it to expand the natural functionality.

In that case you need to fetch the right column with that name.

Before you begin your data extraction, set three values that contain the column index of your datagridview column you're interested in.

This can be done like this:

Int32 accRefDgvColumnIndex = dataGridView1.Columns["AccountRef"].Index;
Int32 nameDgvColumnIndex = dataGridView1.Columns["Name"].Index;
Int32 postcodeDgvColumnIndex = dataGridView1.Columns["PostCode"].Index;

Admittedly this is getting a bit messy now. Might be wise to put all the column indexes into a Dictionary and key it on String.

So you would use:

foreach(DataGridViewRow row in dataGridView1.Rows)
{
    ws.Rows[CurrentRow].Column[0] = row.Cells[accRefDgvColumnIndex].Value;

[sundog1]

Yeahhh..

I'm totally lost now :S

Your 2nd post on this thread made a lot more sence to me. I'm starting to lose understanding in the previous thread. Should I go back to the 2nd post ?

[Ketsuekiame]

No, if you continue on from your last update, just make the amendments I posted. The first three code lines of my last post, place above your foreach loop, adjust the foreach loop to reference the columns as in the posted code at the bottom of the post (I used yours as a template so you should be able to copy/paste)

I made the mistake of forgetting that what I use here, isn't exactly the same as what you will have. We have a toolkit here that extends ADO functionality. What I posted to you before, works here, but won't on your machine. So I had to go and find all the original ways of doing it. It just made it slightly messier.

[sundog1]

OK.. I'll try and stick with it..

Currently though I get the same problem with the code below:

     public int NameColumn = 1; //Column B
        public int TownColumn = 2; //Column C

        public int CurrentRow = 6; //Row 6 on Sheet 1

        private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["BoyceReport"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            Int32 accRefDgvColumnIndex = dataGridView1.Columns["cashAccRef"].Index;
            Int32 nameDgvColumnIndex = dataGridView1.Columns["cashName"].Index;
            Int32 postcodeDgvColumnIndex = dataGridView1.Columns["cashTown"].Index;

            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {

                ws.Rows[CurrentRow].Column[0] = row.Cells[accRefDgvColumnIndex].Value;

                CurrentRow++;
            }


        }

    }

I'm guessing the value set against the columns is the Header from the DatSet and NOT the DGV? As the DGV in my programs headers have been altered?

[Ketsuekiame]

Yep! You access it by the column names that were in your database/dataset table.

It's the name of the column, not the header value :)

[sundog1]

Yeah..

I've done that.. but still get the error talking about.

""Cannot apply indexing with [] to an expression of type 'int'""

[Ketsuekiame]

What line is it referring to?

EDIT:

Try access your spreadsheet like this instead:
ws.Cells[CurrentRow, 0] = row.Cells[accRefDgvColumnIndex]

[sundog1]

The Error above was referring to the following line of code:

ws.Rows[CurrentRow].Column[0] = row.Cells[accRefDgvColumnIndex].Value;

I've tried adjusting the code to accept the change you mentioned but get the following error now (see screenshot).

[Ketsuekiame]

Erk, that error could be a multitude of things :(

Try adding this line above where you Open the workbook:
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

(You might want to save your old culture settings to reset them after you finish working with the spreadsheet...)

YAE! (Yet Another Edit)
Seems Microsoft took retardation to a whole new level. Excel objects start at array index 1 and not 0...

[sundog1]

Hm...

Added those things are you stated and also changed the Index to start a 1 instead of 0. As you can see in the screenshot it pics up the correct value but still get that same error as above.

code currently looks like this...

        public int AccRefColumn = 0; //Column A
        public int NameColumn = 1; //Column B
        public int TownColumn = 2; //Column C

        public int CurrentRow = 6; //Row 6 on Sheet 1

        private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["BoyceReport"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            Int32 accRefDgvColumnIndex = dataGridView1.Columns["cashAccRef"].Index;
            Int32 nameDgvColumnIndex = dataGridView1.Columns["cashName"].Index;
            Int32 postcodeDgvColumnIndex = dataGridView1.Columns["cashTown"].Index;

            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {

               // ws.Rows[CurrentRow].Column[1] = row.Cells[accRefDgvColumnIndex].Value;
                ws.Cells[CurrentRow, 1] = row.Cells[accRefDgvColumnIndex];

                CurrentRow++;
            }


        }

    }
}

[sundog1]

Ah Hah!

I think I'm making head way... I managed to import ALL the Customers into my spreadsheet currently.

using the following:

ws.Cells[CurrentRow, 1] = row.Cells[accRefDgvColumnIndex].Value.ToString();

[sundog1]

Okay.. I've made some headway by being able to populate the Cells in Excel with the Acc Ref etc.. (See Screenshot).

However.. my Code Does not want to see to pick up the QTY'S Of the orders a pull them through. It starts in the Correct cell but the result is wrong?

Code So far:

       public int AccRefColumn = 0; //Column A
        public int NameColumn = 1; //Column B
        public int TownColumn = 2; //Column C

        public int CurrentRow = 6; //Row 6 on Sheet 1
        public int CurrentOrderRow = 10; //Orders Start on this row

        private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["BoyceReport"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            Int32 QTYColumnIndex = dataGridView2.Columns["cashQTY"].Index;


            //Fills Cells with Values within The text Boxes.
            ws.Cells[CurrentRow, 1] = txtAccRef.Text;
            ws.Cells[CurrentRow, 2] = txtAccName.Text;
            ws.Cells[CurrentRow, 3] = txtAccTown.Text;


            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                ws.Cells[CurrentOrderRow, 1] = row.Cells[QTYColumnIndex].Value.ToString();

                CurrentRow++;
            }


        }

    }

[Ketsuekiame]

You're updating CurrentRow, not CurrentOrderRow, so it's over-writing the old value each time :)

[sundog1]

Wh00!!! Nearly Cracked it!

Just having problems passing the Date/Time into the Excel Cell. Any ideas?

Code Below:

        public int CurrentRow = 6; //Row 6 on Sheet 1
        public int CurrentOrderRow = 10; //Orders Start on this row

        private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["BoyceReport"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            Int32 QTYColumnIndex = dataGridView2.Columns["cashQTY"].Index;
            Int32 DESCColumnIndex = dataGridView2.Columns["cashDescription"].Index;
            Int32 SELLColumnIndex = dataGridView2.Columns["cashSell"].Index;
            Int32 DATEColumnIndex = dataGridView2.Columns["cashDate"].Index;


            //Fills Cells with Values within The text Boxes.
            ws.Cells[CurrentRow, 1] = txtAccRef.Text;
            ws.Cells[CurrentRow, 2] = txtAccName.Text;
            ws.Cells[CurrentRow, 3] = txtAccTown.Text;


            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                ws.Cells[CurrentOrderRow, 1] = row.Cells[QTYColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 2] = row.Cells[DESCColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 4] = row.Cells[SELLColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 3] = row.Cells[DATEColumnIndex].Value;

                CurrentOrderRow++;
            }

            //ws.SaveAs("C:\\text.xls");

            //MessageBox.Show("The Report has been Generated");
        }

    }

[Ketsuekiame]

Cast it to DateTime and call ToOADate()

eg. ((DateTime)row.Cells[DATEColumnIndex].Value).ToOADate();

You should probably do a null/empty check first :)

EDIT: You should also set the number format of the cell in Excel to be Date format.

((Range)ws.Cells[CurrentOrderRow, 3]).EntireColumn.NumberFormat = "YYYY/MM/DD";

Note you can do it individually or the entire column like I have shown. If you do the entire column, you don't need to do it every single iteration, just once when you're finished and before you save.

[sundog1]

Good morning Ketsuekaime,

First of all many thanks for all your help yesterday. I could not have got so far without yout guidence.

Now i'm having a little problem with this DateTime issue.. Below is the code i'm tryin to use..

            //Fills Cells with Values within The text Boxes.
            ws.Cells[CurrentRow, 1] = txtAccRef.Text;
            ws.Cells[CurrentRow, 2] = txtAccName.Text;
            ws.Cells[CurrentRow, 3] = txtAccTown.Text;


            //Grab Date from DGV2 and convert to DateTime
            DateTime dt = Convert.ToDateTime(dataGridView2.SelectedCells[5]);


            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                ws.Cells[CurrentOrderRow, 1] = row.Cells[QTYColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 2] = row.Cells[DESCColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 4] = row.Cells[SELLColumnIndex].Value.ToString();
                ((dt).row.Cells[DATEColumnIndex].Value).ToOADate();


                CurrentOrderRow++;
            }

            //ws.SaveAs("C:\\text.xls");

            //MessageBox.Show("The Report has been Generated");
        }

    }

Unfortunatly it is erroring on this line...

DateTime dt = Convert.ToDateTime(dataGridView2.SelectedCells[5]);

Stating...
"Unable to cast object of type 'System.Windows.Forms.DataGridViewTextBoxCell' to type 'System.IConvertible'."

Any Ideas?

Regards
Mark

[Ketsuekiame]

You forgot to put .Value on your SelectedCells line. :)

Not entirely sure what you're trying to do on line 17 though...?

[sundog1]

Hi Ketsueiame,

Thanks for that.. I've amended that line.. but still not too sure how to pass the DateTime dt
into the worksheet ws? :S

Code so far:

           //Grab Date from DGV2 and convert to DateTime
            DateTime dt = Convert.ToDateTime(dataGridView2.SelectedCells[4].Value);


            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                ws.Cells[CurrentOrderRow, 1] = row.Cells[QTYColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 2] = row.Cells[DESCColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 4] = row.Cells[SELLColumnIndex].Value.ToString();

[Ketsuekiame]

Do you only need to pick out the date once from the spreadsheet? Seems counter-intuitive to what you're doing in the foreach loop (aren't they the orders?)

In any case;

ws.Cells[CurrentOrderRow, excelColumnNumber] = ((DateTime)row.Cells[DATEColumnIndex].Value).ToOADate();
((Range)ws.Cells[CurrentOrderRow, excelColumnNumber]).NumberFormat = "YYYY/MM/DD" // International Date Format

[sundog1]

Ahh, thats seems to have done it Kesuekaime,

Many thanks for that. The Excel spreadsheet is actually a Template being filled in from Date off the DGV's to all the formatting has been done within Excel but thank you for that information on setting for format from within C# I'm sure it will come in very handy.

Here is the code that is now working:

        public int CurrentRow = 6; //Row 6 on Sheet 1
        public int CurrentOrderRow = 10; //Orders Start on this row

        private void button1_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open("E:\\boyce[TEMPLATE].xls", Editable: true);
            Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets[1];

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            ws = workbook.Sheets["BoyceReport"];
            ws = workbook.ActiveSheet;

            // changing the name of active sheet
            ws.Name = "BoyceReport";

            Int32 QTYColumnIndex = dataGridView2.Columns["cashQTY"].Index;
            Int32 DESCColumnIndex = dataGridView2.Columns["cashDescription"].Index;
            Int32 SELLColumnIndex = dataGridView2.Columns["cashSell"].Index;
            Int32 DATEColumnIndex = dataGridView2.Columns["cashDate"].Index;


            //Fills Cells with Values within The text Boxes.
            ws.Cells[CurrentRow, 1] = txtAccRef.Text;
            ws.Cells[CurrentRow, 2] = txtAccName.Text;
            ws.Cells[CurrentRow, 3] = txtAccTown.Text;


            //Grab Date from DGV2 and convert to DateTime
            DateTime dt = Convert.ToDateTime(dataGridView2.SelectedCells[4].Value);


            // storing Each row and column value to excel sheet
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                ws.Cells[CurrentOrderRow, 1] = row.Cells[QTYColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 2] = row.Cells[DESCColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 4] = row.Cells[SELLColumnIndex].Value.ToString();
                ws.Cells[CurrentOrderRow, 5] = ((DateTime)row.Cells[DATEColumnIndex].Value).ToOADate();




                CurrentOrderRow++;
            }

            //ws.SaveAs("C:\\text.xls");

            //MessageBox.Show("The Report has been Generated");
        }

    }

1 issue left o solve which I think could be a lot easier now that its working.

1) When I export the information to the report (example is 3 rows of data, stops a row 10) Then print it off, close it and go back to the program. Select another count and export this. The rows will not return to there original starting point and will populate from row 10 downwards.

Is there some kind of Exit or reset method i'm missing?

Regards
Mark

[Ketsuekiame]

There's nothing in Excel to reset, just need to update your row counter back to its original setting (as you keep track of this yourself, up at the top of the method). If this needs to be different each time, it might be worth passing this into the method instead.

[sundog1]

Hi Ketsuekiame,

Done this and worked a treat! This little porject has now been done, just needs cleaning up around the edges.

Once again many thanks for taking the time to help me out with this I really appricate it!

It's people like you who make places Like DaniWeb one of the best recources on the net for Dev.

Kindest Regards
Mark

[Ketsuekiame]

No problem, glad you got everything working :)