英文:
Saving workbook, How to not deal with popups?
问题
我正在尝试更新Excel文件中的数据。我可以打开一个工作簿并添加我的新数据。当我关闭工作簿时,它会触发一个线程,在所有其他页面下方弹出一个询问是否保存页面的窗口。这导致另一个弹出窗口询问新文件的名称。
我需要的是打开文件,更改数据,然后无需人工干预即关闭/更新文件。
以下是我的当前代码:
public static int Make_new_row(string part_number, string part_name,
string control_id, string serial_number, string test_operator)
{
string file_name = "K:\\Test_Data\\Log_File\\Test_Log.xlsx";
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
int loop;
for (loop = 0; loop < 10; loop++)
{
try
{
//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file_name);
xlWorkBook = xlApp.Workbooks.Open(file_name, 0, true, 5, "", "",
true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
break;
}
catch (Exception) // 文件正在使用中。等待文件可用性
{
Thread.Sleep(1000);
continue;
}
}
if (loop == 10)
{
string message = "无法连接到文件。\r排查问题后请重试。";
string title = "连接错误";
MessageBox.Show(message, title);
return -1;
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
// 创建新行
xlWorkSheet.Rows[rw].Insert();
// 获取最后的跟踪号并生成新号码
string last_traxcking_raw = Convert.ToString((xlWorkSheet.Cells[rw - 1, 1]).Value);
string last_traxcking = last_traxcking_raw.Remove(0, 3);
Data_File.last_test_file = last_traxcking;
int t_number = Int32.Parse(last_traxcking);
string new_tracking = ("TS-" + (t_number + 1));
// 用数据填充新行
xlWorkSheet.Cells[rw, 1] = new_tracking;
xlWorkSheet.Cells[rw, 2] = part_number;
xlWorkSheet.Cells[rw, 3] = part_name;
xlWorkSheet.Cells[rw, 4] = control_id;
xlWorkSheet.Cells[rw, 5] = serial_number;
xlWorkSheet.Cells[rw, 6] = test_operator;
//// 退出并释放资源
xlWorkBook.Close();
Marshal.ReleaseComObject(xlWorkBook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(range);
return 1;
}
我期望调用此方法后,我的数据会得到更新,并使文件保持可供程序的其他实例与之交互。
英文:
I am trying to update data in a excel file. I can open a workbook and add my new data. When I close the workbook it fires a thread that has a pop-up below all other pages asking if I want to save the pages. This leads to another pop-up asking the name of the new file.
What I need is to open the file, change data and close / update the file without human intervention.
Here is my current code:
public static int Make_new_row(string part_number, string part_name,
string control_id, string serial_number, string test_operator)
{
string file_name = "K:\\Test_Data\\Log_File\\Test_Log.xlsx";
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
int loop;
for (loop = 0; loop < 10; loop++)
{
try
{
//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file_name);
xlWorkBook = xlApp.Workbooks.Open(file_name, 0, true, 5, "", "",
true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
break;
}
catch (Exception) // file is in use. wait for file avalibility
{
Thread.Sleep(1000);
continue;
}
}
if (loop == 10)
{
string message = "Unable to connect to file." + "\r" +
"Troubleshoot issue and try again";
string title = "Connection Error";
MessageBox.Show(message, title);
return -1;
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
// make a new row
xlWorkSheet.Rows[rw].Insert();
// get last tracking # and generate a new one
string last_traxcking_raw = Convert.ToString((xlWorkSheet.Cells[rw - 1,1]).Value);
string last_traxcking = last_traxcking_raw.Remove(0,3);
Data_File.last_test_file = last_traxcking;
int t_number = Int32.Parse(last_traxcking);
string new_tracking = ("TS-" + (t_number + 1));
// populate new row with data
xlWorkSheet.Cells[rw , 1] = new_tracking;
xlWorkSheet.Cells[rw , 2] = part_number;
xlWorkSheet.Cells[rw, 3] = part_name;
xlWorkSheet.Cells[rw , 4] = control_id;
xlWorkSheet.Cells[rw , 5] = serial_number;
xlWorkSheet.Cells[rw , 6] = test_operator;
//// quit and release
xlWorkBook.Close();
Marshal.ReleaseComObject(xlWorkBook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(range);
return 1;
}
I am expecting to call this method and have my data updated leaving the file available for other instances of the program to interact with.
答案1
得分: 0
第一个问题出现在以下语句的第3个变量中:
xlWorkBook = xlApp.Workbooks.Open(file_name, 0, false, 5, "", "",
true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false,
0, true, 1, 0);
这里是false,原本是为了"Open as read only"而设为true。
其次,以下代码修复了这个问题:
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(file_name, AccessMode: XlSaveAsAccessMode.xlNoChange);
xlWorkBook.Close();
xlApp.DisplayAlerts = true;
英文:
OK, The first issue was in variable 3 in the following statement:
xlWorkBook = xlApp.Workbooks.Open(file_name, 0, false, 5, "", "",
true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false,
0, true, 1, 0);
Here it is false, originally it was true for "Open as read only"
Secondly the following code fixed the issue:
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(file_name, AccessMode: XlSaveAsAccessMode.xlNoChange);
xlWorkBook.Close();
xlApp.DisplayAlerts = true;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论