internal WorkBookClass(string production)
{
pidCheck = new PidChecker(production); //instantiation for later use in checking keys
try
{
xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
}
catch (Exception)
{
xlApp = new Excel.Application();
}
Microsoft.Office.Core.FileDialog fd = this.xlApp.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
fd.AllowMultiSelect = false;
fd.Filters.Clear();
fd.Filters.Add("Excel Files", "*.xls;*.xlsx");
fd.Filters.Add("All Files", "*.*");
fd.Title = "Tax-Aide Update Product Keys";
if (fd.Show() == -1)
try
{
fd.Execute();
}
catch (Exception)
{
MessageBox.Show("There is a problem opening the excel file.\r\nPlease close any open Excel applications and start the program again.");
Environment.Exit(1);
}
else
Environment.Exit(1);
xlApp.Visible = true;
xlWBook = xlApp.ActiveWorkbook;
xlWsheet = xlWBook.ActiveSheet;
Excel.Range colHdrsRng = xlWsheet.Range["A1:E1"];
object[,] colHdrsObj = new object[5, 1];
colHdrsObj = colHdrsRng.Value2;
var colHdrsStr = colHdrsObj.Cast<string>();
//Debug.WriteLine(string.Join(Environment.NewLine + "\t",colHdrsStr.Select(x => x.ToString())));
var result = colHdrsStr.SequenceEqual(colHdrsExpected);
if (!result)
{
MessageBox.Show("The Column Headers in this spreadsheet do not conform to specification.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
DisposeX();
}
//First make sure that working range is one area and has no blank rows at the end.
if (xlWsheet.UsedRange.Areas.Count != 1)
{
MessageBox.Show("The used range on this spreadsheet is not contiguous, something is wrong.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
DisposeX();
}
//xlApp.ScreenUpdating = true;
xlRangeWorking = xlWsheet.UsedRange;
while (xlApp.WorksheetFunction.CountA(xlRangeWorking.Offset[xlRangeWorking.Rows.Count - 1, 0].Resize[1, Type.Missing]) == 0)
{//eliminates blank rows at end of working range from the working range
xlRangeWorking = xlRangeWorking.Resize[xlRangeWorking.Rows.Count - 1, Type.Missing];
}
resultMessageCell = xlWsheet.Range[xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1], xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1]];
//Console.WriteLine(xlRangeWorking.Row.ToString() + " rowCnt= " + xlRangeWorking.Rows.Count.ToString());
//pull spreadsheet data across for faster performance
object[,] sysObj = new object[5, xlRangeWorking.Rows.Count];
sysObj = xlRangeWorking.Value2;
rowList = (from idx1 in Enumerable.Range(1, sysObj.GetLength(0))
select new RowData { mMfr = (string)sysObj[idx1, 1], mSerNo = (sysObj[idx1, 2] != null) ? sysObj[idx1, 2].ToString() : "", mOsPK = (string)sysObj[idx1, 3], mStatus = (string)sysObj[idx1, 4], mRsult = (string)sysObj[idx1, 5] }).ToList<RowData>();
rowList.RemoveAt(0); //Remove Column headers from list
//Debug.WriteLine(sysData.GetType().ToString() + " count elements " + sysData.Count());
//Debug.WriteLine(sysData.ElementAt(1).mMfr);
Debug.WriteLine(string.Join(Environment.NewLine + "\t", from row in rowList select row.mMfr + " " + row.mSerNo));
//Change topic setup status column to receive text strings
try
{
xlWsheet.Range["D1:E1"].ColumnWidth = 40;
}
catch (Exception e)
{
MessageBox.Show("Excel appears to be in a mode of not taking programmatic input. Please close all open Excel copies and restart\r\nThe error message was " + e.Message);
DisposeX();
}
userMessageCell = xlWsheet.Range["D1"];
userMessageCell.Value2 = "Querying the database for these systems";
userMessageCell.Font.Italic = true;
userMessageCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet);
}
internal WorkBookClass(string production)
{
pidCheck = new PidChecker(production); //instantiation for later use in checking keys
try
{
xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
}
catch (Exception)
{
xlApp = new Excel.Application();
}
Microsoft.Office.Core.FileDialog fd = this.xlApp.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
fd.AllowMultiSelect = false;
fd.Filters.Clear();
fd.Filters.Add("Excel Files", "*.xls;*.xlsx");
fd.Filters.Add("All Files", "*.*");
fd.Title = "Tax-Aide Update Product Keys";
if (fd.Show() == -1)
{
try
{
fd.Execute();
}
catch (Exception)
{
MessageBox.Show("There is a problem opening the excel file.\r\nPlease close any open Excel applications and start the program again.");
Environment.Exit(1);
}
}
else
{
Environment.Exit(1);
}
xlApp.Visible = true;
xlWBook = xlApp.ActiveWorkbook;
xlWsheet = xlWBook.ActiveSheet;
Excel.Range colHdrsRng = xlWsheet.Range["A1:E1"];
object[,] colHdrsObj = new object[5, 1];
colHdrsObj = colHdrsRng.Value2;
var colHdrsStr = colHdrsObj.Cast <string>();
//Debug.WriteLine(string.Join(Environment.NewLine + "\t",colHdrsStr.Select(x => x.ToString())));
var result = colHdrsStr.SequenceEqual(colHdrsExpected);
if (!result)
{
MessageBox.Show("The Column Headers in this spreadsheet do not conform to specification.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
DisposeX();
}
//First make sure that working range is one area and has no blank rows at the end.
if (xlWsheet.UsedRange.Areas.Count != 1)
{
MessageBox.Show("The used range on this spreadsheet is not contiguous, something is wrong.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
DisposeX();
}
//xlApp.ScreenUpdating = true;
xlRangeWorking = xlWsheet.UsedRange;
while (xlApp.WorksheetFunction.CountA(xlRangeWorking.Offset[xlRangeWorking.Rows.Count - 1, 0].Resize[1, Type.Missing]) == 0)
{//eliminates blank rows at end of working range from the working range
xlRangeWorking = xlRangeWorking.Resize[xlRangeWorking.Rows.Count - 1, Type.Missing];
}
resultMessageCell = xlWsheet.Range[xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1], xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1]];
//Console.WriteLine(xlRangeWorking.Row.ToString() + " rowCnt= " + xlRangeWorking.Rows.Count.ToString());
//pull spreadsheet data across for faster performance
object[,] sysObj = new object[5, xlRangeWorking.Rows.Count];
sysObj = xlRangeWorking.Value2;
rowList = (from idx1 in Enumerable.Range(1, sysObj.GetLength(0))
select new RowData {
mMfr = (string)sysObj[idx1, 1], mSerNo = (sysObj[idx1, 2] != null) ? sysObj[idx1, 2].ToString() : "", mOsPK = (string)sysObj[idx1, 3], mStatus = (string)sysObj[idx1, 4], mRsult = (string)sysObj[idx1, 5]
}).ToList <RowData>();
rowList.RemoveAt(0); //Remove Column headers from list
//Debug.WriteLine(sysData.GetType().ToString() + " count elements " + sysData.Count());
//Debug.WriteLine(sysData.ElementAt(1).mMfr);
Debug.WriteLine(string.Join(Environment.NewLine + "\t", from row in rowList select row.mMfr + " " + row.mSerNo));
//Change topic setup status column to receive text strings
try
{
xlWsheet.Range["D1:E1"].ColumnWidth = 40;
}
catch (Exception e)
{
MessageBox.Show("Excel appears to be in a mode of not taking programmatic input. Please close all open Excel copies and restart\r\nThe error message was " + e.Message);
DisposeX();
}
userMessageCell = xlWsheet.Range["D1"];
userMessageCell.Value2 = "Querying the database for these systems";
userMessageCell.Font.Italic = true;
userMessageCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet);
}
