目前知識淺薄,只知道讀寫Excel 相關的有三種方式:
一、使用Oledb的方式
二、使用 Microsoft Excel XX.X Object Library 參考
三、NPOI (寫Web時使用)
關於第一種方式,我沒嘗試過。
這篇主要筆記第二種方式。
===== じゃあ、始めよう ======
不意外的,從一顆按鈕開始....
private void btnImport_Click(object sender, EventArgs e)
{
try
{
DialogResult ans = MessageBox.Show("確定匯入資料?", "訊息視窗",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (ans == DialogResult.OK)
{
ImportFile();
}
else
{
return;
}
}
catch (Exception ex)
{
string error = ex.ToString;
MessageBox.Show(error, "執行錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
再來是抓取檔案/匯入
protected void ImportFile()
{
OpenFileDialog dialog = new OpenFileDialog(); //建立檔案選擇視窗
dialog.Title = "Please your files";
dialog.InitialDirectory = ".\\";
dialog.Filter = "xls Files(*.xls; *.xlsx;)| *.xls; *.xlsx;";
//"xls Files(*.xls; *.xlsx;)| *.xls; *.xlsx; | All files(*.*) | *.*"
string msg = "";
string xlsfile = "";
if (dialog.ShowDialog() == DialogResult.OK)
{
xlsfile = dialog.FileName;
msg = "Are you sure import " + dialog.FileName + " ?";
DialogResult ans = MessageBox.Show(msg, "Check Message",
MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (ans == DialogResult.OK)
{
//1.DataTable
dt_cap1 = null;
//2.抓取Excel內容
DataTable dtXls = ExcelProcess(xlsfile);
dt_cap1 = ExcelProcess(xlsfile); //Excel內容抓取至datatable
if (dt_cap1.Rows.Count > 0)
{
//3.抓取後存至畫面元件
dataGridView1.DataSource = dt_cap1;
}
MessageBox.Show("Import Compelete!");
}
}
}
public DataTable ExcelProcess(string path)
{
DataTable dtExcel = new DataTable();
dtExcel.Columns.Add("item_number");
dtExcel.Columns.Add("item_value");
dtExcel.Columns.Add("special_value");
dtExcel.Columns.Add("order_value");
dtExcel.Columns.Add("change_value");
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int row = 12;
int col1 = 1; //A 欄位起始值
int col2 = 7; //G
progressBar1.Visible = true;
progressBar1.Minimum = 0;
//part1
for (int i = 1; i == col1; i++)
{
for (int j = 12; j >= row; j++)
{
if (xlRange.Cells[j, i].Value2 != null) //xlRange.Cells[j, i].Value != null!= null
{
DataRow dRow = dtExcel.NewRow();
dRow["item_number"] = (xlRange.Cells[j, i] as Excel.Range).Text;
dRow["item_value"] = "0";
dRow["special_value"] = "0";
dRow["order_value"] = "0";
dtExcel.Rows.Add(dRow);
}
else
{
break;
}
}
}
progressBar1.Maximum = dtExcel.Rows.Count;
//part2
for (int i = 7; i == col2; i++)
{
for (int j = 12; j >= row; j++)
{
if (xlRange.Cells[j, i].Value != null)
{
DataRow dRow = dtExcel.Rows[j - 12];
dRow["change_value"] = xlRange.Cells[j, i].Value;
if (progressBar1.Value < progressBar1.Maximum)
{ progressBar1.Value += 1; }
}
else { break; }
}
}
/*--------------------*/
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close(true);
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Marshal.FinalReleaseComObject(xlApp);
return dtExcel;
}
未盡事宜,想到再補充。