Copying single cell value from one Excel document to another Excel document using C#

Question

Im making an app that will use one Excel document that is generated, and place some values from that document to another Excel document, and Im stuck in phase where I need to copy value.

My code:

private void buttonIzvrsi_Click(object sender, EventArgs e)
    {
        if(textBoxPolazna.Text!="" && textBoxKrajnja.Text != "")
        {
            Microsoft.Office.Interop.Excel.Application polazniExcel = new Microsoft.Office.Interop.Excel.Application();
            polazniExcel.FileValidation = MsoFileValidationMode.msoFileValidationSkip;
            Microsoft.Office.Interop.Excel.Workbook polazna = polazniExcel.Workbooks.Open(textBoxPolazna.Text.ToString());
            Excel.Worksheet Polazni = polazniExcel.Sheets[1];//sheet koji treba unijeti 


            Microsoft.Office.Interop.Excel.Application krajnjiExcel = new Microsoft.Office.Interop.Excel.Application();
            krajnjiExcel.FileValidation = MsoFileValidationMode.msoFileValidationSkip;
            Microsoft.Office.Interop.Excel.Workbook krajnja = krajnjiExcel.Workbooks.Open(textBoxKrajnja.Text.ToString());
            Excel.Worksheet Krajnji_PoMT = krajnjiExcel.Sheets["Po mjestu troška"];//sheet po mjestu troska
            Excel.Worksheet Krajnji_Ukupno = krajnjiExcel.Sheets["Ukupno po mjesecima"];//sheet ukupno


            Excel.Range brojRedovaPolazni = Polazni.UsedRange;
            int brojacRedovaPolazni = brojRedovaPolazni.Rows.Count; //broj redova u polaznom excelu 


            Excel.Range brojRedovaKrajnjiMT = Krajnji_PoMT.UsedRange;
            int brojacRedovaKrajnjiMT = brojRedovaKrajnjiMT.Rows.Count;//broj redova u krajnjem excelu sheet mjesto troska

            Excel.Range brojRedovaKrajnjiUkupno = Krajnji_Ukupno.UsedRange;
            int brojacRedovaKrajnjiUkupno = brojRedovaKrajnjiUkupno.Rows.Count;//broj redova u krajnjem excelu sheet ukupno


            List<string> listaMT = new List<string>();
            List<string> PostojeciMjeseci = new List<string>(); //tu planiram trpati mjesece koji postoje
            if (brojacRedovaPolazni == brojacRedovaKrajnjiMT)
            {
                for (int i = 1; i <= 12; i++)
                {
                    if(Polazni.Cells[9, i + 2].Value != null || Polazni.Cells[9, i + 2].Value != "")
                    {

                        listaMT.Add(Polazni.Cells[9, i + 2].Value);
                    }
                }
                PostojeciMjeseci = mjeseci.Intersect(listaMT).ToList(); //ovi mjeseci postoje
                int kolumna = 0;
                for (int i = 0; i < PostojeciMjeseci.Count; i++)
                {
                    for (int j = 1; j < 12; j++)
                    {
                        //MessageBox.Show(Polazni.Cells[9, j + 2].Value);
                        if (string.Equals(PostojeciMjeseci[i], Polazni.Cells[9, j + 2].Value))
                        {
                            #region Broj kolumne
                            if (string.Equals(Polazni.Cells[9, j + 2].Value, "siječanj")) kolumna = 3;//"C";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "veljača")) kolumna = 4;// "D";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "ožujak")) kolumna = 5;// "E";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "travanj")) kolumna = 6;// "F";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "svibanj")) kolumna = 7;// "G";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "lipanj")) kolumna = 8;// "H";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "srpanj")) kolumna = 9;// "I";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "kolovor")) kolumna = 10;// "J";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "rujan")) kolumna = 11;// "K";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "listopad")) kolumna = 12;// "L";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "studeni")) kolumna = 13;// "M";
                            else if (string.Equals(Convert.ToString(Polazni.Cells[9, j + 2].Value), "prosinac")) kolumna = 14;// "N";
                            #endregion

                            //MessageBox.Show("U petlji: "+Polazni.Cells[9, j + 2].Value);
                            string sValue = "";
                            for (int x = 1; x <= brojacRedovaKrajnjiMT - 12; x++)
                            {
                                Krajnji_PoMT.Cells[(x + 12), kolumna].Value = Polazni.Cells[(x + 12), kolumna].Value; 

                            }

                        }
                    }
                }
            }
            else if (brojacRedovaPolazni == brojacRedovaKrajnjiUkupno)
            {
                for (int i = 1; i <= brojacRedovaKrajnjiUkupno; i++)
                {

                }
            }
            else
            {
                MessageBox.Show("Broj redova nije konzistentan s glavnom tablicom.");
            }


            polazna.Close(true, Type.Missing, Type.Missing);
            krajnja.Close(true, Type.Missing, Type.Missing);
            polazniExcel.Quit();
            krajnjiExcel.Quit();
        }
        else
        {
            MessageBox.Show("Unesite obe putanje do Excel datoteka.");
        }

    }

My problem is in the line:

Krajnji_PoMT.Cells[(x + 12), kolumna].Value = Polazni.Cells[(x + 12), kolumna].Value;

I tried many variations but I can never get that value pasted to another Excel. If I say:

Krajnji_PoMT.Cells[(x + 12), kolumna].Value = "test";

then it works. Whats the catch here?

maybe [related](https://stackoverflow.com/q/16400302/1132334), [related](https://stackoverflow.com/q/17359835/1132334). there is no error, the value just does not appear, right? Also, inspect the runtime value of `Value` in the debugger. — Cee McSharpface, May 23 '17 at 12:46
@gravity no error, but when its done with pasting values, its just blank where data should be, I even used MessageBox.Show() to see what im writing and it show me blank also. To help u understand what im saying: http://prntscr.com/fb4zr5 — Marko Petričević, May 23 '17 at 12:46
@dlatikay Im not sure how to that since I get this: http://prntscr.com/fb50s6 — Marko Petričević, May 23 '17 at 12:54
@MarkoPetrePetričević - For what it's worth, you're also not really 'copy and pasting,' anywhere that I see. You're just setting values manually. You can also use `.Copy` and `.PasteSpecial` methods of the `Range` or even single cells. — gravity, May 23 '17 at 13:09
@gravity Could u give me an example how to do that, coz all I can find is VB code? — Marko Petričević, May 23 '17 at 13:18
[This is probably a good start](https://stackoverflow.com/questions/6876918/copy-cells-in-excel-using-c-sharp/6877022), and is semi-related — gravity, May 23 '17 at 13:23
I think you need to assign to/read from the `Formula` property. @gravity's linked answer will work too, but it will replace clipboard contents which may be something users won't expect from a tool to do. — Cee McSharpface, May 23 '17 at 13:29
@dlatikay - That's true, this does utilize the clipboard. However, I think it's a matter of semantics. OP refers to 'copy and paste' a lot, but that's not at all what they're actually doing. Semantics. :) — gravity, May 23 '17 at 13:30
thanks guys, I appreciate it, I'll check it out and get back 2 u =) — Marko Petričević, May 23 '17 at 13:32
@gravity If i got it right, he is trying to connect 2 Excel worksheets with Hyperlink, but there is a problem then for me, coz those 2 Excel documents that I'll have wont be on the same computer all the time, so if Im thinking right then that wont work? — Marko Petričević, May 23 '17 at 13:58

Copying single cell value from one Excel document to another Excel document using C#

0 Answers0