Figure 1
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.
Using the Code
This is the Excel application initialization code you can put in page load of the form:
Excel.Application exlObj = null;
exlObj = new Excel.Application();
if (exlObj == null)
{
MessageBox.Show("Problem in starting Excel.",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.Application.Exit();
}
exlObj.Visible = false;
How To Create an Excel Workbook and Worksheet to Work On?
Excel.Workbooks workbooks = exlObj.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
Adding a New Worksheet to a Workbook
sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Naming or Renaming the Worksheet
if (fileName.Length > 30)
{
worksheet.Name = fileName.Substring(0, 29);
}
else
{
worksheet.Name = fileName;
}
Working with Data on Sheet and Formatting
Range range = worksheet.get_Range("A1", "B1");
range.Font.Bold = true;
range.set_Item(1, 1, "KEY");
range.set_Item(1, 2,"VALUE");
worksheet.Columns.ColumnWidth = 40;
worksheet.Rows.WrapText = true;
worksheet.SaveAs(txtTargetPath.Text + "\\"+fileName +".xls"
, 1, null, null, false, false, null, null, null, null);
Points of Interest
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.
History
- 18th October, 07: Initial post