使用C#開excel檔後寫資料再另存新檔
string path = System.Windows.Forms.Application.StartupPath + "\\table.xlsm";
try
{
//應用程序
Excel.Application App1 = new Excel.Application();
//檔案
Excel.Workbook WB1 = App1.Workbooks.Open(path);
//工作表
Excel.Worksheet WS1 = new Excel.Worksheet();
WS1 = WB1.Worksheets[1];
WS1.Name = "手工 BOM";
//寫入表體資料
object[,] arrBody = new object[dataGridView1.RowCount, 12];
for (int i = 0; i < (dataGridView1.RowCount-1); i++)
{
arrBody[i, 0] = dataGridView1[0, i].Value.ToString();
arrBody[i, 1] = dataGridView1[1, i].Value.ToString();
arrBody[i, 2] = dataGridView1[2, i].Value.ToString();
arrBody[i, 3] = dataGridView1[3, i].Value.ToString();
arrBody[i, 4] = dataGridView1[4, i].Value.ToString();
arrBody[i, 5] = dataGridView1[5, i].Value.ToString();
arrBody[i, 6] = dataGridView1[6, i].Value.ToString();
arrBody[i, 7] = dataGridView1[7, i].Value.ToString();
arrBody[i, 8] = dataGridView1[8, i].Value.ToString();
arrBody[i, 9] = dataGridView1[9, i].Value.ToString();
arrBody[i, 10] = dataGridView1[10, i].Value.ToString();
arrBody[i, 11] = dataGridView1[11, i].Value.ToString();
btn_Save.Text = "Save BOM " + (dataGridView1.RowCount - i - 2).ToString();
System.Windows.Forms.Application.DoEvents();
}
WS1.Range[WS1.Cells[2,1], WS1.Cells[dataGridView1.RowCount, 12]].Value2 = arrBody;
btn_Save.Text = "Save BOM";
saveFileDialog1.Title = "另存新檔";
saveFileDialog1.Filter = "OrCAD BOM (*.xlsm)|*.xlsm|All files (*.*)|*.*";
saveFileDialog1.ShowDialog();
WB1.SaveAs(saveFileDialog1.FileName);
WS1 = null;
WB1.Close();
WB1 = null;
App1.Quit();
App1 = null;
try
{
ExcelMacroHelper excelMacroHelper = new ExcelMacroHelper();
// 返回對象
object objRtn = new object();
excelMacroHelper.RunExcelMacro(saveFileDialog1.FileName, "OrCAD_BOM_整理", new Object[] { "OrCAD_BOM_整理" }, out objRtn, true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
MessageBox.Show("存檔完成!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
btn_Convert_SAP.Enabled = true;