private void Importbutton_Click(object sender, EventArgs e)
{
Openfile = new OpenFileDialog()
{
Filter = "*.Xlsx| *.xlsx"
};
if (Openfile.ShowDialog() == DialogResult.OK)
{
try
{
ReportPath = Openfile.FileName;
if (ReportPath != null)
{
using (FileStream file = new FileStream($"{ReportPath}", FileMode.Open, FileAccess.Read))
{
xworkbook = new XSSFWorkbook(file); //Ecexl檔案載入
int sheet = xworkbook.NumberOfSheets; //取得分頁數量
for (int Sheetnum = 1; Sheetnum < 2; Sheetnum++)
{
var data = xworkbook.GetSheetAt(Sheetnum); //載入分頁資訊
for (int Rownum = 9; Rownum < data.LastRowNum; Rownum++) //每一行資料
{
IRow row = data.GetRow(Rownum);
if (row.GetCell(0).ToString() == "以下空白")
{
cell1.Add(row.GetCell(0));
break;
}
else
{
cell1.Add(row.GetCell(0));
}
if (row.GetCell(1).ToString() == "以下空白")
{
cell2.Add(row.GetCell(1));
break;
}
else
{
cell2.Add(row.GetCell(1));
}
if (row.GetCell(2).ToString() == "以下空白")
{
cell3.Add(row.GetCell(2));
break;
}
else
{
cell3.Add(row.GetCell(2));
}
if (row.GetCell(3).ToString() == "以下空白")
{
cell4.Add(row.GetCell(3));
break;
}
else
{
cell4.Add(row.GetCell(3));
}
if (row.GetCell(4).ToString() == "以下空白")
{
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
break;
}
else
{
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
}
if (row.GetCell(5).ToString() == "以下空白")
{
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
break;
}
else
{
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
}
if (row.GetCell(6).ToString() == "以下空白")
{
cell7.Add(row.GetCell(6));
break;
}
else
{
cell7.Add(row.GetCell(6));
}
if (row.GetCell(7).ToString() == "以下空白")
{
cell8.Add(row.GetCell(7));
break;
}
else
{
cell8.Add(row.GetCell(7));
}
}
}
}
}
}
catch (DirectoryNotFoundException ex) { Log.Error(ex, $" [{ReportPath}] KWH查無此資料夾路徑"); }
catch (FileNotFoundException ex) { Log.Error(ex, $"KWH查無此資料檔案"); }
catch (Exception ex) { Log.Error(ex, $"KWH資料匯入失敗 檔案名稱{FieldName}"); }
}
filenamelabel.Text = ReportPath.Split('.')[0].Split('\\')[ReportPath.Split('.')[0].Split('\\').Length - 1];
dataGridView1.ColumnCount = 8;
dataGridView1.Columns[0].Name = Convert.ToString(cell1[0]);
dataGridView1.Columns[1].Name = Convert.ToString(cell2[0]);
dataGridView1.Columns[2].Name = Convert.ToString(cell3[0]);
dataGridView1.Columns[3].Name = Convert.ToString(cell4[0]);
dataGridView1.Columns[4].Name = Convert.ToString(cell5[0]);
dataGridView1.Columns[5].Name = Convert.ToString(cell6[0]);
dataGridView1.Columns[6].Name = Convert.ToString(cell7[0]);
dataGridView1.Columns[7].Name = Convert.ToString(cell8[0]);
for (int i = 1; i < cell1.Count; i++)
{
dataGridView1.Rows.Add(cell1[i], cell2[i], cell3[i], cell4[i], cell5[i], cell6[i], cell7[i], cell8[i]);
}
}
private void OpenFilesimpleButton_Click(object sender, EventArgs e)
{
InsertSQLsimpleButton.Enabled = true;
if (gridControl1.DataSource != null)
{
gridView1.Columns.Clear();
}
#region excel資料匯入
Openfile = new OpenFileDialog()
{
Filter = "*.Xlsx| *.xlsx"
};
if (Openfile.ShowDialog() == DialogResult.OK)
{
try
{
ReportPath = Openfile.FileName;
if (ReportPath != null)
{
using (FileStream file = new FileStream($"{ReportPath}", FileMode.Open, FileAccess.Read))
{
xworkbook = new XSSFWorkbook(file); //Ecexl檔案載入
int sheet = xworkbook.NumberOfSheets; //取得分頁數量
for (int Sheetnum = 1; Sheetnum < 2; Sheetnum++)
{
var data = xworkbook.GetSheetAt(Sheetnum); //載入分頁資訊
for (int Rownum = 9; Rownum < data.LastRowNum; Rownum++) //每一行資料
{
IRow row = data.GetRow(Rownum);
#region 資料抓取
if (row.GetCell(0).ToString() == "" & row.GetCell(1).ToString() == "" & row.GetCell(2).ToString() == "" & row.GetCell(3).ToString() == "" & row.GetCell(4).ToString() == "" & row.GetCell(5).ToString() == "" & row.GetCell(6).ToString() == "" & row.GetCell(7).ToString() == "")
{
}
else
{
if (row.GetCell(7).ToString() == "以下空白")
{
cell1.Add(row.GetCell(0));
cell2.Add(row.GetCell(1));
cell3.Add(row.GetCell(2));
cell4.Add(row.GetCell(3));
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
cell7.Add(row.GetCell(6));
cell8.Add(row.GetCell(7));
break;
}
else
{
cell1.Add(row.GetCell(0));
cell2.Add(row.GetCell(1));
cell3.Add(row.GetCell(2));
cell4.Add(row.GetCell(3));
if (row.GetCell(4).CellType == CellType.Formula)
{
row.GetCell(4).SetCellType(CellType.String);
cell5.Add(row.GetCell(4));
}
else
{
cell5.Add(row.GetCell(4));
}
if (row.GetCell(5).CellType == CellType.Formula)
{
row.GetCell(5).SetCellType(CellType.String);
cell6.Add(row.GetCell(5));
}
else
{
cell6.Add(row.GetCell(5));
}
cell7.Add(row.GetCell(6));
cell8.Add(row.GetCell(7));
}
}
#endregion
}
}
}
}
}
catch (DirectoryNotFoundException ex) { Log.Error(ex, $" [{ReportPath}] 查無此資料夾路徑"); }
catch (FileNotFoundException ex) { Log.Error(ex, $"查無此資料檔案"); }
catch (Exception ex) { Log.Error(ex, $"資料匯入失敗 檔案名稱{FieldName}"); }
}
#endregion
#region GridView顯示匯入excel資料
labelControl2.Text = ReportPath.Split('.')[0].Split('\\')[ReportPath.Split('.')[0].Split('\\').Length - 1];
DataTable dataTable = new DataTable();
dataTable.Columns.Add(Convert.ToString(cell1[0]));
dataTable.Columns.Add(Convert.ToString(cell2[0]));
dataTable.Columns.Add(Convert.ToString(cell3[0]));
dataTable.Columns.Add(Convert.ToString(cell4[0]));
dataTable.Columns.Add(Convert.ToString(cell5[0]));
dataTable.Columns.Add(Convert.ToString(cell6[0]));
dataTable.Columns.Add(Convert.ToString(cell7[0]));
dataTable.Columns.Add(Convert.ToString(cell8[0]));
for (int i = 1; i < cell1.Count; i++)
{
dataTable.Rows.Add(cell1[i], cell2[i], cell3[i], cell4[i], cell5[i], cell6[i], cell7[i], cell8[i]);
}
gridControl1.DataSource = dataTable;
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.Columns[0].BestFit();
gridView1.Columns[1].BestFit();
gridView1.Columns[2].BestFit();
gridView1.Columns[3].BestFit();
gridView1.Columns[4].BestFit();
gridView1.Columns[5].BestFit();
gridView1.Columns[6].BestFit();
gridView1.Columns[7].BestFit();
for (int i = 0; i < gridView1.Columns.Count; i++)
{
gridView1.Columns[i].OptionsColumn.AllowEdit = false;
}
#endregion
}