¿¢¼¿ Ä®·³ ÆíÁý¿¡ ´ëÇؼ ¾Ë¾Æº»´Ù. ¸¹Àº ¿¢¼¿ÆÄÀÏÀÌ ÀÖÀ» ¶§, ¿¢¼¿ ÆÄÀÏÀ» ÀÏ°ýÀûÀ¸·Î ÆíÁýÇÒ´ë ÇÊ¿äÇÏ´Ù.
< ¿¢¼¿ ÂüÁ¶ Ãß°¡ >
¿¢¼¿¿¡ Á¢±ÙÇϱâ À§Çؼ´Â ÂüÁ¶Ãß°¡¿¡ 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
|