C# Excel操作② – テックメモ

この書き方だと落ちた時にプロセスが残る。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace WindowsFormsApp1
{
    class create_excel
    {
        public void create_order(DataGridView dgv, setting setting)
        {
            var ex = new Microsoft.Office.Interop.Excel.Application();
            ex.Visible = true;
            ex.DisplayAlerts = false;

            var wbs = ex.Workbooks;
            var wb = wbs.Open(System.Windows.Forms.Application.StartupPath + @"\po.xlsx");
            var wss = wb.Sheets;
            var ws = wss["po"];

            var _wbs = ex.Workbooks;
            var _wb = _wbs.Add();
            var _wss = _wb.Sheets;
            var _ws = _wss[1];

            ws.copy(_ws);
            wb.Close();

            var order = _wss["po"];

            var selected_rows_index = dgv.SelectedCells.Cast<DataGridViewCell>().Select(x => x.RowIndex).Distinct();

            int counter = 18;
            foreach (int selected_row_index in selected_rows_index)
            {
                if (counter == 29)
                {
                    MessageBox.Show("選択行数が多いため一部表示されていません。");
                    break;
                }

                if (dgv.Rows[selected_row_index].Cells[setting.id_header].Value != null)
                {
                    var rc = order.Cells[4, 1];
                    if (dgv.Rows[selected_row_index].Cells["仕入先"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["仕入先"].Value.ToString() + "　御中";
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;

                    rc = order.Cells[counter, 1];
                    if(dgv.Rows[selected_row_index].Cells["品名"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["品名"].Value.ToString();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;

                    rc = order.Cells[counter, 2];
                    if(dgv.Rows[selected_row_index].Cells["発注数量"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["発注数量"].Value.ToString();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;

                    rc = order.Cells[counter, 3];
                    if(dgv.Rows[selected_row_index].Cells["納期"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["納期"].Value.ToString();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;

                    rc = order.Cells[counter, 4];
                    if(dgv.Rows[selected_row_index].Cells["型式"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["型式"].Value.ToString();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;

                    counter++;
                }
            }

            Marshal.ReleaseComObject(order);
            order = null;

            Marshal.ReleaseComObject(_ws);
            _ws = null;
            Marshal.ReleaseComObject(_wss);
            _wss = null;
            Marshal.ReleaseComObject(_wb);
            _wb = null;
            Marshal.ReleaseComObject(_wbs);
            _wbs = null;

            Marshal.ReleaseComObject(ws);
            ws = null;
            Marshal.ReleaseComObject(wss);
            wss = null;
            Marshal.ReleaseComObject(wb);
            wb = null;
            Marshal.ReleaseComObject(wbs);
            wbs = null;

            Marshal.ReleaseComObject(ex);
            ex = null;
        }

        public void dgv_copy(DataGridView dgv)
        {
            var ex = new Microsoft.Office.Interop.Excel.Application();
            ex.Visible = true;
            ex.DisplayAlerts = false;

            var wbs = ex.Workbooks;
            var wb = wbs.Add();
            var wss = wb.Sheets;
            var ws = wss[1];
            
            for (int r = 0; r < dgv.Rows.Count; r++)
            {
                for (int c = 0; c < dgv.Columns.Count; c++)
                {
                    var rc = ws.Cells[r + 1, c + 1];
                    if (dgv.Rows[r].Cells[c].Value != null) rc.Value = dgv.Rows[r].Cells[c].Value.ToString();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);
                    rc = null;
                }
            }

            Marshal.ReleaseComObject(ws);
            ws = null;
            Marshal.ReleaseComObject(wss);
            wss = null;
            Marshal.ReleaseComObject(wb);
            wb = null;
            Marshal.ReleaseComObject(wbs);
            wbs = null;

            Marshal.ReleaseComObject(ex);
            ex = null;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

namespace WindowsFormsApp1

{

class create_excel

{

public void create_order(DataGridView dgv, setting setting)

{

var ex = new Microsoft.Office.Interop.Excel.Application();

ex.Visible = true;

ex.DisplayAlerts = false;

var wbs = ex.Workbooks;

var wb = wbs.Open(System.Windows.Forms.Application.StartupPath + @"\po.xlsx");

var wss = wb.Sheets;

var ws = wss["po"];

var _wbs = ex.Workbooks;

var _wb = _wbs.Add();

var _wss = _wb.Sheets;

var _ws = _wss[1];

ws.copy(_ws);

wb.Close();

var order = _wss["po"];

var selected_rows_index = dgv.SelectedCells.Cast<DataGridViewCell>().Select(x => x.RowIndex).Distinct();

int counter = 18;

foreach (int selected_row_index in selected_rows_index)

{

if (counter == 29)

{

MessageBox.Show("選択行数が多いため一部表示されていません。");

break;

}

if (dgv.Rows[selected_row_index].Cells[setting.id_header].Value != null)

{

var rc = order.Cells[4, 1];

if (dgv.Rows[selected_row_index].Cells["仕入先"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["仕入先"].Value.ToString() + "　御中";

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

rc = order.Cells[counter, 1];

if(dgv.Rows[selected_row_index].Cells["品名"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["品名"].Value.ToString();

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

rc = order.Cells[counter, 2];

if(dgv.Rows[selected_row_index].Cells["発注数量"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["発注数量"].Value.ToString();

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

rc = order.Cells[counter, 3];

if(dgv.Rows[selected_row_index].Cells["納期"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["納期"].Value.ToString();

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

rc = order.Cells[counter, 4];

if(dgv.Rows[selected_row_index].Cells["型式"].Value != null) rc.value = dgv.Rows[selected_row_index].Cells["型式"].Value.ToString();

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

counter++;

}

Marshal.ReleaseComObject(order);

order = null;

Marshal.ReleaseComObject(_ws);

_ws = null;

Marshal.ReleaseComObject(_wss);

_wss = null;

Marshal.ReleaseComObject(_wb);

_wb = null;

Marshal.ReleaseComObject(_wbs);

_wbs = null;

Marshal.ReleaseComObject(ws);

ws = null;

Marshal.ReleaseComObject(wss);

wss = null;

Marshal.ReleaseComObject(wb);

wb = null;

Marshal.ReleaseComObject(wbs);

wbs = null;

Marshal.ReleaseComObject(ex);

ex = null;

}

public void dgv_copy(DataGridView dgv)

{

var ex = new Microsoft.Office.Interop.Excel.Application();

ex.Visible = true;

ex.DisplayAlerts = false;

var wbs = ex.Workbooks;

var wb = wbs.Add();

var wss = wb.Sheets;

var ws = wss[1];

for (int r = 0; r < dgv.Rows.Count; r++)

{

for (int c = 0; c < dgv.Columns.Count; c++)

{

var rc = ws.Cells[r + 1, c + 1];

if (dgv.Rows[r].Cells[c].Value != null) rc.Value = dgv.Rows[r].Cells[c].Value.ToString();

System.Runtime.InteropServices.Marshal.ReleaseComObject(rc);

rc = null;

}

Marshal.ReleaseComObject(ws);

ws = null;

Marshal.ReleaseComObject(wss);

wss = null;

Marshal.ReleaseComObject(wb);

wb = null;

Marshal.ReleaseComObject(wbs);

wbs = null;

Marshal.ReleaseComObject(ex);

ex = null;

}