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); }