/// <summary>
/// Creates an excel file.
/// Writes the content of a list into a worksheet of an excelfile and save the file.
/// </summary>
/// <param name="autosizeColumnWidth">
/// If true, the columns will be autosized.
/// </param>
/// <returns>
/// The <see cref="MemoryStream"/>.
/// </returns>
private MemoryStream CreateExcelFile(bool autosizeColumnWidth = false)
{
var memoryStream = new MemoryStream();
try
{
using (ExcelPackage package = new ExcelPackage(memoryStream))
{
var resultCalculator = new TaxonGridCalculator(currentUser, SessionHandler.MySettings);
var data = resultCalculator.GetTaxonGridResultFromCacheIfAvailableOrElseCalculate();
// Add a new worksheet to the empty workbook.
// The name of the sheet can not be longer than 31 characters.
var worksheet = package.Workbook.Worksheets.Add("SLW Data");
AddHeaders(worksheet, data);
AddContentData(worksheet, data);
FormatHeader(worksheet, 1, 8);
if (autosizeColumnWidth)
{
worksheet.Cells.AutoFitColumns(0);
}
//Add aditional sheets if user has request that
AddAditionalSheets(package);
package.Save();
}
memoryStream.Position = 0;
return(memoryStream);
}
catch (Exception)
{
memoryStream.Dispose();
throw;
}
}
/// <summary>
/// Constructor of an excel xml file with grid statistics on species counts.
/// </summary>
/// <param name="currentUser">Current user context</param>
/// <param name="coordinateSystem">Coordinate system of the transformated coordinates.</param>
/// <param name="addSettings">True if settings sheet should be included</param>
/// <param name="addProvenance">True if provenance sheet should be included.</param>
public GridStatisticsOnSpeciesCountExcelXml(IUserContext currentUser, CoordinateSystemId coordinateSystem, bool addSettings, bool addProvenance)
: base()
{
var resultCalculator = new TaxonGridCalculator(currentUser, SessionHandler.MySettings);
var data = resultCalculator.GetTaxonGridResultFromCacheIfAvailableOrElseCalculate();
_xmlBuilder = new StringBuilder();
//Add file definitions and basic format settings
_xmlBuilder.AppendLine(base.GetInitialSection());
//Specify column and row counts
_xmlBuilder.AppendLine(base.GetColumnInitialSection(8, data.Cells.Count));
////_xmlBuilder.AppendLine(base.getColumnInitialSection(10, data.Cells.Count));
//Specify column widths
_xmlBuilder.AppendLine(base.GetColumnWidthLine(200));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(100));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(60));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(150));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(150));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(170));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(170));
_xmlBuilder.AppendLine(base.GetColumnWidthLine(170));
////_xmlBuilder.AppendLine(base.getColumnWidthLine(170));
////_xmlBuilder.AppendLine(base.getColumnWidthLine(170));
//Add row with column headers
_xmlBuilder.AppendLine(base.GetRowStart());
_xmlBuilder.AppendLine(base.GetColumnNameRowLine("Id"));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(Resource.GridStatisticsTaxaCount));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(Resource.GridStatisticsObservationCount));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(String.Format("Centre coordinate X ({0})", data.GridCellCoordinateSystem)));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(String.Format("Centre coordinate Y ({0})", data.GridCellCoordinateSystem)));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(String.Format("Centre coordinate X ({0})", coordinateSystem.ToString())));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine(String.Format("Centre coordinate Y ({0})", coordinateSystem.ToString())));
_xmlBuilder.AppendLine(base.GetColumnNameRowLine("Grid cell width (metres)"));
////_xmlBuilder.AppendLine(base.getColumnNameRowLine("long"));
////_xmlBuilder.AppendLine(base.getColumnNameRowLine("lat"));
_xmlBuilder.AppendLine(base.GetRowEnd());
//Data values
foreach (TaxonGridCellResult row in data.Cells)
{
_xmlBuilder.AppendLine(base.GetRowStart());
_xmlBuilder.AppendLine(base.GetDataRowLine("String", row.Identifier));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.SpeciesCount.ToString()));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.ObservationCount.ToString()));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.OriginalCentreCoordinateX.ToString()));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.OriginalCentreCoordinateY.ToString()));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.CentreCoordinateX.ToString(base.GetApprotiateGlobalization())));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", row.CentreCoordinateY.ToString(base.GetApprotiateGlobalization())));
_xmlBuilder.AppendLine(base.GetDataRowLine("Number", data.GridCellSize.ToString()));
////Point convertedPoint = GisTools.CoordinateConversionManager.GetConvertedPoint(
//// new Point(row.CentreCoordinateX, row.CentreCoordinateY),
//// new CoordinateSystem(CoordinateSystemId.GoogleMercator),
//// new CoordinateSystem(CoordinateSystemId.WGS84));
////_xmlBuilder.AppendLine(base.getDataRowLine("Number", convertedPoint.X.ToString(CultureInfo.InvariantCulture.NumberFormat)));
////_xmlBuilder.AppendLine(base.getDataRowLine("Number", convertedPoint.Y.ToString(CultureInfo.InvariantCulture.NumberFormat)));
_xmlBuilder.AppendLine(base.GetRowEnd());
}
// Add final section of the xml document.
_xmlBuilder.AppendLine(GetFinalSection(GetAditionalSheets(currentUser, addSettings, addProvenance)));
}