Surendra Sharma

Surendra Sharma

Search This Blog

Thursday, June 14, 2012

Retrieve Excel Workbook Sheet Names


C# - Retrieve Excel Workbook Sheet Names



Originally posted on 

/// <summary>
/// This mehtod retrieves the excel sheet names from an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetsNames(string excelFile)
{

    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String. Change the excel file to the file you will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach (DataRow row in dt.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        // Loop through all of the sheets if you want too...
        for (int j = 0; j < excelSheets.Length; j++)
        {
            // Query each excel sheet.
        }

        return excelSheets;
    }
    catch (Exception ex)
    {
        return null;
    }
    finally
    {
        // Clean up.
        if (objConn != null)
        {
            objConn.Close();
            objConn.Dispose();
        }
        if (dt != null)
        {
            dt.Dispose();
        }
    }
}

Wednesday, June 6, 2012

How to deploy web application that is using SAMS certificate to authenticate site


How to deploy web application that is using SAMS certificate to authenticate site



Permissions
1.       For Windows 2008 server, grant ‘read/write’ permission to user everyone for folder ‘C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys’.
2.       For Windows 2003 server, grant ‘read/write’ permission to user everyone for folder ‘C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys’.
3.       Allow firewall to access URL ‘https: //api.sams.itm.test.semantico.net’.  
Installation of client certificate in Personal folder

1.       Open a Command Prompt window.
2.       Type mmc and press the ENTER key. Note that to view certificates in the local machine store; you must be in the Administrator role.
3.       On the File menu, click Add/Remove Snap In.
4.       Click Add.
5.       In the Add Standalone Snap-in dialog box, select Certificates.
6.       Click Add.
7.       In the Certificates snap-in dialog box, select Computer account and click Next.
8.       Now highlight personal
9.       Right click all tasks import.
10.    Select p12 certificate given by Semantico.

Installation of client certificate in Trusted Root Certification Authorities folder

1.       Open a Command Prompt window.
2.       Type mmc and press the ENTER key. Note that to view certificates in the local machine store; you must be in the Administrator role.
3.       On the File menu, click Add/Remove Snap In.
4.       Click Add.
5.       In the Add Standalone Snap-in dialog box, select Certificates.
6.       Click Add.
7.       In the Certificates snap-in dialog box, select Computer account and click Next.
8.       Now highlight Trusted Root Certification Authorities
9.       Right click Certificates, all tasks import.
10.    Select p12 certificate given by Semantico


Installation of MSXML4

Tuesday, July 27, 2010

Add or Remove items from windows Combobox

public class ComboBoxItem

{

  
    private string display;

    private object data;

   
    public ComboBoxItem(string display, object data)


    {

      this.display = display;

      this.data = data;

    }

   
    public override string ToString()


    {

      return display;

    }


}





private
void Form1_Load(object sender, EventArgs e)

{

    comboBox1.Items.Add(new ComboBoxItem("a", 1));


    comboBox1.Items.Add(new ComboBoxItem("b", 2));

    comboBox1.Items.Add(new ComboBoxItem("c", 3));

    comboBox1.Items.Add(new ComboBoxItem("d", 4));

}

private void button1_Click(object sender, EventArgs e)


{

    comboBox1.Items.RemoveAt(2);

}

Exporting Grid view or data Grid to Excel ,Formatting cells with style sheet mso-number-format

When you export the gridview to Excel it looses it format. It means that maybe your gridview has string field which consisted of numbers say '002675'. But when you export the grid and see it in excel file you will find that the number changed to '2675'.

You can solve this issue using ‘mso-number-format’

e.Row.Cells[i].Attributes["style"] = "mso-number-format:\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)";
mso-number-format:"0" No Decimals
mso-number-format:"0\.00" 2 Decimals
mso-number-format:"mm\/dd\/yy" Date format
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 05/06/-2008
mso-number-format:"Medium Date" 05-jan-2008
mso-number-format:"Short Time" 8:67
mso-number-format:"Medium Time" 8:67 am
mso-number-format:"Long Time" 8:67:25:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
mso-number-format:"\0022£\0022\#\,\#\#0\.00" £12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " 2 decimals, negative numbers in red and signed
(1.86 -1.66)
mso-number-format:”\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)” Accounting Format –5,(5)

Master Page Fix Height


<
form id="form2" runat="server">

<div>

<table border="0" width="100%">

<tr>

<td colspan="2" height="150" bgcolor="gray" valign="middle"  align="center">

HEADER

</td>

</tr>

<tr>

<td width="20%" bgcolor="silver">SIDEBAR</td>

<td width="80%" height="400" bgcolor="white">

<asp:contentplaceholder id="ContentPlaceHolder1" runat="server">

</asp:contentplaceholder>

</td>

</tr>

<tr>

<td colspan="2" align="center" height="20" bgcolor="gray">

FOOTER

</td>

</tr>

</table>

</div>

</form>

Friday, June 4, 2010

Regex for allowing only Alphabetics, numbers, apostrophe, spaces, dash for string having length 20

ValidationExpression="^[0-9a-zA-Z''\-'' '\s]{1,20}$"

To Display some ListItem in different colour in ListBox in ASP.NET

foreach (ListItem objTempItem in ListBox2.Items)

{

   //Code to change background colour of listitem

   //objTempItem.Attributes.Add("style", "background-color: RED");



  //Code to change text or foreground colour of listitem

  objTempItem.Attributes.Add("style","color:RED");

}