Socal Media Productions

 private void ExportGridView()
        {

           Excel.Application m_objExcel = new Excel.Application();
           Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
           Excel._Workbook m_objBook = (Excel._Workbook)(m_objBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet));
           Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
           Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));     

            try
            {
                int x;
                int y;
                int c;
                int cc = this.dataGridView1.ColumnCount;
                int rc = this.dataGridView1.RowCount;
                for (c = 0; c < cc; c++)
                {
                    m_objSheet.Cells[1, c + 1] = this.dataGridView1.Columns[c].HeaderText;
                }
                for (x = 0; x < (rc - 1); x++)
                {
                    for (y = 0; y < cc; y++)
                    {
                        m_objSheet.Cells[x + 2, y + 1] = this.dataGridView1.Rows[x].Cells[y].Value.ToString();
                    }
                }
                m_objExcel.Visible = true;
                m_objExcel.UserControl = true;
               
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            MessageBox.Show("Data Exported");
          

        }

I got this error during some win form development.  Check all of you reference paths, naviation links, etc.

I tried to call a dll in my navigation that was misspelled.

This took me a while to figure this out but if you use CSS to govern the styles of a gridview in .net then you may know that the styles will not export into excel without a little help.

If you want to export a gridview into excel your function may look something like this:

  private void ExportGridView(GridView gv)

    {

        string xlsfilename = "Name.xls";

        string attachment = "attachment; filename=" + xlsfilename;

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);

 

 

        bool allowSorting = gv.AllowSorting; // save the current AllowSorting setting

        gv.AllowSorting = false;

        gv.RenderControl(htw);

        gv.AllowSorting = allowSorting; // restore the current AllowSorting setting

    

        Response.Write(sw.ToString());

        Response.End();

       

    }

You can call on this function by executing a button cmd like this:

protected void Btn_Click(object sender, ImageClickEventArgs e)

    {

        //Export the GridView to Excel

        PrepareGridViewForExport(GridView1);

        ExportGridView(GridView1);

    }

The PrepareGridViewForExport(GridView1); line is a function to handle objects in the gridveiw, but I’ll leave that out of this post. Now back to the main topic.  The styles of this grid will not show up in excel because the office application takes the html written from the webpage and places it into a worksheet.  If that page makes calls to styles in another file then office does not have the details of those styles in the string it is passed.  To add those detail we just need to pass excel the styles in a string.

 

Here’s how it works:

Add the stylesheet to a streamreader object:

StreamReader sr = new StreamReader(Server.MapPath("Name.css"));

        string s = sr.ReadToEnd();

 

don’t forget to close the object:

sr.Close();

 

That’s it! Just add the opening and closing Head tags and Style tags to export script and Office will be able to read the details of the styles in the html string and apply them to the worksheet.

So your new function will look something like this:

 

private void ExportGridView(GridView gv)

    {

        string xlsfilename = "StatusFunds.xls";

        string attachment = "attachment; filename=" + xlsfilename;

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);

 

 

        bool allowSorting = gv.AllowSorting; // save the current AllowSorting setting

        gv.AllowSorting = false;

        gv.RenderControl(htw);

        gv.AllowSorting = allowSorting; // restore the current AllowSorting setting

 

        //read the stylesheet to include the format with the export

        StreamReader sr = new StreamReader(Server.MapPath("Name.css"));

        string s = sr.ReadToEnd();

       

        Response.Write("<HEAD><STYLE>");

        Response.Write(s.ToString());

        Response.Write("</STYLE></HEAD>");

        Response.Write(sw.ToString());

        Response.End();

        sr.Close();

    }

 

Good luck!

These are so often hard to find so I am posting them here:

 

Metacharacter	Match
\	the escape character - used to find an instance of a metacharacter like a period, brackets, etc.
. (period)	match any character except newline
x	match any instance of x
^x	match any character except x
[x]	match any instance of x in the bracketed range - [abxyz] will match any instance of a, b, x, y, or z
| (pipe)	an OR operator - [x|y] will match an instance of x or y
()	used to group sequences of characters or matches
{}	used to define numeric quantifiers
{x}	match must occur exactly x times
{x,}	match must occur at least x times
{x,y}	match must occur at least x times, but no more than y times
?	preceding match is optional or one only, same as {0,1}
*	find 0 or more of preceding match, same as {0,}
+	find 1 or more of preceding match, same as {1,}
^	match the beginning of the line
$	match the end of a line

POSIX Class	Match
[:alnum:]	alphabetic and numeric characters
[:alpha:]	alphabetic characters
[:blank:]	space and tab
[:cntrl:]	control characters
[:digit:]	digits
[:graph:]	non-blank (not spaces and control characters)
[:lower:]	lowercase alphabetic characters
[:print:]	any printable characters
[:punct:]	punctuation characters
[:space:]	all whitespace characters (includes [:blank:], newline, carriage return)
[:upper:]	uppercase alphabetic characters
[:xdigit:]	digits allowed in a hexadecimal number (i.e. 0-9, a-f, A-F)

 

Character class	Match
\d	matches a digit, same as [0-9]
\D	matches a non-digit, same as [^0-9]
\s	matches a whitespace character (space, tab, newline, etc.)
\S	matches a non-whitespace character
\w	matches a word character
\W	matches a non-word character
\b	matches a word-boundary (NOTE: within a class, matches a backspace)
\B	matches a non-wordboundary

 

• \
  The backslash escapes any character and can therefore be used to force characters to be matched as literals instead of being treated as characters with special meaning. For example, '\[' matches '[' and '\\' matches '\'.
• .
  A dot matches any character. For example, 'go.d' matches 'gold' and 'good'.
• { }
  {n} ... Match exactly n times
  {n,} ... Match at least n times
  {n,m} ... Match at least n but not more than m times
• [ ]
  A string enclosed in square brackets matches any character in that string, but no others. For example, '[xyz]' matches only 'x', 'y', or 'z', a range of characters may be specified by two characters separated by '-'. Note that '[a-z]' matches alphabetic characters, while '[z-a]' never matches.
• [-]
  A hyphen within the brackets signifies a range of characters. For example, [b-o] matches any character from b through o.
• |
  A vertical bar matches either expression on either side of the vertical bar. For example, bar|car will match either bar or car.
• *
  An asterisk after a string matches any number of occurrences of that string, including zero characters. For example, bo* matches: bo, boo and booo but not b.
• +
  A plus sign after a string matches any number of occurrences of that string, except zero characters. For example, bo+ matches: boo, and booo, but not bo or be.
• \d+
  matches all numbers with one or more digits
• \d*
  matches all numbers with zero or more digits
• \w+
  matches all words with one or more characters containing a-z, A-Z and 0-9. \w+ will find title, border, width etc. Please note that \w matches only numbers and characters (a-z, A-Z, 0-9) lower than ordinal value 128.
• [a-zA-Z\xA1-\xFF]+
  matches all words with one or more characters containing a-z, A-Z and characters larger than ordinal value 161 (eg. ä or Ü). If you want to find words with numbers, then add 0-9 to the expression: [0-9a-zA-Z\xA1-\xFF]+

Typical examples

• (bo*)
  will find "bo", "boo", "bot", but not "b"
• (bx+)
  will find "bxxxxxxxx", "bxx", but not "bx" or "be"
• (\d+)
  will find all numbers
• (\d+ visitors)
  will find "3 visitors" or "243234 visitors" or "2763816 visitors"
• (\d+ of \d+ messages)
  will find "2 of 1200 messages" or "1 of 10 messages"
• (\d+ of \d+ messages)
  will filter everything from the last occurrence of "2 of 1200 messages" or "1 of 10 messages" to the end of the page
• (MyText.{0,20})
  will find "MyText" and the next 20 characters after "MyText"
• (\d\d.\d\d.\d\d\d\d)
  will find date-strings with format 99.99.9999 or 99-99-9999 (the dot in the regex matches any character)
• (\d\d\.\d\d\.\d\d\d\d)
  will find date-strings with format 99.99.9999
• (([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+))
  will find all e-mail addresses

thanks

Say you have a variable x and you need to pass it as a parameter in a SQL query and it has to be an INT data type. Just convert the variable to an INT using this syntax:

Convert.ToInt32(x.Value);

 

over and out

For handeling NULLS in a typed DataSet there are several solutions which are all combersome if the datatype is anything other than a string. 

In Visual Studio once you define the typed dataset you can adjust the properties of the column values by clicking on the column in the .xsd GUI.  This allows you to set the datatype and how to handle NullValue.  If the datatype is System.String then you can change the NullValue to empty(""), null(DBNull) or throw exception.  The exception displays an ugly error message in the browser and provides little information on how to correct the issue. (The value for column '*' in table '*' is DBNull) or (System.InvalidCastException: Specified cast is not valid.)

If your datatype is INT, Decimal, etc.. you have to use error handeling in the script to workaround this issue.  Ideally you would expect MS VS2005 to allow you to set the null value to (0), (0m), etc... but that bug is still not fixed. 

Some blogs suggest to change the .cs file to set the value using an if statement.  That will work but it will also get overwritten wehn you uspdate the file with another dataset. 

http://forums.microsoft.com/MSDN/ShowPost.aspx?
PostID=174108&SiteID=1

Fortunately when the Typed Dataset was created it generated a method to return a boolean value if the column data was Null.  So here is how you use it.

 

 define your table adapter:

xTableAdapter ds = new xtableAdapter;

Fill a datatable with the adapters resulting data:

databasename.xDataTable dt = ds.GetData();

Run throught the data and grab the values you need and change the Nulls into values you want.

foreach(databasename.xRow dr in dt)

{  decimal y;

//error check the column value first

    if (dr.Iscolumn1Null())

       { y = 0m;} else { y = dr.column1;}

}

 

That's it!  What sucks is that you have to use error checking in your script everytime you want to grab a column value in a typed dataset.  It should be handeled in the .xsd file.

 

 

When working with datasets and datatables we will run into issues where the resulting column values are null in the database.  These issues can cause your code to fail when you are using the values to make calculations or validations with the data.  The easiest way to handle this is to zero the null value using a custom method call.  The logic will also work with string values and here is how it works:

 object ZeroNull(object i, object defaultvalue)
    {
        if (i.Equals(DBNull.Value))
        {
            return defaultvalue;
        }
        else
        {
            return i;
        }
    }

add data to the dataset (see other blog for instructions)

DataSet ds = new DataSet
adapter.Fill(ds);

choose a row in the dataset to work with.

DataRow dr = ds.Rows[0];

Insert the column value into our new method to zero the null value if it is null otherwise it will return its actual value and cast it as a decimal.

(decimal)x = (decimal)ZeroNull(dr["columnName"], 0m);

 Good Luck

Here is how you will execute a stored procedure in the code behind script in .Net.  The parameter you pass to the procedure can come from controls, forms, defined variable, etc... In this example I will use the profile class to grab my values.

 

First step is to define the connection string.  Mine happens to be stored in the web.config file.

 

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["localconnstring"].
ConnectionString);

Define the command that used your stored procedure.

        SqlCommand cmd = new SqlCommand("stroredprocedure", conn);
        cmd.CommandType = CommandType.StoredProcedure;

Define the parameters.

        SqlParameter pr1 = new SqlParameter("@p1", SqlDbType.NVarChar, 50);
        pr1.Value = Profile.p1;//this is where you will set the value of the parameter passed to the query.  I happened to be calling on the profile for this value.
        cmd.Parameters.Add(pr1);

        SqlParameter pr2 = new SqlParameter("@p2", SqlDbType.NVarChar, 10);
        pr2.Value = Profile.p2;
        cmd.Parameters.Add(pr2);

And open the connection.

        conn.Open();

Thats it!  Now you can fill the resulting data into a dataset and bind it to a gridview.

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

      //  DataRow drow = ds.Tables[0].Rows[0]; use this to get after the column values.

         GridView1.DataSource = ds;
         GridView1.DataBind();

 

First step to fill a dataset is to define the sql connection.  The method below uses the definition in the web.config fie which looks like this:

<connectionStrings>
      <clear />
  <add name="LocalSqlServer" connectionString="Server=192.169.1.1;Initial Catalog=DatabaseName;User ID=username;Password=password;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

Once you have this defined in the config file then call on it by setting a string variable equal to the connectionstring.

String strCon1 = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].
ConnectionString;

Define a container for this connection string.

SqlConnection conn1 = new SqlConnection(strCon1);

Open the connection.

conn1.Open();

Next we set the select statement and put it into a string variable.

string sql1 = "SELECT [ApplicationId],[UserId],[UserName],[LoweredUserName],[MobileAlias],
[IsAnonymous],[LastActivityDate]
FROM [DatabaseName].[dbo].[table]
where [IsAnonymous] = '0' order by [LastActivityDate] desc";

Ok and now the magical adapter which will allow us to grab all of the data in the query and place it into an object that we can do some cool things with. Just set the adapter variable and execute a select command on it using the two string we just defined.

SqlDataAdapter dadapter = new SqlDataAdapter();
dadapter.SelectCommand = new SqlCommand(sql1, conn1);

Now we fill the dataset with all of the returned data from the select command.  A great feature with this method is that the table structure(schema) is retained in the new dataset.

DataSet dset = new DataSet();
dadapter.Fill(dset);

Now you can call on the values in the dataset by using the following syntax.
                     
DataRow dr = dset.Tables[0].Rows[i] ;
dr["userid"].tostring();

Another usefull method is to add the data to a Datatable.  I find it easier to work with datatables when you need to adjust the table values and bind them to a gridview.

Datatable dt = new Datatable
dadapter.Fill(dt);            

 Good luck