C#：Excel（COM）
ひな形サンプル
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; // 参照設定 // Microsoft Excel 16.0 Object Library using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel; using System.Diagnostics; /// <summary> /// /// </summary> namespace Excel_COM_2.ExcelCOM_01 { /// <summary> /// /// </summary> class clsExcelCOM_01 { /// <summary> /// /// </summary> public static void mth_Main() { var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; //表示・非表示 try { Excel.Workbooks wbs = app.Workbooks; try { string sFile_fp = @"D:\COM_TEST.xlsx"; Excel.Workbook wb = wbs.Open( Filename:sFile_fp, ReadOnly:true ); try { Excel.Sheets wss = wb.Sheets; try { //Worksheet ws = wss[1]; Excel.Worksheet ws = wss["シート(No2)"]; try { //================================ // 最終行／最終列 //================================ int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row; int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row; //=============================================== // 使用範囲を一括で二次元配列にコピー //=============================================== Object[,] rangeArray; Excel.Range rng = ws.UsedRange; try { rangeArray = rng.Value; } finally { Marshal.ReleaseComObject(rng); } // 二次元配列に対してループを回す int lastRow = rangeArray.GetLength(0); int lastCol = rangeArray.GetLength(1); for (int r = 1; r <= lastRow; r++) { //Console.WriteLine(r); for (int c = 1; c <= lastCol; c++) { Console.WriteLine(rangeArray[r, c]); } } } finally { Marshal.ReleaseComObject(ws); } } finally { Marshal.ReleaseComObject(wss); } } finally { //if (wb != null) //{ // wb.Close(false); //} Marshal.ReleaseComObject(wb); } } finally { Marshal.ReleaseComObject(wbs); } } finally { //if (app != null) //{ // app.Quit(); //} Marshal.ReleaseComObject(app); } } } }
Find
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; // 参照設定 // Microsoft Excel 16.0 Object Library using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel; namespace Excel_COM_2.ExcelCOM_01 { class clsFind { public static void mth_Main() { var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = true; //表示・非表示 try { Excel.Workbooks wbs = app.Workbooks; try { string sFile_fp = @"D:\COM_TEST.xlsx"; Excel.Workbook wb = wbs.Open( Filename: sFile_fp, ReadOnly: true ); try { Excel.Sheets wss = wb.Sheets; try { //Worksheet ws = wss[1]; Excel.Worksheet ws = wss["シート(No2)"]; try { //================================ // 最終行／最終列 //================================ int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row; int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row; //=============================================== // 使用範囲を一括で二次元配列にコピー //=============================================== Excel.Range targetRange = null; Excel.Range firstFind = null; Excel.Range currentFind = null; try { //targetRange = ws.Columns[colName]; //シート内での検索範囲 //targetRange = ws.Columns[1]; //シート内での検索範囲 targetRange = ws.Cells; //シート内での検索範囲 currentFind = targetRange.Find( "s", //検索キー Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing ); while (currentFind != null) { if (firstFind == null) { firstFind = currentFind; } else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1)) { break; } currentFind = targetRange.FindNext(currentFind); Console.WriteLine(currentFind.Address); currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0); } } finally { if (currentFind != null) Marshal.ReleaseComObject(currentFind); if (firstFind != null) Marshal.ReleaseComObject(firstFind); Marshal.ReleaseComObject(targetRange); } } finally { Marshal.ReleaseComObject(ws); } } finally { Marshal.ReleaseComObject(wss); } wb.SaveAs(@"D:\COM_TEST_2.xlsx"); } finally { if (wb != null) { wb.Close(false); } Marshal.ReleaseComObject(wb); } } finally { Marshal.ReleaseComObject(wbs); } } finally { if (app != null) { app.Quit(); } Marshal.ReleaseComObject(app); } } public static void mth_Mai_BK() { var app = new Microsoft.Office.Interop.Excel.Application(); try { Excel.Workbooks wbs = app.Workbooks; try { string sFile_fp = @"D:\COM_TEST.xlsx"; Excel.Workbook wb = wbs.Open(sFile_fp); try { Excel.Sheets wss = wb.Sheets; try { //Worksheet ws = wss[1]; Excel.Worksheet ws = wss["シート(No2)"]; try { //================================ // 最終行／最終列 //================================ int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row; int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row; //=============================================== // 使用範囲を一括で二次元配列にコピー //=============================================== Excel.Range targetRange = null; Excel.Range firstFind = null; Excel.Range currentFind = null; try { //targetRange = ws.Columns[colName]; //シート内での検索範囲 targetRange = ws.Columns[1]; //シート内での検索範囲 currentFind = targetRange.Find( 14, //検索キー Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing ); while (currentFind != null) { if (firstFind == null) { firstFind = currentFind; } else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1)) { break; } currentFind = targetRange.FindNext(currentFind); Console.WriteLine(currentFind.Address); currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0); } } finally { Marshal.ReleaseComObject(currentFind); Marshal.ReleaseComObject(firstFind); Marshal.ReleaseComObject(targetRange); } } finally { Marshal.ReleaseComObject(ws); } } finally { Marshal.ReleaseComObject(wss); } wb.SaveAs(@"D:\COM_TEST_2.xlsx"); } finally { if (wb != null) { wb.Close(false); } Marshal.ReleaseComObject(wb); } } finally { Marshal.ReleaseComObject(wbs); } } finally { if (app != null) { app.Quit(); } Marshal.ReleaseComObject(app); } } } }
開いているBOOK
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; // 参照設定 // Microsoft Excel 16.0 Object Library using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel; using Microsoft.VisualBasic; using System.Security; namespace Excel_COM_2.ExcelCOM_01 { class clS開いているExcel処理 { public static object CreateObject(string progId, string serverName) { Type t; if (serverName == null || serverName.Length == 0) t = Type.GetTypeFromProgID(progId); else t = Type.GetTypeFromProgID(progId, serverName, true); return Activator.CreateInstance(t); } public static void mth_Main() { //var app = new SecurityCritical.GetObject(, "Excel.Application"); //var app = new Microsoft.Office.Interop.Excel.Application(); var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); //var app = new CreateObject(, "Excel.Application"); try { Excel.Workbooks wbs = app.Workbooks; try { string sBkName = ""; foreach ( Excel.Workbook item_wb in wbs) { Console.WriteLine(item_wb.Name); sBkName =item_wb.Name; } Excel.Workbook wb = wbs[sBkName]; //string sFile_fp = @"D:\COM_TEST.xlsx"; //Excel.Workbook wb = wbs.Open(sFile_fp); try { Excel.Sheets wss = wb.Sheets; try { //Worksheet ws = wss[1]; Excel.Worksheet ws = wss["シート(No2)"]; //Excel.Worksheet ws = wss[1]; try { //Console.WriteLine(ws.Cells[1, 1].value.tostring()); //================================ // 最終行／最終列 //================================ int lastRow_0 = ws.Cells[ws.Rows.Count, 1].End[Excel.XlDirection.xlUp].Row; int lastCol_0 = ws.Cells[1, ws.Columns.Count].End[Excel.XlDirection.xlToLeft].Row; ws.Cells[1, 1].value = 5555; //=============================================== // 使用範囲を一括で二次元配列にコピー //=============================================== Excel.Range targetRange = null; Excel.Range firstFind = null; Excel.Range currentFind = null; try { //targetRange = ws.Columns[colName]; //シート内での検索範囲 //targetRange = ws.Columns[1]; //シート内での検索範囲 targetRange = ws.Cells; //シート内での検索範囲 currentFind = targetRange.Find( "s", //検索キー Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing ); while (currentFind != null) { if (firstFind == null) { firstFind = currentFind; } else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1)) { break; } currentFind = targetRange.FindNext(currentFind); Console.WriteLine(currentFind.Address); currentFind.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0); } } finally { if (currentFind != null) Marshal.ReleaseComObject(currentFind); if (firstFind != null) Marshal.ReleaseComObject(firstFind); Marshal.ReleaseComObject(targetRange); } } finally { Marshal.ReleaseComObject(ws); } } finally { Marshal.ReleaseComObject(wss); } //wb.SaveAs(@"D:\COM_TEST_2.xlsx"); } finally { //if (wb != null) //{ // wb.Close(false); //} Marshal.ReleaseComObject(wb); } } finally { Marshal.ReleaseComObject(wbs); } } finally { //if (app != null) //{ // app.Quit(); //} Marshal.ReleaseComObject(app); } } } }
Excelのバックグラウンドプロセスを直接殺す
【Interop.Excel】Excelプロセス絶対殺すコード
foreach (var p in Process.GetProcessesByName("EXCEL")) { if (p.MainWindowTitle == "") { p.Kill(); } }
