LoginSignup
2
3

More than 5 years have passed since last update.

@ryota_biz

【C#】ボタンひとつでエクセル内の集計ができるツールをつくったので初めてソースコードをさらけ出します

Last updated at Posted at 2019-04-16

前職の先輩から依頼をもらって、
業務改善のためのツールを作りました。
C#からエクセルを操作してます。

システムからダウンロードしてきたエクセルファイルにシートが1枚あって、
その中のいろんな項目を日付ごとに集計したい、
というよくある要件でした。

読み込むファイルを指定する
↓
保存するフォルダを指定する
↓
出力ファイルを作成する

という操作で完成します。

出力ファイルは
全体の集計シートと、
日付ごとの詳細をまとめたシートを作っています。

普段はフリーランスとして働いていますが、
プログラミングの勉強をしはじめてやっと1年経ったぐらいで、
客先常駐なのであまりソースコードを公表する機会はありませんでした。

ただ、やっぱり客観的な目を意識しないと成長しないと思い、
今回思い切ってやってみました。

ついでにGitHubにもプッシュしました。
(これもはじめて)

▼GitHub
https://github.com/goalmaster/MonthlyAggregationTool

自分のソースコードをさらすのは勇気がいりますが
それをやらないと成長もないだろうな、
と思うので以下に記載します！

MonthlyAggregationTool
using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections;
using System.IO;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Reflection;

namespace MonthlyAggregationTool
{
    public partial class Form1 : Form
    {
        int last_row;
        int last_column;
        object[,] target_cells;
        ArrayList term_list = new ArrayList();
        object[,] detail_list;
        object[,] title_list;

        public Form1()
        {
            InitializeComponent();
        }

        private void buttonSelect_Click(object sender, EventArgs e)
        {
            OpenFileDialog open_file_dialog = new OpenFileDialog();
                           open_file_dialog.RestoreDirectory = true;
                           open_file_dialog.Filter = "エクセル|*.xlsx";

            var show = open_file_dialog.ShowDialog();

            if (show == DialogResult.OK)
            {
                txtPath.Text = open_file_dialog.FileName;
                txtDirectory.Text = Path.GetDirectoryName(txtPath.Text);
            }
        }

        private void buttonClear_Click(object sender, EventArgs e)
        {
            txtPath.Text = "";
        }

        private void buttonSelectDirectory_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog folder_browser_dialog = new FolderBrowserDialog();
            folder_browser_dialog.ShowNewFolderButton = false;

            var show_dialog = folder_browser_dialog.ShowDialog();

            if(show_dialog == DialogResult.OK)
            {
                txtDirectory.Text = folder_browser_dialog.SelectedPath;
            }
        }

        private void buttonClearDirectory_Click(object sender, EventArgs e)
        {
            txtDirectory.Text = "";
        }

        private void buttonMake_Click(object sender, EventArgs e)
        {
            Cursor pre_cursor = Cursor.Current;
            Cursor.Current    = Cursors.WaitCursor;

            // エクセルの値をリスト化
            makeTargetCells();


            // 各列を指定
            int term_col         = 4;  // 勤務期間
            int every_day        = 6;  // 毎日交通費
            int every_month      = 7;  // 毎月交通費
            int each_three_month = 8;  // 3ヶ月毎交通費
            int each_half_year   = 9;  // 半年交通費
            int shikyu_col       = 46; // 支給課税分給与額C（A-B）
            int syakai_col       = 53; // 社会保険計F
            int syotoku_col      = 55; // 所得税
            int shityouson_col   = 56; // 市町村民税
            int sashihiki_col    = 58; // 差引支給額（G-I+B）

            // 年月日のリスト作成
            makeTermList(term_col);


            int save_last_row    = term_list.Count + 3; // タイトル行と合計行分を足す
            int save_last_column = 11;
            object[,] values     = new object[save_last_row, save_last_column];

            values[0,  0] = "年月日";
            values[0,  1] = "人数";
            values[0,  2] = "毎日交通費";
            values[0,  3] = "毎月交通費";
            values[0,  4] = "3ヶ月毎交通費";
            values[0,  5] = "半年交通費";
            values[0,  6] = "支給課税分給与額C（A-B）";
            values[0,  7] = "社会保険計F";
            values[0,  8] = "所得税";
            values[0,  9] = "市町村民税";
            values[0, 10] = "差引支給額（G-I+B）";

            for (int tl_i = 0; tl_i < term_list.Count; tl_i++)
            {
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, every_day);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, every_month);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, each_three_month);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, each_half_year);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, shikyu_col);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, syakai_col);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, syotoku_col);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, shityouson_col);
                makeSum(last_row, target_cells, term_col, term_list, tl_i, values, sashihiki_col);
            }

            // string save_directory = Path.GetDirectoryName(txtDirectory.Text);
            string save_directory = Path.GetFullPath(txtDirectory.Text);

            var save_xapp  = new Excel.Application();
            var save_wb    = save_xapp.Workbooks.Add();
            var save_sheet = save_wb.Worksheets[1];

            save_wb.Sheets.Add(Missing.Value, save_sheet, term_list.Count, Missing.Value);
            //                 before       , after     , count          , type


            // sheet1:合計
            int start_row = 2;
            int start_col = 1;

            Excel.Range save_cells      = save_sheet.Cells;
            Excel.Range save_start_cell = save_cells[start_row, start_col];
            Excel.Range save_end_cell   = save_cells[start_row + term_list.Count + 2, save_last_column];
            Excel.Range save_target     = save_sheet.Range[save_start_cell, save_end_cell];

            save_target.Value = values;

            // 書式変更する範囲を指定
            Excel.Range area = save_sheet.Range[save_cells[start_row, 2], save_end_cell];

            // 書式変更
            area.NumberFormatLocal = "#,0";
            /////////////////////////////////////////////////


            // sheet2以降:日付ごとの詳細
            // 年月日ごとの詳細リスト作成
            int row_count = 0;

            // 年月日ごと
            for (int i = 0; i < term_list.Count; i++)
            {
                // valuesからcountを抜き出す(106, 111, 126, 2)
                row_count = int.Parse(values[i + 1, 1].ToString());

                // row_countをもとにリスト作成
                // [0, 0] ～ [row_count - 1, last_column - 1]
                detail_list = new object[row_count, last_column];

                int detail_index = 0;

                for (int j = 1; j < last_row; j++)
                {
                    string date_str = target_cells[j, 4].ToString();

                    if (date_str.Contains(term_list[i].ToString()))
                    {
                        for (int col = 0; col < last_column; col++)
                        {
                            detail_list[detail_index, col] = target_cells[j, col + 1];
                        }

                        detail_index++;

                    }
                }

                // sheetに貼り付け
                var detail_sheet = save_wb.Worksheets[i + 2];

                Excel.Range detail_cells      = detail_sheet.Cells;
                Excel.Range detail_start_cell = detail_cells[start_row, start_col];
                Excel.Range detail_end_cell   = detail_cells[row_count + 1, last_column];
                Excel.Range detail_target     = detail_sheet.Range[detail_start_cell, detail_end_cell];

                Excel.Range title_start = detail_cells[1, 1];
                Excel.Range title_end   = detail_cells[1, last_column];
                Excel.Range title       = detail_sheet.Range[title_start, title_end];

                detail_target.Value = detail_list;
                title.Value = title_list;

                // sheet名の編集
                string sheet_name = term_list[i].ToString().Substring(5,2) + term_list[i].ToString().Substring(8,2);
                detail_sheet.Name = sheet_name;

                Marshal.ReleaseComObject(title);
                Marshal.ReleaseComObject(title_end);
                Marshal.ReleaseComObject(title_start);

                Marshal.ReleaseComObject(detail_target);
                Marshal.ReleaseComObject(detail_end_cell);
                Marshal.ReleaseComObject(detail_start_cell);
                Marshal.ReleaseComObject(detail_cells);
                Marshal.ReleaseComObject(detail_sheet);
            }

            save_sheet.Name = "集計";
            save_sheet.Activate();

            save_wb.SaveAs(save_directory + @"\出力ファイル.xlsx");

            Marshal.ReleaseComObject(save_target);
            Marshal.ReleaseComObject(save_end_cell);
            Marshal.ReleaseComObject(save_start_cell);
            Marshal.ReleaseComObject(save_cells);
            Marshal.ReleaseComObject(save_sheet);

            save_wb.Close();
            Marshal.ReleaseComObject(save_wb);
            save_xapp.Quit();
            Marshal.ReleaseComObject(save_xapp);

            Cursor.Current = pre_cursor;

            MessageBox.Show("完了しました");

        }

        public void makeSum(int last_row, object[,] target_cells, int term_col,
                            ArrayList term_list, int tl_i, object[,] values, int column)
        {
            int sum   = 0;
            int count = 0;

            for (int id_i = 1; id_i < last_row; id_i++)
            {
                if (target_cells[id_i, term_col].ToString().Contains(term_list[tl_i].ToString()))
                {
                    if(target_cells[id_i, column] != null)
                    {
                        sum = sum + int.Parse(target_cells[id_i, column].ToString());
                    }

                    count++;
                }
            }

            values[tl_i + 1, 0] = term_list[tl_i].ToString();
            values[tl_i + 1, 1] = count;

            switch (column.ToString())
            {
                case "6":
                    values[tl_i + 1,  2] = sum;
                    break;

                case "7":
                    values[tl_i + 1,  3] = sum;
                    break;

                case "8":
                    values[tl_i + 1,  4] = sum;
                    break;

                case "9":
                    values[tl_i + 1,  5] = sum;
                    break;

                case "46":
                    values[tl_i + 1,  6] = sum;
                    break;

                case "53":
                    values[tl_i + 1,  7] = sum;
                    break;

                case "55":
                    values[tl_i + 1,  8] = sum;
                    break;

                case "56":
                    values[tl_i + 1,  9] = sum;
                    break;

                case "58":
                    values[tl_i + 1, 10] = sum;
                    break;
            }
        }

        public void makeTermList(int term_col)
        {
            for (int id_i = 1; id_i < last_row; id_i++)
            {
                string term_str = target_cells[id_i, term_col].ToString().Substring(12, 10);

                if (term_list.Contains(term_str) == false)
                {
                    term_list.Add(term_str);
                }
            }
        }

        public void makeTargetCells()
        {
            var xapp  = new Excel.Application();
            var wb    = xapp.Workbooks.Open(txtPath.Text);
            var sheet = wb.Worksheets[1];

            last_row    = sheet.UsedRange.Rows.Count - 1; // 最後の合計行を反映させない
            last_column = sheet.UsedRange.Columns.Count;

            Excel.Range cells      = sheet.Cells;
            Excel.Range start_cell = cells[2, 1]; // タイトルは含めない
            Excel.Range end_cell   = cells[last_row, last_column];
            Excel.Range target     = sheet.Range[start_cell, end_cell];

            // [1, 1] ～ [last_row - 1, last_column]
            target_cells = new object[last_row - 1, last_column];
            target_cells = target.Value2;

            Excel.Range title_start_cell = cells[1, 1];
            Excel.Range title_end_cell   = cells[1, last_column];
            Excel.Range title_target     = sheet.Range[title_start_cell, title_end_cell];

            title_list = title_target.Value2;

            Marshal.ReleaseComObject(title_target);
            Marshal.ReleaseComObject(title_end_cell);
            Marshal.ReleaseComObject(title_start_cell);

            Marshal.ReleaseComObject(target);
            Marshal.ReleaseComObject(end_cell);
            Marshal.ReleaseComObject(start_cell);
            Marshal.ReleaseComObject(cells);
            Marshal.ReleaseComObject(sheet);
            wb.Close();
            Marshal.ReleaseComObject(wb);
            xapp.Quit();
            Marshal.ReleaseComObject(xapp);
        }
    }
}

まずは「コードを載せる」というハードルをクリアしたので、
なぜこの機能が必要なのか？
これは何をしているのか？
ということはおいおい追記していきまっす。

よかったらフィードバックいただけると泣いて喜びます。

2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3