public NameValueCollection CreateDocxMassive(ExcelDocumentReader excelDocumentReader, NameValueCollection collectionForReplace, string sPathTemplateDocx, string sColumnIdName, out string errTags)
{
NameValueCollection oResult = new NameValueCollection();
try
{
errTags = "";
string cfSelected = string.Empty;
var columnNames = excelDocumentReader.GetColumnNames();
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
resourceFileManager.SetResources();
string sColumnIdSubject = resourceFileManager.getConfigData(sColumnIdName);
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
Int16 iIndiceFile = 1;
foreach (var a in excelDocumentReader.getData)
{
//Apro il FileStyleUriParser template DocX da valorizzare
using (Novacode.DocX document = Novacode.DocX.Load(sPathTemplateDocx))
{
// Ciclo tra i nomi delle colonne
foreach (var columnName in columnNames)
{
// sostituisco il valore dei nomi colonne formattandole come TAG che sia suppone sia presente nel template DOCX
document.ReplaceText("«" + columnName + "»", a[columnName].ToString());
if (columnName == sColumnIdSubject)
{
cfSelected = a[columnName].ToString();
}
}
for (int i = 0; i < collectionForReplace.Count; i++)
{
document.ReplaceText("«" + collectionForReplace.GetKey(i) + "»", collectionForReplace.GetValues(i)[0]);
}
// Verifico che tutti i TAG presenti all'interno del file docx siano stati valorizzati
var lineBreaks = document.FindUniqueByPattern("«*\\b[^»]*»", System.Text.RegularExpressions.RegexOptions.None);
// Qualora siano presenti restituisco i tag non ripristinati come segnalazione di una anomalia
if (lineBreaks.Count > 0)
{
foreach (var tagErr in lineBreaks)
{
errTags += tagErr + " ";
}
throw new ArgumentException("Sono stati trovati i seguenti tag non sostituiti: " + errTags);
//break;
}
else
{
document.SaveAs(excelDocumentReader.sTransactionPath + iIndiceFile + ".docx");
oResult.Add(cfSelected, excelDocumentReader.sTransactionPath + iIndiceFile + ".docx");
}
}
iIndiceFile += 1;
}
}
catch (Exception ex)
{ throw new ArgumentException(ex.Message); }
finally { }
return(oResult);
}
public Boolean GetDataFromAnag(ExcelDocumentReader excelDocumentReader, string sColumnName, string sIdAnag, out string sValueAnag)
{
Boolean bResult = false;
sValueAnag = "";
try
{
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
resourceFileManager.SetResources();
var columnNames = excelDocumentReader.GetColumnNames();
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
foreach (var a in excelDocumentReader.getData)
{
// Cerco nella lista il codice fiscale per individuare univocamente il soggetto
if (a[resourceFileManager.getConfigData("CancAnagCodOCFNameField")].ToString() == sIdAnag)
{
sValueAnag = a[a.ColumnNames.Count() + int.Parse(resourceFileManager.getConfigData(sColumnName))].ToString();
break;
}
}
bResult = true;
}
catch (Exception ex)
{ throw ex; }
finally { }
return(bResult);
}
public override void Execute(WorkItem workItem)
{
try
{
m_workItem = workItem;
DocumentText dtCheck;
switch (workItem.Extension)
{
case "doc":
WordDocumentReader wrdRdr = new WordDocumentReader(workItem.BaseFileName);
try
{
dtCheck = wrdRdr.Read();
}
catch (Exception)
{
return; // failed to discover source document
}
CompareDiscoveryForDoc(workItem.BaseFileName, workItem.DiscoveredText, dtCheck);
break;
case "ppt":
PptDocumentReader pptRdr = new PptDocumentReader(workItem.BaseFileName);
try
{
dtCheck = pptRdr.Read();
}
catch (Exception)
{
return; // failed to discover source document
}
CompareDiscoveryForPpt(workItem.BaseFileName, workItem.DiscoveredText, dtCheck);
break;
case "xls":
ExcelDocumentReader xlsRdr = new ExcelDocumentReader(workItem.BaseFileName);
try
{
dtCheck = xlsRdr.Read();
}
catch (Exception)
{
return; // failed to discover source document
}
CompareDiscoveryForXls(workItem.BaseFileName, workItem.DiscoveredText, dtCheck);
break;
default:
throw new Exception("File extension not expected : " + workItem.Extension);
}
}
catch (Exception e)
{
workItem.FailureMode = FailureMode.DiscoveryMismatch;
throw e;
}
}
public Boolean GetCsvRecordKV(ExcelDocumentReader excelDocumentReader, string sColumnIdSubject, string cfFilter, out NameValueCollection lfieldXls)
{
Boolean bResult = false;
lfieldXls = new NameValueCollection();
try
{
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
resourceFileManager.SetResources();
var columnNames = excelDocumentReader.GetColumnNames();
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
var listColumns = columnNames.Cast <string>().ToList();
foreach (var a in excelDocumentReader.getData)
{
// Ciclo tra i nomi delle colonne
foreach (var columnName in columnNames)
{
// Cerco nella lista il codice fiscale per individuare univocamente il soggetto
if (columnName.ToUpper().Trim() == sColumnIdSubject.ToUpper().Trim())
{
if (a[columnName].ToString().ToUpper().Trim() == cfFilter.ToUpper().Trim())
{
for (int i = 0; i < a.Count; i++)
{
DateTime dateValue;
if (DateTime.TryParseExact(a[i].ToString(), "dd/MM/yyyy hh:mm:ss", new CultureInfo("it-IT"), DateTimeStyles.None, out dateValue))
{
lfieldXls.Add(listColumns[i].ToString().Replace(" ", "_"), a[i].ToString().Substring(0, 10));
}
else
{
lfieldXls.Add(listColumns[i].ToString().Replace(" ", "_"), a[i].ToString());
}
}
}
break;
}
}
if (lfieldXls.Count > 0)
{
break;
}
}
bResult = true;
}
catch (Exception ex)
{ throw ex; }
finally { }
return(bResult);
}
public Boolean CreateDocxMassive(string sNameFileDocx, string sNameFileXls, out string errTags)
{
Boolean bResult = false;
try
{
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
errTags = "";
resourceFileManager.SetResources();
string sPathWork = resourceFileManager._resourceManager.GetString("ExcelWorkFolder");
ExcelDocumentReader excelDocumentReader = new ExcelDocumentReader(sNameFileXls);
var columnNames = excelDocumentReader.GetColumnNames();
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
Int16 iIndiceFile = 1;
foreach (var a in excelDocumentReader.getData)
{
//Apro il FileStyleUriParser template DocX da valorizzare
using (DocX document = DocX.Load(@sPathWork + sNameFileDocx))
{
// Ciclo tra i nomi delle colonne
foreach (var columnName in columnNames)
{
// sostituisco il valore dei nomi colonne formattandole come TAG che sia suppone sia presente nel template DOCX
document.ReplaceText("«" + columnName + "»", a[columnName].ToString());
}
// Verifico che tutti i TAG presenti all'interno del file docx siano stati valorizzati
var lineBreaks = document.FindUniqueByPattern("«*\\b[^»]*»", System.Text.RegularExpressions.RegexOptions.None);
// Qualora siano presenti restituisco i tag non ripristinati come segnalazione di una anomalia
if (lineBreaks.Count > 0)
{
foreach (var tagErr in lineBreaks)
{
errTags += tagErr + " ";
}
break;
}
else
{
document.SaveAs(excelDocumentReader.sTransactionPath + iIndiceFile + ".docx");
}
}
iIndiceFile += 1;
}
bResult = true;
}
catch (Exception ex)
{ throw ex; }
finally { }
return(bResult);
}
private void CleanFile(string fileName, List<ContentType> listContentTypes)
{
try
{
ExcelDocumentReader excelReader = new ExcelDocumentReader(fileName, true);
excelReader.Clean(listContentTypes);
}
catch (System.Exception ex)
{
Logger.LogError(ex);
throw;
}
finally
{
}
}
private void CleanFile(string fileName, List<ContentType> listContentTypes, List<Exclusion> listExclusion)
{
try
{
using (ExcelDocumentReader excelReader = new ExcelDocumentReader(fileName, true))
{
excelReader.Clean(listContentTypes, listExclusion);
}
}
catch (System.Exception ex)
{
Logger.LogError("Excel Binary cleaning failed");
Logger.LogError(ex);
throw;
}
}
public override bool VerifyFile(TempFileForActions tempFile)
{
try
{
using (ExcelDocumentReader reader = new ExcelDocumentReader(tempFile.TempFile, true))
{
reader.Read();
return true;
}
}
catch (System.Exception ex)
{
Logger.LogError("Excel Verification failed");
Logger.LogError(ex);
}
return false;
}
public void readDataFilePathAllRows()
{
extent.LoadConfig(AppDomain.CurrentDomain.BaseDirectory + "../../extent-config.xml");
// Initializing test
test = extent
.StartTest("ExcelDocumentReader", "Opens and reads excel datasheet")
.AssignCategory("ExcelUse");
test.Log(LogStatus.Info, "Retrieving xls sheet");
// define test parameters
string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory + "../../test.xls");
string sheetName = "panther";
test.Log(LogStatus.Pass, sheetName + " found!");
test.Log(LogStatus.Info, "Creating a new List");
try
{
// create new List to accept output of GetAllCells()
List <Dataset> myDataset = new List <Dataset>();
test.Log(LogStatus.Pass, "List Created");
// create new ExcelDocumentReader object
var edr = new ExcelDocumentReader();
// call GettAllCells()
myDataset = edr.GetAllCells(filePath, sheetName);
test.Log(LogStatus.Info, "Asserting the data into previously created list");
// Asserts
Assert.NotNull(myDataset);
Assert.True(myDataset.Count == 9);
Assert.True(myDataset[3].ColValues.Equals("Cam"));
Assert.True(myDataset[8].ColName.Equals("position"));
Assert.True(myDataset[0].Column == 0);
test.Log(LogStatus.Pass, "Data Asserted");
}
catch (Exception ex)
{
test.Log(LogStatus.Fail, "<pre>" + ex.StackTrace + "</pre>");
throw;
}
}
public Boolean GetCsvRecord(ExcelDocumentReader excelDocumentReader, string sColumnIdSubject, string cfFilter, out string sVauleAnag)
{
Boolean bResult = false;
sVauleAnag = "";
try
{
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
resourceFileManager.SetResources();
var columnNames = excelDocumentReader.GetColumnNames();
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
var listColumns = columnNames.Cast <string>().ToList();
foreach (var a in excelDocumentReader.getData)
{
// Ciclo tra i nomi delle colonne
foreach (var columnName in columnNames)
{
// Cerco nella lista il codice fiscale per individuare univocamente il soggetto
if (columnName == sColumnIdSubject)
{
if (a[columnName].ToString() == cfFilter)
{
for (int i = 0; i < a.Count; i++)
{
sVauleAnag += listColumns[i].ToString() + "|" + a[i].ToString() + "|";
}
}
break;
}
}
if (sVauleAnag != "")
{
break;
}
}
bResult = true;
}
catch (Exception ex)
{ throw ex; }
finally { }
return(bResult);
}
public Boolean GetSampleRecords(ExcelDocumentReader excelDocumentReader, string sColumnIdSubject, out List <string> lCodiceFiscale)
{
Boolean bResult = false;
lCodiceFiscale = new List <string>();
try
{
ResourceFileManager resourceFileManager = ResourceFileManager.Instance;
resourceFileManager.SetResources();
int iRowSelected = int.Parse(resourceFileManager.getConfigData("IscReadSampleCount"));
//string sColumnIdSubject = resourceFileManager._resourceManager.GetString("IscSearchValueUniqueFromAnag");
var columnNames = excelDocumentReader.GetColumnNames();
//var getData = excelDocumentReader.getData<LinqToExcel.Row>();
// Ciclo i singoli record estrapolati dal file excel
Int16 iIndiceFile = 1;
foreach (var a in excelDocumentReader.getData)
{
// Ciclo tra i nomi delle colonne
foreach (var columnName in columnNames)
{
// Cerco nella lista il codice fiscale per individuare univocamente il soggetto
if (columnName == sColumnIdSubject)
{
lCodiceFiscale.Add(a[columnName].ToString());
break;
}
}
iIndiceFile += 1;
if (iRowSelected < iIndiceFile)
{
break;
}
}
bResult = true;
}
catch (Exception ex)
{ throw ex; }
finally { }
return(bResult);
}
/// <summary>
/// Original incarnation of lightspeed clean method. Left for comparison. There is at least one document that gets corrupted by the
/// lightspeed clean user action as opposed to the method below. see Rally DE8912 in Professional & Deltaview project.
/// </summary>
/// <param name="listContentTypes"></param>
private void DoLightSpeedClean(List<ContentType> listContentTypes)
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
switch (m_ft)
{
case Workshare.Policy.FileType.WordDocument:
using (WordDocumentReader Word2003Reader1 = new WordDocumentReader(m_sFileForBinClean, true))
{
Word2003Reader1.Clean(listContentTypes);
}
break;
case Workshare.Policy.FileType.ExcelSheet:
using (ExcelDocumentReader Excel2003Reader1 = new ExcelDocumentReader(m_sFileForBinClean, true))
{
Excel2003Reader1.Clean(listContentTypes);
}
break;
case Workshare.Policy.FileType.PowerPoint:
using (Workshare.FCS.Lite.PptDocumentReader Ppt2003Reader1 = new PptDocumentReader(m_sFileForBinClean, true))
{
Ppt2003Reader1.Clean(listContentTypes);
}
break;
case Workshare.Policy.FileType.WordDocumentX:
case Workshare.Policy.FileType.WordDocumentMacroX:
case Workshare.Policy.FileType.WordDocumentTemplateX:
case Workshare.Policy.FileType.WordDocumentMacroTemplateX:
{
string outFileName = System.IO.Path.GetTempFileName();
using (DocxDocumentReader Word2007Reader1 = new DocxDocumentReader(m_sFileForBinClean))
{
using (Stream outStr = File.Open(outFileName, FileMode.Create))
{
Word2007Reader1.CleanTo(outStr, listContentTypes);
}
}
File.Copy(outFileName, m_sFileForBinClean, true);
File.Delete(outFileName);
break;
}
case Workshare.Policy.FileType.ExcelSheetX:
case Workshare.Policy.FileType.ExcelSheetMacroX:
case Workshare.Policy.FileType.ExcelSheetTemplateX:
case Workshare.Policy.FileType.ExcelSheetMacroTemplateX:
{
string outFileName = System.IO.Path.GetTempFileName();
using (XlsxDocumentReader Excel2007Reader1 = new XlsxDocumentReader(m_sFileForBinClean))
{
using (Stream outStr = File.Open(outFileName, FileMode.Create))
{
Excel2007Reader1.CleanTo(outStr, listContentTypes);
}
}
File.Copy(outFileName, m_sFileForBinClean, true);
File.Delete(outFileName);
break;
}
case Workshare.Policy.FileType.PowerPointX:
case Workshare.Policy.FileType.PowerPointMacroX:
case Workshare.Policy.FileType.PowerPointTemplateX:
case Workshare.Policy.FileType.PowerPointMacroTemplateX:
case Workshare.Policy.FileType.PowerPointShowX:
case Workshare.Policy.FileType.PowerPointMacroShowX:
{
string outFileName = System.IO.Path.GetTempFileName();
using (PptxDocumentReader Ppt2007Reader1 = new PptxDocumentReader(m_sFileForBinClean))
{
using (Stream outStr = File.Open(outFileName, FileMode.Create))
{
Ppt2007Reader1.CleanTo(outStr, listContentTypes);
}
}
File.Copy(outFileName, m_sFileForBinClean, true);
File.Delete(outFileName);
break;
}
default:
break;
}
watch.Stop();
m_binCleanTime = watch.Elapsed.TotalSeconds;
}
DocumentText DiscoverDocument(string sFilename)
{
switch (m_ft)
{
case Workshare.Policy.FileType.WordDocument:
using (WordDocumentReader Word2003Reader1 = new WordDocumentReader(sFilename, false))
{
return Word2003Reader1.Read();
}
case Workshare.Policy.FileType.ExcelSheet:
using (ExcelDocumentReader Excel2003Reader1 = new ExcelDocumentReader(sFilename, false))
{
return Excel2003Reader1.Read();
}
case Workshare.Policy.FileType.PowerPoint:
using (Workshare.FCS.Lite.PptDocumentReader Ppt2003Reader1 = new PptDocumentReader(sFilename, false))
{
return Ppt2003Reader1.Read();
}
case Workshare.Policy.FileType.WordDocumentX:
case Workshare.Policy.FileType.WordDocumentMacroX:
case Workshare.Policy.FileType.WordDocumentTemplateX:
case Workshare.Policy.FileType.WordDocumentMacroTemplateX:
using (DocxDocumentReader Word2007Reader1 = new DocxDocumentReader(sFilename))
{
return Word2007Reader1.Read();
}
case Workshare.Policy.FileType.ExcelSheetX:
case Workshare.Policy.FileType.ExcelSheetMacroX:
case Workshare.Policy.FileType.ExcelSheetTemplateX:
case Workshare.Policy.FileType.ExcelSheetMacroTemplateX:
using (XlsxDocumentReader Excel2007Reader1 = new XlsxDocumentReader(sFilename))
{
return Excel2007Reader1.Read();
}
case Workshare.Policy.FileType.PowerPointX:
case Workshare.Policy.FileType.PowerPointMacroX:
case Workshare.Policy.FileType.PowerPointTemplateX:
case Workshare.Policy.FileType.PowerPointMacroTemplateX:
case Workshare.Policy.FileType.PowerPointShowX:
case Workshare.Policy.FileType.PowerPointMacroShowX:
using (PptxDocumentReader Ppt2007Reader1 = new PptxDocumentReader(sFilename))
{
return Ppt2007Reader1.Read();
}
default:
break;
}
return null;
}
public DocumentText DiscoverDocument(string sFilename)
{
switch (m_ft)
{
case Workshare.Policy.FileType.WordDocument:
using (WordDocumentReader Word2003Reader1 = new WordDocumentReader(sFilename, false))
{
return Word2003Reader1.Read();
}
case Workshare.Policy.FileType.ExcelSheet:
using (ExcelDocumentReader Excel2003Reader1 = new ExcelDocumentReader(sFilename, false))
{
return Excel2003Reader1.Read();
}
case Workshare.Policy.FileType.PowerPoint:
using (Workshare.FCS.Lite.PptDocumentReader Ppt2003Reader1 = new PptDocumentReader(sFilename, false))
{
return Ppt2003Reader1.Read();
}
default:
break;
}
return null;
}
public void DoBinaryClean(List<ContentType> listContentTypes)
{
switch (m_ft)
{
case Workshare.Policy.FileType.WordDocument:
using (WordDocumentReader Word2003Reader1 = new WordDocumentReader(m_filenameIn, true))
{
Word2003Reader1.Clean(listContentTypes);
}
break;
case Workshare.Policy.FileType.ExcelSheet:
using (ExcelDocumentReader Excel2003Reader1 = new ExcelDocumentReader(m_filenameIn, true))
{
Excel2003Reader1.Clean(listContentTypes);
}
break;
case Workshare.Policy.FileType.PowerPoint:
using (Workshare.FCS.Lite.PptDocumentReader Ppt2003Reader1 = new PptDocumentReader(m_filenameIn, true))
{
Ppt2003Reader1.Clean(listContentTypes);
}
break;
default:
break;
}
}
/// <summary>
/// </summary>
/// <returns>A FCS.Lite.DocumentText object</returns>
public FCS.Lite.Base.DocumentText AdvancedRead()
{
if (m_docText == null)
{
FCS.Lite.Base.DocumentReader docReader;
switch (m_fileData.FileType)
{
case FileType.WordDocument:
docReader = new WordDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.PowerPoint:
docReader = new PptDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.ExcelSheet:
docReader = new ExcelDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.WordDocumentX:
case FileType.WordDocumentMacroX:
case FileType.WordDocumentTemplateX:
case FileType.WordDocumentMacroTemplateX:
docReader = new DocxDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.PowerPointX:
case FileType.PowerPointMacroX:
case FileType.PowerPointTemplateX:
case FileType.PowerPointMacroTemplateX:
case FileType.PowerPointShowX:
case FileType.PowerPointMacroShowX:
docReader = new PptxDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.ExcelSheetX:
case FileType.ExcelSheetMacroX:
case FileType.ExcelSheetTemplateX:
case FileType.ExcelSheetMacroTemplateX:
docReader = new XlsxDocumentReader(m_fileData.BinaryFileData);
break;
case FileType.PDFDocument:
docReader = new PdfDocumentReader(m_fileData.BinaryFileData);
break;
default:
return m_docText;
}
using (docReader)
{
m_docText = docReader.Read();
}
}
return m_docText;
}
