Editing Existing Excel Worksheep in .Net

[anthony.gilliam]

Hi programmer people.

A vendor of mine uses an Excel workbook as an order form. I'm trying
to write a csharp code in VS '07 that could import the workbook and
edit it.

I am able to import the workbook into my project. What syntax do I
use to initialize the workbook as an object so that I can edit it with
code?

I appreciate any suggestions or references to information.

Thanks

 

[Guest]

This will help:
http://www.codeproject.com/office/csharp_excel.asp

Adrian.

 

[Jesse Houwing]

* (e-mail address removed) wrote, On 23-7-2007 19:32:

Hi programmer people.

A vendor of mine uses an Excel workbook as an order form. I'm trying
to write a csharp code in VS '07 that could import the workbook and
edit it.

I am able to import the workbook into my project. What syntax do I
use to initialize the workbook as an object so that I can edit it with
code?

I appreciate any suggestions or references to information.

Thanks

Have a look at the Aspose Spreadsheet component, it allows you to modify
Excel files without having Office installed.

http://www.aspose.com/Products/Aspose.Cells/Default.aspx

Jesse

 

[anthony.gilliam]

Thanks for the Reply All,

Jesse, I might end up getting Aspose if I can't find a .Net solution.

Adrian, I'm trying to implement the code on the page you've shown me
and I can go this far:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace ITWorkOrder
{
class MakeWorkOrder
{
Excel.Application excelApp = new Excel.ApplicationClass();


This much of the code compiles fine with no errors. The problem is,
after I initialize excelApp as an Excel.Application object, I can't
reference it at all in my code. When I try to execute:

excelApp.Visable = true;

excelApp is not even recognized, as if it isn't declared.

Are there any special assemblies that I need to add? I've added:

Microsoft.Office.Core
Microsoft.Office.Tools.Common
Microsoft.Office.Tools.Excel
Microsoft.Office.Tools.Applications.Runtime

Is their anything else??

This thing is really beating me up. Any additional help would very
much be appreciated.

 

[Guest]

To be able to import and edit an Excel document you need to open it first.
The code that you have so far does not even specify an Excel document to work
with.

Lets start at the beginning. I assume you have added a reference to the
Microsoft Excel Object Library before you started? If that doesn't sound
familiar then you need to download the Microsoft Office XP PIAs before you
can go any further (Assuming you are using XP, if not then the Office PIA for
your OS). When I wrote my first program to control an Excel document I found
a lot of useful info at the following url:

http://support.microsoft.com/kb/311452/

This one is also good and is more specific to your needs:
http://support.microsoft.com/kb/302084/

Here is some code I wrote to get you started:

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication2
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
DoWork();
}

public static void DoWork()
{
string workbookPath = @"C:\MyDocument.xls";

if (!System.IO.File.Exists(workbookPath))
{
Console.WriteLine("The source file does not exist. Press any key to
exit.");
Console.ReadLine();
return;
}

Excel.ApplicationClass excelApp = null;
Excel.Workbook excelWorkbook = null;

try
{
// Get an Excel instance and open the file
excelApp = new Excel.ApplicationClass();
excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "",
false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false,
false);

// Get the worksheet with data
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string mySheet = "Sheet X";
Excel.Worksheet excelWorksheet =
(Excel.Worksheet)excelSheets.get_Item(mySheet);

try
{
if (!((Excel.Range)excelWorksheet.get_Range("B1", "B1")).Text.Equals(""))
{
// Display the text in the cell B1
Console.WriteLine(((Excel.Range)excelWorksheet.get_Range("B1",
"B1")).Text);
}
}
catch (Exception ex)
{
Console.WriteLine("An exception occurred while reading from the Excel
document: " + ex.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine("An exception occurred while setting up the
environment: " + ex.ToString());
}
finally
{
// Exit everything
if (excelWorkbook != null)
{
excelWorkbook.Close(false, System.Reflection.Missing.Value, false);
excelWorkbook = null;
}
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}

// Clean up
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}

Adrian.