Excel file to SQL Server: (Part 1) Prepare spreadsheet for import to SQL Server

The following function opens the excel file, does few fixes needed for export and saves it in the new location. I will go through each fix in the comments of the code.

The biggest challenge that exporting excel spredsheets I've encountered is that sometimes data could be in different types. For example, if the first 8 rows in excel sheet start with numbers then column is treated as a number type, and if you have a text field in the column it would get imported as null.

To overcome this design, in my opinion is to make sure all data is imported as text, that way there are no null imported if datatypes don't match.

We are going to be importing data into SQL Server

Here is the flow of things in the function:
1. Open worksheet
2. Change formatting on all cells
a. Select Cells A1: CC60000 (this could be any range) and Unmerge cells.
b. Change number formats on all cells to have 2 digits.
c. Value paste all cells
3. If your data doesn't necessary start at the A1:A1 position, we need to figure out where does the column heading begin. Once we know the positon of the begging of the data, we remove all rows and columns before the column, so our data starts at A1:A1. To do that we take a sample of the first 10 columns and 50 rows and look for the First column name to match the string. When found we delete all rows and cells before that position.
4. Next we need to make sure everything is imported like text, so we set values of the first rows to some dummy text data. That was excel treats all columns as text and imports everything as text without cutting out any data. (When you are doing the import
5. We create a temp directory
6. Save the worksheet in the new location.

/// <summary>
/// Excel: Prepares worksheet by deleting extra rows and columns before the specified column name
/// </summary>
/// <param name="fullFileName">Full name of the file.</param>
/// <param name="resultPath">The result path.</param>
/// <param name="wrkshtName">Name of the worksheet.</param>
/// <param name="FirstColumnName">First name of the column.</param>
/// <returns></returns>
public static bool ExcelGetDatabyFirstColumn(string fullFileName, string resultPath, string wrkshtName, string FirstColumnName)
{
    bool success = true;
    int[] position = new int[2];
    try
    {
        GC.Collect();
        Excel.Application app = new Excel.Application();
        Object missing = (Object)Type.Missing;
        app.Visible = true;
        app.DisplayAlerts = false;
        //Open excel workbook as read only.
        Workbook wb =
            app.Workbooks.Open(fullFileName, false, true, missing, missing, missing, missing, missing, missing,
                               missing,
                               missing, missing, missing, missing, missing);

        //Open our workbook with specified name, this can also be an index number
        Worksheet ws = (Worksheet)wb.Worksheets[wrkshtName];
        //Declare range object
        Excel.Range rng;
        ws.Select(missing);
        ws.AutoFilterMode = false;
        //Prepare sheet (umerge cells and value paste numbers)
        rng = ws.get_Range("A1", "CC60000");
        rng.UnMerge();
        //Change number formats on all cells to have 2 digits.
        rng.NumberFormat = "0.00";
        //Value paste all cells 
        rng.Copy(missing);
        rng.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, missing,
                         missing);

        //Set sample range to find the column name 10 x 50
        rng = ws.get_Range("A1", "J50");
        object[,] arr = new object[10, 50];
        arr = (object[,])rng.get_Value(missing);
        //Find column in the array and get position
        for (int i = 1; i < 11; i++)
        {
            for (int y = 1; y < 51; y++)
            {
                string cell = Convert.ToString(arr[y, i]);
                if (cell == FirstColumnName)
                {
                    position[0] = i;
                    position[1] = y;
                }

            }
        }
        //Unless column names already at the top delete needed rows and columns.

        if (position[0] != 1)
        {
            rng = ws.get_Range("A1", missing);
            rng = rng.get_Resize(1, position[0] - 1);
            rng.EntireColumn.Delete(missing);
        }
        if (position[1] != 1)
        {
            rng = ws.get_Range("A1", missing);
            rng = rng.get_Resize(position[1] - 1, 1);
            rng.EntireRow.Delete(missing);
        }

        //Insert 8 rows after first Column headings
        rng = ws.get_Range("A2", "A9");
        rng.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, missing);

        //Fill first 8 rows with some text
        string[,] dummyData = new string[8, 40];
        for (int x = 0; x < 8; x++)
        {
            for (int i = 0; i < 40; i++)
            {
                dummyData[x, i] = "text";
            }
        }
        rng = ws.get_Range("A2", missing);
        rng = rng.get_Resize(8, 40);
        rng.set_Value(missing, dummyData);


        SetUpTempDir(resultPath);

        //Save the worksheet
        wb.SaveAs(resultPath, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        wb.Close(missing, missing, missing);
        app.Workbooks.Close();
        app.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
        ws = null;
        wb = null;
        app = null;
        success = true;

    }
    catch (Exception ex)
    {
        success = false;
        string errString = "Excel Error:" + ex.Message;
        Console.Write(errString);
    }
    finally
    {
        GC.Collect();
    }
    return success;
}