保存工作簿,如何不处理弹出窗口?

huangapple go评论67阅读模式
英文:

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 = &quot;K:\\Test_Data\\Log_File\\Test_Log.xlsx&quot;;

	int rw = 0;
	int cl = 0;
	xlApp = new Excel.Application();


	int loop;
	for (loop = 0; loop &lt; 10; loop++)
	{
		try
		{

			//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file_name);
			xlWorkBook = xlApp.Workbooks.Open(file_name, 0, true, 5, &quot;&quot;, &quot;&quot;,
				true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, &quot;\t&quot;, 
				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 = &quot;Unable to connect to file.&quot; + &quot;\r&quot; +
			&quot;Troubleshoot issue and try again&quot;;
		string title = &quot;Connection Error&quot;;
		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 = (&quot;TS-&quot; + (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, &quot;&quot;, &quot;&quot;,
true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, &quot;\t&quot;, 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;

huangapple
  • 本文由 发表于 2023年6月13日 00:48:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76458742.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定