Wednesday, November 2, 2011

Open 2 Excel Files in separate windows

WARNING: Changing your registry can be dangerous to the health of your computer. Do so at your own risk.

This change makes it so when you click an Excel file it will ALWAYS open in a new window (a different instance of Excel)

 

.XLSX

  1. I highly recommend you back up your registry before making any changes.
  2. Open regedit.exe
  3. Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/command
  4. Open (Double-click) the key called (Default)
  5. Change the value from
          "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e
    to "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e “%1”
  6. Click the OK button
  7. Below the (Default) key you will see a key with the name command.
    Right click it and select Rename.
    Change the name to something else (it doesn’t matter what)
  8. Your screen should now look something like the following (I appended bv to the command key, but you should probably use something different)

    image
  9. Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/ddeexec
    Right click it and select Rename.
    Change the name to something else (it doesn’t matter what)
  10. Your screen should now look something like the following:

    image
  11. You have now successfully made it so that when you open an existing Excel file (that has the extension .xlsx) it will open in a new window. This allows you to easily compare or view two Excel file at the same time. This is particular helpful if you have two monitors. You can also ways revert back if you decide you don’t like the change by undoing the changes made here, that is why we renamed instead of deleted the keys.

.XLS

You can follow the same instructions for .XLS except you will want to amend the instruction so that where ever you see Excel.Sheet.12, replace it with Excel.Sheet.8.

BTW, I found this tip here.

Thursday, October 6, 2011

PNG Compression Tools

PNG is a great format, but not all applications that save them create the same size files. There are at least four major tools out there that do nothing more than optimize the size using lossless techniques. Some of these tools even work on other formats also.

I have not personally benchmarked them, but this person has. Below are some excerpts from their post.

 

  • punypng: the new kid of the block.
  • smush.it: Uses pngcrush as the main PNG optimizer.  Currently, available in Yahoo’s YSlow Firefox plug-in.  I believe it uses the -brute option for pngcrush.
  • OptiPNG: A slighlty better algorithm, compared to pngcrush.  In case you’re wondering, Google’s PageSpeed plugin also uses OptiPNG for it’s compression library.
  • ImageOptim: The heavyweight contender.  Available for OS X, ImageOptim uses every major library out there: advdef, pngcrush, optipng, pngcrush, jpegoptim, jpegtran, and optionally pngout.  I ran this the benchmarks with pngout enabled.

 

image

If file size is critical I suggest trying all of them since it seems one does better than the other depending on the file.

I use Paint.NET a lot and there is a plug-in for it if you use OptiPNG.

If you want to use Visual Studio, there is a plug-in for it that uses punypng and smush.it.

Friday, September 23, 2011

A look at the topic of Artificial Intelligence (AI)

General AI

Artificial Intelligence Wiki – a good place to start to understand what AI encompasses and its different areas.

Journal of Machine Learning Research – Amazingly complex scientific papers of AI algorithms. Not for light reading.

Machine Learning Open Source Software

 

Areas of interest for me:

Reinforcement Learning – concerned with how an agent ought to take actions in an environment so as to maximize some notion of cumulative reward.

Data Mining

Data Mining – good starting point for learning about data mining

Text Mining

Basically Text Mining is data mining on unstructured data like documents, web pages, etc. instead of a database.

Natural Language Processing – Imagine a machine being able to scour the internet and actually extract knowledge about what it read.

Text Mining – an area of Natural Language Processing, has many commercial uses even today. i.e. Bing

Semantic Web – a machine readable version of the web.

DBpedia – An effort to translate Wikipedia into machine understandable format to facilitate complex queries and meanings of words, not just matches.

Freebase – Similar to DBpedia, but it is hand crafted. DBpedia has lots of links to it as well. A database of “bar codes” for all entities on the web. Aliasing… Also powers Bing

What is Text Mining – describes how text mining is more complex than data mining since it involves natural language processing.

Carrot2 – text and search results clustering framework. Very cool way to browse search results and get to what you are looking for

Wednesday, September 21, 2011

Reading a MS Word 2007 Document in .docx format using C#

If you want need to read (or write) from a MS Word 2007 Document that has been saved in the Open XML format (.docx) then you can use the Open XML SDK 2.0 for Microsoft Office to do just. The first thing you will need to do is download and install the SDK. In particular, you must download and install the OpenXMLSDKv2.msi. In addition, you can download the OpenXMLSDKTool.msi if you want. It has some VERY nice features like generating code from an existing .docx file.

Now that you have the files you need, open Visual Studio (2008 or 2010 works fine), open the project you want to use, and add a reference to the DocumentFormat.OpenXml (I had to browse to it in the adding references windows by going to C:\Program Files (x86)\Open XML SDK\V2.0\lib) and WindowsBase (mine was located in the list of .NET tab when adding references). Please note, this code does not require MS Word be installed and is safe to run on the server such as with ASP.NET.

Now that you have the api, the rest is just working with the document. To get a better understand on how to work with the parts (structure) of the Word Document, click here.  For a list of “How do I…” code samples, click here.

Here is example code on how to get the body of the document.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

…

using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(Filename, false))
{
        var body = wordDocument.MainDocumentPart.Document.Body;
}

 

Here is an example of a more complex line of code that can be used to navigate the structure of the document using LINQ. In this case the document has a table in it and we are getting the first row and first cell of that row and the second element.

wordDocument.MainDocumentPart.Document.Body.ChildElements.First<Table>().ChildElements.First<TableRow>().ChildElements.First<TableCell>().ElementAt(2).InnerText

I hope this gives you an idea of how to get started. There are lots of good links, examples, etc here.

Tuesday, September 20, 2011

Convert a batch of .doc files to .docx using C# and Word 2007

I recently inherited a bunch of Microsoft Word files that are in the .doc format. I want to convert them all to .docx so I can easily parse them later without needing MS Word installed (i.e. on a server). You can do the conversion with no code at all if you have the time. All you have to do is open the file up in MS Word 2007 and save the file as a .docx; Word will do the work for you. This is great, but I had hundred of files to convert, and I could not bear doing something that many times (I’m a programmer after all). Unbelievably there are products that cost $150 and more to do this. There are so trial editions that do 5 at a time, etc, and even some command line ones. Command line might work, but it still involves me figuring out where in a bunch of nested directories where the .doc files are and coming up with the command line arguments. That isn’t much better than opening Word, though I could script that solution at least.

In the end, I decided it really wasn’t that difficult to just sit down and write the code to do this. The code is very simple. I have put it in one class so that you can easily include it in your own project. It could be a command line or WPF or WinForms. It doesn’t really matter. All the code does is

  1. Take the directory path that you pass it and recursively finds all the .doc files (even if they are in sub-directories of sub-directories)
  2. Open MS Word in the background (You can see winword.exe in your Processes under Task Manager).
  3. Loop through each file found
  4. Open the current file
  5. Tell MS Word 2007 to save the file as .docx
  6. Close the File
  7. Close MS Word when all files have been processed.

You will find all the new files right next to the .doc files. You can then search in Windows for .doc and delete them quickly once you have comfortable everything went smoothly.

Things you will need to use the class below.

  • Visual Studio
  • MS Word installed on the same machine as you run your program you create

When you create your project you will need to add a reference to your project for Microsoft.Office.Interop.Word. Besure you choose the version 12 and not version 11 like I did initially. If you do you will get a compiler error.

Below is the actual code you need.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Word;

namespace ConvertDocToDocx
{
    public class DocToDocxConverter
    {

        List<string> AllWordFiles { get; set; }
       
        DirectoryInfo StartingDir { get; set; }

        public DocToDocxConverter(DirectoryInfo startingDir)
        {
            StartingDir = startingDir;
        }

        public void ConvertAll()
        {
            AllWordFiles = new List<string>();
           
            // NOTE: Since .xls is a also in .xlsx this search will find .xls and .xlsx files
            // If the extension is different then this can be called again to include them.
            FindWordFilesRecursively(StartingDir.FullName, "*.doc");

            // only open and close Word once to maximize performance
            Application word = new Application();

            try
            {

                foreach (string filename in AllWordFiles)
                {
                    // exclude the .docx (only include .doc) files as we don't need to convert them. :)
                    if (filename.ToLower().EndsWith(".doc"))
                    {
                        try
                        {
                            var srcFile = new FileInfo(filename);

                            // convert the source file
                            var doc = word.Documents.Open(srcFile.FullName);
                            string newFilename = srcFile.FullName.Replace(".doc", ".docx");

                            // Be sure to include the correct reference to Microsoft.Office.Interop.Word
                            // in the project refences. In this case we need version 12 of Office to get the new formats.
                            doc.SaveAs(FileName: newFilename, FileFormat: WdSaveFormat.wdFormatXMLDocument);
                        }
                        finally
                        {
                            // we want to make sure the document is always closed
                            word.ActiveDocument.Close();
                        }
                    }
                }
            }
            finally
            {
               
                word.Quit();
            }
        }

      

        void FindWordFilesRecursively(string sDir, string filter)
        {

            foreach (string d in Directory.GetDirectories(sDir))
            {
                foreach (string f in Directory.GetFiles(d, filter))
                {
                    AllWordFiles.Add(f);
                }
                FindWordFilesRecursively(d, filter);
            }
        }


      
       
    }
}

Wednesday, September 14, 2011

Email Aliases in Gmail

Gmail doesn't offer traditional aliases, but you can receive messages sent to your.username+any.alias@gmail.com. For example, messages sent to jane.doe+notes@gmail.com are delivered to jane.doe@gmail.com.

You can set up filters to automatically direct these messages to Trash, apply a label or star, skip the inbox, or forward to another email account.

This is great for testing user registration in your app.

Thursday, August 25, 2011

Free Options for reading and writing Excel Files

In general there are three main categories of tools that allow you to interact with Excel files.
  • MS Excel Primary Interop Assembly (PIA) Based: These require MS Excel be installed and licensed. Not a good option of server environment since it basically spins up Excel.
  • OleDB Based: These are very fast a light weight solutions generally that are server friendly. No license or installation of Excel required. I highly recommend the ACE driver instead of JET driver. You can do read, write, update with this option. See OLE DB Article section below for details.
  • Other: This means they have coded their own solution typically using some kind of XML format, but sometimes using binary format.
I have not tested many of these solutions other than using PIA or OLE DB. The exception to that is CarlosAg Excel Xml Writer Library which I do like, but it is only for Writing Excel files, not reading them. It has good performance and works well for tabular and non-tabular spreadsheets.
I would love to add other solutions to the list so please let me know your favorite solutions.

Solution Requires Excel / Uses PIA Uses OleDB Uses
OpenXmlWriter
Server Friendly Supports Read Supports Write Supports
.XLS
Supports
.XLSX
MS Excel Interop (PIA) Yes No No Yes Yes Yes Yes
.NET Framework Data Provider for OLE DB (using ACE OLEDB 12.0) No ACE or JET No Yes Yes Yes Yes
Open XML SDK 2.0 for MS Office No No Yes Yes Yes Yes No Yes
Excel Data Reader No No ? Yes Yes No Yes Yes
ExcelMapper
for tabular data
No ACE & JET ? Yes Yes Yes Yes Yes
CarlosAg Excel Xml Writer Library – uses old xml spreadsheet format No No No Yes No Yes No No (only .xml)
GemBox (for small files it is free) No No? ? Yes Yes Yes Yes Yes
Koogra No No ? Yes? Yes No? Yes? Yes
MyXLS No Yes ? Yes Yes Yes Yes No
ExtremeML No No No Yes Yes Yes No? Yes
ClosedXML No No No Yes Yes Yes No Yes
SpreadSheetLight No No Yes Yes Yes Yes No Yes
EPPlus No No ? Yes Yes Yes No Yes

My thoughts (Feb 2015)

If you are doing anything server side such as ASP.NET, I highly suggest staying away from anything that uses the MS Excel Interop (PIA). It is nice it supports .xls format though.

If you need to read data VERY fast OleDB will be WAY faster than any api.

If you need to write many MB of data I suggest you use a product that uses the OpenXmlWriter.

I have used and like ClosedXML alot. It is reasonably fast and easy to use.

I have read about SpreadSheetLight and it looks very easy to program because it is designed to work like you are using Excel. It appears that it uses the OpenXmlWriter which is as fast as you can get. It appears to be very efficient at writing large amounts of data, but less efficient for editing existing sheets. It has excellent documentation. I think this would be my choice if I were to start a new project again and didn't know the OpenXml API.

EPPlus also look good and has like 4 times the downloads of ClosedXML now. It does do Data Validations and pivot tables which is nice. The documentation seems lacking. If you want to be close to OpenXml and be able to interact with it, you may like this product.

Using Excel PIA Articles:

Loading and reading the Microsoft Excel file content using C#
How to export database to Excel file
C# Excel Tutorial

OLE DB Articles

OLE DB can use either JET or ACE. ACE is MUCH better than JET because it doesn’t have the same pitfalls with guessing data that JET does, etc. So when using OLE DB change the connection string to use ACE instead of JET driver for OleDB.
Connection strings for Excel 2007 - Connection Strings for Excel / ACE OLE DB 12.0
How to read from an Excel file using OLEDB
How to insert data to Excel file using OLEDB 
How to update data in Excel file using OLEDB

My favorite Option for very large amounts of data is to use the Open XML format
Writing Large Excel File with the Open XML SDK
How to create a stylesheet in Excel Open XML


Other Useful Links

FileHelpers – import/export data from fixed length or delimited records using strongly type values
Web Spreadsheet
OpenXMLDeveloper.org