Export Multiple Datasets to Multiple Excel sheets dynamically formatted according to the records Data Type

This might not be a ground breaking discovery but I was thinking I might be able to share this piece of code to someone who might need it.  As usual I did searched online first whether this is available but I haven’t seen something exactly similar yet so it’s a good time to share it.  This article is basically how to Export Multiple Datasets to multiple Excel sheets formatted according to data type, basically what the code does is what the title implies, it will be a 1 is to 1 relationship between a dataset and worksheet which means if you pass 10 datasets there will be 3 worksheets and columns will be formatted according to the datatype of the record, this is created as generic as possible so you can just copy and paste the codes.

So this sample will only consist of one method and here it is:

using Microsoft.Office.Interop.Excel;

You need Office Interop as a reference then lets do the coding

public void DataSetsToExcel(List<DataSet> dataSets, string fileName)
{
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    Sheets xlSheets = null;
    Worksheet xlWorksheet = null;

    foreach (DataSet dataSet in dataSets)
    {
        System.Data.DataTable dataTable = dataSet.Tables[0];
        int rowNo = dataTable.Rows.Count;
        int columnNo = dataTable.Columns.Count;
        int colIndex = 0;

        //Create Excel Sheets
        xlSheets = xlWorkbook.Sheets;
        xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
        xlWorksheet.Name = dataSet.DataSetName;

        //Generate Field Names
        foreach (DataColumn dataColumn in dataTable.Columns)
        {
            colIndex++;
            xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
        }

        object[,] objData = new object[rowNo, columnNo];

        //Convert DataSet to Cell Data
        for (int row = 0; row < rowNo; row++)
        {
            for (int col = 0; col < columnNo; col++)
            {
                objData[row, col] = dataTable.Rows[row][col];
            }
        }

        //Add the Data
        Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
        range.Value2 = objData;

        //Format Data Type of Columns 
        colIndex = 0;
        foreach (DataColumn dataColumn in dataTable.Columns)
        {
            colIndex++;
            string format = "@";
            switch (dataColumn.DataType.Name)
            {
                case "Boolean":
                    break;
                case "Byte":
                    break;
                case "Char":
                    break;
                case "DateTime":
                    format = "dd/mm/yyyy";
                    break;
                case "Decimal":
                    format = "$* #,##0.00;[Red]-$* #,##0.00";
                    break;
                case "Double":
                    break;
                case "Int16":
                    format = "0";
                    break;
                case "Int32":
                    format = "0";
                    break;
                case "Int64":
                    format = "0";
                    break;
                case "SByte":
                    break;
                case "Single":
                    break;
                case "TimeSpan":
                    break;
                case "UInt16":
                    break;
                case "UInt32":
                    break;
                case "UInt64":
                    break;
                default: //String
                    break;
            }
            //Format the Column accodring to Data Type
            xlWorksheet.Range[xlApp.Cells[2, colIndex], xlApp.Cells[rowNo + 1, colIndex]].NumberFormat = format;
        }
    }

    //Remove the Default Worksheet
    ((Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();

    //Save
    xlWorkbook.SaveAs(fileName,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        XlSaveAsAccessMode.xlNoChange,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value);

    xlWorkbook.Close();
    xlApp.Quit();
    GC.Collect();
}

If you notice some datasets is formatted by default, I just added all of the different datatypes of a dataset for you to add your own formatting.  To use the code is will be as simple like this.

DataSet dataSet1 = new DataSet("My Data Set 1");
dataAdapter1.Fill(dataSet1);

DataSet dataSet2 = new DataSet("My Data Set 2");
dataAdapter1.Fill(dataSet2);

DataSet dataSet3 = new DataSet("My Data Set 3");
dataAdapter1.Fill(dataSet3);

List<DataSet> dataSets = new List<DataSet>();
dataSets.Add(dataSet1);
dataSets.Add(dataSet2);
dataSets.Add(dataSet3);

DataSetsToExcel(dataSets, "{Your File Name}")

Take note you have to name your DataSet and that will be the name of the worksheet in Excel.

Getting the default record type of a user for Event Creation in Salesforce

I have been twisting my head around in finding a solution for this simple task and I guess its worth while to share this information to everyone as I cannot search anything on Google or even in Developer Force any solution related to my problem. Eventually it ended up in calling their premier support for assistance, then after a week of emails sent back and forth the answer was:

“you cannot get the default record type for user without logging to salesforce.com”

what that means is that you cannot get the default record type for a user if you are using a privileged account, you must use the users login credentials when performing the query, which defeats the purpose of having a super user account creating events for you programatically.

Anyway here is what I initially done to get the default record type, At first they suggested this solution in Java

Schema.DescribeSObjectResult oSObjectResult = Event.SObjectType.getDescribe();
ListRecordTypeInfo> oRecTypeInfos = oSObjectResult.getRecordTypeInfos();

Schema.RecordTypeInfo has a method isDefaultRecordTypeMapping()

So I used that information to grab what I want in C#.Net, so I created a method to extract that information

public SforceService Authenticate()
{
    try
    {
        SforceService oSalesForceService = new SforceService();
        oSalesForceService.Timeout = 60000;

        // Set Proxy Details if you are using one
        WebProxy oWebProxy = new WebProxy(WebRequest.DefaultWebProxy.GetProxy(new Uri(oSalesForceService.Url.ToString())));
        oWebProxy.Credentials = CredentialCache.DefaultCredentials;
        oWebProxy.UseDefaultCredentials = true;
        oSalesForceService.Proxy = oWebProxy;

        //Initialize SalesForce Service
        LoginResult oLoginResult = oSalesForceService.login(sUserName, string.Concat(sPassword, sToken));

        oSalesForceService.Url = oLoginResult.serverUrl;
        oSalesForceService.SessionHeaderValue = new SessionHeader();
        oSalesForceService.SessionHeaderValue.sessionId = oLoginResult.sessionId;
        GetUserInfoResult oUserInfo = oLoginResult.userInfo;

        return oSalesForceService;
    }
    catch (Exception ex)
    {
        return null;
    }
}

public string GetRecordType()
{
    SforceService oSalesForceService = Authenticate();

    string sRecordTypeId = "";

    DescribeSObjectResult oObjectResult = oSalesForceService.describeSObject("Event");
    List<RecordTypeInfo> oRecTypeInfos = oObjectResult.recordTypeInfos.ToList();
    foreach (RecordTypeInfo oRecTypeInfo in oRecTypeInfos)
    {
        if (oRecTypeInfo.defaultRecordTypeMapping == true)
        {
            sRecordTypeId = oRecTypeInfo.recordTypeId;
        }
    }
    return sRecordTypeId;
}

Using that will be an issue as it is not showing options on filtering it by Owner ID like what SalesForce have in other tables, so the result shown after I executed the method is the default record type of the privileged Account.  So I thought I can go directly to the related tables perform SOQL queries like such

oQueryResult = oSalesForceService.query("Select Id, Name from RecordType where SobjectType = 'Event' and IsActive = True and OwnerID = '" + sOwnerID +"'");

and might have some hope in there but after checking the RecordType and RecordTypeInfo there is not field for Owner ID

and in User class there are no fields for Default record type.

So the only real solution for this as of this post date is to keep a local copy of the default record type per user or as a Global setting if you wish.   So when I create event I assign the RecordTypeId by getting it by the “Record Type Name” defaulted to a user which is stored locally.

oEvent.RecordTypeId = GetRecordTypeId(sUserDefaultRecordType);

public string GetRecordTypeId(string sRecordTypeName)
{
    SforceService oSalesForceService = Authenticate();

    QueryResult oQueryResult = null;

    oSalesForceService.QueryOptionsValue = new QueryOptions();
    oQueryResult = oSalesForceService.query("Select Id from RecordType where SobjectType = 'Event' and IsActive = True and Name = '" + sRecordTypeName + "'");

    if (oQueryResult.size != 0)
    {
        RecordType oRecordType = (RecordType)oQueryResult.records[0];
        return oRecordType.Id;
    }
    return null;
}

I hoped I helped someone out there with a similar situation as I have

Single ClientValidationFunction for Multiple Dynamic CustomValidator and CheckBox

I have been using CheckBoxes and CustomValidators for quite sometime now but never did I know that I will be presented with an issue that I never encountered before and that issue is regarding is dynamic CheckBoxes and dynamic CustomValidators.  To give you an idea on what I am talking about, if you had developed a website that has that “Agree to Terms and Conditions” required before continuing then your guess is a bit near the only difference is making the items dynamic where these questions are pulled from the database, that database table also defines whether the tick is required or not.

For the first scenario (non dynamic) its easy as you only need these controls

<asp:CheckBox ID="CheckBox1" runat="server" />
<asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="CustomValidator"
ClientValidationFunction="ValidateCheckbox"></asp:CustomValidator>

and this javascript to be consumed by the CustomValidator

<script language="JavaScript" type="text/javascript">
function ValidateCheckbox(source, args) {
    args.IsValid = args.IsValid = document.getElementById('<%= CheckBox1.ClientID %>').checked;
}
</script> 

But what if you have 50 checkboxes will you create one script for each one of them? Wouldn’t it be nice to just pass a parameter to your javascipt? By default you cannot do that as the javascript that the CustomValidator uses only 2 parameters which is the source and args.  But don’t worry we can use RegisterExpandoAttribute Method to extend an objects attribute.

So what does that method do? The answer is simple it registers a name/value pair as a custom attribute of the specified control.   Having that in mind we use it in the CustomValidator and extend its attributes to have the ClientID of the checkbox you want to validate. So for clarity refer to the example below.

So lets say we have the following controls that is dynamically driven by the GridVeiw

<asp:GridView ID="myGridView" runat="server" AutoGenerateColumns="False" OnRowDataBound="myGridView_RowDataBound">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="lblQuestionItem" runat="server" Text='<%# Bind("QuestionItem") %>'
                    CssClass="Normal"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:HiddenField ID="hdnIsRequired" runat="server" Value='<%# Bind("IsRequired") %>' />
                <asp:CheckBox ID="chkAnswer" runat="server" CssClass='<%# Bind("ControlClass") %>' />
                <asp:CustomValidator ID="ctmCHKAnswer" ClientValidationFunction="ValidateCurrentCheckbox"
                    runat="server" ErrorMessage="*"></asp:CustomValidator>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Now from the sample above we defined a CustomValidator and that’s the object that we will extend the attributes to contain the chkAnswer‘s client ID, to do that here is the code behind, also remember that you need to do that on a per row basis as well thats why it is in myGridView_RowDataBound, and here is how to achieve it.

protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        HiddenField hdnIsRequired = (HiddenField)e.Row.FindControl("hdnIsRequired");
        CheckBox chkAnswer = (CheckBox)e.Row.FindControl("chkAnswer");
        CustomValidator ctmCHKAnswer = (CustomValidator)e.Row.FindControl("ctmCHKAnswer");

        Page.ClientScript.RegisterExpandoAttribute(ctmCHKAnswer.ClientID, "ClientID", chkAnswer.ClientID, false);
        ctmCHKAnswer.Enabled = bool.Parse(hdnIsRequired.Value);
    }
}

If you noticed we extended ctmCHKAnswer with the “ClientID” attribute that comes from chkAnswer.ClientID.  So when you fire the JavaScript you can then now use the source parameter and use source.ClientID to get the checkbox client ID you need.

<script language="JavaScript" type="text/javascript">
function ValidateCurrentCheckbox(source, args) {
    var checkbox = document.getElementById(source.ClientID)
    args.IsValid = checkbox.checked;
}
</script>