Пример #1
0
        /// <summary>
        /// Prepare file for tricare Monday
        /// </summary>
        /// <param name="generateDate"></param>
        public void PrepareFilesForRADTricareMonday(DateTime generateDate)
        {
            DateTime endDay = generateDate.AddDays(-1);
              DateTime startDay = generateDate.AddDays(-9);

              string endDateStr = endDay.ToString("yyyyMMdd");
              string startDateStr = startDay.ToString("yyyyMMdd");
              string priorGenerateDateStr = startDateStr;

              //TRICATE Team Weekly-demo.xls
              //excelHelper.SaveReportFile(saveFullPath,excelHelper.getFilenameByArg(-1, "Track Weekly(Thursday)") + @"\" + "TRICARE Team Weekly Report -" + startDate + "-" + endDate + ".xlsx");
              // TRICARE Weekly dump(thursdat)
              string ssisPathTricareThursday = packagePath + "tricateWeeklyThursday.dtsx";
              string excelFullNameTricareThursday = trackWeekly1Path + endDateStr + @"\" + "Tricare Monday Weekly Dump-" + startDateStr + "-" + endDateStr + ".xls";
              string excelTricarePaht = trackWeekly1Path + endDateStr;
              if (!Directory.Exists(excelTricarePaht))
              {
              Directory.CreateDirectory(excelTricarePaht);
              }
              ExcelHelper excelHelper = new ExcelHelper();
              excelHelper.ExportDataBySSIS(ssisPathTricareThursday, excelFullNameTricareThursday);

              System.Diagnostics.Process.Start("explorer.exe", trackWeekly1Path + endDateStr);
        }
Пример #2
0
        public void PrepareFilesForTricareRpt(DateTime generateDate)
        {
            DateTime endDay = generateDate.AddDays(-1);
              DateTime startDay = generateDate.AddDays(-7);
              DateTime priorEndDay = generateDate.AddDays(-8);

              string endDateStr = endDay.ToString("yyyyMMdd");
              string startDateStr = startDay.ToString("yyyyMMdd");
              string priorEndDateStr = priorEndDay.ToString("yyyyMMdd");
              string priorGenerateDateStr = startDateStr;

              //TRICATE Team Weekly-demo.xls
              //excelHelper.SaveReportFile(saveFullPath,excelHelper.getFilenameByArg(-1, "Track Weekly(Thursday)") + @"\" + "TRICARE Team Weekly Report -" + startDate + "-" + endDate + ".xlsx");
              // TRICARE Weekly dump(thursdat)
              string packagePath = System.IO.Directory.GetCurrentDirectory().ToString().Replace(@"bin\Debug", @"SSISPackage\");//获取SSISPackage的目录。
              string ssisPathTricareThursday = packagePath + "tricateWeeklyThursday.dtsx";
              string excelFullNameTricareThursday = trackWeekly4Path + endDateStr + @"\" + "Track Wise Weekly Dump-" + startDateStr + "-" + endDateStr + ".xls";
              string excelTricarePaht = trackWeekly4Path + endDateStr;
              if (!Directory.Exists(excelTricarePaht))
              {
              Directory.CreateDirectory(excelTricarePaht);
              }
              ExcelHelper excelHelper = new ExcelHelper();
              excelHelper.ExportDataBySSIS(ssisPathTricareThursday, excelFullNameTricareThursday);

              ////handle tricare report sheet
              //string priorReportPath = trackWeekly4Path + priorEndDateStr + @"\Tricare Benchmark Report -" + startDateStr + ".xls";
              //string ReportPath = trackWeekly4Path + endDateStr + @"\Tricare Benchmark Report -" + generateDate.ToString("yyyyMMdd") + ".xls";
              //File.Copy(priorReportPath, ReportPath,true);
              //_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
              //_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
              //_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(ReportPath, Missing.Value, Missing.Value
              //            , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
              //            Missing.Value, Missing.Value, Missing.Value));
              //_Worksheet worksheet = _ExcelWBook.Worksheets["Dashboard"];
              //worksheet.get_Range("E3", "I58").Value = null;
              //worksheet.get_Range("D3", "D58").Copy(Type.Missing);
              //worksheet.get_Range("I3", "I3").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
              //    Type.Missing , Type.Missing );
              //worksheet.get_Range("D3", "D58").Value = null;
              //_ExcelWBook.Save();
              //_ExcelWBook.Close();
              //_ExcelApp.Quit();

              System.Diagnostics.Process.Start("explorer.exe", trackWeekly4Path + endDateStr);
        }
Пример #3
0
 public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate)
 {
     PreExitExcel();
       string filename1 = fileName.Substring(0, fileName.LastIndexOf("."));
       try
       {
       _ExcelApp = new Microsoft.Office.Interop.Excel.Application();
       _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, Missing.Value, Missing.Value
               , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value, Missing.Value));
       for (int i = 0; i < tableNames.Length; i++)
       {
           _IniRow = irow[i];
           switch (tableNames[i].ToString())
           {
               case "AnnuityOpenPeriod":
               case "AnnuityUrgent":
               case "AnnuityStatusUrgent":
                   _SName = "AnnuityFinal";
                   break;
               case "AnnuityRpt":
                   _SName = "AnnuityRpt";
                   break;
               case "AnnuityStatus":
                   _SName = "AnnuityStatus";
                   break;
               case "EBusinessRpt":
                   _SName = "EBusinessRpt Grouped";
                   break;
               default: ;
                   break;
           }
           _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName];
           for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++)
           {
               for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++)
               {
                   _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString();
               }
               _IniRow++;
           }
       }
       string strDate = generateDate.AddDays(-1).ToString("MMdd");
       ExcelHelper excelHelper = new ExcelHelper();
       _fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate);
       _ExcelApp.Rows.RowHeight = "15";
       _ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
           null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
       _ExcelApp.DisplayAlerts = false;
       _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
       _ExcelApp.Workbooks.Close();
       _ExcelApp.Quit();
       Marshal.ReleaseComObject(_ExcelWSheet);
       Marshal.ReleaseComObject(_ExcelWBook);
       Marshal.ReleaseComObject(_ExcelApp);
       }
       catch (Exception e)
       {
       _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
       _ExcelApp.Workbooks.Close();
       _ExcelApp.Quit();
       throw e;
       }
 }
Пример #4
0
        public void PrepareFilesForDailyReport(DateTime generateDate)
        {
            String dweek = generateDate.DayOfWeek.ToString();
              DateTime priorWeekDate = generateDate.AddDays(-8);
              DateTime priorWeekDateS = generateDate.AddDays(-10);
              DateTime FridayDate = generateDate.AddDays(-3);
              DateTime dTodayLinkDate = generateDate.AddDays(-1);

              DateTime rTodayLinkDate = dweek == "Monday" ? generateDate.AddDays(-4) : generateDate.AddDays(-2);

              string tldDate = dTodayLinkDate.ToString("yyyyMMdd");
              string tlrDate = rTodayLinkDate.ToString("yyyyMMdd");
              string pWeekDate = priorWeekDate.ToString("yyyyMMdd");
              string rDatey = rTodayLinkDate.Year.ToString();
              string rDatem = rTodayLinkDate.ToString("MM");
              string rDated = rTodayLinkDate.ToString("dd");
              string dDatey = dTodayLinkDate.Year.ToString();
              string dDatem = dTodayLinkDate.ToString("MM");
              string dDated = dTodayLinkDate.ToString("dd");

              ExcelHelper excelHelper = new ExcelHelper();

              //export today link
              string ssisPathTodaylink = packagePath + "todaylink.dtsx";
              string excelFullNameTodaylink = excelHelper.getFilenamePF(generateDate) + @"\" + dDatem + dDated + dDatey + "(Exclude Closed Before 6302009).xls";
              excelHelper.ExportDataBySSIS(ssisPathTodaylink, excelFullNameTodaylink);

              //export Tricare dump
              string ssisPathTricareDaily = packagePath + "tricaredaily.dtsx";
              string excelFullNameTricareDaily = excelHelper.getFilenamePF(generateDate) + @"\" + "Track Wise Dump-" + dDatem + dDated + dDatey + ".xls";
              excelHelper.ExportDataBySSIS(ssisPathTricareDaily, excelFullNameTricareDaily);

              //Annuity and Ebusiness report
              File.Copy(finalReportsPath + tldDate + @"\" + "Annuity and EBusiness-" + dDatem + dDated + ".xls", excelHelper.getFilenamePF(generateDate) + @"\" + "Annuity and EBusiness-" + dDatem + dDated + ".xls", true);

              ////business surpport backlog report
              File.Copy(finalReportsPath + tldDate + @"\" + "Service and TricareDaily-" + dDatem + dDated + ".xls", excelHelper.getFilenamePF(generateDate) + @"\" + "Service and TricareDaily-" + dDatem + dDated + ".xls", true);

              ////service remedy group level report
              File.Copy(finalReportsPath + tldDate + @"\" + "Service Remedy Group Level Report-" + dDatem + dDated + ".xls", excelHelper.getFilenamePF(generateDate) + @"\" + "Service Remedy Group Level Report-" + dDatem + dDated + ".xls", true);

              ////ebusiness dump
              File.Copy(finalReportsPath + tldDate + @"\" + "EBusiness ticket-" + dDatem + dDated + ".xls", excelHelper.getFilenamePF(generateDate) + @"\" + "EBusiness ticket-" + dDatem + dDated + dDatey + ".xls", true);

              ////annuity backlog dump
              File.Copy(finalReportsPath + tldDate + @"\" + "Annuity ticket-" + dDatem + dDated + ".xls", excelHelper.getFilenamePF(generateDate) + @"\" + "Annuity ticket-" + dDatem + dDated + dDatey + ".xls", true);

              System.Diagnostics.Process.Start("explorer.exe", excelHelper.getFilenamePF(generateDate));
        }
Пример #5
0
        /// <summary>
        /// Prepare file for tricare Monday
        /// </summary>
        /// <param name="generateDate"></param>
        public void PrepareFilesForCorpTricareMonday(DateTime generateDate)
        {
            DateTime endDay = generateDate.AddDays(-3);
              DateTime startDay = generateDate.AddDays(-9);

              string endDateStr = endDay.ToString("yyyyMMdd");
              string startDateStr = startDay.ToString("yyyyMMdd");
              string priorGenerateDateStr = startDateStr;

              string ssisPathCorpTricare = packagePath + "tricateCorpWeeklyMonday.dtsx";
              string excelCorpTricarePath = corpTrackWeekly1Path + startDay.ToString("MMdd") + "-" + endDay.ToString("MMdd");
              string excelFullNameCorpTricare = excelCorpTricarePath + @"\" + "Corp Tricare Weekly New dump " + startDateStr + "-" + endDateStr + ".xls";

              if (!Directory.Exists(excelCorpTricarePath))
              {
              Directory.CreateDirectory(excelCorpTricarePath);
              }
              ExcelHelper excelHelper = new ExcelHelper();
              excelHelper.ExportDataBySSIS(ssisPathCorpTricare, excelFullNameCorpTricare);

              System.Diagnostics.Process.Start("explorer.exe", excelCorpTricarePath);
        }
Пример #6
0
        public void ExportDataToExcel_New(string saveFileFullPath, string fileName, string[] tableName, string[] sheetName, DataSet ds)
        {
            PreExitExcel();
              string filename = fileName.Substring(0, fileName.LastIndexOf("."));
              try
              {
              _ExcelApp = new Microsoft.Office.Interop.Excel.Application();
              _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
                      , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value));

              for (int i = 0; i < tableName.Length; i++)
              {
                  _IniRow = 3;
                  _sheetName = sheetName[i].ToString();
                  _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
                  int rowcount = ds.Tables[i].Rows.Count;
                  for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                  {
                      for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
                      {
                          _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[i].Rows[j][k].ToString();
                      }
                      _IniRow++;
                  }
              }

              ExcelHelper excelHelper = new ExcelHelper();
              _ExcelApp.Rows.RowHeight = "15";

              //ExApp.DisplayAlerts = false;
              _ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
              _ExcelWBook.SaveAs(saveFileFullPath, XlFileFormat.xlWorkbookNormal,
                  null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
              _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
              _ExcelApp.Workbooks.Close();
              _ExcelApp.Quit();

              Marshal.ReleaseComObject(_ExcelWSheet);
              Marshal.ReleaseComObject(_ExcelWBook);
              Marshal.ReleaseComObject(_ExcelApp);
              }
              catch (Exception e)
              {
              _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
              _ExcelApp.Workbooks.Close();
              _ExcelApp.Quit();
              throw e;
              }
        }
Пример #7
0
        public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate)
        {
            PreExitExcel();
              string filename = fileName.Substring(0, fileName.LastIndexOf("."));
              try
              {
              _ExcelApp = new Microsoft.Office.Interop.Excel.Application();
              _ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
                      , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      Missing.Value, Missing.Value, Missing.Value));

              for (int i = 0; i < tableName.Length; i++)
              {
                  if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog")
                  {
                      _IniRow = 2;
                      _sheetName = sheetName[i].ToString();
                      _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
                      for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
                      {
                          for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
                          {
                              _ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
                          }
                          _IniRow++;
                      }
                  }
                  else
                  {
                      _IniRow = 3;
                      _sheetName = sheetName[i].ToString();
                      _ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
                      int rowcount = ds.Tables[i].Rows.Count;
                      for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
                      {
                          for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
                          {
                              _ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
                          }
                          _IniRow++;
                      }
                  }
              }

              string strDate = generateDate.AddDays(-1).ToString("MMdd");
              ExcelHelper excelHelper = new ExcelHelper();
              _fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate);
              _ExcelApp.Rows.RowHeight = "15";

              //ExApp.DisplayAlerts = false;
              _ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
              _ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
                  null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
              _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
              _ExcelApp.Workbooks.Close();
              _ExcelApp.Quit();

              Marshal.ReleaseComObject(_ExcelWSheet);
              Marshal.ReleaseComObject(_ExcelWBook);
              Marshal.ReleaseComObject(_ExcelApp);
              }
              catch (Exception e)
              {
              _ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
              _ExcelApp.Workbooks.Close();
              _ExcelApp.Quit();
              throw e;
              }
        }
Пример #8
0
        /// <summary>
        /// Generates tricarte weekly report on Thursday.
        /// </summary>
        public void GenerateTricareWeeklyRpt(DateTime generateDate, out string errorMsg)
        {
            DateTime todayDate = DateTime.Now;
            int delayDays = (generateDate - todayDate).Days;//get number for store procedure parameter
            int startDelayDays = delayDays - 6;//get number for store procedure parameter
            string[] ProcNamesW = new string[4] { "IMRpt_TricareForMulti", "SRRpt_TricareForMulti", "PBLMRpt_TricareForMulti", "TriBenchMarkRpt_MatchedBacklog" };
            string[,] ParamsW = new string[3, 2] {
                                                    { delayDays.ToString(), startDelayDays.ToString() }
                                                    ,{ delayDays.ToString(), startDelayDays.ToString() }
                                                    ,{ delayDays.ToString(), startDelayDays.ToString() }
                                                 };//benchmark para add in ExecuteProcWithParam method
            string[] sheetnameW = new string[4] { "IM", "SR", "PM", "BenchMarkData" };
            string filename1W = "TRICATE Team Weekly-demo.xls";

            DBAccess dbAccess = new DBAccess();
            dbAccess.ExecuteProcWithParam(filename1W, ProcNamesW, sheetnameW, ParamsW, true, generateDate);

            DateTime EndDay = generateDate.AddDays(-1);
            DateTime StartDay = generateDate.AddDays(-7);

            string endDate = EndDay.ToString("yyyyMMdd");
            string startDate = StartDay.ToString("yyyyMMdd");
            string endDateShort = EndDay.ToString("MMdd");

            //TRICATE Team Weekly-demo.xls
            ExcelHelper excelHelper = new ExcelHelper();
            string sourceFilePath = finalReportsPath + endDate + @"\" + "TRICATE Team Weekly-demo-" + endDateShort + ".xls";
            string copyToPath = trackWeekly4Path + endDate;
            string copyToFullPath = copyToPath + @"\" + "TRICATE Team Weekly-" + startDate + "-" + endDate + ".xls";

            if (!Directory.Exists(copyToPath))
            {
                Directory.CreateDirectory(copyToPath);
            }
            File.Copy(sourceFilePath, copyToFullPath, true);

            ExcelBus excelBiz = new ExcelBus();
            errorMsg = excelBiz.handleBenchmarkReportSheet(generateDate);
        }
Пример #9
0
        private void export(string tableName)
        {
            using (SQLiteConnection connection = createConnection())
            {
                List <string> columns = fetchColumns(connection, tableName);

                string appFolder        = Path.GetDirectoryName(Application.ExecutablePath);
                string templateFileName = Path.Combine(appFolder, string.Format(@"resources\{0}.xlsx", tableName));
                string exportFileName   = string.Format("{0}.xlsx", tableName);

                if (File.Exists(templateFileName))
                {
                    using (ExcelHelper helper = new ExcelHelper(templateFileName, exportFileName))
                    {
                        helper.Direction        = ExcelHelper.DirectionType.TOP_TO_DOWN;
                        helper.CurrentSheetName = "Sheet1";

                        helper.InsertRange("header");
                        CellRangeTemplate rowTemplate = helper.CreateCellRangeTemplate("row", columns);

                        helper.InsertRange(rowTemplate, fetchData(connection, tableName, columns));

                        helper.DeleteSheet("Templates");
                    }

                    MessageBox.Show("Exported!");
                }
                else
                {
                    MessageBox.Show(String.Format("Template xlsx not found ('{0}')!\nAutocreating it, please check it and retry!", templateFileName));
                    string basicTempateFileName = Path.Combine(appFolder, @"resources\BasicTemplate.xlsx");
                    using (ExcelHelper helper = new ExcelHelper(basicTempateFileName, templateFileName))
                    {
                        helper.Direction        = ExcelHelper.DirectionType.LEFT_TO_RIGHT;
                        helper.CurrentSheetName = "Templates";

                        CellRangeTemplate template = helper.CreateCellRangeTemplate("header_row", new List <string>()
                        {
                            "header", "row"
                        });

                        var result =
                            from item in columns
                            select new List <object>()
                        {
                            item, string.Format("<{0}>", item)
                        };

                        helper.InsertRange(template, result);

                        var names = new string[] { "header", "row" };
                        foreach (string name in names)
                        {
                            CellRangeRef range = helper.FindDefinedNameRange(name);

                            //extending it to the length of the columns
                            CellRef end = range.End;
                            end.OffsetIt(new CellRef(0, columns.Count - 1));
                            range.End       = end;
                            range.SheetName = "Templates";

                            helper.SetDefinedNameRange(name, range);
                        }

                        helper.DeleteSheet("_Templates");
                    }
                }
            }
        }