public static SchoolReportTemplate FromExcel(string filename)
{
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
var toRet = new SchoolReportTemplate();
toRet.Subjects = new List <Subject>();
ISheet sheet = hssfwb.GetSheet("Matières");
Subject currentSubject = null;
for (int row = 1; row <= sheet.LastRowNum; row++) // skip first line
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var firstCol = sheet.GetRow(row).GetCell(0);
var secondCol = sheet.GetRow(row).GetCell(1);
if (firstCol != null && !String.IsNullOrEmpty(firstCol.StringCellValue))
{
currentSubject = new Subject()
{
ChoiceCount = 0,
Name = firstCol.StringCellValue,
Acquisitions = new List <string>()
};
toRet.Subjects.Add(currentSubject);
}
if (secondCol != null && !String.IsNullOrEmpty(secondCol.StringCellValue))
{
currentSubject.Acquisitions.Add(secondCol.StringCellValue);
Console.WriteLine(string.Format("adding {0} --> {1}", currentSubject.Name, secondCol.StringCellValue));
}
}
}
return(toRet);
}
public static void GeneratePersonalizedReport(PersonalizedSchoolReportData data, SchoolReportTemplate GlobalModel, string outputFolder, string intermediateTemplate)
{
string outputFile = System.IO.Path.Combine(outputFolder, data.FirstName + "_" + data.LastName + ".docx");
using (DocX document = DocX.Load(intermediateTemplate))
{
document.ReplaceText("{{fn}}", data.FirstName);
document.ReplaceText("{{ln}}", data.LastName);
// Check if all the replace patterns are used in the loaded document.
foreach (var aTable in document.Tables)
{
int rowIndex = 0;
foreach (var aRow in aTable.Rows)
{
var firstCell = aRow.Cells[0];
foreach (var aP in firstCell.Paragraphs)
{
string text = aP.Text;
var trimmed = data.Acquisitions.Keys.Select(p => p.Trim().ToLower().Replace(" ", "")).ToList();
if (data.Acquisitions.ContainsKey(text.Trim().ToLower().Replace(" ", "")) || trimmed.Contains(text.Trim().ToLower().Replace(" ", "")) && !string.IsNullOrEmpty(text) && !string.IsNullOrWhiteSpace(text))
{
bool isSet = false;
int acquisitionValue = -1;
foreach (var a in data.Acquisitions)
{
if (a.Key.Trim().ToLower().Replace(" ", "") == text.Trim().ToLower().Replace(" ", ""))
{
acquisitionValue = a.Value;
}
}
//int acquisitionValue = data.Acquisitions[text];
for (int c = 1; c < aRow.Cells.Count; c++)
{
if (acquisitionValue == c - 1)
{
aRow.Cells[c].ReplaceText("{X}", "X");
isSet = true;
}
else
{
aRow.Cells[c].ReplaceText("{X}", "");
}
}
if (!isSet)
{
for (int c = 1; c < aRow.Cells.Count; c++)
{
aRow.Cells[c].FillColor = System.Drawing.Color.LightGray;
}
}
}
}
var commentTest = firstCell.FindAll("commentaires}}");
if (commentTest.Count > 0)
{
firstCell.ReplaceText("&&place_pour_les_commentaires&&", "");
//firstCell.ReplaceText("\r", "");
//firstCell.ReplaceText("\n", "");
}
}
}
foreach (var aSubject in GlobalModel.Subjects)
{
if (data.Comments.ContainsKey(aSubject.Name))
{
document.ReplaceText("{{" + aSubject.Name + "_commentaires" + "}}", ""); // data.Comments[aSubject.Name]);
}
else
{
document.ReplaceText("{{" + aSubject.Name + "_commentaires" + "}}", "");
}
}
//if (document.fin FindUniqueByPattern(@"<[\w \=]{4,}>", RegexOptions.IgnoreCase).Count == _replacePatterns.Count)
{
/*
* // Do the replacement
* for (int i = 0; i < _replacePatterns.Count; ++i)
* {
* document.ReplaceText("<(.*?)>", DocumentSample.ReplaceFunc, false, RegexOptions.IgnoreCase, null, new Formatting());
* }
*/
// Save this document to disk.
document.SaveAs(outputFile);
}
}
}
public static void GenerateTemplateFile(string rootTemplatePath, SchoolReportTemplate GlobalModel, string intermediateTemplatePath)
{
string blablaComment = "&&place_pour_les_commentaires&&";
using (DocX document = DocX.Load(rootTemplatePath))
{
// Check if all the replace patterns are used in the loaded document.
foreach (var aTable in document.Tables)
{
Console.WriteLine("table " + aTable.Index + " with " + aTable.RowCount + "x" + aTable.ColumnCount);
int rowIndex = 0;
foreach (var aRow in aTable.Rows)
{
Console.WriteLine("row at " + aTable.Index + " with " + aRow.ColumnCount);
var firstCell = aRow.Cells[0];
foreach (var aP in firstCell.Paragraphs)
{
string text = aP.Text;
if (text.StartsWith("{{") && text.EndsWith("}}"))
{
string tText = aP.Text.Replace("{{", "").Replace("}}", "");
var subject = GlobalModel.Subjects.SingleOrDefault(p => p.Name == tText);
if (subject != null)
{
subject.Acquisitions.Reverse();
foreach (var aC in subject.Acquisitions)
{
var nRow = aTable.InsertRow(aRow, rowIndex);
// nRow.InsertParagraph(0, "hey bitchy", false);
nRow.ReplaceText(text, aC);
/*
* if(aRow.ColumnCount-1 > 0)
* for (int i = 1; i < aRow.ColumnCount;i++){
* if(aRow.Cells[i].Paragraphs != null && aRow.Cells[i].Paragraphs.Count() > 0)
* {
* // foreach (var pa in aRow.Cells[i].Paragraphs)
* // aRow.Cells[i].RemoveParagraph(pa);
* }
* aRow.Cells[i].InsertParagraph(0, "{{X}}", false);
* }*/
}
aRow.Remove();
}
subject = null;
subject = GlobalModel.Subjects.SingleOrDefault(p => p.Name + "_commentaires" == tText);
if (subject != null)
{
aP.ReplaceText("{{" + subject.Name + "_commentaires" + "}}", "{{" + subject.Name + "_commentaires" + "}}\r\n" + blablaComment + "\r\n" + blablaComment + "\r\n" + blablaComment + "\r\n" + blablaComment);
}
}
}
rowIndex++;
}
}
//if (document.fin FindUniqueByPattern(@"<[\w \=]{4,}>", RegexOptions.IgnoreCase).Count == _replacePatterns.Count)
{
/*
* // Do the replacement
* for (int i = 0; i < _replacePatterns.Count; ++i)
* {
* document.ReplaceText("<(.*?)>", DocumentSample.ReplaceFunc, false, RegexOptions.IgnoreCase, null, new Formatting());
* }
*/
// Save this document to disk.
document.SaveAs(intermediateTemplatePath);
}
}
}