open Excel file, change it and then save it

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 5674 Views - Last Post: 04 July 2011 - 01:16 PM Rate Topic: -----

#1 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

open Excel file, change it and then save it

Posted 03 July 2011 - 10:11 AM

I need to do the following:
-> open Excel file
-> change values of required cells
-> then save the file

right now I am doing this:
Excel.Application xlAppoutput = null;
Excel.Workbook xlWorkBookoutput = null;
Excel.Worksheet xlWorkSheetoutput = null;
Excel.Range rangeoutput = null;
try
                {  
                    xlAppoutput = new Excel.ApplicationClass();
                    xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                    rangeoutput = xlWorkSheetoutput.UsedRange;

                    //(rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "new value";

                    xlWorkBookoutput.Close(true, null, null);
                    xlAppoutput.Quit();

                    releaseObject(xlWorkSheetoutput);
                    releaseObject(xlWorkBookoutput);
                    releaseObject(xlAppoutput);
                }catch(Exception ex){
                    System.Console.Write(ex.StackTrace);
                    System.Console.Write("System could not successfully generate bill for House#" + Reader.GetValue(houseNoFieldIndexInDB).ToString()+"\n");
                    releaseObject(xlWorkSheetoutput);
                    releaseObject(xlWorkBookoutput);
                    releaseObject(xlAppoutput);
                }




I get an exception on 'xlAppoutput.Quit();'


Can anyone please help me fix this? or tell me any other way of doing the same job (open excel file, change it then save it)

Is This A Good Question/Topic? 0
  • +

Replies To: open Excel file, change it and then save it

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9278
  • View blog
  • Posts: 34,789
  • Joined: 12-June 08

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 10:35 AM

What is the exception you got?
Was This Post Helpful? 0
  • +
  • -

#3 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:01 PM

View Postmodi123_1, on 03 July 2011 - 10:35 AM, said:

What is the exception you got?


Bellow is the exception with error code#-2146827284

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in Askari9.exe
at Microsoft.Office.Interop.Excel.WorkbookClass.Close(Object SaveChanges, Object Filename, Object RouteWorkbook)
at Askari9.mainForm.buttonstartPrinting_Click(Object sender, EventArgs e) in C:\Users\adil\Documents\Visual Studio 2008\Projects\Askari9\Askari9\Form1.cs:line 374


See line#12 in the code. When I comment it there is no exception. But when I uncomment it I get exception.
That means if I open the file and just close it it work's fine. But if I open the file, write something and then try to close it it throws exception. I've checked the file properties it's not 'read only'. I don't know what else could be the reason of this exception :(
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5535
  • View blog
  • Posts: 11,861
  • Joined: 02-June 10

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:18 PM

10 rangeoutput = xlWorkSheetoutput.UsedRange;

12 //(rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "new value";


Have you put a breakpoint at line 10?
By the time this gets to line 12 what is the actual value of rangeoutput? Can you show us a screenshot of the Locals pallet showing us the true value of rangeoutput at line 12?
If it is null, then you can't call .Cells[12,3]
If it doesn't have that many cells, then you can get 12,3 out of it.
You don't seem to be doing any range changing to confirm you are calling a valid range.
Was This Post Helpful? 0
  • +
  • -

#5 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:24 PM

It's working on my Excel2007(student release), Win7
Try this out
 private void button2_Click(object sender, EventArgs e)
        {
            //' This line is very important!
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-EN");//<-- change culture on whatever you need
            
            Excel.Application xlAppoutput = null;
            Excel.Workbook xlWorkBookoutput = null;
            Excel.Worksheet xlWorkSheetoutput = null;
            Excel.Range rangeoutput = null;
            object missing = Type.Missing;
            try
            {
                xlAppoutput = new Excel.ApplicationClass();
                xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                rangeoutput = xlWorkSheetoutput.UsedRange;

                (rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "new value";
  

                ((Excel._Workbook)xlWorkBookoutput).Close(true, missing, missing);

                xlAppoutput.Quit();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput);
                releaseObject(xlWorkSheetoutput);
                releaseObject(xlWorkBookoutput);
                releaseObject(xlAppoutput);
            }
            catch (Exception ex)
            {
                System.Console.Write(ex.StackTrace);
                System.Console.Write("System could not successfully generate bill for House#" + Reader.GetValue(houseNoFieldIndexInDB).ToString() + "\n");
                releaseObject(xlWorkSheetoutput);
                releaseObject(xlWorkBookoutput);
                releaseObject(xlAppoutput);
            }
        }

Was This Post Helpful? 0
  • +
  • -

#6 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:35 PM

View PosttlhIn`toq, on 03 July 2011 - 01:18 PM, said:

10 rangeoutput = xlWorkSheetoutput.UsedRange;

12 //(rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "new value";


Have you put a breakpoint at line 10?
By the time this gets to line 12 what is the actual value of rangeoutput? Can you show us a screenshot of the Locals pallet showing us the true value of rangeoutput at line 12?
If it is null, then you can't call .Cells[12,3]
If it doesn't have that many cells, then you can get 12,3 out of it.
You don't seem to be doing any range changing to confirm you are calling a valid range.


I've added breakpoint
the value of rangeoutput.Rows.Count is 41
and the value of rangeoutput.Columns.Count is 17


That means I am accessing a valid cell. If you need any other info please let me know ??

I've added this:
xlWorkBookoutput.Save();

Before line#15

Now the exception is gone but the data i put on the cell[12, 3] is not there in the file
I mean after the code execution I dont see the effect of line#12
Was This Post Helpful? 0
  • +
  • -

#7 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:46 PM

View Postfixo, on 03 July 2011 - 01:24 PM, said:

It's working on my Excel2007(student release), Win7
Try this out
 private void button2_Click(object sender, EventArgs e)
        {
            //' This line is very important!
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-EN");//<-- change culture on whatever you need
            
            Excel.Application xlAppoutput = null;
            Excel.Workbook xlWorkBookoutput = null;
            Excel.Worksheet xlWorkSheetoutput = null;
            Excel.Range rangeoutput = null;
            object missing = Type.Missing;
            try
            {
                xlAppoutput = new Excel.ApplicationClass();
                xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                rangeoutput = xlWorkSheetoutput.UsedRange;

                (rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "new value";
  

                ((Excel._Workbook)xlWorkBookoutput).Close(true, missing, missing);

                xlAppoutput.Quit();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput);
                releaseObject(xlWorkSheetoutput);
                releaseObject(xlWorkBookoutput);
                releaseObject(xlAppoutput);
            }
            catch (Exception ex)
            {
                System.Console.Write(ex.StackTrace);
                System.Console.Write("System could not successfully generate bill for House#" + Reader.GetValue(houseNoFieldIndexInDB).ToString() + "\n");
                releaseObject(xlWorkSheetoutput);
                releaseObject(xlWorkBookoutput);
                releaseObject(xlAppoutput);
            }
        }


While trying your code I got following error:
Unable to release the Object System.NullReferenceException: Object reference is not set to an instance of an object.
Was This Post Helpful? 0
  • +
  • -

#8 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 01:50 PM

Reason is on I've forgot to remove this line from code
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput); 

Sorry
Try again
Was This Post Helpful? 0
  • +
  • -

#9 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 02:06 PM

View Postfixo, on 03 July 2011 - 01:50 PM, said:

Reason is on I've forgot to remove this line from code
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput); 

Sorry
Try again


Still no luck :( :(
Did you change your Excel's Macro Setting ??
Can it be some security settings problem ??
I have the default settings right now..
Was This Post Helpful? 0
  • +
  • -

#10 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 02:11 PM

View Postadi.shoukat, on 03 July 2011 - 03:06 PM, said:

View Postfixo, on 03 July 2011 - 01:50 PM, said:

Reason is on I've forgot to remove this line from code
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput); 

Sorry
Try again


Still no luck :( :(
Did you change your Excel's Macro Setting ??
Can it be some security settings problem ??
I have the default settings right now..

Yes, I've used default settings
Show your *releseObject* function,
my guess it's wrong, but not sure about
Try this code again it's worked like charm on my end as well
        private void button2_Click(object sender, EventArgs e)
        {
            //' This line is very important!
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-EN");//<-- change culture on whatever you need
            
            Excel.Application xlAppoutput = null;
            Excel.Workbook xlWorkBookoutput = null;
            Excel.Worksheet xlWorkSheetoutput = null;
            Excel.Range rangeoutput = null;
            object missing = Type.Missing;
            try
            {
                xlAppoutput = new Excel.ApplicationClass();
                xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"C:\UsedFiles\points.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                rangeoutput = xlWorkSheetoutput.UsedRange;

                (rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "another new value";


                ((Excel._Workbook)xlWorkBookoutput).Close(true, missing, missing);

                xlAppoutput.Quit();

            }
            catch (Exception ex)
            {
                System.Console.Write(ex.StackTrace);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput);
            }
        }


This post has been edited by fixo: 03 July 2011 - 02:24 PM

Was This Post Helpful? 0
  • +
  • -

#11 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 02:45 PM

View Postfixo, on 03 July 2011 - 02:11 PM, said:

View Postadi.shoukat, on 03 July 2011 - 03:06 PM, said:

View Postfixo, on 03 July 2011 - 01:50 PM, said:

Reason is on I've forgot to remove this line from code
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput); 

Sorry
Try again


Still no luck :( :(
Did you change your Excel's Macro Setting ??
Can it be some security settings problem ??
I have the default settings right now..

Yes, I've used default settings
Show your *releseObject* function,
my guess it's wrong, but not sure about
Try this code again it's worked like charm on my end as well
        private void button2_Click(object sender, EventArgs e)
        {
            //' This line is very important!
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-EN");//<-- change culture on whatever you need
            
            Excel.Application xlAppoutput = null;
            Excel.Workbook xlWorkBookoutput = null;
            Excel.Worksheet xlWorkSheetoutput = null;
            Excel.Range rangeoutput = null;
            object missing = Type.Missing;
            try
            {
                xlAppoutput = new Excel.ApplicationClass();
                xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"C:\UsedFiles\points.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                rangeoutput = xlWorkSheetoutput.UsedRange;

                (rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "another new value";


                ((Excel._Workbook)xlWorkBookoutput).Close(true, missing, missing);

                xlAppoutput.Quit();

            }
            catch (Exception ex)
            {
                System.Console.Write(ex.StackTrace);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput);
            }
        }




Don't know if it's working for you then y not for me :(
now getting this exception:
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in Askari9.exe
Was This Post Helpful? 0
  • +
  • -

#12 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 03:12 PM

I've recreated this windows form application from on SharpDevelop
(instead of VStudio - just for testing) as console application and now it isn't working
for me too :(
I will look at this problem closely
tomorrow only

This post has been edited by fixo: 03 July 2011 - 11:09 PM

Was This Post Helpful? 0
  • +
  • -

#13 Jeff H  Icon User is offline

  • D.I.C Regular

Reputation: 112
  • View blog
  • Posts: 308
  • Joined: 30-January 11

Re: open Excel file, change it and then save it

Posted 03 July 2011 - 04:02 PM

Change
xlWorkBookoutput.Close(true, null, null); 


to
xlWorkBookoutput.Close(true, Type.Missing, Type.Missing);



This works for me
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open("C:\\Test\\ExcelTest.xlsx");
            Microsoft.Office.Interop.Excel.Worksheet ws = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                       
            
            ws.Range["A1"].Value = "nnn";
            ws.Range["B1"].Value = "bb";          
            ws.Cells[3, 3] = "asdfg";        
            ws.Cells[4, 4] = "qwerty";
            wb.Close(true, Type.Missing, Type.Missing);
            excel.Quit();


Errors out if change Typed.Missing to null

Your error message says its happens when tryin to cal WorkBook.Close.

This post has been edited by Jeff H: 03 July 2011 - 04:05 PM

Was This Post Helpful? 0
  • +
  • -

#14 adi.shoukat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 29-May 11

Re: open Excel file, change it and then save it

Posted 04 July 2011 - 12:51 AM

View Postfixo, on 03 July 2011 - 03:12 PM, said:

I've recreated this windows form application from on SharpDevelop
(instead of VStudio - just for testing) as console application and now it isn't working
for me too :(
I will look at this problem closely
tomorrow only


Sure,
I'll wait for your kind guidance .. :)
Till then I am trying different ways .. If I got any fix I'll paste that here ..
Was This Post Helpful? 0
  • +
  • -

#15 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: open Excel file, change it and then save it

Posted 04 July 2011 - 04:55 AM

View Postadi.shoukat, on 04 July 2011 - 01:51 AM, said:

View Postfixo, on 03 July 2011 - 03:12 PM, said:

I've recreated this windows form application from on SharpDevelop
(instead of VStudio - just for testing) as console application and now it isn't working
for me too :(
I will look at this problem closely
tomorrow only


Sure,
I'll wait for your kind guidance .. :)
Till then I am trying different ways .. If I got any fix I'll paste that here ..

Ok, what I have got after extensive testing this code on several files
My code is working good on files with or without embedded
VBA macroses or add-ins
I used Excel 12, Win7, Internet Explorer 9, .NET Framework 2.0
I've found that Sharpdevelop 3.2 is not working
with IE9 though
And also it's very important anyway on my machine
set Copy Local to False for Excel/Office libraries

I couldn't help you more than I can
Try again, slightly edited code
        //All references in tested form
//        using System;
//using System.Collections.Generic;
//using System.ComponentModel;
//using System.Data;
//using System.Drawing;
//using System.Text;
//using System.Windows.Forms;

//using System.Runtime.InteropServices;
//using System.Globalization;
//using System.Threading;
//using Microsoft.Office.Interop.Excel;
//using Excel = Microsoft.Office.Interop.Excel;

        //Office/ Excel specific references:
        //Microsof.Office.Core - Microsoft Office 12.0 Object Library
        //(Path: C:\Windows\assembly\GAC\Office\12.0.0.0__71e9bce111e9429c\Office.dll)
        //--------------------------------------------------------------------------//
        //Microsoft.Office.Interop.Excel - Microsoft Excel 12.0 Object Library
        //(Path: C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll)
        //--------------------------------------------------------------------------//
        // Set 'Copy Local' for both .dll to False

        public static void testXL()
        {
            //' This line is very important!
            CultureInfo oldCult = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");//<-- change culture on whatever you need

            Excel.Application xlAppoutput = null;
            Excel.Workbooks xlWorkBooksOutput = null;
            Excel.Workbook xlWorkBookoutput = null;
            Excel.Worksheet xlWorkSheetoutput = null;
            Excel.Range rangeoutput = null;
            object missing = Type.Missing;
            try
            {

                xlAppoutput = new Excel.ApplicationClass();

                // working a swell:
                //xlAppoutput = new Excel.Application();
                // optional thisngs added:
                xlAppoutput.Visible = true;
                xlAppoutput.DisplayAlerts = false;
                xlAppoutput.UserControl = true;
                xlWorkBooksOutput = (Excel.Workbooks)xlAppoutput.Workbooks;
                System.Console.WriteLine("\n{0}", xlWorkBooksOutput.Count);
                //xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"D:\Askari9\output\myFile.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                // all code lines below was working good
                //working code line(Excel file with an embeded macroses):
                xlWorkBookoutput = xlAppoutput.Workbooks.Open(@"C:\UsedFiles\points.xls", missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //working code line(Excel file with no embeded macroses):
               // xlWorkBookoutput = (Excel.Workbook)xlWorkBooksOutput.Open(@"C:\UsedFiles\stations.xls", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //working code line(Excel file with no embeded macroses):
                //xlWorkBookoutput = (Excel.Workbook)xlWorkBooksOutput.Open(@"C:\UsedFiles\stations.xls", true, false, missing, "", missing, false, missing, missing, true, missing, missing, missing, missing, missing);
                xlWorkSheetoutput = (Excel.Worksheet)xlWorkBookoutput.Worksheets.get_Item(1);
                rangeoutput = (Excel.Range)xlWorkSheetoutput.UsedRange;

                (rangeoutput.Cells[12, 3] as Excel.Range).Value2 = "HUH?";


                ((Excel._Workbook)xlWorkBookoutput).Close(true, missing, missing);

                xlAppoutput.Quit();

            }
            catch (Exception ex)
            {
                System.Console.Write(ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                // System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlAppoutput);
                releaseObject(xlAppoutput);
                Thread.CurrentThread.CurrentCulture = oldCult;
            }
        }


        public static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
                obj = null;
            }
            catch (System.Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.ToString());
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }



~'J'~
Was This Post Helpful? 2
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2