public void Export(string businessDate, string exportFilePath)
        {
            System.Data.DataSet ds = GetByBusinessDate(businessDate);
            System.Data.DataTable dt1 = ds.Tables[0];
            System.Data.DataTable dt2 = ds.Tables[1];

            string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
            string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet1 = excelProcess.GetSheet(1);
                sheet1.Activate();
                excelProcess.CopyRowInsert(sheet1, 2, 3, arrayExport1.GetLength(0) + 1);
                Range range1 = excelProcess.GetRange(sheet1, 2, 1, arrayExport1.GetLength(0) + 1, arrayExport1.GetLength(1));
                range1.Value2 = arrayExport1;
                range1.Value2 = range1.Value2;

                Worksheet sheet2 = excelProcess.GetSheet(2);
                sheet2.Activate();
                excelProcess.CopyRowInsert(sheet2, 2, 3, arrayExport2.GetLength(0) + 1);
                Range range2 = excelProcess.GetRange(sheet2, 2, 1, arrayExport2.GetLength(0) + 1, arrayExport2.GetLength(1));
                range2.Value2 = arrayExport2;
                range2.Value2 = range2.Value2;

                excelProcess.Save();
            }
        }
        public void Export(string businessDate,
         string conditionREPDZABM,
         string conditionMonFee,
         string conditionNoMaster,
         string conditionQQJY,
         string conditionPresent,
         string conditionAgio,
         string exportFilePath)
        {
            System.Data.DataTable dt = GetByCondition(businessDate,
                conditionREPDZABM, conditionMonFee, conditionNoMaster,
                conditionQQJY, conditionPresent, conditionAgio
                );

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 7, 1, 9, 7);
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.SetCells(sheet, 3, 2, businessDate);
                excelProcess.SetCells(sheet, 15, 7, DateTime.Now.ToString("yyyyMMdd"));

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, 
                           string conditionOffset,
                           string conditionLowestConsumption,
                           string conditionGroupPrepay,
                           string conditionUser1,
                           string conditionUser2,
                           string exportFilePath)
        {
            DataSet ds = GetByCondition(businessDate, conditionOffset, conditionLowestConsumption, conditionGroupPrepay, conditionUser1, conditionUser2);
            System.Data.DataTable dt1 = ds.Tables[0];
            System.Data.DataTable dt2 = ds.Tables[1];

            string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
            string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range1 = excelProcess.GetRange(sheet, 8, 1, 8, 7);
                range1.Value2 = arrayExport1;
                range1.Value2 = range1.Value2;

                Range range2 = excelProcess.GetRange(sheet, 16, 1, 16, 3);
                range2.Value2 = arrayExport2;
                range2.Value2 = range2.Value2;

                excelProcess.SetCells(sheet, 3, 2, businessDate);
                excelProcess.SetCells(sheet, 20, 7, DateTime.Now.ToString("yyyyMMdd"));

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, string exportFilePath)
        {
            System.Data.DataTable dt = GetByBusinessDate(businessDate);

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 2, 1, 5, 10);
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                Range rangeBusinessDate = excelProcess.GetRange(sheet, 2, 1, 4, 1);
                rangeBusinessDate.Merge(rangeBusinessDate.MergeCells);

                Range rangeMoneyTotal = excelProcess.GetRange(sheet, 2, 2, 4, 2);
                rangeMoneyTotal.Merge(rangeMoneyTotal.MergeCells);

                excelProcess.SetCells(sheet, 5, 2, string.Empty);

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, string queryCondition, string queryText, string exportFilePath)
        {
            System.Data.DataTable dtExport = GetByCondition(businessDate, queryCondition, queryText);

            bool isAddTitle = true;
            int sheetIndex = 1;
            int rowIndexStart = 1;
            int columnIndexStart = 1;

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                excelProcess.Export(sheetIndex, rowIndexStart, columnIndexStart, dtExport, isAddTitle);
                excelProcess.Save();
            }
        }
Example #6
0
        public void Export(string businessDate, string queryCondition, string queryText, string exportFilePath)
        {
            System.Data.DataTable dt = GetByCondition(businessDate, queryCondition, queryText);

            string[,] arrayExport = Tools.DataTableToArray(dt, true);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, string exportFilePath)
        {
            System.Data.DataTable dt = GetByBusinessDate(businessDate);

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 2, 1, 5, 2);
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly, string exportFilePath)
        {
            System.Data.DataTable dt = GetAllByCondition(businessDate, synthesizeAnaly);
            dt.Columns.RemoveAt(0);

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 2, 1, arrayExport.GetLength(0) + 1, arrayExport.GetLength(1));
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.Save();
            }
        }
        public void Export(string businessDateStart, string businessDateEnd, string exportFilePath)
        {
            System.Data.DataTable dt = GetByBusinessDate(businessDateStart, businessDateEnd);

            string[,] arrayExport = Tools.DataTableToArray(dt, true);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                range.Borders.LineStyle = 1;

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, string exportFilePath)
        {
            System.Data.DataTable dt = GetByBusinessDate(businessDate);

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                Range range = excelProcess.GetRange(sheet, 9, 1, 11, 10);
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.SetCells(sheet, 3, 2, businessDate);
                excelProcess.SetCells(sheet, 18, 6, DateTime.Now.ToString("yyyyMMdd"));

                excelProcess.Save();
            }
        }
        public void Export(string businessDate, string exportFilePath)
        {
            System.Data.DataTable dt = GetByBusinessDate(businessDate);

            string[,] arrayExport = Tools.DataTableToArray(dt, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);

                excelProcess.CopyRowInsert(sheet, 7, 8, 6 + arrayExport.GetLength(0));

                Range range = excelProcess.GetRange(sheet, 7, 1, 6 + arrayExport.GetLength(0), arrayExport.GetLength(1));
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.SetCells(sheet, 3, 2, businessDate);

                excelProcess.Save();
            }
        }
Example #12
0
        public void NotInFeeClassExport(string exportFilePath)
        {
            System.Data.DataTable dt = GetNotInFeeClass();

            string[,] arrayExport = Tools.DataTableToArray(dt, true);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet = excelProcess.GetSheet(1);
                Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
                range.Value2 = arrayExport;
                range.Value2 = range.Value2;

                excelProcess.Save();
            }
        }
Example #13
0
        public string Export(string businessDateStart, string businessDateEnd, byte businessType, string businessTypeValue, string exportFilePath)
        {
            System.Data.DataTable dt = GetAllByBusinessType(businessDateStart, businessDateEnd, businessType, businessTypeValue);

            string[,] arrayExport = Tools.DataTableToArray(dt, true);

            if (businessDateStart == businessDateEnd)
            {
                using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
                {
                    Worksheet sheet = excelProcess.GetSheet(1);

                    Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
                    range.Value2 = arrayExport;
                    range.Value2 = range.Value2;
                    excelProcess.Save();
                }
            }
            else
            {
                int fixedColumnCount = 4;

                using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
                {
                    Worksheet sheet = excelProcess.GetSheet(1);

                    Range range = excelProcess.GetRange(sheet, 1, 1, arrayExport.GetLength(0), arrayExport.GetLength(1));
                    range.Value2 = arrayExport;
                    range.Value2 = range.Value2;

                    excelProcess.CopyRowInsert(sheet, 1, 2, 2);

                    excelProcess.GetRange(sheet, 1, 1, 2, 1).Merge();
                    excelProcess.GetRange(sheet, 1, 2, 2, 2).Merge();
                    excelProcess.GetRange(sheet, 1, 3, 2, 3).Merge();
                    excelProcess.GetRange(sheet, 1, 4, 2, 4).Merge();

                    for (int colIndex = 0; colIndex < arrayExport.GetLength(1) - fixedColumnCount; colIndex++)
                    {
                        excelProcess.SetCells(sheet, 1, colIndex + fixedColumnCount + 1, "金额");
                    }

                    excelProcess.GetRange(sheet, 1, fixedColumnCount + 1, 1, arrayExport.GetLength(1)).Merge();

                    excelProcess.Save();
                }
            }//end else (businessDateStart != businessDateEnd)

            string businessTypeName = string.Empty;
            switch (businessType)
            {
                case (byte)SynthesizeAnalyBusinessType.VoiceClass:
                    {
                        businessTypeName = "语单类";
                        break;
                    }
                case (byte)SynthesizeAnalyBusinessType.DreamClass:
                    {
                        businessTypeName = "梦网类";
                        break;
                    }
                case (byte)SynthesizeAnalyBusinessType.DataClass:
                    {
                        businessTypeName = "数据类";
                        break;
                    }
                case (byte)SynthesizeAnalyBusinessType.GroupClass:
                    {
                        businessTypeName = "集团类";
                        break;
                    }
            }

            string saveFileName = string.Empty;
            if (businessDateStart != businessDateEnd)
            {
                saveFileName = string.Format("{0}-{1}无主{2}.xls", businessDateStart, businessDateEnd, businessTypeName);
            }
            else
            {
                saveFileName = string.Format("{0}无主{1}.xls", businessDateStart, businessTypeName);
            }

            return saveFileName;
        }
        public void Export(string businessDate, string exportFilePath)
        {
            DataSet ds = GetByBusinessDate(businessDate);

            System.Data.DataTable dt1 = ds.Tables[0];
            System.Data.DataTable dt2 = ds.Tables[1];
            System.Data.DataTable dt3 = ds.Tables[2];
            System.Data.DataTable dt4 = ds.Tables[3];
            System.Data.DataTable dt5 = ds.Tables[4];
            System.Data.DataTable dt6 = ds.Tables[5];

            string[,] arrayExport1 = Tools.DataTableToArray(dt1, false);
            string[,] arrayExport2 = Tools.DataTableToArray(dt2, false);
            string[,] arrayExport3 = Tools.DataTableToArray(dt3, false);
            string[,] arrayExport4 = Tools.DataTableToArray(dt4, false);
            string[,] arrayExport5 = Tools.DataTableToArray(dt5, false);
            string[,] arrayExport6 = Tools.DataTableToArray(dt6, false);

            using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
            {
                Worksheet sheet1 = excelProcess.GetSheet(1);
                sheet1.Activate();
                excelProcess.CopyRowInsert(sheet1, 2, 3, arrayExport1.GetLength(0) + 1);
                Range range1 = excelProcess.GetRange(sheet1, 2, 1, arrayExport1.GetLength(0) + 1, arrayExport1.GetLength(1));
                range1.Value2 = arrayExport1;
                range1.Value2 = range1.Value2;

                Worksheet sheet2 = excelProcess.GetSheet(2);
                sheet2.Activate();
                excelProcess.CopyRowInsert(sheet2, 2, 3, arrayExport2.GetLength(0) + 1);
                Range range2 = excelProcess.GetRange(sheet2, 2, 1, arrayExport2.GetLength(0) + 1, arrayExport2.GetLength(1));
                range2.Value2 = arrayExport2;
                range2.Value2 = range2.Value2;

                Worksheet sheet3 = excelProcess.GetSheet(3);
                sheet3.Activate();
                excelProcess.CopyRowInsert(sheet3, 2, 3, arrayExport3.GetLength(0) + 1);
                Range range3 = excelProcess.GetRange(sheet3, 2, 1, arrayExport3.GetLength(0) + 1, arrayExport3.GetLength(1));
                range3.Value2 = arrayExport3;
                range3.Value2 = range3.Value2;

                Worksheet sheet4 = excelProcess.GetSheet(4);
                sheet4.Activate();
                excelProcess.CopyRowInsert(sheet4, 2, 3, arrayExport4.GetLength(0) + 1);
                Range range4 = excelProcess.GetRange(sheet4, 2, 1, arrayExport4.GetLength(0) + 1, arrayExport4.GetLength(1));
                range4.Value2 = arrayExport4;
                range4.Value2 = range4.Value2;

                Worksheet sheet5 = excelProcess.GetSheet(5);
                sheet5.Activate();
                excelProcess.CopyRowInsert(sheet5, 2, 3, arrayExport5.GetLength(0) + 1);
                Range range5 = excelProcess.GetRange(sheet5, 2, 1, arrayExport5.GetLength(0) + 1, arrayExport5.GetLength(1));
                range5.Value2 = arrayExport5;
                range5.Value2 = range5.Value2;

                Worksheet sheet6 = excelProcess.GetSheet(6);
                sheet6.Activate();
                excelProcess.CopyRowInsert(sheet6, 2, 3, arrayExport6.GetLength(0) + 1);
                Range range6 = excelProcess.GetRange(sheet6, 2, 1, arrayExport6.GetLength(0) + 1, arrayExport6.GetLength(1));
                range6.Value2 = arrayExport6;
                range6.Value2 = range6.Value2;

                excelProcess.Save();
            }
        }
        public ActionResult Upload(HttpPostedFileBase fileData, string businessDate)
        {
            string zipDirectory = string.Empty;

            try
            {
                if (fileData == null)
                {
                    return Content("失败:上传文件不存在!");
                 }

                string uploadDirectory = Server.MapPath(NMBConfig.UploadDirectory);

                BLLFileProcess bllFileProcess = new BLLFileProcess();
                NMB.Model.FileInfo fileInfo;

                string extenName = Path.GetExtension(fileData.FileName).ToLower();
                if (extenName.Contains("zip"))
                {
                    zipDirectory = Path.Combine(uploadDirectory, "TempZip");
                    if (!Directory.Exists(zipDirectory))
                    {
                        Directory.CreateDirectory(zipDirectory);
                    }

                    foreach (string file in Directory.GetFiles(zipDirectory))
                    {
                        System.IO.File.Delete(file);
                    }

                    string zipFileName = Path.Combine(zipDirectory, fileData.FileName);
                    fileData.SaveAs(zipFileName);

                    using (ZipFile zipFile = ZipFile.Read(zipFileName))
                    {
                        foreach (ZipEntry entry in zipFile)
                        {
                            entry.Extract(zipDirectory);
                        }
                    }

                    System.IO.File.Delete(zipFileName);

                    string unZipFile = Directory.GetFiles(zipDirectory)[0];
                    string unZipFileName = Path.GetFileName(unZipFile);

                    if (bllFileProcess.IsCanUpload(businessDate, unZipFileName, out fileInfo))
                    {
                        if (fileInfo.Type != FileInfoType.NoMasterBase && fileInfo.Type != FileInfoType.BillBase)
                        {
                            uploadDirectory = Path.Combine(uploadDirectory, businessDate);
                        }

                        if (!Directory.Exists(uploadDirectory))
                        {
                            Directory.CreateDirectory(uploadDirectory);
                        }

                        string saveFileName = Path.Combine(uploadDirectory, unZipFileName);

                        //File.Copy(unZipFile, saveFileName, true);

                        FTP ftp = new FTP(new Uri(NMBConfig.FtpURL), NMBConfig.FtpUserName, NMBConfig.FtpUserPass);

                        string projectDirectory = "NoMasterAndBill";

                        if (!ftp.DirectoryExist(projectDirectory))
                        {
                            ftp.MakeDirectory(projectDirectory);
                        }

                        ftp.GotoDirectory(projectDirectory);

                        if (!ftp.DirectoryExist(businessDate))
                        {
                            ftp.MakeDirectory(businessDate);
                        }

                        ftp.GotoDirectory(businessDate);
                        ftp.UploadFileBigFile(unZipFile, true);

                        bllFileProcess.FileUploadRecord(businessDate, fileInfo.Id, saveFileName);

                        return Content("完成");
                    }
                    else
                    {
                        return Content("失败:上传压缩包内文件名不符合要求!");
                    }
                }
                else if (bllFileProcess.IsCanUpload(businessDate, fileData.FileName, out fileInfo))
                {
                    if (fileInfo.Type != FileInfoType.NoMasterBase && fileInfo.Type != FileInfoType.BillBase)
                    {
                        uploadDirectory = Path.Combine(uploadDirectory, businessDate);
                    }

                    if (!Directory.Exists(uploadDirectory))
                    {
                        Directory.CreateDirectory(uploadDirectory);
                    }

                    string saveFileName = Path.Combine(uploadDirectory, fileData.FileName);

                    fileData.SaveAs(saveFileName);

                    if (Path.GetExtension(saveFileName).ToLower() == ".xls")
                    {
                        using (ExcelProcess excelProcess = new ExcelProcess(saveFileName))
                        {
                            excelProcess.SaveAs(saveFileName);
                            excelProcess.Dispose();
                        }
                    }

                    bllFileProcess.FileUploadRecord(businessDate, fileInfo.Id, saveFileName);

                    return Content("完成");
                }
                else
                {
                    return Content("失败:上传压缩包内文件名不符合要求!");
                }
            }
            catch (Exception ex)
            {
                Tools.LogWrite(ex.ToString());
                return Content("异常:" + ex.Message);
            }
            finally
            {
                if (Directory.Exists(zipDirectory))
                {
                    foreach (string file in Directory.GetFiles(zipDirectory))
                    {
                        System.IO.File.Delete(file);
                    }
                }
            }
        }