CODEWITHSUNDEEP
c#excel
user1396678
user1396678

Reputation: 21

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

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.

Upvotes: 0

Views: 22401

Answers (2)

user2369270
user2369270

Reputation: 1

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();

Upvotes: 0

Dimitris
Dimitris

Reputation: 2036

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.

Upvotes: 1

Related Questions