Esempio n. 1
0
        public Dictionary <string, object> ExportRecipet(Dictionary <string, object> d)
        {
            Dictionary <string, object> r = new Dictionary <string, object>();
            int    ExportType             = Convert.ToInt32(d["ExportType"]); //导出类型,0为物业类型,1为普丰类型
            double Total = 0;                                                 //金额合计 默认0
            Dictionary <int, string> FileHeaderName = new Dictionary <int, string>()
            {
                { 0, "物业收据" },
                { 1, "普丰收据" }
            };
            string BasePath = Directory.GetCurrentDirectory() + "/";
            //FileInfo Template = new FileInfo(BasePath+ "/WY_API/ExcelModel/收据模板.xls");
            string fileName = FileHeaderName[ExportType] + "-" + d["USER_NAME"] + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string filePath = @"WY_API/DownLoadFiles/";

            try
            {
                //商户基本信息查询
                DataSet ds = db.GetShopInfo(d["HOUSE_ID"].ToString());

                //excel插入处理
                IWorkbook  workbook = null;
                FileStream fs       = null;
                IRow       row      = null;
                ISheet     sheet    = null;
                //读取复制的excel模板信息
                using (fs = new FileStream(BasePath + "/WY_API/ExcelModel/收据模板.xls", FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    workbook = new HSSFWorkbook(fs);   //初始化
                    sheet    = workbook.GetSheetAt(0); //获取第一个sheet页
                    string JZMJ = ds.Tables[0].Rows[0].IsNull("JZMJ") ? "0" : Math.Round(Convert.ToDecimal(ds.Tables[0].Rows[0]["JZMJ"]) * 100) / 100 + "平方米";
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        row = sheet.GetRow(1);
                        //商户姓名
                        row.GetCell(1).SetCellValue(d["USER_NAME"].ToString());
                        //坐落位置
                        row.GetCell(4).SetCellValue(ds.Tables[0].Rows[0].IsNull("ZLWZ") ? "" : ds.Tables[0].Rows[0]["ZLWZ"].ToString());
                        //建筑面积
                        row.GetCell(7).SetCellValue(JZMJ);

                        row = sheet.GetRow(8);
                        //收款人
                        row.GetCell(1).SetCellValue("普丰物业");
                        //交款人
                        row.GetCell(3).SetCellValue(d["USER_NAME"].ToString());
                        //打印日期
                        row.GetCell(7).SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
                    }
                    int i = 3;//第四行开始是费用明细项
                    if (d.ContainsKey("WYF") && d["WYF"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue(d["REMARK"] == null || d["REMARK"].ToString() == "" ? "物业费":d["REMARK"].ToString());
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //物业单价
                        row.GetCell(5).SetCellValue(d["HOUSE_SERVICE_UNITPRICE"] + "元/月/平");
                        //物业费金额
                        row.GetCell(6).SetCellValue(d["WYF"] + "元");
                        //物业费备注(起止日期)
                        row.GetCell(7).SetCellValue(d["YXQ"].ToString());
                        Total = Convert.ToDouble(d["WYF"]);
                        i++;
                    }
                    if (d.ContainsKey("DF") && d["DF"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue("电费");
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //电费单价
                        row.GetCell(5).SetCellValue(ds.Tables[1].Select("CONF_CODE='PER_ELECTRIC_SET_PRICE'").Length > 0 ? ds.Tables[1].Select("CONF_CODE='PER_ELECTRIC_SET_PRICE'")[0]["CONF_VALUE"] + "元/度" : 0 + "元/度");
                        //电费金额
                        row.GetCell(6).SetCellValue(d["DF"] + "元");
                        Total += Convert.ToDouble(d["DF"]);
                        i++;
                    }
                    if (d.ContainsKey("SF") && d["SF"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue("水费");
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //水费单价
                        row.GetCell(5).SetCellValue(ds.Tables[1].Select("CONF_CODE='PER_WATER_PRICE'").Length > 0 ? ds.Tables[1].Select("CONF_CODE='PER_WATER_PRICE'")[0]["CONF_VALUE"] + "元/吨": 0 + "元/吨");
                        //水费金额
                        row.GetCell(6).SetCellValue(d["SF"] + "元");
                        Total += Convert.ToDouble(d["SF"]);
                        i++;
                    }
                    if (d.ContainsKey("WYBZJ") && d["WYBZJ"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue("违约保证金");
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //row.GetCell(6).SetCellValue(ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE") ? "" : ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE").ToString());
                        //违约保证金
                        row.GetCell(6).SetCellValue(d["WYBZJ"] + "元");
                        //物业费备注(起止日期)
                        Total += Convert.ToDouble(d["WYBZJ"]);
                        i++;
                    }
                    if (d.ContainsKey("ZXYJ") && d["ZXYJ"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue("装修押金");
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //row.GetCell(6).SetCellValue(ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE") ? "" : ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE").ToString());
                        //装修押金
                        row.GetCell(6).SetCellValue(d["ZXYJ"] + "元");
                        Total += Convert.ToDouble(d["ZXYJ"]);
                        i++;
                    }
                    if (d.ContainsKey("XFBZJ") && d["XFBZJ"].ToString() != "0")
                    {
                        row = sheet.GetRow(i);
                        //费用名称
                        row.GetCell(0).SetCellValue("消防保证金");
                        //建筑面积
                        row.GetCell(2).SetCellValue(JZMJ);
                        //row.GetCell(6).SetCellValue(ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE") ? "" : ds.Tables[1].Rows[0].IsNull("PER_WATER_PRICE").ToString());
                        //消防保证金
                        row.GetCell(6).SetCellValue(d["XFBZJ"] + "元");
                        Total += Convert.ToDouble(d["XFBZJ"]);
                        i++;
                    }
                    //总计金额
                    string ChineseTotal = MoneyHelper.GetNumberCapitalized(Total);

                    row = sheet.GetRow(7);
                    row.GetCell(2).SetCellValue(ChineseTotal);
                    row.GetCell(6).SetCellValue(Total + "元");
                    //准备生成文件
                    while (File.Exists(BasePath + filePath + fileName))
                    {
                        fileName = FileHeaderName[ExportType] + "-" + d["USER_NAME"] + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                    }
                    //将excel写入流
                    using (FileStream Reportfile = new FileStream(BasePath + filePath + fileName, FileMode.Create))
                    {
                        workbook.Write(Reportfile);
                    }
                    r["code"] = 2000;
                    r["path"] = filePath + fileName;
                }
            }
            catch (Exception e)
            {
                r["code"]    = -1;
                r["message"] = e.Message;
            }
            return(r);
        }