Excel Column Edit

엑셀 칼럼 편집에 대해서 알아본다.
많은 엑셀파일이 있을 때, 엑셀 파일을 일괄적으로 편집할대 필요하다.

< 엑셀 참조 추가 >

엑셀에 접근하기 위해서는 참조추가에 Microsoft.Office.Interop.Excel이 필요하다.

using 또한 다음과 같이 추가한다.

//for excel

using Excel = Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices; //Marshal 클래스

< 엑셀 파일 열기 >

엑셀 파일을 열어서 저장, 해제 하는 기본 기능부터 알아본다.

1 오브젝트 변수정의
2 엑셀 워크시트 열기
3 저장
4 오브젝트 해제

1 오브젝트 변수 정의

Excel.Application xlApp = null;

Excel.Workbook xlWorkBook = null;

Excel.Worksheet xlWorkSheet = null;

Excel.Range rng = null;

object misValue = System.Reflection.Missing.Value;

2 엑셀 워크시트 열기

xlApp = new Excel.Application();

xlApp.Visible = true;

xlWorkBook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false,

    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); 

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(sheetNum);

//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNum);

3 저장

//save and quit

//xlWorkBook.SaveAs(@"H:\TestFile.xlsx", misValue, misValue, misValue, misValue, misValue,

//    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Save();

 

xlWorkBook.Close(misValue, misValue, misValue);

xlApp.Quit();

4 오브젝트 해제

// release all the application object from the memory  

Marshal.ReleaseComObject(rng);

Marshal.ReleaseComObject(xlWorkSheet);

Marshal.ReleaseComObject(xlWorkBook);

Marshal.ReleaseComObject(xlApp);

rng = null;

xlWorkBook = null;

xlWorkSheet = null;

xlApp = null;

 

GC.Collect();

GC.WaitForPendingFinalizers();

엑셀 파일 열기, 오브젝트 해제 전체 코드는 다음과 같다.

public static void AddNewColumn(string fileName, int sheetNum)

{

    //1 오브젝트 변수 정의

    Excel.Application xlApp = null;

    Excel.Workbook xlWorkBook = null;

    Excel.Worksheet xlWorkSheet = null;

    Excel.Range rng = null;

    object misValue = System.Reflection.Missing.Value;

 

    try

    {

        //2 엑셀 워크시트 열기

        xlApp = new Excel.Application();

        xlApp.Visible = true;

        xlWorkBook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false,

            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); 

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(sheetNum);

        //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetNum);

 

 

        //여기에 컬럼 편집 코드를 추가한다.       

     //여기에 컬럼 편집 코드를 추가한다.

     //여기에 컬럼 편집 코드를 추가한다.

 

        //3 저장

        //save and quit

        //xlWorkBook.SaveAs(@"H:\TestFile.xlsx", misValue, misValue, misValue, misValue, misValue,

        //    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

        xlWorkBook.Save();

 

        xlWorkBook.Close(misValue, misValue, misValue);

        xlApp.Quit();

    }

    catch (Exception ex)

    {

        MessageBox.Show(ex.ToString());

    }

    finally

    {

        //4 오브젝트 해제

        // release all the application object from the memory  

        Marshal.ReleaseComObject(rng);

        Marshal.ReleaseComObject(xlWorkSheet);

        Marshal.ReleaseComObject(xlWorkBook);

        Marshal.ReleaseComObject(xlApp);

        rng = null;

        xlWorkBook = null;

        xlWorkSheet = null;

        xlApp = null;

 

        GC.Collect();

        GC.WaitForPendingFinalizers();

    }

}

 

< 엑셀 편집 코드 >

칼럼 추가

//칼럼 추가

rng = (Excel.Range)xlWorkSheet.Cells[1, 2];

rng.EntireColumn.Insert(

    Excel.XlInsertShiftDirection.xlShiftToRight, false);

xlWorkSheet.Cells[1, 2] = "Add Column";

Marshal.ReleaseComObject(rng);

 

열 추가

//열 추가

rng = (Excel.Range)xlWorkSheet.Cells[1, 2];

rng.EntireRow.Insert(

    Excel.XlInsertShiftDirection.xlShiftDown, false);

xlWorkSheet.Cells[1, 2] = "Add Row";

Marshal.ReleaseComObject(rng);

 

열 삭제

//열 삭제

rng = (Excel.Range)xlWorkSheet.Cells[1, 2];

rng.EntireRow.Delete(misValue);

Marshal.ReleaseComObject(rng);

 

칼럼 삭제

//칼럼 삭제

rng = (Excel.Range)xlWorkSheet.Cells[1, 2];

rng.EntireColumn.Delete(misValue);

Marshal.ReleaseComObject(rng);

 

마지막 칼럼에 셀 추가

//마지막 컬럼에 셀 추가

rng = xlWorkSheet.UsedRange;

//int colCount = rng.Columns.Count;

int colCount = xlWorkSheet.Cells.Find("*",

    Type.Missing,

    Excel.XlFindLookIn.xlValues,

    Excel.XlLookAt.xlWhole,

    Excel.XlSearchOrder.xlByColumns,

    Excel.XlSearchDirection.xlPrevious,

    false,

    false,

    Type.Missing).Column;

int rowCount = rng.Rows.Count;

xlWorkSheet.Cells[1, colCount + 1] = "Last Column1";

xlWorkSheet.Cells[5, colCount + 1] = "Last Column5";

Marshal.ReleaseComObject(rng);

 

칼럼 개수 구하기 Tip

Columns.Count로 칼럼의 갯수를 구한다.  빈공백이 입력 되었을 때도 칼럼갯수에 포함된다.
빈공백이 포함된 칼럼을 제외 하려면 Cells.Find와 같은 함수를 이용해서 칼럼갯수를 구해야 한다.

 

칼럼 이동( 하드코딩 )

Excel.Range src = (Excel.Range)xlWorkSheet.get_Range("E1", "E5");

Excel.Range dest = (Excel.Range)xlWorkSheet.get_Range("B1", "B5");

dest.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, src.Cut( misValue ));

Marshal.ReleaseComObject(src);

Marshal.ReleaseComObject(dest);

E와 B는 칼럼의 인덱스이다.
1과 5는 열의 영역으로 처음부터 마지막까지 인덱스이다.
Inser로 지정한 영역을 카피한다.

칼럼 이동( 칼럼을 숫자로 )

rng = xlWorkSheet.UsedRange;

int nCount = rng.Rows.Count;

int srcColIndex = 5;

int destColIndex = 2;

 

int A = Convert.ToInt32('A') - 1;

srcColIndex += A;

destColIndex += A;

 

char s = Convert.ToChar(srcColIndex);

char d = Convert.ToChar(destColIndex);

 

string srcStr1 = s + "1";

string srcStr2 = s + nCount.ToString();

 

string destStr1 = d + "1";

string destStr2 = d + nCount.ToString();

Excel.Range src = (Excel.Range)xlWorkSheet.get_Range(srcStr1, srcStr2);

Excel.Range dest = (Excel.Range)xlWorkSheet.get_Range(destStr1, destStr2);

 

dest.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, src.Cut( misValue ));

Marshal.ReleaseComObject(src);

Marshal.ReleaseComObject(dest);

Marshal.ReleaseComObject(rng);

숫자를 알파벳으로 변환하여 칼럼 인덱스를 넘겨준다.
열은 1부터 Rows.Count까지 전체를 복사한다.
사용한 rng, src, dest 오브젝트는 바로 해제한다.

소스 : ExcelMacro.zip