//匯入 學生排序資料
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("匯入完成，請記得儲存設定。");
}
