Export data to EXCEL form ListView in Windows Application

Overview :  While working on windows form application sometimes we need to copy all data of listview to an EXCEL sheet. This is very important and needy task in most of the application because EXCEL provides as a great and easy data management platform for those who are not very much expert in handling large database, I am talking about the end user of the application.

Example: Suppose I have a listview in my form having columns as described below -

             listView1.Columns.Add("Item Code", 120);

            listView1.Columns.Add("Party Name", 100);

            listView1.Columns.Add("Brand", 80);

            listView1.Columns.Add("Model No", 100);

            listView1.Columns.Add("Item Name", 100);

            listView1.Columns.Add("Category", 80);

            listView1.Columns.Add("Size", 50);

            listView1.Columns.Add("Color", 70);

            listView1.Columns.Add("Quantity", 50);

            listView1.Columns.Add("Purchase Rate", 90);

            listView1.Columns.Add("Sale Rate", 90);

            listView1.Columns.Add("Disc(%)", 50);

            listView1.Columns.Add("Final Price", 70);

            listView1.Columns.Add("Branch", 100);

 I have a button placed somewhere on the form, so code for the button is as follows -

            copyAlltoClipboard(listView1);

            Microsoft.Office.Interop.Excel.Application xlexcel;

            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;

            xlexcel = new Excel.Application();

            xlexcel.Visible = true;

            xlWorkBook = xlexcel.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];

            CR.Select();

            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

function copyAlltoClipboard(ListView lv) used on buttins's click event as follows -

        public void copyAlltoClipboard(ListView lv)

        {

            StringBuilder buffer = new StringBuilder();

            for (int i = 0; i < lv.Columns.Count; i++)

            {

                buffer.Append(lv.Columns[i].Text);

                buffer.Append("\t");

            }

            buffer.Append("\n");

            for (int i = 0; i < lv.Items.Count; i++)

            {

                for (int j = 0; j < lv.Columns.Count; j++)

                {

                    buffer.Append(lv.Items[i].SubItems[j].Text);

                    buffer.Append("\t");

                }

                buffer.Append("\n");

            }

            Clipboard.SetText(buffer.ToString());

        }

NemaSpace used : 

using Excel = Microsoft.Office.Interop.Excel;

using fm = System.Windows.Forms;

Use of HAVING clause in SQL

Overview : 

Having clause in SQL is used to place aggregate functions  to filter data because where condition does not allow the use of aggregate function.

Syntax :

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Demo :

Suppose we have a database of items as shown below. 

Now if we need to find out if there is any duplicate bar-code in these items then we can use having clause.

Query :

select count(item_code), item_name, item_purchase_rate, item_sale_rate from item group by item_code, item_name, item_purchase_rate, item_sale_rate having count(item_code)  > 0

Using PIVOT in SQL

Overview : PIVOT turns the unique values from one column in the expression to multiple columns in result, and applies aggregation on remaining value found in resultant output.

Example: lets create a table for this as follows –

create table demo(id int identity(1,1), item nvarchar(max), category nvarchar(max), qty int)

now lets insert some items in this table –

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat02', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat02', 5)

Lets apply our PIVOT query on this –

select * from(select item, category, qty from demo

)datatable

 PIVOT

 (

   sum(qty) for category  IN ([cat01], [cat02], [cat03])

 )

 Pivottable

Result :