I've looked at all the possible answers on stackoverflow and still cant figure out how to solve my problem.
My program creates an excel file, exports data into the file and then should close it. It doesn't close it completely however and the excel process is still running in the background which makes using my code more than once impossible as it always throws up an error.
I start of by creating my excel object
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
I then assign variables to everything I need to use:
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
then do all my manipulation.
To close the excel process I use:
xlWorkBook.SaveAs(@"C:\Users\dphillips\Desktop\Projects\C#\Exported Excel File", Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, "Exported Excel File", misValue);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlWorkSheet);
Marshal.FinalReleaseComObject(xlWorkBook);
Marshal.FinalReleaseComObject(xlApp);
xlWorkSheet = null;
xlApp = null;
xlWorkBook = null;
The process doesn't close though. I used http://csharp.net-informations.com/excel/csharp-create-excel.htm as my template to do this as I'm new to C# and don't have enough experience to do it on my own.
Any advice on how to correctly close the program would be greatly appreciated
EDIT: Since I can't seem to get any luck with it I will just post my whole program. Maybe then you could tell me exactly where I'm going wrong
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace FormPractice
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
FolderBrowserDialog FBD = new FolderBrowserDialog();
if (FBD.ShowDialog() == DialogResult.OK)
{
textBox1.Text = FBD.SelectedPath;
//Creating excel object
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
//Check to see that excel is installed.
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
// regular expressions to check for the ID number in the file and the issue number of the document
Regex regularExpressionDocID = new Regex(@"\d{3}[A-Z]\W\d{4}\W\d{6}\W\d{3}\W[A-Z]\d{2}");
Regex regularExpressionIssueNumber = new Regex(@"[i,I]s+(ue)*.?\d{1,2}$");
var filesToBeRead = Directory.GetFiles(FBD.SelectedPath, "*.*", SearchOption.AllDirectories)
.Where(fileNameBeingProcessed =>
(fileNameBeingProcessed.EndsWith(".txt")
|| fileNameBeingProcessed.EndsWith(".docx")
|| fileNameBeingProcessed.EndsWith(".doc")
|| fileNameBeingProcessed.EndsWith(".xls")
|| fileNameBeingProcessed.EndsWith(".pdf")
|| fileNameBeingProcessed.EndsWith(".docm")
|| fileNameBeingProcessed.EndsWith(".xlsm")
|| fileNameBeingProcessed.EndsWith(".xlsx")))
.ToList();
var xlWorkBooks = xlApp.Workbooks;
object misValue = System.Reflection.Missing.Value;
var xlWorkBook = xlWorkBooks.Add(misValue);
var xlWorkSheets = xlWorkBook.Worksheets;
var xlWorkSheet = (Excel.Worksheet)xlWorkSheets.get_Item(1);
string comparingString;
int counter = 2;
// setting all headers
xlWorkSheet.Cells[1, 1] = "Document Number";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "System Revision";
xlWorkSheet.Cells[1, 4] = "Revision";
xlWorkSheet.Cells[1, 5] = "Type";
xlWorkSheet.Cells[1, 6] = "Old Name";
xlWorkSheet.Cells[1, 7] = "File Name";
xlWorkSheet.Cells[1, 8] = "Directory";
xlWorkSheet.Cells[1, 9] = "Owner";
foreach (string file in filesToBeRead)
{
string documentID, documentName, revision, oldName, directoryOfFile, stringToBeManipulated;
comparingString = Path.GetFileNameWithoutExtension(file);
Match matchesFormat = regularExpressionDocID.Match(comparingString);
Match matchesIssue = regularExpressionIssueNumber.Match(comparingString);
// All manipulations to get each of the required fields for the ARAS upload xlsx
if (matchesFormat.Success)
{
stringToBeManipulated = (Path.GetFileNameWithoutExtension(file)); // document ID sectiom
documentID = stringToBeManipulated.Substring(0, 24);
xlWorkSheet.Cells[counter, 1] = documentID;
if (matchesIssue.Success)
{
//If "issue" is found then gives name(from char [25] and inserts issue into the correct cells
revision = matchesIssue.Value;
documentName = stringToBeManipulated[25..matchesIssue.Index];
}
else
{
//If "issue" is not found fills the issue column with a "-" and makes the entire string the name.
revision = "-";
documentName = stringToBeManipulated[25..stringToBeManipulated.Length];
}
xlWorkSheet.Cells[counter, 2] = documentName;
xlWorkSheet.Cells[counter, 3] = revision;
xlWorkSheet.Cells[counter, 4] = revision;
oldName = (Path.GetFileName(file));
xlWorkSheet.Cells[counter, 6] = oldName;
xlWorkSheet.Cells[counter, 7] = oldName;
directoryOfFile = Path.GetDirectoryName(file);
xlWorkSheet.Cells[counter, 8] = directoryOfFile;
counter++;
}
}
xlWorkBook.SaveAs(@"C:\Users\dphillips\Desktop\Projects\C#\Exported_Old_Doc_ID", Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, "Exported Excel File", misValue);
xlApp.DisplayAlerts = false;
xlApp.Quit();
//Releasing all objects to stop memory leaks
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
Marshal.FinalReleaseComObject(xlWorkSheet);
Marshal.FinalReleaseComObject(xlWorkBook);
Marshal.FinalReleaseComObject(xlWorkSheets);
Marshal.FinalReleaseComObject(xlWorkBooks);
Marshal.FinalReleaseComObject(xlApp);
xlWorkSheet = null;
xlApp = null;
xlWorkBook = null;
xlWorkBooks = null;
xlWorkSheets = null;
MessageBox.Show(@"Excel file created , you can find the file C:\Users\dphillips\Desktop\Projects\C#\Directory to pull");
}
GC.Collect();
Close();
