public void mergeReports(MergeProperties mp)
{
SelectColumns sc = new SelectColumns();
int startMonth,
startYear,
endMonth,
endYear,
thisMonth,
thisYear,
rows,
columns,
thisRow,
thisColumn,
totalRows = 1,
state = 0;
Boolean totalsRow = true;
string rootFolder,
prefix,
filePath;
List<string> columnList = new List<string>();
Excel.Workbook xlWorkBook,
thisWorkBook = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet xlWorkSheet;
Excel.Range range, blankRange;
startMonth = thisMonth = mp.startDate.Month;
startYear = thisYear = mp.startDate.Year;
endMonth = mp.endDate.Month;
endYear = mp.endDate.Year;
rootFolder = mp.rootFolder;
prefix = mp.prefix;
while(thisMonth <= endMonth && thisYear <= endYear)
//if (thisMonth <= endMonth && thisYear <= endYear)
{
filePath = makeFilepath(rootFolder, prefix, thisMonth, thisYear);
try
{
xlWorkBook = this.Application.Workbooks.Open(filePath, false, true);
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Error opening file at path: " + filePath, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
thisMonth++;
thisYear++;
continue;
}
xlWorkSheet = xlWorkBook.Worksheets[REPORT_SHEET];
range = xlWorkSheet.UsedRange;
rows = range.Rows.Count;
columns = range.Columns.Count;
thisRow = thisColumn = 1;
if(state == 0)
{
while(thisColumn <= columns)
{
columnList.Add((string)(xlWorkSheet.Cells[thisRow, thisColumn] as Excel.Range).Value);
thisColumn++;
}
sc.setAvailableColumns(columnList);
if (sc.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
columnList.Clear();
columnList = sc.getSelectedColumns();
setTheseColumnHeaders(thisWorkBook.ActiveSheet, columnList);
state++;
sc.Close();
}
else
{
sc.Close();
continue;
}
}
range = xlWorkSheet.Range[xlWorkSheet.Cells[rows][1], xlWorkSheet.Cells[rows][columns]];
try
{
blankRange = range.SpecialCells(Excel.XlCellType.xlCellTypeBlanks);
}
catch (Exception)
{
totalsRow = false;
}
copyPasteColumns(thisWorkBook.ActiveSheet, getColumns(xlWorkSheet, columnList, rows, columns),totalRows+1);
if (totalsRow)
{
rows -= 1;
totalRows += rows;
}
else totalRows += rows;
xlWorkBook.Close(false);
if (thisMonth == MONTHS_IN_A_YEAR)
{
thisMonth = 1;
thisYear++;
}
else thisMonth++;
}
Globals.ThisAddIn.Dispose();
return;
}
public MergeProperties setMergeProperties(string pre, DateTime sd, DateTime ed, string rf)
{
MergeProperties mp = new MergeProperties();
mp.prefix = pre;
mp.startDate = sd;
mp.endDate = ed;
mp.rootFolder = rf;
return mp;
}