Click here to Skip to main content
15,868,025 members
Articles / Programming Languages / C#

Microsoft Excel File to .NET Resource File Conversion

Rate me:
Please Sign up or sign in to vote.
3.07/5 (6 votes)
16 Oct 2007CPOL2 min read 39.4K   593   11   9
This article shows how to convert an Excel file to a .NET resource file.

Screenshot - 1.jpg

Fig. 1

Image 2

Fig. 2

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:

C#
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:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionError message Pin
v_man8-Aug-22 5:50
v_man8-Aug-22 5:50 
QuestionResource file Pin
Member 24544006-Jan-13 16:04
Member 24544006-Jan-13 16:04 
QuestionError Message Pin
kopradip226329-May-12 2:22
kopradip226329-May-12 2:22 
GeneralError Message Pin
kopradip226329-May-12 2:19
kopradip226329-May-12 2:19 
GeneralMy vote of 4 Pin
Ba Nyar Thein16-Jan-12 19:24
Ba Nyar Thein16-Jan-12 19:24 
GeneralThank you Pin
stevenpkent29-Jan-09 8:26
stevenpkent29-Jan-09 8:26 
Generaldesigner created Pin
jarajeshwaran25-Dec-07 12:18
jarajeshwaran25-Dec-07 12:18 
Generalhi this is anand Pin
jarajeshwaran25-Dec-07 12:11
jarajeshwaran25-Dec-07 12:11 
GeneralThanks you very much - excellent work Pin
tbenami18-Dec-07 2:51
tbenami18-Dec-07 2:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.