none
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? 

    Friday, October 06, 2006 4:21 PM

Answers

  • 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.

    Monday, July 27, 2009 12:16 PM

All replies

  • 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.
    Friday, October 06, 2006 6:01 PM
  • 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.

    Friday, October 06, 2006 6:58 PM
  • Where can I find the dll?
    Sunday, October 08, 2006 12:37 AM
  • In references, right click, add reference

    Choose the COM tab

    Choose Microsoft Excel 10.0 Object Library

    Sunday, October 08, 2006 12:45 AM
  • Can you possibly show me code that opens an excel worksheet and writes a few values into specified cells?
    Sunday, October 08, 2006 1:03 AM
  • Can you possibly show me code that opens an excel worksheet and writes a few values into specified cells?
    Sunday, October 08, 2006 1:03 AM
  • 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.
    Tuesday, October 10, 2006 8:21 PM
  • 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.

    Monday, July 27, 2009 12:16 PM
  • Thanks! .. just what i was searching for
    Thursday, August 20, 2009 5:03 PM
  • 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;
    • Edited by Impulse6 Friday, September 04, 2009 2:08 AM Typo
    Friday, September 04, 2009 2:06 AM
  • 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

     

    • Edited by mdhawan Monday, June 07, 2010 4:21 PM formatting
    Monday, June 07, 2010 4:19 PM
  • 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

    Thursday, July 08, 2010 6:01 AM
  • 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.?

    Tuesday, September 21, 2010 9:31 AM
  • probably you are not putting a semicolon at the end of the last line.
    Wednesday, October 06, 2010 1:27 PM
  • oh and change that last line to read-> myExcelWorksheet.get_Range("C22", misValue).Formula = "New Value";
    Wednesday, October 06, 2010 1:36 PM
  • 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!
    Thursday, January 27, 2011 9:43 PM
  •  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.

    Friday, January 28, 2011 6:54 AM
  • "myExcelWorkbooks.Open " Opens the Excel sheet abruptly getting popped up.

    How to stop it..



    Monday, April 25, 2011 10:10 AM
  • 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

    Monday, May 23, 2011 2:30 AM
  • 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

     

    Tuesday, May 24, 2011 8:46 AM
  • Thursday, October 13, 2011 8:34 AM
  • i found your and jasonMc86 post very usefull.
    Thanks 
    Tuesday, October 18, 2011 11:17 AM
  • 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
    Saturday, January 07, 2012 7:27 PM
  • 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.

    Monday, January 16, 2012 3:33 PM
  • 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
    Friday, January 20, 2012 9:04 AM
  • 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

     

     

     


    Monday, January 23, 2012 7:45 AM
  • 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
    Tuesday, January 24, 2012 1:07 AM
  • 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.

    Wednesday, January 25, 2012 9:51 AM
  • 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");

    Friday, April 13, 2012 7:52 AM
  • 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";

    • Edited by mtbfreak Monday, September 03, 2012 8:42 PM added example
    Monday, September 03, 2012 8:41 PM
  • 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.

    Thursday, October 18, 2012 8:05 AM
  • 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?

    Friday, November 23, 2012 11:40 AM
  • 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.

    Thursday, February 14, 2013 8:32 AM
  • 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

    Thursday, February 14, 2013 3:52 PM
  • Would you mind to tell us the use of Microsoft.Office.Core class in this block of code?
    Tuesday, April 08, 2014 12:16 AM
  • Hi

    There is an error at this part of the code:myExcelApp = new Excel.ApplicationClass();

    Error message : Error    1    The type 'Microsoft.Office.Interop.Excel.ApplicationClass' has no constructors defined   

    Thanks in advance

    Friday, July 18, 2014 10:09 AM