在桌面上“打开"时，是否可以写入现有的Excel文件? [英] Is it possible to write to an existing Excel file when it is **open** on the desktop?
问题描述
我想创建一个将数据写入现有Excel文件的代码.仅当文件关闭时，它才会读/写文件.如果我尝试在桌面上打开该文件时写入该文件，则不会更改或保存该文件.在代码运行之前或运行期间打开Excel文件时，我也无法关闭工作簿(使用.close())或退出应用程序(使用.quit()).
I am tying to create a code that will write data to an existing Excel file. It will read/write to a file only when the file is closed. If I attempt to write to the file while it is open on the desktop it will not change or save the document. I also cannot close the workbook (using .close()) or quit the application (using .quit()) when the Excel file is opened before or while the code is running.
有什么方法可以在桌面上打开并实际显示更改的情况下写入excel文件吗?还是至少可以关闭对已打开文件的读/写操作，保存并使用C#代码再次打开?这是我正在使用的代码；它有点杂乱无章，但是如果您运行它，则可以看到我实际上正在尝试做的事情.请不要为使代码正常工作而必须更改要保存文件的类属地址(类属地址保存为名为excelsource的字符串变量).该代码将首先创建一个以今天的月份和日期(MM_YY)命名的文件.每当您初始化代码时，它将继续向其写入数据.如果您在打开新创建的文件时尝试写入文件，则不会对该文件进行任何更改(仅在关闭文件时才写入excel文件).
Is there a way I can write to an excel file while it is open on my desktop and actually show the changes? Or can I at least close an already open file read/write to it, save it and open it again with a C# code? Here is the code I am using; it is a bit unorganized but if you run it you can see what I am essentially trying to do. Please not you must change the genera address in which you are going to save the file in order for the code to work (general address is saved as a string variable called excelsource). the code will first create a file named with todays month and date (MM_YY). it will continue to write to it everytime you initialize the code. If you attempt to write to the file while the newly created file is open no changes will by applied to the file (only writes to the excel file when the file is closed).
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics;
using System.Threading;
namespace ConsoleApplication4
{
class Program
{
static public string excelsource = @"\user\desktop\generic\";
// excelsource is the "general" address of where excel file wil be saved.
static public bool truth;
static public bool truth1;
static public bool scan_thru = false;
static public int range2;
static public int index = 1;
static Excel.Application excel = new Excel.Application();
static Excel.Workbook workbook;
static Excel.Worksheet sheet;
static Excel.Range range;
static FileInfo file;
static void Main(string[] args)
{
DateTime current_time = DateTime.Now;
string file_name = excelsource + current_time.Month.ToString() + "_" + current_time.Year.ToString() + ".xlsx";
string curfile = file_name;
truth = File.Exists(curfile);
// truth determines if file exists if truth == true file exists and does not need to be created, if false a new file is created.
if (truth == false)
{
workbook = excel.Workbooks.Add();
sheet = workbook.Sheets[1];
sheet.Name = (current_time.Month.ToString() + "_" + current_time.Day + "_" + current_time.Year);
}
else
{
file = new FileInfo(file_name);
truth1 = IsFileinUse(file);
// truth1 determines if the existing file is open; truth1 == false if the file is currently closed and is true when it is open.
workbook = excel.Workbooks.Open(file_name);
sheet = workbook.Sheets[current_time.Month.ToString() + "_" + current_time.Day + "_" + current_time.Year];
if (truth1 == true)
{
excel.Visible = false;
excel.DisplayAlerts = false;
workbook.Save();
}
while (scan_thru == false & truth1 == false)
{
string box = "A" + index.ToString();
range = sheet.get_Range(box, Missing.Value);
string range1 = range.Text;
bool judge = int.TryParse(range1, out range2);
if (judge == true)
{
index++;
}
else
{
scan_thru = true;
}
}
scan_thru = false;
}
int i = index;
while (i < (index + 100) & truth1 == false)
{
sheet.Cells[i, "A"].Value2 = i.ToString();
i++;
}
if (truth == false)
{
workbook.SaveAs(file_name);
}
if (truth == true & truth1 == false)
{
excel.DisplayAlerts = false;
}
index = 1;
workbook.Close(true);
excel.Quit();
}
protected static bool IsFileinUse(FileInfo file)
{
FileStream stream = null;
try
{
stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}
catch (IOException)
{
return true;
}
finally
{
if (stream != null)
stream.Close();
}
return false;
}
}
}
推荐答案
很有可能这是无法通过C#应用程序完成的.在Excel中打开文件时，它会阻止来自其他应用程序的写访问，以维护打开文件中的数据完整性.这意味着您应该具有只读访问权限.
There is a very good chance this is not possible to do from a C# application. While a file is open in Excel, it blocks write access from other applications to maintain data integrity within the file that is open. This means you should have read-only access.
此外，从外部应用程序中强制打开和关闭任何应用程序都是非常很少的更新文件的最佳方法.如果您要修改当前打开文件中的数据，则应该学习如何编写Excel宏.
Also, force-open and closing any application from an external application is very, very rarely the best method of updating a file. If you are looking to modify data from a currently open file, you should look into learning to write Excel macros.
在澄清问题之后:
Edit 1: After clarification of question:
您无法添加到打开的Excel文件中.但是，如果我正确理解了您的问题，我认为这可以解决您的问题(我尝试过并且对我有用):
You can't add to an open Excel file. However, if I understand your problem correctly, I think this will solve your problem (I tried it and it works for me):
- 写入逗号分隔的.csv文件，以控制对C#应用程序的读写访问.
- 在引用您的.csv文件的excel电子表格来自文本"中添加外部数据源".来源: https://support.office.com/zh-cn/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba 在通过连接到文本文件导入"部分下.
- 在数据"选项卡的连接"标题下，您想要更改属性"以经常刷新或在打开文件时刷新.如果您不会每次都更改文件名，那么我将取消选中刷新时提示输入文件名"复选框.
- Write to a comma-separated .csv file that you control read/write access to from your C# application.
- Add a "External Data Source" in your excel spreadsheet "From Text" that references your .csv file. Source: https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba under the "Import a text file by connecting to it" section.
- Under the "Connections" heading in the "Data" tab, you want to change your "Properties" to refresh every so often or when opening the file. I would uncheck the "Prompt for file name on refresh" box if you won't be changing your filename every time.
希望这对您有用.如果您需要更多说明，请再次发表评论.
Hopefully this works for you. Comment again if you need more clarification.
这篇关于在桌面上“打开"时，是否可以写入现有的Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
