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