/// <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);
}
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);
}
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;
}
}
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));
}
/// <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);
}
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;
}
}
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;
}
}
/// <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);
}
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");
}
}
}
}
