private void BT_IMPORT_RESULT(object sender, RoutedEventArgs e) { gcollect.Clear(); List <string> duplicate = new List <string>(); string folderName = System.Environment.CurrentDirectory + @"\資料匯總"; try { foreach (var finame in System.IO.Directory.GetFileSystemEntries(folderName)) { if (System.IO.Path.GetExtension(finame) != ".xlsx") { continue; } using (var wb = new XLWorkbook()) { var ws = wb.Worksheet(1); if (!DateTime.TryParse(ws.Cell(1, 5).GetString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime time)) { continue; } if (time.Year != DateTime.Now.AddMonths(-1).Year || time.Month != DateTime.Now.AddMonths(-1).Month) { continue; } for (int i = 2; i < 500; i++) { if (string.IsNullOrEmpty(ws.Cell(i, 1).GetString())) { break; } if (string.IsNullOrEmpty(ws.Cell(i, 3).GetString())) { continue; } MElements data = new MElements { Element = ws.Cell(i, 3).GetString().Trim(), ElementData = ws.Cell(i, 5).GetString().Trim(), Eledate = time }; if (gcollect.Count > 0) { bool dup = false; foreach (var x in gcollect) { if (data.Element == x.Element) { duplicate.Add(x.Element); dup = true; break; } } if (!dup) { gcollect.Add(data); } } else { gcollect.Add(data); } if (gduplicate.ContainsKey(data.Element)) { var glists = gduplicate.Where(o => o.Key == data.Element).FirstOrDefault().Value; foreach (var x in glists) { if (gcollect.Find(o => o.Element == x.ToString()) == null && gdata.Find(o => o.MeasureID == x.ToString()) != null) { MElements dupdata = new MElements { Element = x.ToString(), ElementData = data.ElementData, Eledate = data.Eledate }; gcollect.Add(dupdata); } } } } wb.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } MessageBox.Show("匯入成功 : " + gcollect.Count()); if (duplicate.Count > 0) { TxtBox1.Text += Environment.NewLine + "重複資料清單 : " + string.Join(",", duplicate) + Environment.NewLine; } if (gcollect.Count > 0) { TxtBox1.Text += Environment.NewLine + string.Format("指標收回數量 : {0}/{1} ({2}%)", gcollect.Count, gdata.Count, gcollect.Count * 100 / gdata.Count) + Environment.NewLine; gcollect.Sort((x, y) => { return(x.Element.CompareTo(y.Element)); }); string fpath = Environment.CurrentDirectory + @"\要素備份"; if (!Directory.Exists(fpath)) { Directory.CreateDirectory(fpath); } string fname = @"\指標收集存檔(月份)" + DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + ".xlsx"; string fname2 = @"\指標收集存檔總檔.xlsx"; try { using (var wb = new XLWorkbook()) { var ws = wb.Worksheets.Add("工作表1"); ws.Style.Font.FontSize = 12; ws.Style.Font.FontName = "微軟正黑體"; ws.Columns(1, 2).Width = 15; ws.Cell(1, 1).Value = "指標要素"; ws.Cell(1, 2).Value = DateTime.Now.AddMonths(-1).ToString("yyyy/MM"); ws.Cell(1, 2).Style.DateFormat.Format = "yyyy/MM"; ws.Cell(1, 2).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Column(2).AdjustToContents(); for (int i = 0; i < gcollect.Count; i++) { ws.Cell(i + 2, 1).Value = gcollect[i].Element; ws.Cell(i + 2, 2).Value = gcollect[i].ElementData; } wb.SaveAs(fpath + fname); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } if (!System.IO.File.Exists(fpath + fname)) { return; } if (!System.IO.File.Exists(fpath + fname2)) { using (var wb = new XLWorkbook(fpath + fname)) { wb.SaveAs(fpath + fname2); } } else { try { using (var wb = new XLWorkbook(fpath + fname2)) { var ws = wb.Worksheet(1); if (!DateTime.TryParse(ws.Cell(1, 2).GetString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dts)) { wb.Dispose(); return; } if ((dts.Month == DateTime.Now.Month && dts.Year == DateTime.Now.Year) || dts >= DateTime.Now) { return; } if (dts.Month != DateTime.Now.AddMonths(-1).Month&& dts.Year != DateTime.Now.AddMonths(-1).Year) { ws.Column(1).InsertColumnsAfter(1); ws.Cell(1, 2).Value = DateTime.Now.AddMonths(-1).ToString("yyyy/MM"); } foreach (var x in gcollect) { int wsrows = ws.LastRowUsed().RowNumber(); var same = ws.RangeUsed().Rows(r => r.Cell(1).GetString() == x.Element).FirstOrDefault(); if (same != null) { same.Cell(2).Value = x.ElementData; } else { ws.Cell(wsrows + 1, 1).Value = x.Element; ws.Cell(wsrows + 1, 2).Value = x.ElementData; } /* * bool oldele = false; * for (int j = 0; j < 500; j++) * { * if (string.IsNullOrEmpty(ws.Cell(j + 2, 1).GetString())) * break; * if (ws.Cell(j + 2, 1).GetString() == x.Element) * { * ws.Cell(j + 2, 2).Value = x.ElementData; * oldele = true; * break; * } * } * if (!oldele) * { * ws.Cell(wsrows + 1, 1).Value = x.Element; * ws.Cell(wsrows + 1, 2).Value = x.ElementData; * } */ } wb.SaveAs(fpath + fname2); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }
private void LoadDataBASE() { string fpath = Environment.CurrentDirectory + @"\要素備份"; if (!Directory.Exists(fpath)) { Directory.CreateDirectory(fpath); } string fname = fpath + @"\指標收集存檔總檔.xlsx"; if (!System.IO.File.Exists(fname)) { return; } using (var wb = new XLWorkbook(fname)) { var ws = wb.Worksheet(1); for (int i = 0; i < 500; i++) { if (string.IsNullOrEmpty(ws.Cell(i + 2, 1).GetString())) { break; } //if (string.IsNullOrEmpty(sl.GetCellValueAsString(i + 2, 2))) // continue; if (gbackups.Count > 0 && gbackups.ContainsKey(ws.Cell(i + 2, 1).GetString())) { continue; } List <MElements> lme = new List <MElements>(); List <string> duplicate = new List <string>(); for (int j = 0; j < 12; j++) { if (string.IsNullOrEmpty(ws.Cell(1, j + 2).GetString())) { break; } if (string.IsNullOrEmpty(ws.Cell(2, j + 2).GetString())) { continue; } if (!DateTime.TryParse(ws.Cell(1, j + 2).GetString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dts)) { continue; } if (dts > DateTime.Now.AddMonths(-1 - 12) && dts < DateTime.Now) { if (duplicate.Contains(dts.ToString("yyyy/MM"))) { continue; } else { duplicate.Add(dts.ToString("yyyy/MM")); } MElements data = new MElements { Element = ws.Cell(i + 2, 1).GetString().Trim(), ElementData = ws.Cell(i + 2, j + 2).GetString().Trim(), Eledate = dts }; lme.Add(data); } } gbackups[ws.Cell(i + 2, 1).GetString().Trim()] = lme; try { if (gduplicate.ContainsKey(ws.Cell(i + 2, 1).GetString().Trim())) { var glists = gduplicate.Where(o => o.Key == ws.Cell(i + 2, 1).GetString().Trim()).FirstOrDefault().Value; foreach (var x in glists) { if (!gbackups.ContainsKey(x) && gdata.Find(o => o.MeasureID == x) != null) { gbackups[x] = lme; } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } } wb.Dispose(); } }