Thursday, April 19, 2012

Regex in html list tag not working (multiline string)

I have this string:
< li class="MsoNormal" style="color:white;mso-themecolor:background1; mso-margin-top-alt:mso-list:l1 level1 lfo1;tab-stops:list 36.0pt">
and the regex has a lazy quantifier to replace the string above to < li>. Using a simple regex pattern, it won't change the string above to ordinary < li>. The solution would be to add a regex to replace new line characters. Since the string has newline characters.
Code:
Regex.Replace(source, @"\n", string.Empty, RegexOptions.IgnoreCase);

Wednesday, April 18, 2012

Set Background color of Excel cell using C#

Here's how to set MS Excel cell or cells background color using C#.
chartRange = sheet.Cells[1, i + 1];  
 chartRange.Interior.Color = Color.LightBlue;
or using get_Range() function and ColorTranslator.ToOle() to set the background color.
Excel.Range chartRange;
chartRange = xlWorkSheet.get_Range("b1", "b3");
chartRange.Interior.Color = ColorTranslator.ToOle(Color.Red);
Where chartRange is a RangeObject.

Thursday, April 12, 2012

Clear checkboxes inside groupbox not working (bug?)

In a project that i am working with, I have 18 checkboxes. Normally the code below should clear all 18 checkboxes in the groupbox.
Code:
 private void ClearChecklist(GroupBox control)  
     {        
         foreach (Control checklist in control.Controls)  
         {  
           if (checklist is CheckBox)  
           {  
             ((CheckBox)checklist).Checked = false;  
           }  
         }  
     }  

However only 14 checkboxes are deselected. So I modified the code to this:
Code:
private void ClearChecklist(GroupBox control)  
     {  
       if (visited_recursion &lt;= 1)   
       {  
         foreach (Control checklist in control.Controls)  
         {  
           if (checklist is CheckBox)  
           {  
             ((CheckBox)checklist).Checked = false;  
           }  
         }  
         visited_recursion++;  
         ClearChecklist(grpActiveSiteCategories);  
       }  
     }  
I applied a recursion only once to prevent infinite loop/exception. This worked for now.

Method's type signature is not Interop compatible. [Copying DataGridValues to excel cell object]]

I have this original snippet which copies datagridview values into the microsoft excel sheet:
for (int i = 0; i < dataGridViewObjects[gcount].Columns.Count; i++)  
{  
   sheet.Cells[1, i + 1] = dataGridViewObjects[gcount].Columns[i].Name;  
             
   for (int row = 0; row < dataGridViewObjects[gcount].Rows.Count; row++)  
   {  
        for (int column = 0; column < dataGridViewObjects[gcount].Columns.Count; column++)  
        {  
            sheet.Cells[row + 2, column + 1] =dataGridViewObjects[gcount].Rows[row].Cells[column].Value;  
        }  
   }
} 
The code above throws an exception of method type signature is not interop compatible. I was debugging and checking MSDN classes on Workbook. When I debugged at adding cell values, i did catch the exception.
The solution is simple. Just add ToString() after the value, since value is of type object.
sheet.Cells[row + 2, column + 1] = dataGridViewObjects[gcount].Rows[row].Cells[column].Value.ToString();

Wednesday, April 11, 2012

MySQL Subquery Result as Aliased table name

Here's a query that has a subquery in which the result
is aliased as inner table and then the outer query compares the result from the subquery against the field in the outer query.
SELECT id,w_id, start, f_count, count_percent
FROM tblLogs,
(select max(id) as max_id from tblLogs where w_id = 92
and categ = 'bike' and
date(start) = '2012-01-24') as inner_table
WHERE DATE(start) = '2012-01-24'
AND w_id =92
AND categ = 'bike'
and err = 'none'
and remark = ''
and inner_table.max_id = tblLogs.id;

Sunday, April 1, 2012

Equi-join in MySQL

Here's and example:
select
distinct site_scripts._id,
crw_site.site,
crw_subcategory.subcategory
from site_scripts,
crw_site ,
crw_subcategory
where site_scripts.subcategory_id = 8
and crw_site._id
= site_scripts._id
and crw_subcategory.subcategory_id
= site_scripts.subcategory_id
and crw_scripts.country_id = 1
and crw_site._active = 1
order by crw_site.site;
Note: The concept can be used as an alternate to inner join...