Microsoft Excel File to .NET Resource File Conversion

• Download source code - 319 KB

Introduction

This application takes a Microsoft Excel file as input, reads it, and writes its content to a .NET resource file. The code uses a COM component named Microsoft Excel 11.0 Object Library; it provides APIs to manipulate the Excel application, so before using this code, you need to add the reference to this DLL as shown in Fig. 1 above. If a Workbook contains more than one worksheet, it will read all the worksheets and will generate different resource files for different Worksheets. Keep in mind the Excel file should be in the same format as the .NET recourse files are, like KEY and VALUE columns only (see Fig. 2).

Background

There is a functionality called Localization in .NET which most web application developers in ASP.NET must be familiar with. I came across implementing the same functionality in one of my projects, and for that, I needed to create a resource file for different languages. I made a resource file for English language, but I also needed the values in different languages for the same keys, so I put the key value pairs in an Excel file and gave it to the designated person who was going to write the corresponding values for other languages. I got the resultant Excel file but it was very hectic to produce resource files from that manually, and then an idea struck my mind, and I developed this tool that did the task for me very efficiently. See the Resource to Excel file conversion article also.

Using the Code

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

ExlObj = new Excel.Application();

// Showing the error message if any problem occures in starting 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 = true;

This is the code that performs the actual task:

private void btnStart_Click(object sender, EventArgs e)
{
    // Creating a reference to .Net resource file object
    System.Resources.ResXResourceWriter resWriter = null;

    try
    {
        if (txtFilePath.Text.Trim() != "")
        {        
            // Opening the excel file. in excel version 5.0.0
            // it takes 15 arguments some of which 
            // like filepath, format, origin etc are mandatory,
            // other you can put null or 0. 

            Excel.Workbook theWorkBook = 
               ExlObj.Workbooks.Open(txtFilePath.Text, 0, true, 5,"", 
                      "", true, Excel.XlPlatform.xlWindows, 
                      "\t", false, false, 0, true, null, null);
            // Extracting the worksheet collection out of workbook,
            // there can be any number of worksheet in a workbook
            Excel.Sheets sheets = theWorkBook.Worksheets;     
        

            // Iterating for each worksheet
            for (int index = 1; index <= sheets.Count; index++)
            {
                // Extracting a single worksheet out of the collection of worksheet
                Excel.Worksheet workSheet = (Excel.Worksheet)sheets.get_Item(index);

                // Initializing the resource writer object 
                resWriter = new System.Resources.ResXResourceWriter(
                                       workSheet.Name.ToString() + ".resx");

                // Calculating the total number of data rows in a worksheet 
                // here "A65536" is the index of last row in excel sheet
                // get_End method gives the index of data
                // row after which it finds an empty row
                // so that's why we start it from last most index of sheet. 
                int noOfDataRowsInExcelFile = workSheet.get_Range("A65536", 
                             "A65536").get_End(Excel.XlDirection.xlUp).Row;

                // Iterating for number of data rows in a worksheet    
                for (int rowIndex = 1; rowIndex <= noOfDataRowsInExcelFile; rowIndex++)
                {
                    // get_range gives the object
                    // of a particular row from index1 to index2
                    Excel.Range range = workSheet.get_Range("A" + 
                             rowIndex.ToString(), "B" + rowIndex.ToString());

                    // Cells.Value2 returns the array of alements in that row
                    System.Array itemArray = (System.Array)range.Cells.Value2;


                    // Extracting the values from array
                    string key = itemArray.GetValue(1, 1) == null ? 
                                 string.Empty : 
                                 itemArray.GetValue(1, 1).ToString();
                    string value = itemArray.GetValue(1, 2) == null ? 
                                         string.Empty : 
                                         itemArray.GetValue(1, 2).ToString();

                    if (key.Trim() != "")
                    {
                        // Writing the values to .Net resource file
                        resWriter.AddResource(key, value);
                    }
                }             

                //pp
                // Colosing the Resource Writer. It is a necessary
                // step to make the changes permanent to a 
                // resorce file otherwise you could also call
                // Generate method in order to commit changes to 
                // file but Close does both tasks closing as well as commit.
                resWriter.Close();
                resWriter = null;
            }

            MessageBox.Show("Operation completed successfully.");
        } 
        else
        {
            MessageBox.Show("Input the excel file.");
        }
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    ExlObj.Application.Quit();
}

Points of Interest

The basic idea behind this code is to read the Excel file. The sample I presented here just fits my requirements, but you can do a lot with this with a little exploration. With a little modification to the code, you can export the Excel data to any programming object like a DataSet, Table, DataGrid etc.

History

• Posted on 15 Nov. 07.