Conditionally suppress a line or a box object in Crystal Reports

As of Crystal Reports 2008 there is still no option to conditionally suppress a line or a box drawing object in Crystal Reports, so the closest work-around is to use a blank Text Field.

With the text field you can use the border properties to create most lines. By using the top, bottom, left or right border line properties you can draw a single, double, dashed, or dotted line almost anywhere you want that can be conditionally suppressed and/or colored. In the following picture, i was required to draw a line on first column. In the first look, it seems that this is a box object used but it is not so. There are text objects behind Accrued Profit, Cash Out and Deferred Collection. As first column is group 1 and second column is group 2 and third one is the sum of group 2 and Total is the sum of group 1. So in this kind of display first coulmn's values were repeated with group 2 values. So first i applied some formula on the group values then to draw line I have followed the following steps:

 Here are the steps:

1- Drag some Text Object and right click on it. Select Format Text... from the context menu.

2 - Select Border tab and click any Line style option as per requirement and click its formula button on right side. 

3 - Write the following code and change accordingly

By using multiple text fields of varying sizes, you could also conditionally size the lines too. You may be able to use the size and position dialog to conditionally change the length too, but that method seems to be grayed out (unavailable) sometimes.

Horizontal lines are simpler to create with this method, but vertical lines can also be created by stacking text fields in adjacent sections.

You may have to move the text field to the back if it blocks another field, but in most cases it won't be necessary as long as your text field is empty - maybe you want some text to conditionally appear too - just insert a formula or text. The text box gives you a lot of options.

How to access child controls and their events via element host in Winform

 MessageBox.Show(((WPFCustomControl.WPFComboBox)((System.Windows.Forms.Integration.ElementHost)ctl).Child).SelectedIndex.ToString());

How to remove SkypEmoticons malware - Part 1

"Your skype does not support extended icons. Please download the extension here: 
http://skypemoticonsb.asia/index.php?skype=1"

SkypEmoticon is a malware so do not install any kind of new extensions for viewing skype animated icons. Please install the animated icons from well reputed companies do not install them on your friend's request. 
Anyways I installed it and it started disturbing my chat as well. It automatically added new line after every sentence and start sending the above blue sentence as well almost with every sentence of emoticon. Surprisingly, no antivirus recognizes it. :(

So here are the following steps to remove it:
1- Uninstall SkypEmoticons from Control Panel -> Add/Remove Programs

but it is still there
2 - So go to your Task Manager

3 - Right click and Open file location like that

4 - As the folder opens, click End task the SkypEmoticons (32 bit) process from task manager and then delete the folder named SkypEmoticons from Roaming folder as in the image. Usually the path is "C:\Users\username\AppData\Roaming\SkypEmoticons"

5 - Now you are free from this malware. :)

Sending Email in C#

 SmtpClient client = new SmtpClient();
                client.Port = 25;
                client.Host = "smtp.gmail.com";
                client.EnableSsl = true;
                client.Timeout = 10000000;
                client.DeliveryMethod = SmtpDeliveryMethod.Network;
                client.UseDefaultCredentials = false;
                client.Credentials = new System.Net.NetworkCredential("asad.naeem@gmail.com", "Password");
//  MailMessage mm = new MailMessage("asad.naeem@gmail.com", "asad.naeem@gmail.com", "test", "test");


                MailMessage mm = new MailMessage("FromEmail", "ToEmail", "Subject", "Body");

                mm.BodyEncoding = UTF8Encoding.UTF8;
                mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;

                client.Send(mm);
         
                MessageBox.Show("Email Sent");

Open On-Screen Keyboard In C#

There are some of the ways to on-screen keyboard:

1 - Process.Start("C:\Windows\System32\OSK.EXE")

2 - System.Diagnostics.Process.Start("osk");

3 -Process[] pArr = Process.GetProcesses();

   foreach (Process p in pArr)

   {

            if (p.ProcessName == "osk" || p.ProcessName == "msswchx")

              p.Kill();

   }

ProcessStartInfo pInfo = new ProcessStartInfo(((Environment.GetFolderPath(Environment.SpecialFolder.System) + @"\osk.exe")));              

                Process pr = Process.Start(pInfo);

               System.Threading.Thread.Sleep(100);

                pr.WaitForInputIdle();
4-   string windir = Environment.GetEnvironmentVariable("WINDIR");
            string osk = null;
            if (osk == null)
            {
                osk = Path.Combine(Path.Combine(windir, "system32"), "osk.exe");
                if (!File.Exists(osk))
                {
                    osk = null;
                }
            }
            if (osk == null)
                osk = "osk.exe";

            Process.Start(osk);

but when you have 32-bit built application and you want to open osk on 64-bit Windows 7. It will give error "Could not start On-Screen keyboard".  Simply you have to build your application's every module with Any CPU Setting, then this issue will be resolved immediately.

Windows won't allow you to call a 64-bit OSK.exe from your program. Anyone can start osk.exe from Run, but call it from within a 32-bit application won't work in 64-bit Windows.

Ideally Microsoft should include a 32-bit version of OSK.exe in the system folder and allow us to use it, if being called from a 32-bit application.

Another work around is to get your hands on 32-bit Windows XP and pull osk.exe from it, bundle this with you app. When you call the on-screen keyboard, check if OS is 64-bit, if it is call the Windows XP osk.exe, works OK. Although not ideal.

Write data to an Excel worksheet with C# fast

Source: http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx

The current project I am working on requires writing large amount of data to Excel worksheets. In this type of situation, I create an array with all the data I want to write, and set the value of the entire target range at once. I know from experience that this method is much faster than writing cells one by one, but I was curious about how much faster, so I wrote a little test, writing larger and larger chunks of data and measuring the speed of both methods:

private static void WriteArray(int rows, int columns, Worksheet worksheet)

{

var data = new object[rows, columns];

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

data[row - 1, column - 1] = "Test";

}

}

var startCell = (Range)worksheet.Cells[1, 1];

var endCell = (Range)worksheet.Cells[rows, columns];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.Value2 = data;

}

private static void WriteCellByCell(int rows, int columns, Worksheet worksheet)

{

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

var cell = (Range)worksheet.Cells[row, column];

cell.Value2 = "Test";

}

}

}

Clearly, the array approach is the way to go, performing close to 1000 times faster per cell. It also seems to improve as size increases, but that would require a bit more careful testing.

However, one additional thing I needed to do was to format the data, using NumberFormat as well as font, borders and color fills, and I thought I would use the same approach – and I observed a significant performance degradation.

private static void WriteNumberFormatArray(int rows, int columns, Worksheet worksheet)

{

var data = new object[rows, columns];

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

data[row - 1, column - 1] = "0.000%";

}

}

var startCell = (Range)worksheet.Cells[1, 1];

var endCell = (Range)worksheet.Cells[rows, columns];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.NumberFormat = data;

}

private static void WriteNumberFormatCellByCell(int rows, int columns, Worksheet worksheet)

{

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

var cell = (Range)worksheet.Cells[row, column];

cell.NumberFormat = "0.000%";

}

}

}

Here is the benchmark I ran, comparing writing NumberFormat by array vs. cell by cell:

The cell-by-cell version performs about the same writing values or number formats; however, the array version works about 100 times worse for NumberFormat compared to Value2. It still runs way faster than the cell-by-cell approach, but it’s not night-and-day any more.

Fortunately, when you are writing large amount of data like this, chances are, you are really writing records to a worksheet. And while every cell could potentially have a different value, the format is likely consistent, either by row or by column. That is, every cell in a column probably has the same number format. In that case, we have an alternative, which is to apply the format to an entire range at once, like this:

private static void WriteNumberFormatByColumn(int rows, int columns, Worksheet worksheet)

{

for (var column = 1; column <= columns; column++)

{

var startCell = (Range)worksheet.Cells[1, column];

var endCell = (Range)worksheet.Cells[rows, column];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.NumberFormat = "0.000%";

}

}

I ran my test again, and observed the following:

The format by column runs initially as fast as the array-based approach, but its time remains roughly constant as we increase the number of rows, making it an increasingly attractive option as the number of rows increases.

How do you handle writing large amounts of data to Excel? Any Jedi tricks you care to share?

And for completeness, here is the code I used to run my tests; I used an Action delegate in my test loop, which allowed me to easily swap the functions I wanted to compare – feel free to comment and criticize!

namespace ExcelSpeedTest

{

using System;

using System.Diagnostics;

using Microsoft.Office.Interop.Excel;

class Program

{

static void Main(string[] args)

{

var excel = new Application();

excel.DisplayAlerts = false;

var workbooks = excel.Workbooks;

var stopwatch = new Stopwatch();

var blockSize = 10;

Console.WriteLine("Write by array.");

MeasureOverIncreasingSize(workbooks, blockSize, stopwatch, WriteNumberFormatArray);

Console.WriteLine("Write by column.");

MeasureOverIncreasingSize(workbooks, blockSize, stopwatch, WriteNumberFormatByColumn);

Console.WriteLine("Write cell by cell.");

MeasureOverIncreasingSize(workbooks, blockSize, stopwatch, WriteNumberFormatCellByCell);

Console.ReadLine();

excel.Quit();

}

private static void MeasureOverIncreasingSize(Workbooks workbooks, intblockSize, Stopwatch stopwatch, Action<int, int, Worksheet> method)

{

for (int size = 1; size <= 10; size++)

{

var workbook = workbooks.Add(Type.Missing);

var worksheets = workbook.Sheets;

var worksheet = (Worksheet)worksheets[1];

var rows = blockSize * size;

var columns = blockSize;

stopwatch.Reset();

stopwatch.Start();

method(rows, columns, worksheet);

stopwatch.Stop();

WriteEvaluation(stopwatch, rows, columns);

workbook.Close(false, Type.Missing, Type.Missing);

}

}

private static void WriteArray(int rows, int columns, Worksheet worksheet)

{

var data = new object[rows, columns];

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

data[row - 1, column - 1] = "Test";

}

}

var startCell = (Range)worksheet.Cells[1, 1];

var endCell = (Range)worksheet.Cells[rows, columns];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.Value2 = data;

}

private static void WriteCellByCell(int rows, int columns, Worksheet worksheet)

{

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

var cell = (Range)worksheet.Cells[row, column];

cell.Value2 = "Test";

}

}

}

private static void WriteNumberFormatArray(int rows, int columns, Worksheet worksheet)

{

var data = new object[rows, columns];

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

data[row - 1, column - 1] = "0.000%";

}

}

var startCell = (Range)worksheet.Cells[1, 1];

var endCell = (Range)worksheet.Cells[rows, columns];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.NumberFormat = data;

}

private static void WriteNumberFormatByColumn(int rows, int columns, Worksheet worksheet)

{

for (var column = 1; column <= columns; column++)

{

var startCell = (Range)worksheet.Cells[1, column];

var endCell = (Range)worksheet.Cells[rows, column];

var writeRange = worksheet.Range[startCell, endCell];

writeRange.NumberFormat = "0.000%";

}

}

private static void WriteNumberFormatCellByCell(int rows, int columns, Worksheet worksheet)

{

for (var row = 1; row <= rows; row++)

{

for (var column = 1; column <= columns; column++)

{

var cell = (Range)worksheet.Cells[row, column];

cell.NumberFormat = "0.000%";

}

}

}

private static void WriteEvaluation(Stopwatch stopwatch, int rows, intcolumns)

{

var cells = rows * columns;

var time = stopwatch.ElapsedMilliseconds;

var timePerCell = Math.Round((double)time / (double)cells, 5);

Console.WriteLine(string.Format("Writing {0} values took {1} ms or {2} ms/cell.", cells, time, timePerCell));

}

}

}