Create Folders Using Data from Excel File

Download TankFolderCreation.rar

Introduction

The uploaded control provides several options for creating folders based on hierarchy. It has a user friendly interface build using windows forms, .NET and Microsoft Excel Libraries

Background

There was a need in our organization for creating folders from Excel file based on hierarchy. As an example of hierarchy, let's consider the folder structure we want to create for the administrative boundaries of Telangana (a state of India).

I have a sample Excel file of the administrative names of Districts, Mandals and Villages. Districts contain many mandals which together contain many villages. Therefore, my aim is to create all the village folder names corresponding to their mandal and district name. Here is a snapshot of the Excel file that is used to show as an example for the developed tool.

Using the Tool

The developed tool contains many parameters. First, browse to the Excel file and give as source input for the tool. After selecting the file, the column names of the Excel file are loaded into the dropdown list of the column section. Now choose the column on which you want to create the folders from the dropdown list and then click add to add them into the Columns for Generating Folders section. Here, the column names can be moved up and down as desired. If you want to visualize the data from the Excel sheet, then click on load data to load it into the listview. This section displays the column names along with the data.

Now in the end, click on Create Folders to create the folders. Here is a snapshot of the folder structure created.

Description of Code

For the part of reading the excel file. I have made use of the code that was already existing on one of the forums of Stack Overflow. Here's a link for the forum - http://stackoverflow.com/questions/7244971/how-do-i-import-from-excel-to-a-dataset-using-microsoft-office-interop-excel. The following code reads an excel file and stores the data into the datatable object.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace TankFolderCreation
{
    class ConvertXLStoDT
    {
        private StringBuilder errorMessages;

        public StringBuilder ErrorMessages
        {
            get { return errorMessages; }
            set { errorMessages = value; }
        }

        public ConvertXLStoDT()
        {
            ErrorMessages = new StringBuilder();
        }

        public System.Data.DataTable XLStoDTusingInterOp(string FilePath)
        {
            #region Excel important Note.
            /*
             * Excel creates XLS and XLSX files. These files are hard to read in C# programs. 
             * They are handled with the Microsoft.Office.Interop.Excel assembly. 
             * This assembly sometimes creates performance issues. Step-by-step instructions are helpful.
             * 
             * Add the Microsoft.Office.Interop.Excel assembly by going to Project -> Add Reference.
             */
            #endregion

            Microsoft.Office.Interop.Excel.Application excelApp = null;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;


            System.Data.DataTable dt = new System.Data.DataTable(); //Creating datatable to read the content of the Sheet in File.

            try
            {

                excelApp = new Microsoft.Office.Interop.Excel.Application(); // Initialize a new Excel reader. Must be integrated with an Excel interface object.

                //Opening Excel file(myData.xlsx)
                workbook = excelApp.Workbooks.Open(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);

                Microsoft.Office.Interop.Excel.Range excelRange = ws.UsedRange; //gives the used cells in sheet

                ws = null; // now No need of this so should expire.

                //Reading Excel file.               
                object[,] valueArray = (object[,])excelRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);

                excelRange = null; // you don't need to do any more Interop. Now No need of this so should expire.

                dt = ProcessObjects(valueArray);

            }
            catch (Exception ex)
            {
                ErrorMessages.Append(ex.Message);
            }
            finally
            {
                #region Clean Up
                if (workbook != null)
                {
                    #region Clean Up Close the workbook and release all the memory.
                    workbook.Close(false, FilePath, Missing.Value);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    #endregion
                }
                workbook = null;

                if (excelApp != null)
                {
                    excelApp.Quit();
                }
                excelApp = null;

                #endregion
            }
            return (dt);
        }

        /// <summary>
        /// Scan the selected Excel workbook and store the information in the cells
        /// for this workbook in an object[,] array. Then, call another method
        /// to process the data.
        /// </summary>
        private void ExcelScanIntenal(Microsoft.Office.Interop.Excel.Workbook workBookIn)
        {
            //
            // Get sheet Count and store the number of sheets.
            //
            int numSheets = workBookIn.Sheets.Count;

            //
            // Iterate through the sheets. They are indexed starting at 1.
            //
            for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
            {
                Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];

                //
                // Take the used range of the sheet. Finally, get an object array of all
                // of the cells in the sheet (their values). You can do things with those
                // values. See notes about compatibility.
                //
                Range excelRange = sheet.UsedRange;
                object[,] valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                //
                // Do something with the data in the array with a custom method.
                //
                ProcessObjects(valueArray);
            }
        }
        private System.Data.DataTable ProcessObjects(object[,] valueArray)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            #region Get the COLUMN names

            for (int k = 1; k <= valueArray.GetLength(1); k++)
            {
                dt.Columns.Add((string)valueArray[1, k]);  //add columns to the data table.
            }
            #endregion

            #region Load Excel SHEET DATA into data table

            object[] singleDValue = new object[valueArray.GetLength(1)];
            //value array first row contains column names. so loop starts from 2 instead of 1
            for (int i = 2; i <= valueArray.GetLength(0); i++)
            {
                for (int j = 0; j < valueArray.GetLength(1); j++)
                {
                    if (valueArray[i, j + 1] != null)
                    {
                        singleDValue[j] = valueArray[i, j + 1].ToString();
                    }
                    else
                    {
                        singleDValue[j] = valueArray[i, j + 1];
                    }
                }
                dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
            }
            #endregion


            return (dt);
        }
    }
}

The above class can be used in the following way to fetch data from excel into the DataTable:

ConvertXLStoDT test = new ConvertXLStoDT();
DataTable dt = test.XLStoDTusingInterOp(fileName);

Create folder command reads data from the ListView control to create folders. So first we need to load data into the listview control. Only datacolumns that are added into the "Columns for Generating Folders" section are loaded into the listview control. For this, the following code snipet is used.

private void btnLoadData_Click(object sender, EventArgs e)
{
    listView1.Clear();

    try
    {
        if (lstSelectedColumns.Items.Count > 0)
        {
            string strFields = null;
            for (int i = 0; i < lstSelectedColumns.Items.Count; i++)
            {
                strFields += lstSelectedColumns.Items[i] + ",";
            }
            strFields = strFields.TrimEnd(',');
            StringBuilder sbCreateSqlStatement = new StringBuilder();
            DataSet ds = GetDataFromExcel(txtFolderPath.Text);
            string strColumns = null, objValue = null;

            List<string> strRemoveColumns = new List<string>();


            for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
            {
                DataColumn myColumn = ds.Tables[0].Columns[column];
                strColumns = myColumn.ToString().Replace(" ", "");

                if (lstSelectedColumns.Items.Contains(strColumns))
                {
                }
                else
                {
                    ds.Tables[0].Columns[column].ColumnMapping = MappingType.Hidden;
                    strRemoveColumns.Add(strColumns);
                    //mydatatable.Columns["Colname"].ColumnMapping = MappingType.Hidden;
                }
            }

            foreach (string colName in strRemoveColumns) // Loop through List with foreach
            {
                ds.Tables[0].Columns.Remove(colName);
                //break;
            }

            for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
            {
                DataColumn myColumn = ds.Tables[0].Columns[column];
                strColumns = myColumn.ToString().Replace(" ", "");
                listView1.Columns.Add(strColumns, -2, HorizontalAlignment.Left);
            }
            listView1.Columns.Add("SNo", -2, HorizontalAlignment.Left);

            for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
            {
                strColumns = null;
                objValue = null;
                for (int column = 0; column < ds.Tables[0].Columns.Count; column++)
                {
                    DataColumn myColumn = ds.Tables[0].Columns[column];
                    DataRow myRow = ds.Tables[0].Rows[row];
                    objValue += myRow[myColumn].ToString() + ",";

                }
                objValue += row;
                string[] myItems = objValue.Split(',');
                listView1.Items.Add(GetItem(myItems));
            }

            listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
            listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);

        }
        else
        {
            MessageBox.Show("Select column(s) from the columns dropdown list");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

private ListViewItem GetItem(string[] myItems)
{
    ListViewItem item1 = new ListViewItem(myItems[0]);
    for (int i = 1; i < myItems.Length - 1; i++)
    {
        item1.SubItems.Add(myItems[i]);
    }
    item1.SubItems.Add(myItems[myItems.Length - 1]);
    return item1;
}

Finally, the following code is used for creating the folders. the code loops through all the ListViewItems to generate the path variable. Then Directory.CreateDirectory() object of System.IO is used for creating the folders.

private void btnCreateDir_Click(object sender, EventArgs e)
{
    if (lstSelectedColumns.Items.Count > 0)
    {
        if (listView1.Items.Count > 0)
        {
            if (txtDestinationDir.Text.Length > 0)
            {
                foreach (ListViewItem itemRow in this.listView1.Items)
                {
                    string strDirPath = null;
                    for (int i = 0; i < itemRow.SubItems.Count - 1; i++)
                    {
                        strDirPath += "\\" + itemRow.SubItems[i].Text;
                    }
                    Directory.CreateDirectory(txtDestinationDir.Text + strDirPath);
                }
                MessageBox.Show("Finished creating folders.");
            }
            else
            {
                MessageBox.Show("Select the path to destination directory");
            }
        }
        else
        {
            MessageBox.Show("Load list view before creating directories");
        }
    }
    else
    {
        MessageBox.Show("Select column(s) from the columns dropdown list");
    }
}