C# how to Open excel file if it exists, create excel file if it doesn't, from WinForm?

Question

I'm making a program that works with Excel files. I need it to try to open an existing Excel file, or create a new one if it doesn't exist, for read and write options. And I have to do this without using OleDb. So the thing is, when I click the button it tries to create a new Excel file, even if I already have that file in the directory. Any ideas on how to fix this? All the user input must be done from WinForm. Here's my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace test2
{
    public partial class Form1 : Form
    {
        Microsoft.Office.Interop.Excel.Application oXL;
        Microsoft.Office.Interop.Excel._Workbook oWB;
        Microsoft.Office.Interop.Excel._Worksheet oSheet;
        Microsoft.Office.Interop.Excel.Range oRng;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        FileInfo fi = new FileInfo(@"C:\Users\User\Desktop\data.xls");
        if (!fi.Exists)
        {
            oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.Visible = true;
            oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(System.Reflection.Missing.Value));
            oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            oSheet.Cells[1, 1] = "First Name";
            oSheet.Cells[1, 2] = "Last Name";
            oSheet.Cells[1, 3] = "Full Name";
            oSheet.Cells[1, 4] = "Age";

            oSheet.get_Range("A1", "D1").Font.Bold = true;
            oSheet.get_Range("A1", "D1").VerticalAlignment =
            Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;      
        }
        else
        {
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            string myPath = (@"C:\Users\User\Desktop\data.xlsx");
            excelApp.Workbooks.Open(myPath);
            excelApp.Visible = true;                
        }
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }
}

}

Also, I would be grateful if someone could give me a code example for saving and closing Excel files without interaction with the user. The user would input data then click button and it would automatically save and close the Excel document, without any pop-ups being displayed to the user.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            string myPath = (@"C:\Users\User\Desktop\data.xlsx");
            excelApp.Workbooks.Open(myPath);
            excelApp.Visible = false;
            int rowIndex = 2; int colIndex = 2;
            excelApp.Cells[rowIndex, colIndex] = textBox1.Text;
            //how do I save workbook?
            //how do I colose Workbook?

Thanks in advance.

If you're targetting newer versions of Excel then the OpenXML SDK is a nicer API: openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/06/02/… — user159335
– user159335, Commented May 16, 2012 at 9:13
Try something like File.Exists(filepath) to check if the file exists. Also to save the file without asking the user, you should set the Application.DisplayAlerts property to False and then use App.Workbook.SaveAs(filepath) to save the file and lastly set the DisplayAlert property to true. Setting the display alert to false will prevent asking the user to save the file. Hope this will help. — vpv
– vpv, Commented May 16, 2012 at 9:28
try if (!System.IO.File.Exists(@"\Path")) to check the file exists — Nikson Kanti Paul
– Nikson Kanti Paul, Commented May 16, 2012 at 9:36

Dimitris · Accepted Answer · 2012-05-16 12:24:59Z

1

First of all I would definitely recommend using Visual Studio Tools for Office (VSTO) if you happen to have a newish version of Visual Studio such as 2010. You can create Excel projects within that framework and speed up your development time. If you want to find out more about it look here: http://msdn.microsoft.com/en-US/office/hh133430.aspx

To answer your question on saving your Excel workbook from a WinForm application you can do the following:

excelApp.ScreenUpdating = false;
excelApp.DisplayAlerts = false;

excelApp.ActiveWorkbook.Save();

excelApp.ScreenUpdating = true;
excelApp.DisplayAlerts = true;

Hope that helps but let us know if there is anything else there.

answered May 16, 2012 at 12:24

Dimitris

2,0463 gold badges28 silver badges46 bronze badges

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

Comments

Tony Stark · Accepted Answer · 2013-05-13 11:08:02Z

0

excelWorkbook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close();
excelApp.Quit();

edited May 13, 2013 at 11:08

Tony Stark

8,1028 gold badges47 silver badges63 bronze badges

answered May 10, 2013 at 8:39

user2369270

1

1 Comment

Aleksei Zyrianov Over a year ago

You may use 4 spaces before lines of your code to have it highlighted. It's also strongly suggested to add some description to your code, because code may be not self-descriptive for everybody.

Collectives™ on Stack Overflow

C# how to Open excel file if it exists, create excel file if it doesn't, from WinForm?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related