C#

ClassExcel

안녕1999 2020. 10. 10. 23:42
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.Threading;
using System.Reflection;
using System.IO.Ports;
using System.IO;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
/*
https://docs.microsoft.com/ko-kr/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects
https://docs.microsoft.com/ko-kr/previous-versions/office/troubleshoot/office-developer/automate-excel-from-visual-c
https://stackoverflow.com/questions/19543279/microsoft-office-interop-excel-reference-cannot-be-found
Project menu->Add Reference(참조)->COM->Microsoft Excel Object Library


"Microsoft.Office.Interop.Excel not found"
도구 > NuGet 패키지 관리자 > 패키지 관리자 콘솔
Install-Package Microsoft.Office.Interop.Excel


"Microsoft.CSharp.RuntimeBinder.Binder.Convert' 멤버가 필요한 컴파일러가 없습니다
Project menu->Add Reference(참조)->어셈블리->Microsoft.CSharp.dll
*/

public class ClassExcel
{
	Excel.Application excelApp;
	Excel._Workbook wb;
	Excel._Worksheet ws;
	Excel.Range oRng;
	public string m_File;
	bool m_bSaved;

	public ClassExcel()
	{
		excelApp = null;
		wb = null;
		ws = null;
		oRng = null;
		m_bSaved = true;
	}
	~ClassExcel()
	{
		Close();
	}
	void On_err(Exception theException)
	{
		String errorMessage;
		errorMessage = "Error: ";
		errorMessage = String.Concat(errorMessage, theException.Message);
		errorMessage = String.Concat(errorMessage, "\r\n\r\nLine: ");
		errorMessage = String.Concat(errorMessage, theException.Source);

		MessageBox.Show(errorMessage, "Error");
	}
	public bool New(string file, bool Visible = true, bool UserControl = true)
	{
		bool ret = false;
		m_File = file;
		try
		{
			//Start Excel and get Application object.
			excelApp = new Excel.Application();

			excelApp.Visible = Visible;
			excelApp.UserControl = UserControl;

			//Get a new workbook.
			wb = (Excel._Workbook)(excelApp.Workbooks.Add(Missing.Value));
			ws = (Excel._Worksheet)wb.ActiveSheet;

			m_bSaved = false;
			ret = true;
		}
		catch (Exception theException)
		{
			On_err(theException);
		}
		return ret;
	}
	public bool Open(string file, bool Visible = true, bool UserControl = true)
	{
		bool ret = false;
		m_File = G.MakeFullPath(file);
		try
		{
			//Start Excel and get Application object.
			excelApp = new Excel.Application();

			excelApp.Visible = Visible;
			excelApp.UserControl = UserControl;

			// 엑셀 파일 열기
			wb = excelApp.Workbooks.Open(m_File);
			// 첫번째 Worksheet
			ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

			ret = true;
		}
		catch (Exception theException)
		{
			//open err
			On_err(theException);
		}
		return ret;
	}
	public bool SelectWorksheet(string name)
	{
		bool ret = false;
		int i;
		if (ws.Name != name)
		{
			Excel._Worksheet w;
			for (i = 1; i <= wb.Worksheets.Count; i++)
			{
				w = wb.Worksheets.get_Item(i);// as Excel.Worksheet;
				if (w != null)
				{
					if (w.Name == name)
					{
						w.Activate();
						ws = w;
						ret = true;
						break;
					}
					else
					{ }
				}
				else
				{ }
			}
			if (ret == false)
			{
				//동일한 이름의 시트가 없다. 새로생성
				ws = wb.Worksheets.Add();
				ws.Activate();
				ws.Name = name;
				m_bSaved = false;
				ret = true;
			}
			else
			{ }
		}
		else
		{
			//이미선택됨
		}
		return ret;
	}
	public bool SelectWorksheet(int no1)
	{
		bool ret = false;
		if ((1 <= no1) && (no1 <= wb.Worksheets.Count))
		{
			ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
			ret = true;
		}
		return ret;
	}
	public void Show(bool bShow)
	{
		excelApp.Visible = bShow;
	}
	public bool Puts(int row1,int col1,string s)
	{
		bool ret = false;
		try
		{
			ws.Cells[row1, col1] = s;
			m_bSaved = false;
		}
		catch
		{
		}
		return ret;
	}
	public bool Puts(string cell, string s)//cell="A2"
	{
		bool ret = false;
		try
		{
			Excel.Range c = ws.get_Range(cell, cell);
			c.Cells[1,1] = s;
			m_bSaved = false;
		}
		catch
		{
		}
		return ret;
	}
	public void SaveAs(string file)
	{
		m_File = file;
		m_bSaved = false;
		Save();
	}
	public void Save()
	{
		try
		{
			if (m_bSaved == false)
			{
				wb.SaveAs(m_File);
			}
			else
			{ }
			m_bSaved = true;
		}
		catch (Exception theException)
		{
			On_err(theException);
		}
	}
	public void Close()
	{
		if (excelApp != null)
		{
			Save();
			try
			{
				wb.Close();
				excelApp.Quit();
				ReleaseExcelObject(excelApp);
				ReleaseExcelObject(wb);
				ReleaseExcelObject(ws);
				excelApp = null;
				wb = null;
				ws = null;
			}
			catch (Exception theException)
			{
				On_err(theException);
			}
		}
		else
		{ }
	}
	private static void ReleaseExcelObject(object obj)
	{
		try
		{
			if (obj != null)
			{
				Marshal.ReleaseComObject(obj);
				obj = null;
			}
		}
		catch (Exception ex)
		{
			obj = null;
			throw ex;
		}
		finally
		{
			GC.Collect();
		}
	}
}