Dailycode.info

Short solution for short problems

Inserting functions from .net into Excel. (VLOOKUP) "Exception from HRESULT: 0x800A03EC"

I got a small task to write a program that takes single language excel sheets and makes them multilanguage.
To accomplisch this we added a translation worksheet that will be filled with all translations of text in de excel worksheets for 4 different languages.
Then the text in the excel will be replaced with a VLOOKUP function that looks up the string in the Translation worksheet.
The program was ready in half a day, but then Excel strarted giving me a hard time.
If I tried to insert the VLOOKUP functino from code, it kept giving me an error. Inserting strings or even a SUM function was no problem, but the VLOOKUP wouldn't work. The functino looks like this: =VLOOKUP("Bath / Object";TRANSLATIONS;SPRACHE;FALSE).
This code was trying to insert the code in the cell where the text Bath / Object was:
 
xlApp.Worksheets.get_Item(index).Select();
string value = String.Format(@"=VLOOKUP(""{0}"";TRANSLATIONS;SPRACHE;FALSE)", str);
rangeSet.FormulaR1C1 = value;
First I tried all kinds of things to replace the quotes, but it didn't help. I tried setting the Value or Value2, Formula etc but it kept ginving me the error. Finally after a long search I recorded a macro in Excel and edited this macro and swaw that the Macro was using comma's (,) instead of colons (;).
So I changed the code looking like this and it works!!!!
xlApp.Worksheets.get_Item(index).Select();
string value = String.Format(@"=VLOOKUP(""{0}"",TRANSLATIONS,SPRACHE,FALSE)", str);
rangeSet.FormulaR1C1 = value;
The Nice error I got was: Exception from HRESULT: 0x800A03EC. Try to figure this out!

Calculate the standard deviation

These functions help you to calculate the standard deviation for a list of values. The flow is simple, first calculate the average, then the total variance and return the square root of the total variance divided by the number of values.

/// <summary>

/// Returns the standard deviation for a row of values

/// </summary>

/// <param name="data">Row of values to calculate the standard deviation from</param>

/// <returns>STDev</returns>

public static double StandardDeviation(double[] data)

{

    double ret = 0;

    double DataAverage = 0;

    double TotalVariance = 0;

    int Max = 0;

 

    try

    {

 

        Max = data.Length;

 

        if (Max == 0) { return ret; }

 

        DataAverage = Average(data);

 

        for (int i = 0; i < Max; i++)

        {

            TotalVariance += Math.Pow(data[i] - DataAverage, 2);

        }

 

        ret = Math.Sqrt(SafeDivide(TotalVariance, Max));

 

    }

    catch (Exception) { throw; }

    return ret;

}

 

 

/// <summary>

/// Divides 2 numbers, if error occurs, return 0, if one of the numbers = 0, returns 0

/// </summary>

/// <param name="value1">Value 1</param>

/// <param name="value2">Value 2</param>

/// <returns>Result or in case of error 0</returns>

public static double SafeDivide(double value1, double value2)

{

 

    double ret = 0;

 

    try

    {

 

        if ((value1 == 0) || (value2 == 0)) { return ret; }

 

        ret = value1 / value2;

 

    }

    catch { }

    return ret;

}

 

 

/// <summary>

/// Calculated the average for a list of values

/// </summary>

/// <param name="data">Values</param>

/// <returns>Average</returns>

public static double Average(double[] data)

{

    //double ret = 0;

    double DataTotal = 0;

 

    try

    {

 

        for (int i = 0; i < data.Length; i++)

        {

            DataTotal += data[i];

        }

 

        return SafeDivide(DataTotal, data.Length);

 

    }

    catch (Exception) { throw; }

}


Get file location from a user

I made a small function to reduce the code you write when you want to prompt the user for a file location.

Here the result:

public static string GetLocationFromUser(string text, string defaultExt, string filterExp)

{

    // Select a file     OpenFileDialog dlgOpenFile = new OpenFileDialog();  

    dlgOpenFile.Title = text;

    if (!String.IsNullOrEmpty(defaultExt))

    {

        dlgOpenFile.DefaultExt = defaultExt;

        if (String.IsNullOrEmpty(filterExp))

        {

            dlgOpenFile.Filter = "(*." + defaultExt + ")|*." + defaultExt + "|All files (*.*)|*.*";

        }

    }

    if (!String.IsNullOrEmpty(filterExp))

    {

        dlgOpenFile.Filter = "comma separated files (*.csv)|*.csv|All files (*.*)|*.*";

        dlgOpenFile.FilterIndex = 1;

    }

    dlgOpenFile.RestoreDirectory = true;

    //Only when OK has been clicked then execute next code ...

    if(dlgOpenFile.ShowDialog() == DialogResult.OK)

    {

        return dlgOpenFile.FileName;

    }

    else

    {

        return String.Empty;

    }

}

 

 

 


General function: Check for leading zero's

Here a function I end up putting in the general functions library. I bumped into into this problem more then once and decided to add it to our general library.

Remember this one, it can come in handy some time.

public static string CheckForDigits(string p, int length)
{
if (p.Length < length)
      {
            for (int i = p.Length; i < length; i++)
            {
                  p = "0" + p;
}
}
      return p;
}

 

Examples:

CheckForDigits('try1234',8) returns 0try1234

CheckForDigits('try12',8) returns 000try12

You can also use the ToString('00000000') on a string to add leading zero's. So if you have a string test = '123' and you use test.ToString('00000000') then you get: '00000123'. But when you want to use this function on a number that is 24 digits, it gets hard, so then it is easier to use the CheckForDigits function. Also the padleft function can be used:

PadLeft(8,'0');

Now it gets really interesting when you have comma or point seperated numbers that need to be checked before and ofter the split sign. For this I created this variant:

/// <summary>

/// Checks a string for digits before and after the split sign

/// If the split sign is not present, it will add only digits up to the "numbersBeforeComma" in front is needed

/// </summary>

/// <remarks>

/// Given p = 25,2 -> numbersBeforeComma = 3 and numbersAfterComma = 3 and splitSign = ,

/// Then the result will be 025,200

/// </remarks>

/// <param name="p">Unformatted string</param>

/// <param name="numbersBeforeComma">Number of digits before the spit sign</param>

/// <param name="numbersAfterComma">Number of digits after the spit sign</param>

/// <param name="splitSign">The sign that splits the string</param>

/// <returns>Formatted string</returns>

public static string CheckForDigitsSplit(string p, int numbersBeforeComma, int numbersAfterComma, string splitSign)

{

    int splitSpot = p.IndexOf(splitSign);

    if (splitSpot > -1)

    {

        string before = p.Substring(0, splitSpot);

        before = CheckForDigits(before, numbersBeforeComma);

        string after = p.Substring(splitSpot + 1);

        after = CheckForDigitsFromRight(after, numbersAfterComma);

        return before + "." + after;

    }

    else

    {

        string before = CheckForDigits(p, numbersBeforeComma);

        return before;

    }

}


How to count string in string

I was looking for a c# function to count the number of occurrences of a string in a string. But I couldn't find any by default.

So I wrote my own little function. Migth come in handy!

In this scenario I had a string that contains line feeds, I had to know how many to correctly calculate the length of the string. Here is the function that does the work:

public static int Count(string src, string find)
        {
            int ret = 0;
            int len = find.Length;
            for (int i=0; i < src.Length-len;i++)
            {
                if (src.Substring(i,len) == find)
                {
                    ++ret;
                }
            }
            return ret;
        }

So I was looking for "\r\n" in a string:

int countNewLines = Count(day.Remark,"\r\n");
 

It's Simple and easy, any person could find this, but I like to blog it so next time I won't have to look for it again.

This improved function by feedback of Tom is more performant:

private int countOccurences(string text, string toFind)
{
  return (toFind.Length > 0) ? 
  (text.Length - text.Replace(toFind, string.Empty).Length) / toFind.Length : 0;
}
 

I'll explain what happens: it returns the length of the text minus the lenght of the text without the string that you are looking for divided by the length of the search string.

Eg. text = aaaabgaabg to find = bg. string lenght = 10 - string lenght without bg = 6 makes 4. We divide 4 by the length of the search string bg = 2 makes 2 occurences: (10 - 6) /2=2