.NET Resource File to Excel File Converter

Introduction

This application exports data from a .NET resource file to an Excel file. The code uses a COM component named Microsoft Excel 11.0 object library that provides APIs to manipulate Excel applications. So before using this code, you need to add the reference to this DLL as shown in Figure 1 above. This article shows how you can manipulate Excel files as well as .NET resource files through programming.

Background

There is a functionality called Localization in .NET that most of the Web application developers in ASP.NET must be familiar with. I had to implement the same functionality in one of my projects and for that I needed to create a resource file for different languages. I made one resource file for the English language but I also needed the values in different languages for the same keys, so I had to give the key value pairs in an Excel file to a designated person who was going to write the corresponding values for other languages in the same Excel file. Now the task of manually exporting resource file data to Excel files seemed quite time consuming to me as there were a large number of resource files (more than 100). So I decided to develop a small tool first to do the task for me. Apart from getting my task done easily, I find it very interesting as I got a lot of learning out of it. After that, I also developed a tool for importing data back from an Excel file to a resource file. You can read it in my article, Excel to Resource file conversion.

Code

Add following reference to your project:

This is the Excel application initialization code you can put in page load of the form:

// reference to Microsoft Excel object
Excel.Application exlObj = null;

// initializing the Excel application
exlObj = new Excel.Application();

// Showing the error message if any problem occurs in starting the Excel application
if (exlObj == null)
{ MessageBox.Show("Problem in starting Excel.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); System.Windows.Forms.Application.Exit();
}

// you can keep the Excel application visible or invisible while writing to it
exlObj.Visible = false; 

How To Create an Excel Workbook and Worksheet to Work On? 

// creating Excel workbook
Excel.Workbooks workbooks = exlObj.Workbooks;

// "workbook.Add" method returns a workbook with specified template
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

// getting the collection of Excel sheets from workbook. 
// However right now there is only one worksheet in the workbook
Sheets sheets = workbook.Worksheets;

// getting the worksheet from Sheet's collection. 
// Keep in mind that in Excel, the index starts from 1
// and index 1 always gives the newly added worksheet
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1); 

Adding a New Worksheet to a Workbook  

// This method adds a new sheet to Sheet collection of workbook
// here Missing.Value signifies that these arguments are unknown
// we can also pass appropriate arguments if we wish
sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); 

Naming or Renaming the Worksheet

// naming the worksheet. The name of the worksheet should not exceed 31 characters
if (fileName.Length > 30)
{
worksheet.Name = fileName.Substring(0, 29);
}
else
{
worksheet.Name = fileName;
} 

Working with Data on Sheet and Formatting 

// get_range gives the object of a particular row from index1 to index2
Range range = worksheet.get_Range("A1", "B1");

// applying text format on a particular range. These can be applied on 
// the whole worksheet too
range.Font.Bold = true;

// writing text to a cell
range.set_Item(1, 1, "KEY");
range.set_Item(1, 2,"VALUE");

// applying format on worksheet
worksheet.Columns.ColumnWidth = 40;
worksheet.Rows.WrapText = true;
worksheet.SaveAs(txtTargetPath.Text + "\\"+fileName +".xls" 
    , 1, null, null, false, false, null, null, null, null);

Interesting Points

The basic ideas used in this code are manipulating an Excel file and a .NET resource file. The sample I have presented here fits my requirements and I implemented only those things which were needed out of my explorations. You can take this article as a starting point for further exploration in this subject as there are a lot of manipulations you can do with Excel files and even some with *.resx files.