Adding text into Excel sheet by using C#

Question

I'm trying to add a pretty long text into the Excel sheet by using C#. I use this code:

worksheet.Cells[1, 1] = textString;

The result is here:

What I want is:

Suggestions?

Split on newline and add each element of the resulting array to a new row. — Tim Williams
– Tim Williams, Commented Aug 5, 2012 at 4:18

Nimantha · Accepted Answer · 2022-01-07 04:59:22Z

11

To get this effect you have to copy the text to clipboard and then paste it to the relevant cell. See this example

Note: Set textString to your very long string.

CODE

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //~~> Change Your String here
            String textString = "I'm trying to add a pretty long text into the Excel sheet by using sheet. I use this code:" + Environment.NewLine +
                               "worksheet.Cells[1, 1] = textString;" + Environment.NewLine +
                               "The result is here:";

            Clipboard.SetText(textString);

            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;
            xlexcel = new Excel.Application();
            xlexcel.Visible = true;

            //~~> Add a new a workbook
            xlWorkBook = xlexcel.Workbooks.Add(misValue);

            //~~> Set Sheet 1 as the sheet you want to work with
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //~~> Set your range
            Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];

            CR.Select();

            xlWorkSheet.Paste(CR, false);

            // xlWorkBook.Close(true, misValue, misValue);
            //  xlexcel.Quit();

            // releaseObject(xlWorkSheet);
            // releaseObject(xlWorkBook);
            // releaseObject(xlexcel);
        }

        //private void releaseObject(object obj)
        //{
        //    try
        //    {
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        //        obj = null;
        //    }
        //    catch (Exception ex)
        //    {
        //        obj = null;
        //        MessageBox.Show("Unable to release the Object " + ex.ToString());
        //    }
        //    finally
        //    {
        //        GC.Collect();
        //    }
        //} 
    }
}

SNAPSHOT

enter image description here

edited Jan 7, 2022 at 4:59

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Aug 5, 2012 at 4:18

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Angelo Mascaro Over a year ago

The answer is pretty old, so probably this is why it doesn't work anymore. The call to Paste throws an exception with this message: Exception from HRESULT: 0x800A03EC

Siddharth Rout Over a year ago

Try CR.Copy(); instead of CR.Select(); and then try again.

Siddharth Rout Over a year ago

Create a new question with the excat code that you are using and paste the link of the question here.

Collectives™ on Stack Overflow

Adding text into Excel sheet by using C#

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related