Tip: Delay Seconds in Batch Script

I found this tip to delay batch progress by seconds. PING is a commonly used MS-DOS command for checking a TCP/IP client. By using PING's -n and -w options, it simulates a time delay for its running.

I created a batch file: DeleySeconds.bat:

IF %1.==. PING 1.1.1.1 -n 1 -w 60000 > NUL
IF NOT %1.==. PING 1.1.1.1 -n 1 -w %1 > NUL

If the call to the batch script does not have parameter, the default delay time is 60 seconds. A specific time can be passed in as parameter.

Reference: Batch file examples - Wait.

Read More...

Collapse...

.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.

Read More...

Collapse...

My EverNote Entry: OpenRasta, OpenWrap, and OWIN

Today I listened to a podcast by Herding Code. The podcast on Jan 25, 2011 is a very interesting and informational one. It is a talk with Seb Lambla on OpenEverthing, which includes OpenRasta, OpenWrap, OWIN and many other related resources.

I have used EverNote for quite awhile. It is an excellent web service for quickly making any notes. I have created a .Net notebook as public shared one. Therefore, I add one note on this podcast and related links in the notebook. For more detail information about this podcast and the talk on three major Open projects, see my .Net notebook.

The public notebook provided by EverNote can be used as a RSS feed. That means public notebooks can be subscribed to. I use EverNote to make notes for important things such as events, thoughts, and interesting thinks I found on web. In a way, it is my alternative way to write blogs to record my life's journey, almost everything, or a supplementary to my blogs.

Read More...

Collapse...