Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ClosedXMLTest
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClosedXML.Excel;
namespace ClosedXMLTest
{
class ExcelRead
{
// Excelファイルからインプットを読み込む
static public Dictionary<string, List<string>> Read(string filePath, string sheetName)
{
Dictionary<string, List<string>> inputs = new Dictionary<string, List<string>>();
var workbook = new XLWorkbook(filePath);
var worksheet = workbook.Worksheet(sheetName);
int inputStartRow = 2; // 入力の開始行
int inputNameCol = 1; // 各入力変数の名前を指定する列
int inputValueStartCol = 5; // 各入力変数の値が開始する列
// 名前が空白となるまで読み込む
int r = inputStartRow;
while (true)
{
string name = worksheet.Cell(r, inputNameCol).Value.ToString();
if (name == string.Empty) break;
// 値に空白があるまで読み込み、stringのリストとする
int c = inputValueStartCol;
List<string> values = new List<string>();
while (true)
{
string value = worksheet.Cell(r, c).Value.ToString();
if (value == string.Empty) break;
values.Add(value);
c++;
}
inputs.Add(name, values);
r++;
}
return inputs;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClosedXML.Excel;
namespace ClosedXMLTest
{
class ExcelWrite
{
// Excelファイルに結果を出力する
public static void Write(string filepath, string sheetName, List<Record<double>> records)
{
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add(sheetName);
int outputStartRow = 2; // 入力の開始行
int outputNameCol = 1; // 各入力変数の名前を指定する列
int outputValueStartCol = 5; // 各入力変数の値が開始する列
for (int i = 0; i < records.Count; i++)
{
Record<double> record = (Record<double>)records[i];
ws.Cell(i + outputStartRow, outputNameCol).Value = record.Name;
for (int e = 0; e < record.Values.Length; e++)
{
ws.Cell(i + outputStartRow, e + outputValueStartCol ).Value = record.Values[e];
}
}
ws.Column(outputNameCol).AdjustToContents();
workbook.SaveAs(filepath);
}
}
// 名前と値(配列)の組
public class Record<T>
{
public string Name;
public T[] Values;
public Record(string name, T[] values)
{
this.Name = name;
this.Values = values;
}
}
}
using System.Collections.Generic;
using System.Linq;
namespace ClosedXMLTest
{
class Program
{
static void Main(string[] args)
{
Dictionary<string, List<string>> inputs = ExcelRead.Read("Input.xlsm", "Input");
List<string> keys = inputs.Keys.ToList();
List<Record<double>> records = keys.Select(k => new Record<double>(k, inputs[k].Select(e => double.Parse(e)).ToArray())).ToList();
ExcelWrite.Write("Output.xlsx", "Output", records);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment