public static void ClosedXmlReader(string inFile, string outFile)
{
var workbook = new ClosedXML.Excel.XLWorkbook(inFile);
ClosedXML.Excel.IXLWorksheet sheet = workbook.Worksheets.Worksheet(1);
var sb = new StringBuilder();
var rows = sheet.LastRowUsed().RangeAddress.FirstAddress.RowNumber;
var columns = sheet.LastColumnUsed().RangeAddress.FirstAddress.ColumnNumber;
foreach (var row in sheet.Rows())
{
foreach (var cell in row.Cells())
{
string s = cell.GetString();
if (s.Contains('\n'))
{
sb.Append("\"" + s + "\"\t");
}
else
{
sb.Append(s + "\t");
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append("\n");
}
File.WriteAllText(outFile, sb.ToString());
}
/// <summary>
/// ファイルの読み込み処理
/// </summary>
/// <param name="path"></param>
private List <Interface.ImportData> ReadData(string path)
{
var result = new List <Interface.ImportData>();
int rowNo = 1;
using (var workbook = new ClosedXML.Excel.XLWorkbook(path))
{
ClosedXML.Excel.IXLWorksheet sheet = workbook.Worksheet("Sheet1");
// 読み込み処理
Boolean headerflag = true;
foreach (var r in sheet.Rows())
{
// カウントアップ
rowNo++;
// 1行目はヘッダなので読み飛ばす
if (headerflag)
{
headerflag = false;
continue;
}
// 最終行までいったら終わる
if (r == sheet.LastRowUsed())
{
break;
}
// 登録項目がすべて未入力の場合は読み飛ばす
if (string.IsNullOrEmpty(r.Cell(COL_WORKDATE).Value.ToString()) && string.IsNullOrEmpty(r.Cell(COL_STARTTIME).Value.ToString()) &&
string.IsNullOrEmpty(r.Cell(COL_ENDTIME).Value.ToString()) && string.IsNullOrEmpty(r.Cell(COL_STAFFID).CachedValue.ToString()) &&
string.IsNullOrEmpty(r.Cell(COL_WORKNAIYOU).Value.ToString()) && string.IsNullOrEmpty(r.Cell(COL_PROJECTID).CachedValue.ToString()) &&
string.IsNullOrEmpty(r.Cell(COL_WORKID).CachedValue.ToString()))
{
continue;
}
//登録項目のいずれかが未入力の場合はエラー
if (string.IsNullOrEmpty(r.Cell(COL_WORKDATE).Value.ToString()) || string.IsNullOrEmpty(r.Cell(COL_STARTTIME).Value.ToString()) ||
string.IsNullOrEmpty(r.Cell(COL_ENDTIME).Value.ToString()) || string.IsNullOrEmpty(r.Cell(COL_STAFFID).CachedValue.ToString()) ||
string.IsNullOrEmpty(r.Cell(COL_WORKNAIYOU).Value.ToString()) || string.IsNullOrEmpty(r.Cell(COL_PROJECTID).CachedValue.ToString()) ||
string.IsNullOrEmpty(r.Cell(COL_WORKID).CachedValue.ToString()))
{
MessageBox.Show(rowNo.ToString() + "未入力の項目があります。");
return(null);
}
DateTime workDate = (DateTime)r.Cell(COL_WORKDATE).Value;
DateTime startTime = (DateTime)r.Cell(COL_STARTTIME).Value;
DateTime editedStartTime = new DateTime(workDate.Year, workDate.Month, workDate.Day, startTime.Hour, startTime.Minute, startTime.Second);
DateTime endTime = (DateTime)r.Cell(COL_ENDTIME).Value;
DateTime editedEndTime = new DateTime(workDate.Year, workDate.Month, workDate.Day, endTime.Hour, endTime.Minute, endTime.Second);
result.Add(new Interface.ImportData
{
StaffID = long.Parse(r.Cell(COL_STAFFID).CachedValue.ToString()),
ProjectID = long.Parse(r.Cell(COL_PROJECTID).CachedValue.ToString()),
WorkID = long.Parse(r.Cell(COL_WORKID).CachedValue.ToString()),
WorkDate = workDate,
StartTime = editedStartTime,
EndTime = editedEndTime,
WorkNaiyou = r.Cell(COL_WORKNAIYOU).Value.ToString()
});
}
}
return(result);
}