如何在.xlsx工作簿中替换整个1张工作表 [英] How to replace 1 entire sheet in an .xlsx workbook
问题描述
我一直在研究一个使用预定义的 .xlsx 工作簿的项目，该工作簿具有37个选项卡(是的，有37个，太多了！).
我从多种来源和格式(exl，xlsx，csv，txt，多个数据库，XML等)收集数据，将其收集到大量的SQL表中，然后根据需要提取数据并将其推送到工作簿中.
我遇到的问题是，有一个来源向我发送了一个带有大量数据的单个 .xlsx 文件.这只是最终报告文件中的选项卡18.
问题是两件事，它们每周更改行数(最少40行，最多2000行).他们更改了一些标题.这会导致将数据推送到模板中的问题.
我想尝试执行以下操作，而不是尝试将变量数据读入SQL然后再将其推回到选项卡18:
打开 Report.xlsx 工作簿和 WeekData.xlsx 工作簿，然后清空report.xlsx文件中的整个选项卡18并将其完全替换为WeekData.xlsx文件中的单个选项卡.这需要复制数据，公式，格式，图表，文本框以及所有内容.
结果需要保留在选项卡18中(无法更改顺序或选项卡名称).
我正在使用C#.我已经看过了，但是似乎找不到任何参考来使用程序来构建此功能.
在开始工作之前，我只是打开WeekData工作表，然后在报表"中打开选项卡18，然后进行复制/粘贴以获取所需的结果.
手工如此简单，但需要自动化.
任何引用整个图纸移动/副本的建议，示例，链接将不胜感激.
谢谢
I have been working on a Project that uses a pre-defined .xlsx workbook with 37 tabs (yes, 37, wayyy too many!).
I gather data from multiple sources and formats (exl, xlsx, csv, txt, multiple databases, XML, etc.), collect it in a raft of SQL tables, and then extract and push data into the workbook in each tab as needed.
The issue I have is that one source sends me a single tab .xlsx file with a ton of data. This is just tab 18 from the final report file.
The issue is two things, they change the number of rows each week (minimum of 40, up to 2000). and they change some of the titles. This cuases issues with pushing data into the template.
Rather than trying to read variable data into SQL and then push it back to tab 18, I want to try to do the following:
Open both the Report.xlsx workbook, and the WeekData.xlsx workbook, then empty the entire tab 18 in the report.xlsx file and replace it entirely with the content of the single tab in the WeekData.xlsx file. This needs to copy data, formulas, formatting, charts, textboxes, everything.
The results need to stay in tab 18 (cannot change the order, or the tab name).
I''m using C#. I have looked and looked, but cannot seem to find any references to build this functionallity with a program.
Until I get this working, I''m simply opening the WeekData sheet, then opening the tab 18 in the Report, and doing a copy/Paste to get the results I need.
So easy by hand, but need to automate.
Any suggestions, examples, links that reference entire sheet moves/copies would be much appreciated.
Thanks
推荐答案
我会使用Excel宏功能.您可能需要通过在单击Microsoft Office按钮后单击面板上的按钮，转到Excel Optons，来添加开发人员菜单.在热门类别中
这将允许您记录按钮的点击次数(开发人员"选项卡上的记录宏").然后，您可以编辑VBA代码以使其更加灵活.此代码也可能有助于使用office interopt编写C#代码.
注意:在以下链接上有一个示例或从C#运行宏:
http://support.microsoft.com/kb/306683 [
I would use the Excel macro capability. You may need to add the developer menu by going to Excel Optons by clicking the button on the panel after clicking the Microsoft Office button. It is in the popular category
This will allow you to record your button clicks (Record Macro on Developer tab). You can then edit the VBA code to make it more flexible. This code may also help in writing C# code using the office interopt.
Note: there is an example or running a macro from C# on the following link:
http://support.microsoft.com/kb/306683[^]
由于安全性以及其复杂性和要求等诸多问题目标xlsx文件，我走了很长的路.
由于源文档的格式无法更改(如果添加或删除列，则违反业务规则)，因此我构建了一个小型的翻录"程序，该程序计算每个节中的行数，然后将这些节上载到SQL. /> 一旦存在，我将使用由较早构建的程序调用的存储过程，该程序从存储过程中获取结果，并将其插入报表模板xlsx文件中的正确工作表和位置.
这样可以按需要保持选项卡顺序，并且只会在自动化过程中增加一些额外的步骤.
感谢您的建议，我将可以在今年夏天进行的另一个项目中使用.
唐
Due to a number of issues with security and the complexity and requirments of the destination xlsx file, I went the long route.
Because the format of the source document cannot change (violation of business rules if they add or drop a column), I built a small "ripping" program that counts the number of lines in each section, then uploads the sections to SQL.
Once it''s there, I use stored procedures called by a program built earlier that takes resuklts from a stored procedure and plugs it into the correct sheet and location on my reporting template xlsx file.
This keeps the tab order as needed and only adds a few extra steps to the automated process.
Thanks for the suggestions, I will be able to use on another project coming up this summer.
Don
这篇关于如何在.xlsx工作簿中替换整个1张工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
