第 12 章 查询统计功能

1. 第12章 查询统计功能 宋桂岭

2. 本章要点 • 掌握查询统计功能界面设计方法 • 掌握复杂SQL语句的编写 • 掌握StringBuilder类的应用 • 实现将数据导出到Excel功能

3. 12.1 功能概述

4. 12.1 功能概述 • 举例：商品分类采购统计

5. 12.2 商品分类采购统计功能界面设计 • 在VS2005的解决方案资源管理器中，选择“HcitPos”，右键，在下拉菜单中选择“添加”→“新建文件夹”，并将文件夹命名为“Query” • 右键单击“Query”文件夹，在下拉菜单中选择“添加”→“新建文件夹”，并将文件夹命名为“Purchase”,同理，新建“Sale”、“Store”文件夹，实现统计窗体的分目录存放

6. 12.2 商品分类采购统计功能界面设计 • 右键单击“Purchase”文件夹，在下拉菜单中选择“添加”→“新建项”，在弹出的添加新项对话框中选择“Windows窗体”，并将该窗体命名为“FrmPurchaseClassCollect.cs”

7. 12.2 商品分类采购统计功能界面设计 • 在VS2005工具箱中选择“容器”选项卡，拖动Panel控件到采购入库窗体上，这里需要两个Panel控件

8. 12.2 商品分类采购统计功能界面设计 • 选中ComboBox控件comboDayRange，设置其Items属性

9. 12.2 商品分类采购统计功能界面设计 • 在VS2005工具箱中选择“数据”选项卡，选择DataGridView控件，拖动到panel2容器控件中，设置DataGridView控件的Name属性为gvPurchaseCollect，Dock属性为Fill，SelectionMode属性为FullRowSelect

10. 12.2 商品分类采购统计功能界面设计 • DataGridView字段：

11. 12.3 初始化工作 • comboDayRange private void comboDayRange_SelectedIndexChanged(object sender, EventArgs e) { switch (comboDayRange.Text) { case "当天": dtpBegin.Value = DateTime.Now.Date; dtpEnd.Value = DateTime.Now.Date.AddHours(24).AddSeconds(-1); break; case "当月": dtpBegin.Value = DateTime.Now.Date.AddDays(-1 * (DateTime.Now.Day - 1)); dtpEnd.Value = dtpBegin.Value.AddMonths(1).AddSeconds(-1); break; case "上月": dtpBegin.Value = DateTime.Now.Date.AddDays(-1 * (DateTime.Now.Day - 1)).AddMonths(-1); dtpEnd.Value = dtpBegin.Value.AddMonths(1).AddSeconds(-1); break; case "当年": dtpBegin.Value = DateTime.Now.Date.AddDays(-1 * (DateTime.Now.DayOfYear - 1)); dtpEnd.Value = dtpBegin.Value.AddYears(1).AddSeconds(-1); break; case "上年": dtpBegin.Value = DateTime.Now.Date.AddDays(-1 * (DateTime.Now.DayOfYear - 1)).AddYears(-1); dtpEnd.Value = dtpBegin.Value.AddYears(1).AddSeconds(-1); break; } }

12. 12.3 初始化工作 • 主界面初始化 private void FrmPurchaseClassCollect_Load(object sender, EventArgs e) { gvPurchaseCollect.AutoGenerateColumns = false; comboDayRange.SelectedIndex = 0; }

13. 12.4 实现查询统计功能 • 商品分类采购统计功能的核心在于SQL语句 select.Append("select classname, goodsclass.classid,realcou, realtm,purcou, purtm,returncou,returntm "); select.Append("from goodsclass left join "); select.Append("(select goodsclass.classid,sum(purchasecount) as realcou, sum(purchasecount)*avg(unitprice) as realtm "); select.Append("from purchasedetails,goodsclass,goodsinfo,purchaseinfo "); select.Append("where purchasedetails.goodsid=goodsinfo.goodsid and goodsinfo.classid=goodsclass.classid "); select.Append("and purchaseinfo.purchaseid=purchasedetails.purchaseid "); if (comboDayRange.Text != "所有") { select.Append(" and purchaseinfo.purchasedate>=@begindate "); select.Append(" and purchaseinfo.purchasedate<=@enddate "); db.AddParameter("begindate", dtpBegin.Value); db.AddParameter("enddate", dtpEnd.Value); } select.Append("group by goodsclass.classid) as realpurchase on goodsclass.classid=realpurchase.classid left join "); select.Append("(select goodsclass.classid,sum(purchasecount) as purcou, sum(purchasecount)*avg(unitprice) as purtm "); select.Append("from purchasedetails,goodsclass,goodsinfo,purchaseinfo "); select.Append("where purchasedetails.goodsid=goodsinfo.goodsid and goodsinfo.classid=goodsclass.classid "); select.Append("and purchaseinfo.purchaseid=purchasedetails.purchaseid and purchaseinfo.purchasetype=1 "); select.Append("group by goodsclass.classid) as purchase on purchase.classid=realpurchase.classid left join "); select.Append("(select goodsclass.classid,sum(purchasecount) as returncou, sum(purchasecount)*avg(unitprice) as returntm "); select.Append("from purchasedetails,goodsclass,goodsinfo,purchaseinfo "); select.Append("where purchasedetails.goodsid=goodsinfo.goodsid and goodsinfo.classid=goodsclass.classid "); select.Append("and purchaseinfo.purchaseid=purchasedetails.purchaseid and purchaseinfo.purchasetype=2 "); select.Append("group by goodsclass.classid) as purreturn on purchase.classid=purreturn.classid");

14. 12.4 实现查询统计功能 • 为DataGridView控件gvPurchaseCollect的DataBindingComplete事件编写如下代码，从而使显示数据更加友好 private void gvPurchaseCollect_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e) { foreach (DataGridViewRow gvr in gvPurchaseCollect.Rows) { for (int i = 1; i <= 6; i++) { if (gvr.Cells[i].Value.ToString().Trim() == "") gvr.Cells[i].Value = "0"; } } }

15. 12.5 实现Excel导出功能 • 为了在VS2005中对Excel文件进行操作，需要用到Excel.dll，生成该文件的方法是将Office目录下的Excel.exe文件Copy到C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin目录下，在命令行窗口内，调用该目录下的 TlbImp.exe，具体是TlbImp.exe Excel.exe Excel.dll，就可以在目录中生成Excel.dll，注意把Excel.exe文件拷到TlbImp.exe同一个文件夹下

16. 12.5 实现Excel导出功能 • 在VS2005的解决方案资源管理器中选择“添加引用”，弹出添加引用对话框，在“浏览”选项卡中，选择生成的Excel.dll文件

17. 12.5 实现Excel导出功能 • 导出Excel代码： object missing = Missing.Value; Excel.Application myExcel = new Excel.Application(); myExcel.Application.Workbooks.Add(true); //标题列 myExcel.Cells[1, 1] = "商品分类采购汇总"; //汇总日期 myExcel.Cells[2, 1] = "汇总时段:" + dtpBegin.Value.ToString("yyyy-MM-dd") + "至" + dtpEnd.Value.ToString("yyyy-MM-dd"); //其他代码参考教材

18. 12.5 实现Excel导出功能 • 最终效果