前職の先輩から依頼をもらって、
業務改善のためのツールを作りました。
C#からエクセルを操作してます。
システムからダウンロードしてきたエクセルファイルにシートが1枚あって、
その中のいろんな項目を日付ごとに集計したい、
というよくある要件でした。
読み込むファイルを指定する
↓
保存するフォルダを指定する
↓
出力ファイルを作成する
という操作で完成します。
出力ファイルは
全体の集計シートと、
日付ごとの詳細をまとめたシートを作っています。
普段はフリーランスとして働いていますが、
プログラミングの勉強をしはじめてやっと1年経ったぐらいで、
客先常駐なのであまりソースコードを公表する機会はありませんでした。
ただ、やっぱり客観的な目を意識しないと成長しないと思い、
今回思い切ってやってみました。
ついでにGitHubにもプッシュしました。
(これもはじめて)
▼GitHub
https://github.com/goalmaster/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);
}
}
}
まずは「コードを載せる」というハードルをクリアしたので、
なぜこの機能が必要なのか?
これは何をしているのか?
ということはおいおい追記していきまっす。
よかったらフィードバックいただけると泣いて喜びます。