public static void GenerateReport(IList<Datatype.DoublePoint> newPoints, Datatype.ImageData data, Datatype.Stats stats, ConfigReader reader) { string imagePath = data.origFilename; string savePath = data.reportFilename; if (savePath == null) { throw new NullReferenceException("A save path must be provided in order for the report to save"); } //Open Excel File ExcelFile excelFile = new ExcelFile(); try { excelFile.LoadXlsx(reader.getValue("Template Location"), XlsxOptions.PreserveKeepOpen); } //Catch and report error if directory is not valid catch (System.IO.DirectoryNotFoundException) { MessageBox.Show("A report could not be generated. Please check the configuration file for errors. " + "The path to the default template contains a directory that doesn't seem to exist.", "Directory Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); throw new System.InvalidOperationException("DirectoryNotFoundException"); } //Catch and return error if file is not found. catch (System.IO.FileNotFoundException) { MessageBox.Show("A report could not be generated. Please check the configuration file for errors. " + "The file name given doesn't seem to exist.", "File Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); throw new System.InvalidOperationException("FileNotFoundException"); } //Catch and report error if the file is already opened and locked catch (System.IO.IOException) { MessageBox.Show("A report could not be generated because the template file is locked. " + "Please close any application that may be using the file and try again.", "File Could Not Load", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); throw new System.InvalidOperationException("FileLoadException"); } ExcelWorksheet reportWorksheet = excelFile.Worksheets[0]; ExcelWorksheet otherStatsWorksheet = excelFile.Worksheets[1]; //Set row fill color. Color rowFillColor = Color.FromArgb(219, 229, 241); //Check that data.distance != 0 if (data.distance == 0) { throw new DivideByZeroException("Data.distance cannot be 0"); } //Determine minutes of angle factor. If yards, use the literal data.distance; else convert to yards. if (data.distanceUnits == SANTA.Datatype.UnitsOfMeasure.Yard) { minutesOfAngleConverter = minutesOfAngleConversionFactor*100/data.distance; } else if (data.distanceUnits == SANTA.Datatype.UnitsOfMeasure.Meter) { minutesOfAngleConverter = minutesOfAngleConversionFactor*100/(data.distance*1.0936133); } else { //Should never get here, but exception will trigger if we do. throw new InstanceNotFoundException("Distance Units are not of a known datatype."); } // Column width of 8, 30, 16, 9, 9, 9, 9, 4 and 5 characters. reportWorksheet.Columns[0].Width = 285/7*256; reportWorksheet.Columns[1].Width = 107/7*256; reportWorksheet.Columns[2].Width = 12*(256 - 24); reportWorksheet.Columns[3].Width = 12*(256 - 24); reportWorksheet.Columns[4].Width = 12*(256 - 24); reportWorksheet.Columns[5].Width = 12*(256 - 24); reportWorksheet.Columns[6].Width = 12*(256 - 24); reportWorksheet.Columns[7].Width = 12*(256 - 24); // Fill in cell data placeValue(reportWorksheet, "Target Distance", data.distance); reportWorksheet.Cells[reader.getValue("Shooter Name")].Value = data.shooterFName + " " + data.shooterLName; reportWorksheet.Cells[reader.getValue("Shoot Date")].Value = data.dateTimeFired; reportWorksheet.Cells[reader.getValue("Shoot Date")].Style.NumberFormat = reader.getValue("Date Format"); reportWorksheet.Cells[reader.getValue("Range Name")].Value = data.rangeLocation; reportWorksheet.Cells[reader.getValue("Temperature")].Value = Datatype.ImageData.tempDetails[(int) data.temperature]; reportWorksheet.Cells[reader.getValue("Target Distance")].Value = data.distance + " " + data.distanceUnits; if (data.shotsFired == newPoints.Count) { reportWorksheet.Cells[reader.getValue("Shots Fired")].Value = data.shotsFired; } else { reportWorksheet.Cells[reader.getValue("Shots Fired")].Value = newPoints.Count.ToString() + " found of " + data.shotsFired.ToString() + " indicated"; } reportWorksheet.Cells[reader.getValue("Weapon Name")].Value = data.weaponName; reportWorksheet.Cells[reader.getValue("Weapon Serial Number")].Value = data.serialNumber; reportWorksheet.Cells[reader.getValue("Ammo Caliber")].Value = data.caliberValue + " " + data.caliber.unitName; reportWorksheet.Cells[reader.getValue("Ammo Lot Number")].Value = data.lotNumber; reportWorksheet.Cells[reader.getValue("Ammo Mass")].Value = data.projectileMassGrains + " grains"; reportWorksheet.Cells[reader.getValue("Target ID")].Value = "Target ID: " + data.targetID; reportWorksheet.Cells[reader.getValue("Weapon Notes")].Value = "Weapon Notes: " + data.weaponNotes; reportWorksheet.Cells[reader.getValue("Ammo Notes")].Value = "Ammo Notes: " + data.ammunitionNotes; //Fill in the shot records Random generator = new Random(); for (int i = 0; i < newPoints.Count && i < 50; i++) { //TODO: Set first point of x, y, z values (or specify each point) insertPoints(reportWorksheet, "G" + (i + 15).ToString(), newPoints[i].X, reader); insertPoints(reportWorksheet, "H" + (i + 15).ToString(), newPoints[i].Y, reader); //Color every other row in the shot record table. if (i % 2 == 1) { reportWorksheet.Cells["F" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); reportWorksheet.Cells["G" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); reportWorksheet.Cells["H" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); } } //Fill in the color for for (int i = newPoints.Count; i < 50; i = i + 2) { //if the first time through, we are on an odd instead of an even, put us back on evens. if (i%2 == 0) { i++; } reportWorksheet.Cells["F" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); reportWorksheet.Cells["G" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); reportWorksheet.Cells["H" + (i + 15).ToString()].Style.FillPattern.SetSolid(rowFillColor); } //Display stats insertStat(reportWorksheet, "Extreme Spread X", stats.extremeSpreadX, reader); insertStat(reportWorksheet, "Extreme Spread Y", stats.extremeSpreadY, reader); insertStat(reportWorksheet, "Extreme Spread", stats.extremeSpread, reader); insertStat(reportWorksheet, "Mean Radius", stats.meanRadius, reader); insertStat(reportWorksheet, "Sigma X", stats.sigmaX, reader); insertStat(reportWorksheet, "Sigma Y", stats.sigmaY, reader); insertStat(reportWorksheet, "Furthest Left", stats.furthestLeft, reader); insertStat(reportWorksheet, "Furthest Right", stats.furthestRight, reader); insertStat(reportWorksheet, "Highest Round", stats.highestRound, reader); insertStat(reportWorksheet, "Lowest Round", stats.lowestRound, reader); insertStat(reportWorksheet, "CEP", stats.CEPRadius, reader); //Add a small image of the pic in the upper right corner. Bitmap image = new Bitmap(imagePath); int width = image.Width; int height = image.Height; double aspectRatio = (double)width / (double)height; image.Dispose(); if (aspectRatio > (double)reportImageWidth / (double)reportImageHeight) { double newHeight = (double)reportImageWidth / aspectRatio * 0.9; // The 0.9 is a fudge factor because GemBox doesn't know how to scale things properly. reportWorksheet.Pictures.Add(imagePath, new Rectangle(0, (int) ((reportImageHeight - newHeight) * 0.5), reportImageWidth, (int) (newHeight + 0.5))); } else { double newWidth = (double)reportImageHeight * aspectRatio / 0.9; // The 0.9 is a fudge factor because GemBox doesn't know how to scale things properly. reportWorksheet.Pictures.Add(imagePath, new Rectangle((int) ((reportImageWidth - newWidth) * 0.5), 0, (int) (newWidth + 0.5), reportImageHeight)); } //Add CEP Data otherStatsWorksheet.Cells[reader.getValue("X Offset")].Value = stats.center.X; otherStatsWorksheet.Cells[reader.getValue("Y Offset")].Value = stats.center.Y; otherStatsWorksheet.Cells[reader.getValue("Mean X Offset")].Value = stats.center.X; otherStatsWorksheet.Cells[reader.getValue("Mean Y Offset")].Value = stats.center.Y; otherStatsWorksheet.Cells[reader.getValue("CEP Radius")].Formula = "=IF(Report!" + reader.getValue("CEP Toggle Cell") + " =\"CEP Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.CEPRadius + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.CEPRadius * 2.54) + ", " + (stats.CEPRadius * minutesOfAngleConverter) + ")))"; otherStatsWorksheet.Cells[reader.getValue("Mean Radius Circle")].Formula = "=IF(Report!" + reader.getValue("Mean Radius Toggle Cell") + " =\"Mean Radius Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.meanRadius + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.meanRadius * 2.54) + ", " + (stats.meanRadius * minutesOfAngleConverter) + ")))"; otherStatsWorksheet.Cells[reader.getValue("Extreme Spread x1")].Formula = "=IF(Report!" + reader.getValue("Extreme Spread Toggle Cell") + " =\"Extreme Spread Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.extremePoints[0].X + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.extremePoints[0].X * 2.54) + ", " + (stats.extremePoints[0].X * minutesOfAngleConverter) + ")))"; otherStatsWorksheet.Cells[reader.getValue("Extreme Spread x2")].Formula = "=IF(Report!" + reader.getValue("Extreme Spread Toggle Cell") + " =\"Extreme Spread Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.extremePoints[1].X + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.extremePoints[1].X * 2.54) + ", " + (stats.extremePoints[1].X * minutesOfAngleConverter) + ")))"; otherStatsWorksheet.Cells[reader.getValue("Extreme Spread y1")].Formula = "=IF(Report!" + reader.getValue("Extreme Spread Toggle Cell") + " =\"Extreme Spread Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.extremePoints[0].Y + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.extremePoints[0].Y * 2.54) + ", " + (stats.extremePoints[0].Y * minutesOfAngleConverter) + ")))"; otherStatsWorksheet.Cells[reader.getValue("Extreme Spread y2")].Formula = "=IF(Report!" + reader.getValue("Extreme Spread Toggle Cell") + " =\"Extreme Spread Off\", " + 0 + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in inches)\", " + stats.extremePoints[1].Y + ", IF(Report!" + reader.getValue("Cell To Determine Unit of Points") + " =\"Shot Record (in centimeters)\", " + (stats.extremePoints[1].Y * 2.54) + ", " + (stats.extremePoints[1].Y * minutesOfAngleConverter) + ")))"; //Generate CEP Circle formula data for (int cell = 0; cell <= 39; cell++) { otherStatsWorksheet.Cells["B" + (cell + 9).ToString()].Formula = "=$B$3+A" + (cell + 9).ToString(); otherStatsWorksheet.Cells["C" + (cell + 9).ToString()].Formula = "=SQRT(ABS(POWER($B$5,2)-POWER(B" + (cell + 9).ToString() + ",2))) +$B$4"; otherStatsWorksheet.Cells["D" + (cell + 9).ToString()].Formula = "=$B$4 - SQRT(ABS(POWER($B$5,2)-POWER(B" + (cell + 9).ToString() + ",2)))"; } //Generate Mean Circle formula data for (int cell = 0; cell <= 39; cell++) { otherStatsWorksheet.Cells["M" + (cell + 9).ToString()].Formula = "=$M$3+L" + (cell + 9).ToString(); otherStatsWorksheet.Cells["N" + (cell + 9).ToString()].Formula = "=SQRT(ABS(POWER($M$5,2)-POWER(M" + (cell + 9).ToString() + ",2))) +$M$4"; otherStatsWorksheet.Cells["O" + (cell + 9).ToString()].Formula = "=$M$4 - SQRT(ABS(POWER($M$5,2)-POWER(M" + (cell + 9).ToString() + ",2)))"; } try { //Write Report excelFile.SaveXlsx(savePath); } catch (System.IO.IOException) { MessageBox.Show("A report could not be generated. The file you attempted to save to is busy. Please close all applications using " + savePath + " and press ok to continue.", "File Open", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); throw new System.InvalidOperationException("DirectoryNotFoundException"); } finally { //Close Report excelFile.ClosePreservedXlsx(); } //Check to see what to do with report now switch (reader.getValue("Open Excel By Default").ToString()) { case "nothing": break; case "prompt": DialogResult response = MessageBox.Show("Would you like to open the report you just created?", "Open report?", MessageBoxButtons.YesNo); if (response == DialogResult.Yes) { openReportWithExcel(savePath); } break; case "open": openReportWithExcel(savePath); break; } }