Writing to excel using C#

Question

I know there is a simple answer to this, but I just can't find it.

I am able to create an excel file and write to it, but I can only write to the first column. I would like to be able to write data to the second column in excel as well.

Is this possible? 

Answer 1

Use the Interop.Excel, you can do everything

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlhowConstants_HV01049962.asp

http://www.c-sharpcorner.com/Code/2002/Jan/CreateExcelSheetGAG.asp

Answer 2

When you're writing to the file you can also specify the column not just the row...

rowIndex = 1; columnIndex = 2; excelAPP.Cells(rowIndex, columnIndex) = "Blah"

That would place the word Blah into Range "B1" in excel.

Answer 3

There are several options you can use to manipulate an excel file.

The first and possibly simplest is to use COM integration to manipulate an excel spreadsheet programmatically. I have a co-worker who does this with Visio.

I also found a library on CodeProject that does excel exporting for you (you hand it a DataTable and it exports it to Excel). I haven't tried it myself, but it will likely work.

Finally, you can find the Excel format on the internet and code your own library to read and write from/to Excel.

If the codeproject library works like it says, it will probably be the easiest method to use.

Answer 4

Where can I find the dll?

Answer 5

In references, right click, add reference

Choose the COM tab

Choose Microsoft Excel 10.0 Object Library

Answer 6

Can you possibly show me code that opens an excel worksheet and writes a few values into specified cells?

Answer 7

Can you possibly show me code that opens an excel worksheet and writes a few values into specified cells?

Answer 8

Chekc out the link I gave

 

http://www.c-sharpcorner.com/Code/2002/Jan/CreateExcelSheetGAG.asp

Answer 9

It doesn't get much simpler than this:

Excel.Application excelApp = new Excel.Application(); string myPath = @"C:\Excel.xls"; excelApp.Workbooks.Open(myPath); int rowIndex = 1; int colIndex = 1; excelApp.Cells[rowIndex, colIndex] = "First"; excelApp.Visible = true;

That will open the workbook named Excel.xls located in your C:\ directory.  rowIndex controls which row you're in and assumingly so, colIndex controls the column you are in.  The code will place the word "First" into the workbook in cell "A1".  The visible method tells your program to make "excelApp" visible.  You can of course save/close the workbook without displaying it first.

Answer 10

You can also use...

In references, right click, add reference

Choose the COM tab

Choose Microsoft Excel 12.0 Object Library

using Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

        Excel.Application myExcelApp;

        Excel.Workbooks myExcelWorkbooks;

        Excel.Workbook myExcelWorkbook;

        object misValue = System.Reflection.Missing.Value;

myExcelApp = new Excel.ApplicationClass();

        myExcelApp.Visible = true;

myExcelWorkbooks = myExcelApp.Workbooks;

String fileName = "C:\\book1.xls"; \\ set this to your file you want

  myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);                 

Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;

 

String cellFormulaAsString = myExcelWorksheet.get_Range("A2", misValue).Formula.ToString(); \\ this puts the formula in Cell A2 or text depending whats in it in the string.

myExcelWorksheetToChange.get_Range("C22", misValue).Formula = "New Value" \\ this changes the cell value in C2 to "New Value"

                        

 Hope this helps.

Answer 11

Thanks! .. just what i was searching for

Answer 12

Check this out, http://msdn.microsoft.com/en-us/library/ms173186%28VS.80%29.aspx .

I had to modify mine from

using
 System.Reflection; 

using
 Microsoft.Office.Interop.Excel;

 
to

using Excel;

Answer 13

Hi

I am able to OPen an Excel, Perform Read Write.

Problem: Everytime i run the code it opens a new excel file (i have used

exObj.Workbooks.Add("")

 

) function.

I want to modify the code if the Excel file(on which i want to perform read / write operations) is already open then it should not open another copy of the excel but rather perform read and write operations on the open file.

 

Best,

Mohit

 

Answer 14

Easiest way to writing to excel using c#, I use it long time, name Spire.XLS, may help to you.

http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

Answer 15

Hi Jason., 

 

I am using ur code to write data in excel., last line shows error ... i don't know why.? please can u help me with it.?

Answer 16

probably you are not putting a semicolon at the end of the last line.

Answer 17

oh and change that last line to read-> myExcelWorksheet.get_Range("C22", misValue).Formula = "New Value";

Answer 18

And now with C# 4.0 and the dynamic keyword:

using Excel = Microsoft.Office.Interop.Excel;
namespace COMInterop
{
  class Program
  {
   static void Main(string[] args)
   {
     var excelApplication = new Excel.Application();
     excelApplication.Visible = true;
     dynamic excelWorkBook = excelApplication.Workbooks.Add();
     Excel.Worksheet wkSheetData = excelWorkBook.ActiveSheet;
     excelWorkBook.SaveAs(
      "Testfile.xls",
      AccessMode: Excel.XlSaveAsAccessMode.xlShared);
   }
  }
}

---

Javaman, Cowboy Coders Unite!

Answer 19

 Insert cell data in an Excel file using OLEDB

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel 2007 file using OLEDB in VB.NET 2005 . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For add new content in the cell or insert a new content , We can use the INSERT command like in SQL Operations.

sample UPDATE sql

sql = "Insert into [Sheet1$] (id,name) values('5','e')"

Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try Dim MyConnection As System.Data.OleDb.OleDbConnection Dim myCommand As New System.Data.OleDb.OleDbCommand Dim sql As String MyConnection = New System.Data.OleDb.OleDbConnection _ ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _ "'c:\testfile.xls';Extended Properties=Excel 8.0;") MyConnection.Open() myCommand.Connection = MyConnection sql = "Insert into [Sheet1$] (id,name) values('5','e')" myCommand.CommandText = sql myCommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try MsgBox("Row Added ") End Sub End Class

When you execute this source code the will insert a row in the Excel file.

Answer 20

"myExcelWorkbooks.Open " Opens the Excel sheet abruptly getting popped up.

How to stop it..

---

Answer 21

There is a free solution to write excel,word, pdf  without ole automation.

http://www.codeproject.com/Tips/190144/Export-Database-to-Excel-PDF-HTML-RTF-XML-etc-for-.aspx

Answer 22

Hi,

 The line " myExcelApp = new Excel.ApplicationClass();" does not work, have to be changed to:

myExcelApp = new Excel.Application();

 

The comments signs are worng "\\" have to be changed to "//"

 

Thanks for the code!

Cheers

mz

 

Answer 23

Hi Michael,

 

Look at below link,

it may help you.

http://dotnet-magic.blogspot.com/2011/10/createformat-excel-file-from-cnet.html

Answer 24

i found your and jasonMc86 post very usefull.
Thanks 

Answer 25

hi jason;

I have an error with this code for this line

" myExcelApp = new excel.ApplicationClass();"

 it says :

Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded.Use the applicable interface instead .

 

would u help me to solve this problem...

 

---

Sorry For My English

Answer 26

Hi Mohammad,

try this,

 myExcelApp = new Microsoft.Office.Interop.Excel.Application();

 

My whole code, which opens an excel worksheet, writes in it, saves and closes is here.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    class ExcelWriter
    {
        private Microsoft.Office.Interop.Excel.Application app = null;
        private Microsoft.Office.Interop.Excel.Workbook workbook = null;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        private Microsoft.Office.Interop.Excel.Range workSheet_range = null;
       
        public ExcelWriter()
        {
            try
            {
               

                app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
          
                workbook = app.Workbooks.Add(1);
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];

                //open the file to write
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
            }
            finally
            {
            }
        }

        public void addData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;  //writes data
        }

        public void close(string s)
        {
            String path = s;
            workbook.SaveAs(path + @"\filename", Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

//saved it to the path

            workbook.Close(true, null, null);

//and finally closed the file.

       }
       
    }
}

Hope helps.

Answer 27

thank you julia ;

would you give me some tips or codes for reading excel files . I have an excel file and I want change formulas of one of the columns .I need number and name of active sheets . active columns . and active rows.

---

Sorry For My English

Answer 28

Hi again,

I used the code to create a Xml file, which is used in Android OS, as strings.xml. My code reads 1st an 3rd columns and do what it needs. Here is my Excel Reader class

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class ExcelReader
    {
        private Excel.Application app;
        private Excel.Workbook workBook;
        private Excel.Worksheet workSheet;
        private Excel.Range workSheet_range;

        public ExcelReader(string s)
        {
            try
            {
                app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;

                workBook = app.Workbooks.Open(s, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0);
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);

                workSheet_range = workSheet.UsedRange;
           
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
            }
           
        }

        public String read(int rCnt, int cCnt)
        {
            workSheet_range = workSheet.UsedRange;
            return Convert.ToString((workSheet_range.Cells[rCnt, cCnt] as Excel.Range).Value2);

           //here is the reader part

        }

        public int getRowCount()
        {
            return workSheet_range.Rows.Count;
        }

        public void close()
        {
            workBook.Close();
            app.Quit();
        }
    }
}

here is my reference: http://csharp.net-informations.com/excel/csharp-read-excel.htm  check this web site for more information.

 

best regards

 

 

 

Answer 29

I have an excel file , it contains a column with values such as these examples :

2*2.5

3*25

...

I want change these cells values to this type : 

2*2.5        --->           =2*2.5

3*25         --->           =3*25 

null          --->           null

AAA         --->            AAA 

would you help me ?

I read cells  values and put them in an object array . but I dont know how I change them .

I use excel 2007 and  'Office.Interop.Excel' refrence .

---

Sorry For My English

Answer 30

use the class I wrote above. then create another class which uses the ExcelReader class. In new class create a function. In this function create an ExcelReader and an ExcelWriter(I'll explain it below) object, and get its row count:

ExcelWriter ew = new ExcelWriter();

ExcelReader er = new ExcelReader(s); // the "s" parameter is the path of the file, can use the file name directly.
            int rows = er.getRowCount();

now we need an ExcelWriter class.

namespace WindowsFormsApplication1
{
    class ExcelWriter
    {
        private Microsoft.Office.Interop.Excel.Application app = null;
        private Microsoft.Office.Interop.Excel.Workbook workbook = null;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        private Microsoft.Office.Interop.Excel.Range workSheet_range = null;
       
        public ExcelWriter()
        {
            try
            {
                app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
          
                workbook = app.Workbooks.Add(1);
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                workSheet_range = worksheet.get_Range("A1", "B1");
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
            }
          
        }

        public void addData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }

}

}

this can be used as ExcelWriter class, or you can add the constructor and addData function to the ExcelReader class.

 

now in the class you have created, you have rows variable. make a for loop with var rows:

 

for (int r = 1; r <= rows; r++)
            {
                string value = er.read(r, 1); //reads your excel file
              
               //here you should create your if conditions. like if the "value==null"              

              // don't forget that your value type is always string. so you cannot make your conditions using the type variable. you should find another way to check it.

             if (a == b){

              ew.addData(r, 1, "=" + value);

              //assume the value is "2*2.5", it makes a new Excel file with "=2*2.5" value.

}

}

 

Thats all I know.

Answer 31

Hi,

you can easily C# write XLS, XLSX with this C# / VB.NET Excel component that doesn't use Excel Automation.

Here is a sample C# code:

ExcelFile ef = new ExcelFile();
 
// Adds new worksheet to excelFile.
ExcelWorksheet ws = ef.Worksheets.Add("New worksheet");
 
// Sets the value of the cell "A1".
ws.Cells["A1"].Value = "Hello world!";
 
// Saves the excel file.
ef.SaveXls("excelFile.xls");

Answer 32

something that makes file locations easier is putting a @ infront of the string. thiss makes it absolute, so that escape chars "\" dont do anything. this removes the need for double backslashing everywhere.

example:

string FileLocation = @"C:\Users\Andrew\mySpreadSheet.xlsx";

Answer 33

How hot this post is.

There are soooo many solutions.

But I have another question. Is it possible to insert an image in one cell (in one cell not in worksheet)?

Please give me some suggestions.

Answer 34

Hi

Will this code work if the end user machine doesnt have excel installed or if the code hosted in the server doesnt have excel installed?

Answer 35

Hello,

To write excel file and insert an image in a cell you can use the Excel component by eleriumsoft.com

See this C# example:

ExcelWorkbook wb = new ExcelWorkbook();
wb.Worksheets.Add("sh1");
ExcelWorksheet Wsh = wb.Worksheets[0];
Wsh.Pictures.Add(@"c:\example.jpg");
Wsh.Pictures[0].SetPosition(0, 0, 1, 1);
wb.WriteXLSX(@"c:\output.xlsx");

In SetPosition() function the first two parameters specify the cell where do you want insert image, the last two  parameters specify right bottom corner.

Answer 36

Hi Michael,

You can simply create CSV (Comma Separated Value) file like excel file with Code which need no Dll, as I have write the code as Below.

public static void GetCSVSheet(List<ClassType> dataFromClass)
        {
            if (!Directory.Exists(Properties.Settings.Default.CSVLocation))
                Directory.CreateDirectory(Properties.Settings.Default.CSVLocation);

            string csvdownloadPath = Path.Combine(Properties.Settings.Default.CSVLocation, string.Format("DemoCSV_{1:yyyyMMddhhmmss}.csv",DateTime.Now));
            using (FileStream File_Stream = new FileStream(csvdownloadPath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
            {
                using (StreamWriter FileWriter = new StreamWriter(File_Stream))
                {
                    FileWriter.BaseStream.Seek(0, SeekOrigin.End);
                    string[] columns = { "Column1", "Column2", "Column3" };
                    CreateColumns(FileWriter, columns);
                    FileWriter.WriteLine();
                    List<ClassType> DataListWriteToXl = new List<ClassType>();
                    DataListWriteToXl = dataFromClass;
                    foreach (var dataToWrite in DataListWriteToXl)
                    {
                        string[] values = {dataToWrite.value1.ToString(), dataToWrite.value2.ToString(),dataToWrite.value3.ToString()};
                        CreateColumns(FileWriter, values);
                        FileWriter.WriteLine();
                    }
                }
            }
        }

        private static void CreateColumns(StreamWriter FileWriter, string[] columnNames)
        {
            foreach (string str in columnNames)
            {
                FileWriter.Write(String.Format("{0},", removeCommaForCSV(str)));
            }
        }

        private static string removeCommaForCSV(string csvString)
        {
            if (csvString.IndexOf(",") != -1)
            {
                System.Text.StringBuilder escapedcsvString = new System.Text.StringBuilder();
                char[] csvStringArray = csvString.ToCharArray();
                for (int i = 0; i < csvStringArray.Length; i++)
                {
                    if (csvStringArray[i] != ',')
                        escapedcsvString.Append(csvStringArray[i]);
                }

                return escapedcsvString.ToString();
            }
            else
            {
                return csvString;
            }
        }

You can customize colums as you need, I think It may help you.

---

Software Developer

Answer 37

Check this out:

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

This is really good too:

http://siddharthrout.wordpress.com/vb-net-and-excel/

To convert VB to C#, use one of these tools:

http://converter.telerik.com/

http://www.developerfusion.com/tools/convert/vb-to-csharp/

http://www.developerfusion.com/tools/convert/vb-to-csharp/

---

Ryan Shuell