.Net Project to Copy Excel Data

In the past weeks, I have been working on a project to copy Excel data between worksheets. The source worksheet contains some formulas with links to other worksheets and add-ins. The problem is that the API functions defined in add-ins take long time to get data from a remote service. When there are hundreds and even thousands of rows in Excel, it will take to long time to refresh data. That's the reason to have a scheduled job to copy the daily data from source excel file to another one with only values. Since the output put excel file contains only values, the show time is much responsive.

The project was initially created in .Net by using Microsoft.Interop.Excel, which is a warper class for Excel COM components. This is the first time I saw this namespace and real case usage. Basically, with this namespace and classes there, you can create Excel application, open existing or create new workbook, create or get worksheet.

I found that it is very powerful and interesting to do almost the same work in .Net project as you do interactively with Excel in Windows. For example, if the source excel file contains some links, you can ignore those links and rebuild them in .Net. You can reset excel cell data value and simulate special copy and paste to copy only values to another worksheet.

I updated the project to move many hard-coded settings out to a configuration file so that the tool will be more generic. The goal of the tool is to copy values from an excel file to another one. The figuration contains those settings:

• Input and output excel files;
• The links in both input and output files to be rebuilt;
• Data values to be set in input file. Those values are in range object of an excel worksheet object. The values are strings or Excel formulas;
• The range of data to be copied in the input file and destination range in the output file;
• Page layout settings: display grid lines, window display headings, page area and line breaks.

I found one setting is very helpful. You can set excel object visibility so that you can debug your codes by viewing Excel status. With this debug feature, I found many bugs or wrong settings in the existing codes.

The project is almost rewritten to become a more generic tool with configurable feature. I used this not only resolve the original required excel report issue, but it has been used in many other cases. However, personally, I don't think doing the excel work in .Net is a practicable. The excel classes are to easy to be broken. When the project becomes a more generic tool, many unseen issues have not been tested. I don't want to spend too much time to fix issues. The coupling data and report to Excel is too much pain.