//匯入 學生排序資料 private void ImportSeqBtn_Click(object sender, EventArgs e) { OpenFileDialog od = new OpenFileDialog(); od.Filter = "Excel檔案(*.xlsx)|*.xlsx|Excel檔案(*.xls)|*.xls"; if (od.ShowDialog() != DialogResult.OK) { return; } Workbook book = new Workbook(od.FileName); //book.Open(od.FileName); Worksheet ws = book.Worksheets[0]; int index = 0; Dictionary <string, int> colmap = new Dictionary <string, int>(); Dictionary <int, int> map = new Dictionary <int, int>(); // 驗證欄位 List <string> fields = new List <string>(); fields.Add("學號"); fields.Add("分班排序"); Dictionary <string, IColumnValidator> validators = new Dictionary <string, IColumnValidator>(); #region 檢查標題是否正確 for (int i = 0; i <= ws.Cells.MaxDataColumn; i++) { Cell cell = ws.Cells[index, i]; string value = "" + cell.Value; int dgvColIndex; if (fields.Contains(value) && TryGetColumnIndex(dataGridViewX1, value, out dgvColIndex)) { colmap.Add(value, i); map.Add(i, dgvColIndex); fields.Remove(value); } } if (fields.Count > 0) { StringBuilder builder = new StringBuilder(""); builder.AppendLine("匯入資料有誤。"); builder.Append("缺少欄位:"); foreach (var f in fields) { builder.Append(f + "、"); } string msg = builder.ToString(); if (msg.EndsWith("、")) { msg = msg.Substring(0, msg.Length - 1); } MessageBox.Show(msg); return; } #endregion #region 檢查欄位是否有效 bool valid = true; foreach (string header in validators.Keys) { if (!colmap.ContainsKey(header)) { continue; } int colIndex = colmap[header]; IColumnValidator validator = validators[header]; for (int i = 1; i <= ws.Cells.MaxDataRow; i++) { Cell cell = ws.Cells[i, colIndex]; if (!validator.IsValid("" + cell.Value)) { valid &= false; } } } if (!valid) { MsgBox.Show("資料有誤。"); return; } #endregion #region 填入 DataGridView if (MsgBox.Show("匯入動作會將會新增排序資料,請問是否要繼續?", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } dataGridViewX1.SuspendLayout(); //dataGridViewX1.Rows.Clear(); Dictionary <string, string> stuNumberSeqDict = new Dictionary <string, string>(); for (int i = 1; i <= ws.Cells.MaxDataRow; i++) { // 舊寫法,全面置換 UI 介面 //DataGridViewRow row = new DataGridViewRow(); //row.CreateCells(dataGridViewX1); //foreach (int colIndex in map.Keys) //{ // Cell cell = ws.Cells[i, colIndex]; // row.Cells[map[colIndex]].Value = "" + cell.Value; //} //dataGridViewX1.Rows.Add(row); if (!stuNumberSeqDict.ContainsKey("" + ws.Cells[i, 0].Value)) { stuNumberSeqDict.Add("" + ws.Cells[i, colmap["學號"]].Value, "" + ws.Cells[i, colmap["分班排序"]].Value); } } foreach (DataGridViewRow row in dataGridViewX1.Rows) { row.Cells[6].Value = stuNumberSeqDict.ContainsKey("" + row.Cells[0].Value) ? stuNumberSeqDict["" + row.Cells[0].Value] : ""; } dataGridViewX1.ResumeLayout(); #endregion MsgBox.Show("匯入完成,可以點擊排序後重新指定分班。"); }
internal static void Import(DataGridView dgv, List <string> fields, Dictionary <string, IColumnValidator> validators) { OpenFileDialog od = new OpenFileDialog(); od.Filter = "Excel檔案(*.xls)|*.xls"; if (od.ShowDialog() != DialogResult.OK) { return; } Workbook book = new Workbook(); book.Open(od.FileName); Worksheet ws = book.Worksheets[0]; int index = 0; Dictionary <string, int> colmap = new Dictionary <string, int>(); Dictionary <int, int> map = new Dictionary <int, int>(); #region 檢查標題是否正確 for (int i = 0; i <= ws.Cells.MaxDataColumn; i++) { Cell cell = ws.Cells[index, i]; string value = "" + cell.Value; int dgvColIndex; if (fields.Contains(value) && TryGetColumnIndex(dgv, value, out dgvColIndex)) { colmap.Add(value, i); map.Add(i, dgvColIndex); fields.Remove(value); } } if (fields.Count > 0) { StringBuilder builder = new StringBuilder(""); builder.AppendLine("匯入資料有誤。"); builder.Append("缺少欄位:"); foreach (var f in fields) { builder.Append(f + "、"); } string msg = builder.ToString(); if (msg.EndsWith("、")) { msg = msg.Substring(0, msg.Length - 1); } MsgBox.Show(msg); return; } #endregion #region 檢查欄位是否有效 bool valid = true; foreach (string header in validators.Keys) { if (!colmap.ContainsKey(header)) { continue; } int colIndex = colmap[header]; IColumnValidator validator = validators[header]; for (int i = 1; i <= ws.Cells.MaxDataRow; i++) { Cell cell = ws.Cells[i, colIndex]; if (!validator.IsValid("" + cell.Value)) { //ws.Comments[cell.Name].Note = validator.GetErrorMessage(); valid &= false; } } } if (!valid) { MsgBox.Show("資料有誤。"); return; } #endregion #region 填入 DataGridView if (MsgBox.Show("匯入動作會將覆蓋目前畫面上的設定,請問是否要繼續?", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } dgv.SuspendLayout(); dgv.Rows.Clear(); for (int i = 1; i <= ws.Cells.MaxDataRow; i++) { DataGridViewRow row = new DataGridViewRow(); row.CreateCells(dgv); foreach (int colIndex in map.Keys) { Cell cell = ws.Cells[i, colIndex]; row.Cells[map[colIndex]].Value = "" + cell.Value; } dgv.Rows.Add(row); } dgv.ResumeLayout(); #endregion MsgBox.Show("匯入完成,請記得儲存設定。"); }