ClassExcel
C# / 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();
}
}
}
'C#' 카테고리의 다른 글
| Form 자식창이 부모창의 중앙에 위치 (0) | 2020.10.10 |
|---|---|
| Ini File class (0) | 2020.10.10 |
| 리스트뷰 헤더 컬럼들이 안보이는 경우 (0) | 2020.10.10 |
| FIFO, GUI 쓰레드 엑세스위반 다른 대안 (0) | 2020.10.10 |
| 함수 포인터 전달 (0) | 2020.10.10 |