/// <summary>
/// Creates a table of the play diffs in EAR vs. VQ(i) report
/// </summary>
/// <param name="wsDiff"></param>
/// <param name="dtM5"></param>
/// <param name="dtMB"></param>
/// <param name="targetDate"></param>
protected static void CreatePlaceProbDiffsReport(BaseExcelInteropWorksheet wsDiff, System.Data.DataTable dtM5, System.Data.DataTable dtMB, DateTime targetDate)
{
CalculatePlacementForDiffReport(dtM5, "prbVQ Desc", "plVQ");
CalculatePlacementForDiffReport(dtM5, "prbAE Desc", "plAE", true);
CalculatePlacementForDiffReport(dtMB, "prbVQ Desc", "plVQ");
CalculatePlacementForDiffReport(dtMB, "prbAE Desc", "plAE", true);
ProcessTableForDiffReport(wsDiff, dtM5);
ProcessTableForDiffReport(wsDiff, dtMB, 1);
}
/// <summary>
/// Supposed to populate a given DataSheet with A content of a given string
/// </summary>
/// <param name="theTextData"></param>
/// <param name="xlSheet"></param>
/// <param name="beginIncludingFromTableRow"></param>
/// <param name="endIncludingToRow"></param>
public static void ReadStringAndFillInTemplateDataSheet
(
string theTextData,
BaseExcelInteropWorksheet xlSheet,
int beginIncludingFromTableRow = 1,
int beginIncludingFromTableColumn = 1,
int endIncludingToRow = -1
)
{
//DateTime currentRulesDate = theGame.GetLatestRulesDate();
TextReader tr = new System.IO.StringReader(theTextData);
//Skip the headers
// string input = sr.ReadLine();
string input = null;
int fileRow = 1;
int tableRow = beginIncludingFromTableRow;
while ((input = tr.ReadLine()) != null)
{
if (fileRow >= 1)
{
string[] currentLine = input.Split(new char[] { '\t', '\n' });
int colNum = beginIncludingFromTableColumn;
foreach (string currentEntry in currentLine)
{
string theEntry = currentEntry;
DateTime dt = new DateTime();
double x;
if (DateTime.TryParse(currentEntry, out dt) && !double.TryParse(currentEntry, out x))
{
theEntry = dt.ToString("yyyy-MM-dd");
}
xlSheet.setCellTo(tableRow, colNum, theEntry);
colNum++;
}
tableRow++;
}
if (endIncludingToRow > 0 && fileRow > endIncludingToRow)
{
break;
}
fileRow++;
}
return;
}
/// <summary>
/// Open an existing Excel file
/// </summary>
/// <param name="ExcelFileName"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="activeWorksheetNumber"></param>
/// <returns></returns>
protected bool InitExcelFile(string ExcelFileName, int firstRow = 1, int lastRow = int.MaxValue, int activeWorksheetNumber = 1)
{
FileInfo fi = new FileInfo(ExcelFileName);
if (null != ExcelFileName && fi.Exists)
{
excelSource = new BaseExcelInteropReport(ExcelOpenType.OpenExistingTemplateFile, ExcelFileName, false);
activeWorksheet = excelSource.getWorksheet(activeWorksheetNumber);
row2Begin = firstRow;
row2Finish = lastRow;
}
else
{
return(false);
}
return(true);
}
private static void ProcessTableForDiffReport(BaseExcelInteropWorksheet wsDiff, DataTable dt, int block = 0)
{
DataView dv = new DataView(dt, "", "plDiI Asc, prbDiD Asc", DataViewRowState.CurrentRows);
for (int colH = 0; colH < dt.Columns.Count; colH++)
{
wsDiff.setHeaderCellTo(1, ((dt.Columns.Count * block + 3 * block) + (colH + 1) + 1), dt.Columns[colH].ColumnName);
}
// Building a header
for (int row = 0; row < dv.Count; row++)
{
wsDiff.setHeaderCellTo(row + 2, ((dt.Columns.Count * block + 3 * block) + 1), (row + 1).ToString());
for (int col = 0; col < dt.Columns.Count; col++)
{
wsDiff.setCellTo(row + 2, ((dt.Columns.Count * block + 3 * block) + (col + 1) + 1), dv[row][col].ToString());
}
}
}
/// <summary>
/// Creates a new Excel Report
/// </summary>
/// <param name="ExcelFileName">The File Name</param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="activeWorksheetNumber"></param>
/// <returns></returns>
protected bool InitNewExcelFile(string ExcelFileName, int firstRow = 1, int lastRow = int.MaxValue, int activeWorksheetNumber = 1)
{
FileInfo fi = new FileInfo(ExcelFileName);
//ExcelCapabilitiesModel theModel = new ExcelCapabilitiesModel();
if (null != ExcelFileName && !fi.Exists)
{
excelSource = new BaseExcelInteropReport(ExcelOpenType.CreateNewFile, ExcelFileName, true);
activeWorksheet = excelSource.getWorksheet(1);
activeWorksheet.Name = "report-on-" + DateTime.Now.ToString("yyyy-MM-dd");
row2Begin = firstRow;
row2Finish = lastRow;
}
else
{
return(false);
}
return(true);
}
public static void ReadLLFileAndFillInTemplateData(string theFile, BaseExcelInteropWorksheet xlSheet, int beginIncludingFromRow = 2, int endIncludingToRow = -1)
{
//DateTime currentRulesDate = theGame.GetLatestRulesDate();
FileInfo fi = new FileInfo(theFile);
if (fi.Exists)
{
StreamReader sr = new StreamReader(fi.FullName);
//Skip the headers
// string input = sr.ReadLine();
string input = null;
int fileRow = 1;
int tableRow = 2;
while ((input = sr.ReadLine()) != null)
{
if (fileRow >= beginIncludingFromRow)
{
string[] currentLine = input.Split(new char[] { ';', '\t', '\n' }, StringSplitOptions.RemoveEmptyEntries);
for (int colNum = 1; colNum <= currentLine.Length; colNum++)
{
DateTime dt = new DateTime();
double x;
if (DateTime.TryParse(currentLine[colNum - 1], out dt) && !double.TryParse(currentLine[colNum - 1], out x))
{
currentLine[colNum - 1] = dt.ToString("yyyy-MM-dd");
}
xlSheet.setCellTo(tableRow, colNum, currentLine[colNum - 1]);
}
tableRow++;
}
if (endIncludingToRow > 0 && fileRow > endIncludingToRow)
{
break;
}
fileRow++;
}
}
return;
}
