/// <summary>
/// <para>
/// This will generate word file directly from the folder, we just
/// need to know the connection and the team project we want to
/// use.
/// </para>
/// <para>
/// This can be used only if the folder with the template have a
/// text file to parse a <see cref="TemplateDefinition"/>
/// </para>
/// </summary>
/// <param name="fileNameWithoutExtension"></param>
/// <param name="connectionManager"></param>
/// <param name="teamProjectName"></param>
/// <param name="parameters">Parameters should be given from external code
/// because this class does not know how to ask parameter to the user.</param>
/// <returns>File name genreated</returns>
public String GenerateFile(
String fileNameWithoutExtension,
ConnectionManager connectionManager,
String teamProjectName,
Dictionary <string, Object> parameters)
{
var fileName = fileNameWithoutExtension;
if (_wordTemplateFolderManager.TemplateDefinition == null)
{
throw new ApplicationException("Cannot generate work file, template folder name does not contain valid structure.txt file");
}
TemplateDefinition templateDefinition = _wordTemplateFolderManager.TemplateDefinition;
if (templateDefinition.Type == TemplateType.Word)
{
fileName += ".docx";
Boolean createNew = true;
if (templateDefinition.BaseTemplate != null)
{
_wordTemplateFolderManager.CopyFileToDestination(templateDefinition.BaseTemplate, fileName);
File.Copy(templateDefinition.BaseTemplate, fileName);
createNew = false;
}
//Ok, we start creating the empty file, then proceed to create everything.
using (WordManipulator manipulator = new WordManipulator(fileName, createNew))
{
//now we need to scan the sections of the definition so we can use
//each section to build the file
foreach (var section in templateDefinition.AllSections)
{
//now each section can do something with my standard word manipulator
section.Assemble(manipulator, parameters, connectionManager, _wordTemplateFolderManager, teamProjectName);
}
}
}
else if (templateDefinition.Type == TemplateType.Excel)
{
fileName += ".xlsx";
_wordTemplateFolderManager.CopyFileToDestination(templateDefinition.BaseTemplate, fileName);
//Ok, we start creating the empty file, then proceed to create everything.
using (ExcelManipulator manipulator = new ExcelManipulator(fileName))
{
//now we need to scan the sections of the definition so we can use
//each section to build the file
foreach (var section in templateDefinition.AllSections)
{
//now each section can do something with my standard word manipulator
section.AssembleExcel(manipulator, parameters, connectionManager, _wordTemplateFolderManager, teamProjectName);
}
}
}
else
{
throw new NotSupportedException($"Unsupported type {templateDefinition.Type}");
}
return(fileName);
}
/// <summary>
/// <para>Each section should be able to generate a part of the word file.</para>
/// <para>
/// Not all sections are used to manipulate word file, for those sections
/// this function could be not overriden
/// </para>
/// </summary>
/// <param name="manipulator"></param>
/// <param name="parameters"></param>
/// <param name="connectionManager"></param>
/// <param name="teamProjectName"></param>
public virtual void AssembleExcel(
ExcelManipulator manipulator,
Dictionary <string, Object> parameters,
ConnectionManager connectionManager,
WordTemplateFolderManager wordTemplateFolderManager,
string teamProjectName)
{
}
/// <summary>
/// Generates the output file.
/// </summary>
/// <param name="data">The processing data.</param>
/// <returns></returns>
private string GenerateOutputFile(ProcessingModel data)
{
var progressReporter = new Progress <int>();
progressReporter.ProgressChanged += (sender, percent) =>
{
data.Percent = percent;
this.StoreGenerationProgress(data);
};
var serializer = this.Encoder;
var imageManipulator = new ImageManipulator(this.Environment);
var excelManipulator = new ExcelManipulator(serializer, imageManipulator, progressReporter);
var sourceFilePath = HomeController.GetTempInputFilePath(data.RequestId);
var templateFilePath = Path.Combine(this.Environment.WebRootPath, "platobne_predpisy.xltx");
var outputFilePath = HomeController.GetTempOutputFilePath(data.RequestId);
// another request tries to download the same file?
if (System.IO.File.Exists(outputFilePath))
{
// wait for the other thread to complete
while (true)
{
try
{
using (System.IO.File.OpenWrite(outputFilePath))
{
break;
}
}
catch
{
// wait a while
Thread.Sleep(5000);
}
}
return(outputFilePath);
}
// read source data
(string paymentPurpose, var bySquareDocuments) = excelManipulator.ReadPaymentList(sourceFilePath);
// create the output
return(excelManipulator.CreateOutputFile(templateFilePath, outputFilePath, paymentPurpose, bySquareDocuments));
}
public static List <BatchImport> CreateBatchFromCsv(string file)
{
var sheetValues = ExcelManipulator.GetArrayFromCsvFile(file);
var newBatchList = new List <BatchImport>(sheetValues.GetLength(1));
for (int i = 0; i < sheetValues.GetLength(1); i++)
{
newBatchList.Add(new BatchImport());
newBatchList[i].BatchDescription = sheetValues[0, i].ToString();
for (int j = 1; j < sheetValues.GetLength(0); j++)
{
if (sheetValues[j, i] != null && !string.IsNullOrWhiteSpace(sheetValues[j, i].ToString()))
{
newBatchList[i].ComputerNames.Add(sheetValues[j, i].ToString());
}
}
}
return(newBatchList);
}
public static List <BatchImport> CreateBatchFromExcel(string file)
{
var sheetValues = ExcelManipulator.GetArrayFromWorkbookFile(file);
var newBatchList = new List <BatchImport>(sheetValues.GetLength(0));
for (int i = 1; i < sheetValues.GetLength(1) + 1; i++)
{
newBatchList.Add(new BatchImport());
newBatchList[i - 1].BatchDescription = sheetValues[1, i].ToString();
for (int j = 2; j < sheetValues.GetLength(0) + 1; j++)
{
if (sheetValues[j, i] != null)
{
newBatchList[i - 1].ComputerNames.Add(sheetValues[j, i].ToString());
}
}
}
return(newBatchList);
}
private static void TestExcelExtraction(ConnectionManager connection)
{
WorkItemManger workItemManger = new WorkItemManger(connection);
workItemManger.SetTeamProject("CMMI Playground");
var hr = workItemManger.ExecuteHierarchicQuery(@"SELECT
*
FROM workitemLinks
WHERE
(
[Source].[System.TeamProject] = '{teamProjectName}'
AND[Source].[System.WorkItemType] = 'Feature'
AND[Source].[Microsoft.VSTS.Scheduling.TargetDate] < '2062-01-01T00:00:00.0000000'
AND[Source].[Microsoft.VSTS.Scheduling.TargetDate] > '1990-02-02T00:00:00.0000000'
)
AND(
[System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
)
AND(
[Target].[System.TeamProject] = '{teamProjectName}'
AND[Target].[System.WorkItemType] <> ''
)
MODE(Recursive)
",
new[] { "task", "requirement", "feature", "epic" });
var tempFile = @"c:\temp\test2.xlsx";
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
File.Copy(@"c:\temp\test.xlsx", tempFile);
using (ExcelManipulator m = new ExcelManipulator(tempFile))
{
m.FillWorkItems(hr);
}
System.Diagnostics.Process.Start(tempFile);
}
public IActionResult SampleFile()
{
var serializer = new BySquareXmlSerializer();
var encoder = new BySquareInternalEncoder();
var imageManipulator = new ImageManipulator(this.Environment);
var excelManipulator = new ExcelManipulator(encoder, imageManipulator, null);
var filePath = Path.Combine(this.Environment.ContentRootPath, "Samples", "platobny_harok.xlsx");
(_, var bySquareDocuments) = excelManipulator.ReadPaymentList(filePath);
// alter date to current date
bySquareDocuments[0].Payments[0].PaymentDueDate = DateTime.Today.AddDays(1);
ViewData["XML"] = serializer.SerializeAsXml(bySquareDocuments[0]);
// generate QR code
var qrstring = this.Encoder.Encode(bySquareDocuments[0]);
var image = imageManipulator.CreateQrCodeWithLogo(qrstring);
ViewData["IMAGE"] = imageManipulator.EncodeImageAsBase64String(image);
return(View("Sample"));
}
public override void AssembleExcel(
ExcelManipulator manipulator,
Dictionary <string, object> parameters,
ConnectionManager connectionManager,
WordTemplateFolderManager wordTemplateFolderManager,
string teamProjectName)
{
WorkItemManger workItemManger = PrepareWorkItemManager(connectionManager, teamProjectName);
//If we do not have query parameters we have a single query or we can have parametrized query with iterationPath
var queries = PrepareQueries(parameters);
foreach (var query in queries)
{
if (HierarchyMode?.Length > 0)
{
var hr = workItemManger.ExecuteHierarchicQuery(query, HierarchyMode);
manipulator.FillWorkItems(hr);
}
else
{
throw new NotSupportedException("This version of the program only support hierarchy mode for excel");
}
}
}
