public static ParsingResult ParseAcquisitions(string filename)
{
ParsingResult result = new ParsingResult();
result.Data = new List <PersonalizedSchoolReportData>();
result.Errors = new List <ParsingError>();
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
var template = FromExcel(filename); // so what, perf importants here??
var aquisitions = template.Subjects.SelectMany(p => p.Acquisitions);
var toRet = new List <PersonalizedSchoolReportData>();
ISheet sheet = hssfwb.GetSheet("Acquisitions");
var aquisitionRow = sheet.GetRow(1);
for (int row = 3; 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);
string firstname = null;
string lastname = null;
try
{
if (firstCol != null && !String.IsNullOrEmpty(firstCol.StringCellValue) && firstCol.StringCellValue != "0")
{
firstname = firstCol.StringCellValue;
}
if (secondCol != null && !String.IsNullOrEmpty(secondCol.StringCellValue) && secondCol.StringCellValue != "0")
{
lastname = secondCol.StringCellValue;
}
}
catch (Exception ee)
{
}
if (!string.IsNullOrEmpty(firstname) && !string.IsNullOrEmpty(lastname))
{
PersonalizedSchoolReportData data = new PersonalizedSchoolReportData()
{
FirstName = firstname,
LastName = lastname,
Acquisitions = new Dictionary <string, int>(),
Comments = new Dictionary <string, string>()
};
toRet.Add(data);
for (int c = 2; c < aquisitionRow.LastCellNum; c++)
{
var aquisitionCell = aquisitionRow.GetCell(c);
if (aquisitionCell != null && aquisitionCell.CellType != CellType.Numeric)
{
string acText = null;
try{
acText = aquisitionCell.StringCellValue;
}catch { }
if (acText != null)
{
var cell = sheet.GetRow(row).GetCell(c);
if (cell != null && cell.CellType != CellType.Blank)
{
if (!data.Acquisitions.ContainsKey(aquisitionCell.StringCellValue))
{
try
{
data.Acquisitions.Add(aquisitionCell.StringCellValue, (int)cell.NumericCellValue);
} catch (Exception eee)
{
if (string.IsNullOrWhiteSpace(cell.StringCellValue))
{
data.Acquisitions.Add(aquisitionCell.StringCellValue, -1);
}
else
{
result.Errors.Add(new ParsingError()
{
Eleve = firstname + " " + lastname,
Compétence = acText,
ColumnIndex = c,
RowIndex = row + 1,
Message = "La valeur doit être numérique mais elle contient: '" + cell.StringCellValue + "'. " + eee.Message
});
}
}
}
}
else
if (!data.Acquisitions.ContainsKey(aquisitionCell.StringCellValue))
{
data.Acquisitions.Add(aquisitionCell.StringCellValue, -1);
}
}
}
}
}
}
}
// now comments
/*
* sheet = hssfwb.GetSheet("Commentaires");
*
* var subjectRow = sheet.GetRow(1);
*
*
* for (int row = 3; 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);
* string firstname = null;
* string lastname = null;
* try
* {
* if (firstCol != null && !String.IsNullOrEmpty(firstCol.StringCellValue) && firstCol.StringCellValue != "0")
* {
* firstname = firstCol.StringCellValue;
* }
* if (secondCol != null && !String.IsNullOrEmpty(secondCol.StringCellValue) && secondCol.StringCellValue != "0")
* {
* lastname = secondCol.StringCellValue;
* }
* }
* catch { }
*
* if (!string.IsNullOrEmpty(firstname) && !string.IsNullOrEmpty(lastname))
* {
* var data = toRet.Single(p => p.FirstName == firstname && p.LastName == lastname);
*
* for (int c = 2; c < subjectRow.LastCellNum; c++)
* {
* var subjectCell = subjectRow.GetCell(c);
* if (subjectCell != null && subjectCell.CellType != CellType.Numeric)
* {
* string acText = null;
*
* try
* {
* acText = subjectCell.StringCellValue;
* if (acText != null)
* {
* var cell = sheet.GetRow(row).GetCell(c);
* if (cell != null)
* data.Comments.Add(acText, cell.StringCellValue);
*
* }
* }
* catch { }
*
*
* }
* }
* }
* }
* }
*/
result.Data = toRet;
return(result);
}
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);
}
}
}
