Applying text to cells in Excel

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 788 Views - Last Post: 05 December 2019 - 09:15 AM Rate Topic: -----

#1 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Applying text to cells in Excel

Posted 18 November 2019 - 09:12 AM

I am using the the following class to insert specific textbox values to specific Excel Cells:
 class TextToExcel
    {

        /// <summary>
        /// The method use to put data into specific Excel cell
        /// </summary>
        /// <param name="path">Excel document path</param>
        /// <param name="input">the text value you want to input</param>
        /// <param name="workSheetName">Worksheet name "</param>
        /// <param name="address">Cell address</param>
        /// <returns>if operation completed return true</returns>
        /// 

        public static bool PopulateText(string path, string input, string workSheetName, string address)
        {
            Excel.Application xlApp = null;
            bool flag = false;
            try
            {
                // Try to  get Excel application instance. If it exists we will
                // use it, if it doesn't exists we will create it.

                Process[] ps = Process.GetProcessesByName("Excel");
                if (ps.Length > 0)
                {
                    xlApp = Marshal.GetActiveObject("Excel.Application") as Excel.Application;
                    if (xlApp == null)
                    {
                        throw new InvalidOperationException("Can't get Excel Object");
                    }
                }
                else
                {
                    xlApp = new Excel.Application();
                    flag = true;
                }

                // Open Workbook
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);

                // Get the worksheet
                Excel.Worksheet xlWorkSheet = xlWorkbook.Worksheets[workSheetName];

                // Set the Value
                xlWorkSheet.Range[address].Value2 = input;
           
                // Save the excel document
                xlWorkbook.Save();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (xlApp != null && flag)
                {
                    xlApp.Quit();
                }
            }
            return false;

        }
    }

:code:

I have a form with 3 textboxes and a button.

Right now my button click code is the following:


private void btn_applyprams_Click(object sender, EventArgs e)
        {
            string path = "C:\\Users\\username\\Desktop\\doc.xls";
            TextToExcel.PopulateText(path, textBox1.Text, "X 0.1 x", "C4");
            TextToExcel.PopulateText(path, textBox2.Text, "X 0.1 x","C5");
            TextToExcel.PopulateText(path, textBox3.Text, "X 0.1 x","A8"); 
           
        }

:code:


This does seem to work , however it needs to reopen the excel documents 3 times to insert the values.
There must be a better way to accomplish this and open the excel document once and insert all 3 relevant values. Any help or insight would be greatly appreciated. Thank you.

Sorry about the tags. My mistake. Thank you for correcting it. I will use in future posts. Thank you and I apologize.

This post has been edited by modi123_1: 18 November 2019 - 09:10 AM
Reason for edit:: In the future, please use the [code] tag button in the editor.


Is This A Good Question/Topic? 0
  • +

Replies To: Applying text to cells in Excel

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 09:13 AM

Rewrite the function you made to take in collection of objects, apply them in a loop, and save.

presumably a quick 'struct' of string and string would be sufficient or a dictionary collection.
Was This Post Helpful? 0
  • +
  • -

#3 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 09:53 AM

Do you mean add something like this to the TestToExcel Class?

class TextToExcel
    {

       
        public struct PramData
        {
            public string refdate;   //Textbox1
            public string userid;    //Textbox2
            public string prampath;  //Textbox3
        }
        
}


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:06 AM

The path seems constant. So that doesn't need to be part of the struct.
Was This Post Helpful? 0
  • +
  • -

#5 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:08 AM

Ok. I am not very familiar with using struct. So any help you could provide would be awesome.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:15 AM

A struct is like a less flexible form of a class.

REad up here:
https://docs.microso...keywords/struct
Was This Post Helpful? 0
  • +
  • -

#7 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:20 AM

Am I on the right track below:

 public struct PramData
        {
            public string refdate;   //Textbox1
            public string userid;    //Textbox2
            public string prampath;  //Textbox3

            public PramData(string rdate, string user, string ppath)
        {
                refdate = rdate;
                userid = user;
                prampath = ppath;

        }
        }


Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:32 AM

*shrug* That's barely a change.

See post 4.

Though I am not going to tag along for a "single line/ask if right" game of programming by numbers.
Was This Post Helpful? 0
  • +
  • -

#9 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:34 AM

Should the struct be in its own file like "PramStruct.cs"? Like below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PAF_Reporter
{
    public struct PramData
    {
        public string refdate;   //Textbox1
        public string userid;    //Textbox2
        public string prampath;  //Textbox3

        public PramData(string rdate, string user, string ppath)
        {
            refdate = rdate;
            userid = user;
            prampath = ppath;

        }
    }
}


I was going to leave the path for now, just because I am not totally sure it will be constant or change yet.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:35 AM

I don't put them in their own file.

I mean you can use a class too. Either or.
Was This Post Helpful? 0
  • +
  • -

#11 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:37 AM

So it is ok in its own file for now ? I can always change that later I suppose.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:43 AM

Try it out.. see if it makes a difference.
Was This Post Helpful? 0
  • +
  • -

#13 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:47 AM

If I use as a constant I can do it like this right :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PAF_Reporter
{
    public struct PramData
    {
        public string refdate;   //Textbox1
        public string userid;    //Textbox2
        public const string prampath = "C:\\Users\\username\\Desktop\\doc.xls";  

        public PramData(string rdate, string user, string ppath)
        {
            refdate = rdate;
            userid = user;
            ppath = prampath;
            

        }
    }
}



Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15496
  • View blog
  • Posts: 62,055
  • Joined: 12-June 08

Re: Applying text to cells in Excel

Posted 18 November 2019 - 10:59 AM

Try it out.
Was This Post Helpful? 0
  • +
  • -

#15 Narflak   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 19-November 18

Re: Applying text to cells in Excel

Posted 18 November 2019 - 11:00 AM

How can I try it out at this point?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2