public void CanCreateSpreadsheetFromTemplate()
{
using (var packageDocument = SpreadsheetDocument.CreateFromTemplate(SpreadsheetTemplatePath))
{
var part = packageDocument.WorkbookPart;
var root = part.Workbook;
packageDocument.SaveAs(SpreadsheetPath).Close();
// We are fine if we have not run into an exception.
Assert.True(true);
}
}
public void CanCreateSpreadsheetFromTemplate()
{
using (var stream = OpenFile(TestFiles.Templates.Spreadsheet, FileAccess.ReadWrite))
using (var packageDocument = SpreadsheetDocument.CreateFromTemplate(stream.Path))
{
var part = packageDocument.WorkbookPart;
var root = part.Workbook;
packageDocument.SaveAs(Path.GetTempFileName()).Close();
// We are fine if we have not run into an exception.
Assert.True(true);
}
}
/// <summary>
/// Creates a SpreadSheetDocument from the specified
/// template
/// </summary>
/// <param name="template">Template url</param>
/// <returns>SpreadSheetDocument reference</returns>
public static SpreadsheetDocument ExcelDocument(string template = null)
{
template ??= $@"{AppDomain.CurrentDomain.BaseDirectory}Extensions\Excel\Templates\Report.xlsx";
if (!template.IsFilePath(out Uri path))
{
return(null);
}
//Initialize via report template
SpreadsheetDocument document = SpreadsheetDocument.CreateFromTemplate(template);
//Compress using maximum value possible
document.CompressionOption = CompressionOption.Maximum;
//Bond the workbook to the document or else create a new workspace
WorkbookPart book = document.WorkbookPart ?? document.AddWorkbookPart();
//Initialize workspace
WorksheetPart workSpace = document.WorkbookPart.WorksheetParts.FirstOrDefault() ?? document.WorkbookPart.AddNewPart <WorksheetPart>();
//Initialize worksheet
if (workSpace.Worksheet == null)
{
workSpace.Worksheet = new Worksheet();
}
//Load default style sheet
document.LoadDefaultStyleSheet();
//Share string table part
StringPart = book.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
TableStrings = StringPart.SharedStringTable
.Select(s => s.InnerText)
.ToList();
//Add Document properties
document.SetProperties(@"Reporte");
//Add extended Document properties
document.SetExtendedProperties(null, null);
//Save all changes
document.Save();
return(document);
}
public void ExportToFileByTemplate(string strTemplatePath, string strSheetName, DataTable sourceDataTable, int intInputDataStartRowIndex, int intInputDataStartColumnIndex, string strFileSavedPath)
{
if (string.IsNullOrWhiteSpace(strTemplatePath) || string.IsNullOrWhiteSpace(strSheetName) || string.IsNullOrWhiteSpace(strFileSavedPath))
{
return;
}
using (var document = SpreadsheetDocument.CreateFromTemplate(strTemplatePath))
{
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;
var listSheet = workbook.Descendants <Sheet>();
var objSheet = listSheet.FirstOrDefault(item => strSheetName.Equals(item.Name, StringComparison.OrdinalIgnoreCase));
if (objSheet == null)
{
return;
}
var worksheetPart = workbookPart.GetPartById(objSheet.Id) as WorksheetPart;
var listRow = worksheetPart.Worksheet.Descendants <Row>();
if (listRow == null || listRow.Count() == 0)
{
return;
}
bool blnResult = ImportDataTable(worksheetPart, listRow, sourceDataTable, intInputDataStartRowIndex, intInputDataStartColumnIndex);
if (!blnResult)
{
return;
}
document.SaveAs(strFileSavedPath);
document.Close();
}
}
public async System.Threading.Tasks.Task <Stream> ConvertPncToPdfAsync(string formId)
{
var form = await _appDbContext
.TCL_PNCs
.Include(p => p.Entries)
.FirstOrDefaultAsync(p => p.TCL_PNCId == formId);
var fileName = @"Templates\PNC_Template.xlsx";
// Open the document for editing.
using (SpreadsheetDocument doc =
SpreadsheetDocument.CreateFromTemplate(fileName))//.Open(fileName, true))
{
var wbp = doc.WorkbookPart;
var sheet = wbp.Workbook.Descendants <Sheet>().First();
var wsp = (WorksheetPart)(wbp.GetPartById(sheet.Id));
var stringTable = wbp.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
uint row = 4;
foreach (var entry in form.Entries)
{
InsertText(entry.DateOfRegistration.Value.ToString("MM-dd-yy"), stringTable, "A", row, wsp);
MergeCells($"A{row}", $"A{row + 1}", wsp.Worksheet);
InsertText(entry.FamilySerialNumber, stringTable, "B", row, wsp);
MergeCells($"B{row}", $"B{row + 1}", wsp.Worksheet);
InsertText(entry.Name, stringTable, "C", row, wsp);
MergeCells($"C{row}", $"C{row + 1}", wsp.Worksheet);
InsertText(entry.Address, stringTable, "D", row, wsp);
MergeCells($"D{row}", $"D{row + 1}", wsp.Worksheet);
InsertText(entry.Age.ToString(), stringTable, "E", row, wsp);
MergeCells($"E{row}", $"E{row + 1}", wsp.Worksheet);
if (entry.LMPDate != null)
{
InsertText(entry.LMPDate.Value.ToString("MM-dd-yy"), stringTable, "F", row, wsp);
}
if (!string.IsNullOrWhiteSpace(entry.LMPGP))
{
InsertText(entry.LMPGP, stringTable, "F", row + 1, wsp);
}
if (entry.EDC != null)
{
InsertText(entry.EDC.Value.ToString("MM-dd-yy"), stringTable, "G", row, wsp);
MergeCells($"G{row}", $"G{row + 1}", wsp.Worksheet);
}
if (entry.PrenatalVisitTrimester1 != null)
{
InsertText(entry.PrenatalVisitTrimester1.Value.ToString("MM-dd-yy"), stringTable, "H", row, wsp);
MergeCells($"H{row}", $"H{row + 1}", wsp.Worksheet);
}
if (entry.PrenatalVisitTrimester2 != null)
{
InsertText(entry.PrenatalVisitTrimester2.Value.ToString("MM-dd-yy"), stringTable, "I", row + 1, wsp);
MergeCells($"I{row}", $"I{row + 1}", wsp.Worksheet);
}
if (entry.PrenatalVisitTrimester3 != null)
{
InsertText(entry.PrenatalVisitTrimester3.Value.ToString("MM-dd-yy"), stringTable, "J", row, wsp);
MergeCells($"J{row}", $"J{row + 1}", wsp.Worksheet);
}
InsertText(entry.TetanusStatus, stringTable, "K", row, wsp);
MergeCells($"K{row}", $"K{row + 1}", wsp.Worksheet);
if (entry.DateTetanusToxiodVaccine1 != null)
{
InsertText(entry.DateTetanusToxiodVaccine1.Value.ToString("MM-dd-yy"), stringTable, "L", row, wsp);
MergeCells($"L{row}", $"L{row + 1}", wsp.Worksheet);
}
if (entry.DateTetanusToxiodVaccine2 != null)
{
InsertText(entry.DateTetanusToxiodVaccine2.Value.ToString("MM-dd-yy"), stringTable, "M", row, wsp);
MergeCells($"M{row}", $"M{row + 1}", wsp.Worksheet);
}
if (entry.DateTetanusToxiodVaccine3 != null)
{
InsertText(entry.DateTetanusToxiodVaccine3.Value.ToString("MM-dd-yy"), stringTable, "N", row, wsp);
MergeCells($"N{row}", $"N{row + 1}", wsp.Worksheet);
}
if (entry.DateTetanusToxiodVaccine4 != null)
{
InsertText(entry.DateTetanusToxiodVaccine4.Value.ToString("MM-dd-yy"), stringTable, "O", row, wsp);
MergeCells($"O{row}", $"O{row + 1}", wsp.Worksheet);
}
if (entry.DateTetanusToxiodVaccine5 != null)
{
InsertText(entry.DateTetanusToxiodVaccine5.Value.ToString("MM-dd-yy"), stringTable, "P", row, wsp);
MergeCells($"P{row}", $"P{row + 1}", wsp.Worksheet);
}
if (entry.IronWithFolicDateGiven1 != null)
{
InsertText(entry.IronWithFolicDateGiven1.Value.ToString("MM-dd-yy"), stringTable, "Q", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven1.ToString(), stringTable, "Q", row + 1, wsp);
if (entry.IronWithFolicDateGiven2 != null)
{
InsertText(entry.IronWithFolicDateGiven2.Value.ToString("MM-dd-yy"), stringTable, "R", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven3.ToString(), stringTable, "R", row + 1, wsp);
if (entry.IronWithFolicDateGiven3 != null)
{
InsertText(entry.IronWithFolicDateGiven3.Value.ToString("MM-dd-yy"), stringTable, "S", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven3.ToString(), stringTable, "S", row + 1, wsp);
if (entry.IronWithFolicDateGiven4 != null)
{
InsertText(entry.IronWithFolicDateGiven4.Value.ToString("MM-dd-yy"), stringTable, "T", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven4.ToString(), stringTable, "T", row + 1, wsp);
if (entry.IronWithFolicDateGiven5 != null)
{
InsertText(entry.IronWithFolicDateGiven5.Value.ToString("MM-dd-yy"), stringTable, "U", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven5.ToString(), stringTable, "U", row + 1, wsp);
if (entry.IronWithFolicDateGiven6 != null)
{
InsertText(entry.IronWithFolicDateGiven6.Value.ToString("MM-dd-yy"), stringTable, "V", row, wsp);
}
InsertText(entry.IronWithFolicNumberGiven6.ToString(), stringTable, "V", row + 1, wsp);
if (entry.DateSTITested != null)
{
InsertText(entry.DateSTITested.Value.ToString("MM-dd-yy"), stringTable, "W", row, wsp);
MergeCells($"W{row}", $"W{row + 1}", wsp.Worksheet);
}
if (entry.DateSTIResult != null)
{
InsertText(entry.DateSTIResult.Value.ToString("MM-dd-yy"), stringTable, "X", row, wsp);
MergeCells($"X{row}", $"X{row + 1}", wsp.Worksheet);
}
if (entry.DateSTIPenicillin != null)
{
InsertText(entry.DateSTIPenicillin.Value.ToString("MM-dd-yy"), stringTable, "Y", row, wsp);
MergeCells($"Y{row}", $"Y{row + 1}", wsp.Worksheet);
}
if (entry.PregnancyDateTerminated != null)
{
InsertText(entry.PregnancyDateTerminated.Value.ToString("MM-dd-yy"), stringTable, "Z", row, wsp);
MergeCells($"Z{row}", $"Z{row + 1}", wsp.Worksheet);
}
InsertText(entry.PregnancyOutcome, stringTable, "AA", row, wsp);
InsertText(entry.PregnancyGender, stringTable, "AA", row + 1, wsp);
InsertText(entry.BirthWeight.ToString(), stringTable, "AB", row, wsp);
MergeCells($"AB{row}", $"AB{row + 1}", wsp.Worksheet);
InsertText(entry.PlaceOfHealthFacility, stringTable, "AC", row, wsp);
MergeCells($"AC{row}", $"AC{row + 1}", wsp.Worksheet);
InsertText(entry.PlaceOfNIO, stringTable, "AD", row, wsp);
MergeCells($"AD{row}", $"AD{row + 1}", wsp.Worksheet);
InsertText(entry.AttendedBy, stringTable, "AE", row, wsp);
MergeCells($"AE{row}", $"AE{row + 1}", wsp.Worksheet);
InsertText(entry.Remarks, stringTable, "AF", row, wsp);
MergeCells($"AF{row}", $"AF{row + 1}", wsp.Worksheet);
// ITERATEOR
row += 2;
}
var ms = new MemoryStream();
doc.Clone(ms);
return(ms);
}
}
public async System.Threading.Tasks.Task <Stream> ConvertFpToPdfAsync(string formId)
{
var form = await _appDbContext
.TCL_FPs
.Include(p => p.Entries)
.FirstOrDefaultAsync(p => p.TCL_FPId == formId);
var fileName = @"Templates\FP_Template.xlsx";
// Open the document for editing.
using (SpreadsheetDocument doc =
SpreadsheetDocument.CreateFromTemplate(fileName))//.Open(fileName, true))
{
var wbp = doc.WorkbookPart;
var sheet = wbp.Workbook.Descendants <Sheet>().First();
var wsp = (WorksheetPart)(wbp.GetPartById(sheet.Id));
var stringTable = wbp.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
uint row = 3;
foreach (var entry in form.Entries)
{
InsertText(entry.DateOfRegistration.ToString("MM-dd-yy"), stringTable, "A", row, wsp);
MergeCells($"A{row}", $"A{row + 1}", wsp.Worksheet);
InsertText(entry.FamilySerialNumber, stringTable, "B", row, wsp);
MergeCells($"B{row}", $"B{row + 1}", wsp.Worksheet);
InsertText(entry.Name, stringTable, "C", row, wsp);
MergeCells($"C{row}", $"C{row + 1}", wsp.Worksheet);
InsertText(entry.Address, stringTable, "D", row, wsp);
MergeCells($"D{row}", $"D{row + 1}", wsp.Worksheet);
InsertText(entry.Age.ToString(), stringTable, "E", row, wsp);
InsertText(entry.BirthDate.ToString("MM-dd-yy"), stringTable, "E", row + 1, wsp);
InsertText(entry.TypeOfClient, stringTable, "F", row, wsp);
MergeCells($"F{row}", $"F{row + 1}", wsp.Worksheet);
InsertText(entry.PresentMethod, stringTable, "G", row, wsp);
InsertText(entry.PreviousMethod, stringTable, "G", row + 1, wsp);
if (entry.DateNextService1 != null)
{
InsertText(entry.DateNextService1.Value.ToString("MM-dd-yy"), stringTable, "H", row, wsp);
}
if (entry.DateAccomplishedService1 != null)
{
InsertText(entry.DateAccomplishedService1.Value.ToString("MM-dd-yy"), stringTable, "H", row + 1, wsp);
}
if (entry.DateNextService2 != null)
{
InsertText(entry.DateNextService2.Value.ToString("MM-dd-yy"), stringTable, "I", row, wsp);
}
if (entry.DateAccomplishedService2 != null)
{
InsertText(entry.DateAccomplishedService2.Value.ToString("MM-dd-yy"), stringTable, "I", row + 1, wsp);
}
if (entry.DateNextService3 != null)
{
InsertText(entry.DateNextService3.Value.ToString("MM-dd-yy"), stringTable, "J", row, wsp);
}
if (entry.DateAccomplishedService3 != null)
{
InsertText(entry.DateAccomplishedService3.Value.ToString("MM-dd-yy"), stringTable, "J", row + 1, wsp);
}
if (entry.DateNextService4 != null)
{
InsertText(entry.DateNextService4.Value.ToString("MM-dd-yy"), stringTable, "K", row, wsp);
}
if (entry.DateAccomplishedService4 != null)
{
InsertText(entry.DateAccomplishedService4.Value.ToString("MM-dd-yy"), stringTable, "K", row + 1, wsp);
}
if (entry.DateNextService5 != null)
{
InsertText(entry.DateNextService5.Value.ToString("MM-dd-yy"), stringTable, "L", row, wsp);
}
if (entry.DateAccomplishedService5 != null)
{
InsertText(entry.DateAccomplishedService5.Value.ToString("MM-dd-yy"), stringTable, "L", row + 1, wsp);
}
if (entry.DateNextService6 != null)
{
InsertText(entry.DateNextService6.Value.ToString("MM-dd-yy"), stringTable, "M", row, wsp);
}
if (entry.DateAccomplishedService6 != null)
{
InsertText(entry.DateAccomplishedService6.Value.ToString("MM-dd-yy"), stringTable, "M", row + 1, wsp);
}
if (entry.DateNextService7 != null)
{
InsertText(entry.DateNextService7.Value.ToString("MM-dd-yy"), stringTable, "N", row, wsp);
}
if (entry.DateAccomplishedService7 != null)
{
InsertText(entry.DateAccomplishedService7.Value.ToString("MM-dd-yy"), stringTable, "N", row + 1, wsp);
}
if (entry.DateNextService8 != null)
{
InsertText(entry.DateNextService8.Value.ToString("MM-dd-yy"), stringTable, "O", row, wsp);
}
if (entry.DateAccomplishedService8 != null)
{
InsertText(entry.DateAccomplishedService8.Value.ToString("MM-dd-yy"), stringTable, "O", row + 1, wsp);
}
if (entry.DateNextService9 != null)
{
InsertText(entry.DateNextService9.Value.ToString("MM-dd-yy"), stringTable, "P", row, wsp);
}
if (entry.DateAccomplishedService9 != null)
{
InsertText(entry.DateAccomplishedService9.Value.ToString("MM-dd-yy"), stringTable, "P", row + 1, wsp);
}
if (entry.DateNextService10 != null)
{
InsertText(entry.DateNextService10.Value.ToString("MM-dd-yy"), stringTable, "Q", row, wsp);
}
if (entry.DateAccomplishedService10 != null)
{
InsertText(entry.DateAccomplishedService10.Value.ToString("MM-dd-yy"), stringTable, "Q", row + 1, wsp);
}
if (entry.DateNextService11 != null)
{
InsertText(entry.DateNextService11.Value.ToString("MM-dd-yy"), stringTable, "R", row, wsp);
}
if (entry.DateAccomplishedService11 != null)
{
InsertText(entry.DateAccomplishedService11.Value.ToString("MM-dd-yy"), stringTable, "R", row + 1, wsp);
}
if (entry.DateNextService12 != null)
{
InsertText(entry.DateNextService12.Value.ToString("MM-dd-yy"), stringTable, "S", row, wsp);
}
if (entry.DateAccomplishedService12 != null)
{
InsertText(entry.DateAccomplishedService12.Value.ToString("MM-dd-yy"), stringTable, "S", row + 1, wsp);
}
if (entry.DropoutDate != null)
{
InsertText(entry.DropoutDate.Value.ToString("MM-dd-yy"), stringTable, "T", row, wsp);
MergeCells($"T{row}", $"T{row + 1}", wsp.Worksheet);
}
if (!string.IsNullOrEmpty(entry.DropoutReason))
{
InsertText(entry.DropoutReason, stringTable, "U", row, wsp);
MergeCells($"U{row}", $"U{row + 1}", wsp.Worksheet);
}
if (!string.IsNullOrEmpty(entry.Remarks))
{
InsertText(entry.Remarks, stringTable, "V", row, wsp);
MergeCells($"V{row}", $"V{row + 1}", wsp.Worksheet);
}
// ITERATEOR
row += 2;
}
var ms = new MemoryStream();
doc.Clone(ms);
return(ms);
}
}
public async System.Threading.Tasks.Task <Stream> ConvertNepiToPdfAsync(string formId)
{
var form = await _appDbContext
.TCL_NEPIs
.Include(p => p.Entries)
.FirstOrDefaultAsync(p => p.TCL_NEPIId == formId);
var fileName = @"Templates\NEPI_Template.xlsx";
// Open the document for editing.
using (SpreadsheetDocument doc =
SpreadsheetDocument.CreateFromTemplate(fileName))//.Open(fileName, true))
{
var wbp = doc.WorkbookPart;
var sheet = wbp.Workbook.Descendants <Sheet>().First();
var wsp = (WorksheetPart)(wbp.GetPartById(sheet.Id));
var stringTable = wbp.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
uint row = 5;
foreach (var entry in form.Entries)
{
InsertText(entry.DateOfRegistration.Value.ToString("MM-dd-yy"), stringTable, "A", row, wsp);
InsertText(entry.DateOfBirth.ToString("MM-dd-yy"), stringTable, "B", row, wsp);
InsertText(entry.FamilySerialNumber, stringTable, "C", row, wsp);
InsertText(entry.NHTS, stringTable, "D", row, wsp);
InsertText(entry.NameOfChild, stringTable, "E", row, wsp);
InsertText(entry.Weight.ToString(), stringTable, "F", row, wsp);
InsertText(entry.Height.ToString(), stringTable, "G", row, wsp);
InsertText(entry.Gender, stringTable, "H", row, wsp);
InsertText(entry.NameOfMother, stringTable, "I", row, wsp);
InsertText(entry.Address, stringTable, "J", row, wsp);
if (entry.DateNewbornScreeningReferral != null)
{
InsertText(entry.DateNewbornScreeningReferral.Value.ToString("MM-dd-yy"), stringTable, "K", row, wsp);
}
if (entry.DateNewbornScreeningDone != null)
{
InsertText(entry.DateNewbornScreeningDone.Value.ToString("MM-dd-yy"), stringTable, "L", row, wsp);
}
InsertText(entry.CPABTTStatus, stringTable, "M", row, wsp);
if (entry.CPABTTAssessed != null)
{
InsertText(entry.CPABTTAssessed.Value.ToString("MM-dd-yy"), stringTable, "N", row, wsp);
}
InsertText(entry.ChildExclusiveBreastFeed1.ToString(), stringTable, "O", row, wsp);
InsertText(entry.ChildExclusiveBreastFeed2.ToString(), stringTable, "P", row, wsp);
InsertText(entry.ChildExclusiveBreastFeed3.ToString(), stringTable, "Q", row, wsp);
InsertText(entry.ChildExclusiveBreastFeed4.ToString(), stringTable, "R", row, wsp);
InsertText(entry.ChildExclusiveBreastFeed5.ToString(), stringTable, "S", row, wsp);
if (entry.ChildExclusiveBreastFeed6 != null)
{
InsertText(entry.ChildExclusiveBreastFeed6.Value.ToString("MM-dd-yy"), stringTable, "T", row, wsp);
}
InsertText(entry.ComplimentaryFeeding6.ToString(), stringTable, "U", row, wsp);
InsertText(entry.ComplimentaryFeeding7.ToString(), stringTable, "V", row, wsp);
InsertText(entry.ComplimentaryFeeding8.ToString(), stringTable, "W", row, wsp);
if (entry.BCG != null)
{
InsertText(entry.BCG.Value.ToString("MM-dd-yy"), stringTable, "X", row, wsp);
}
if (entry.HepaB1Within24hrs != null)
{
InsertText(entry.HepaB1Within24hrs.Value.ToString("MM-dd-yy"), stringTable, "Y", row, wsp);
}
if (entry.HepaB1MoreThan24hrs != null)
{
InsertText(entry.HepaB1MoreThan24hrs.Value.ToString("MM-dd-yy"), stringTable, "Z", row, wsp);
}
if (entry.Pentavalent1 != null)
{
InsertText(entry.Pentavalent1.Value.ToString("MM-dd-yy"), stringTable, "AA", row, wsp);
}
if (entry.Pentavalent2 != null)
{
InsertText(entry.Pentavalent2.Value.ToString("MM-dd-yy"), stringTable, "AB", row, wsp);
}
if (entry.Pentavalent3 != null)
{
InsertText(entry.Pentavalent3.Value.ToString("MM-dd-yy"), stringTable, "AC", row, wsp);
}
if (entry.OPV1 != null)
{
InsertText(entry.OPV1.Value.ToString("MM-dd-yy"), stringTable, "AD", row, wsp);
}
if (entry.OPV2 != null)
{
InsertText(entry.OPV2.Value.ToString("MM-dd-yy"), stringTable, "AE", row, wsp);
}
if (entry.OPV3 != null)
{
InsertText(entry.OPV3.Value.ToString("MM-dd-yy"), stringTable, "AF", row, wsp);
}
if (entry.IPV != null)
{
InsertText(entry.IPV.Value.ToString("MM-dd-yy"), stringTable, "AG", row, wsp);
}
if (entry.MCV1 != null)
{
InsertText(entry.MCV1.Value.ToString("MM-dd-yy"), stringTable, "AH", row, wsp);
}
if (entry.MCV2 != null)
{
InsertText(entry.MCV2.Value.ToString("MM-dd-yy"), stringTable, "AI", row, wsp);
}
if (entry.DateFullyImmunizedChild != null)
{
InsertText(entry.DateFullyImmunizedChild.Value.ToString("MM-dd-yy"), stringTable, "AJ", row, wsp);
}
if (entry.RotaVirusVaccine1 != null)
{
InsertText(entry.RotaVirusVaccine1.Value.ToString("MM-dd-yy"), stringTable, "AK", row, wsp);
}
if (entry.RotaVirusVaccine2 != null)
{
InsertText(entry.RotaVirusVaccine2.Value.ToString("MM-dd-yy"), stringTable, "AL", row, wsp);
}
if (entry.PCV1 != null)
{
InsertText(entry.PCV1.Value.ToString("MM-dd-yy"), stringTable, "AM", row, wsp);
}
if (entry.PCV2 != null)
{
InsertText(entry.PCV2.Value.ToString("MM-dd-yy"), stringTable, "AN", row, wsp);
}
if (entry.PCV3 != null)
{
InsertText(entry.PCV3.Value.ToString("MM-dd-yy"), stringTable, "AO", row, wsp);
}
InsertText(entry.VitaminA1.ToString(), stringTable, "AP", row, wsp);
InsertText(entry.VitaminA2.ToString(), stringTable, "AQ", row, wsp);
InsertText(entry.VitaminA3.ToString(), stringTable, "AR", row, wsp);
InsertText(entry.IronA1.ToString(), stringTable, "AS", row, wsp);
InsertText(entry.IronA2.ToString(), stringTable, "AT", row, wsp);
InsertText(entry.MNP1.ToString(), stringTable, "AU", row, wsp);
InsertText(entry.MNP1.ToString(), stringTable, "AV", row, wsp);
if (entry.Deworming != null)
{
InsertText(entry.Deworming.Value.ToString("MM-dd-yy"), stringTable, "AW", row, wsp);
}
InsertText(entry.Remarks, stringTable, "AX", row, wsp);
// ITERATEOR
row += 1;
}
var ms = new MemoryStream();
doc.Clone(ms);
return(ms);
}
}
public override void Write()
{
ExportFileName = PopulatedName(ExportFileName);
if (!String.IsNullOrWhiteSpace(ExportFileName))
{
DocProperties["FileName"] = ExportFileName;
DocProperties["TableCount"] = _dataSet.Tables.Count.ToString();
if (PopulatePropertiesOnly)
{
if (_dataSet != null)
{
foreach (DataTable dTable in _dataSet.Tables)
{
if (dTable.Rows.Count > 0)
{
foreach (DataColumn dColumn in dTable.Columns)
{
DocProperties[dColumn.ColumnName] = dTable.Rows[0][dColumn].ToString();
}
}
}
}
}
switch (DestinationType)
{
case OfficeFileType.WordDocument:
WordprocessingDocument doc;
if (File.Exists(TemplateFileName))
{
doc = WordprocessingDocument.CreateFromTemplate(TemplateFileName);
doc = (WordprocessingDocument)doc.SaveAs(ExportFileName);
}
else
{
doc = WordprocessingDocument.Create(ExportFileName, WordprocessingDocumentType.Document);
}
CustomFilePropertiesPart customProp = doc.CustomFilePropertiesPart;
if (customProp == null)
{
customProp = doc.AddCustomFilePropertiesPart();
}
SetFileProperties(customProp);
MainDocumentPart mainDoc = doc.MainDocumentPart;
if (mainDoc == null)
{
mainDoc = doc.AddMainDocumentPart();
}
DocumentSettingsPart settingsPart = mainDoc.GetPartsOfType <DocumentSettingsPart>().First();
UpdateFieldsOnOpen updateFields = new UpdateFieldsOnOpen
{
Val = new OnOffValue(true)
};
settingsPart.Settings.PrependChild <UpdateFieldsOnOpen>(updateFields);
settingsPart.Settings.Save();
if (!PopulatePropertiesOnly)
{
if (mainDoc.Document == null)
{
mainDoc.Document = new word.Document();
}
word.Body body = new word.Body();
bool firstTable = true;
foreach (DataTable dt in _dataSet.Tables)
{
if (!firstTable)
{
body.Append(GetPageBreak());
}
else
{
firstTable = false;
}
body.Append(GetParagraph(dt.TableName));
body.Append(GetWordTable(dt));
}
mainDoc.Document.Append(body);
}
mainDoc.Document.Save();
doc.Dispose();
break;
case OfficeFileType.ExcelWorkbook:
SpreadsheetDocument spreadSheet;
if (File.Exists(TemplateFileName))
{
spreadSheet = SpreadsheetDocument.CreateFromTemplate(TemplateFileName);
spreadSheet = (SpreadsheetDocument)spreadSheet.SaveAs(ExportFileName);
}
else
{
spreadSheet = SpreadsheetDocument.Create(ExportFileName, SpreadsheetDocumentType.Workbook);
spreadSheet.Save();
}
using (SpreadsheetDocument workbook = spreadSheet)
{
CustomFilePropertiesPart excelCustomProp = workbook.AddCustomFilePropertiesPart();
SetFileProperties(excelCustomProp);
if (workbook.WorkbookPart == null)
{
workbook.AddWorkbookPart();
}
if (workbook.WorkbookPart.Workbook == null)
{
workbook.WorkbookPart.Workbook = new excel.Workbook();
}
if (workbook.WorkbookPart.Workbook.Sheets == null)
{
workbook.WorkbookPart.Workbook.Sheets = new excel.Sheets();
}
excel.Sheets sheets = workbook.WorkbookPart.Workbook.Sheets;
foreach (DataTable table in _dataSet.Tables)
{
excel.SheetData sheetData = null;
WorksheetPart sheetPart = null;
excel.Sheet sheet = null;
foreach (OpenXmlElement element in sheets.Elements())
{
if (element is Sheet)
{
sheet = (Sheet)element;
if (sheet.Name.Value.Equals(table.TableName, StringComparison.CurrentCultureIgnoreCase))
{
//Assign the sheetPart
sheetPart = (WorksheetPart)workbook.WorkbookPart.GetPartById(sheet.Id.Value);
sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
break;
}
}
sheet = null;
}
if (sheet == null)
{
sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); //Create a new WorksheetPart
sheetData = new excel.SheetData(); //create a new SheetData
sheetPart.Worksheet = new excel.Worksheet(sheetData); /// Create a new Worksheet with the sheetData and link it to the sheetPart...
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); //get the ID of the sheetPart.
sheet = new excel.Sheet()
{
Id = relationshipId, SheetId = 1, Name = table.TableName
}; //create a new sheet
sheets.Append(sheet); //append the sheet to the sheets.
}
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
}
if (PrintTableHeader)
{
excel.Row headerRow = new excel.Row();
foreach (string column in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(GetColumnName(table.Columns[column]))
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (DataRow dsrow in table.Rows)
{
excel.Row newRow = new excel.Row();
foreach (String col in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(dsrow[col].ToString()) //
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Save();
}
workbook.WorkbookPart.Workbook.Save();
workbook.Save();
workbook.Close();
}
break;
}
}
}
/// <summary>
/// Creates the workbook to which all sheets will be added, using the specified template file
/// </summary>
/// <returns>The newly created <see cref="SpreadsheetDocument"/></returns>
private SpreadsheetDocument CreateTargetWorkbook()
{
Trace.Indent();
try
{
SpreadsheetDocument targetWorkbook = null;
SpreadsheetDocument workbook = null;
try
{
// we will either use a template from a path they specify OR one of the built in templates
if (!string.IsNullOrEmpty(Settings.TemplateFilePath) && File.Exists(Settings.TemplateFilePath))
{
Trace.WriteLine(string.Format("Creating target workbook from template at path ({0})", Settings.TemplateFilePath), TraceCategory);
workbook = SpreadsheetDocument.CreateFromTemplate(Settings.TemplateFilePath);
workbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook = (SpreadsheetDocument)workbook.SaveAs(OutputPath);
}
else
{
Trace.WriteLine(string.Format("Creating target workbook from compiled template ({0})", Settings.TemplateName), TraceCategory);
// this is done to juggle limitations in open xml creating a file from a stream where the stream is readonly
var resourceName = string.Format(BuiltInTemplateNameFormat, Settings.TemplateName);
if (Assembly.GetExecutingAssembly().GetManifestResourceNames().Contains(resourceName, StringComparer.OrdinalIgnoreCase))
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
{
workbook = SpreadsheetDocument.Open(stream, false);
var temp = workbook.SaveAs(OutputPath);
workbook.Close();
temp.Close(); // this allows us to reopen the file below
}
targetWorkbook = SpreadsheetDocument.Open(OutputPath, true);
targetWorkbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook.Save();
}
else
{
var message = string.Format("Could not locate embedded resource template {0}.", resourceName);
Trace.TraceError(message);
throw new Exception(message);
}
}
Trace.WriteLine(string.Format("Created target workbook at {0}", OutputPath), TraceCategory);
}
finally
{
if (workbook != null)
{
workbook.Dispose();
}
}
return(targetWorkbook);
}
catch (Exception err)
{
Trace.TraceError("Error creating output workbook from template");
Trace.TraceError(err.ToString());
throw;
}
finally
{
Trace.Unindent();
}
}