public void TestReadExcel() { foreach (string aFile in Directory.GetFiles(Path.Combine(ResourceFolderPath, @"excel"))) { // Resolve resource Assert.IsTrue(File.Exists(aFile)); // Interpret as Excel using (FileStream file = new FileStream(aFile, FileMode.Open, FileAccess.Read)) { IWorkbook aWB = WorkbookFactory.Create(file); // EExcelVersion.XLS.readWorkbook(aXls.getInputStream()); Assert.IsNotNull(aWB); // Check whether all required sheets are present ISheet aSheet = aWB.GetSheetAt(0); Assert.IsNotNull(aSheet); ExcelReadOptions <UseType> aReadOptions = new ExcelReadOptions <UseType>().SetLinesToSkip(1) .SetLineIndexShortName(0); aReadOptions.AddColumn(0, "id", UseType.required, "string", true); aReadOptions.AddColumn(1, "name", UseType.required, "string", false); CodeListDocument aCodeList = ExcelSheetToCodeList04.ConvertToSimpleCodeList(aSheet, aReadOptions, "ExampleList", "1.0", new Uri("urn:www.helger.com:names:example"), new Uri("urn:www.helger.com:names:example-1.0"), null); string aDoc = new Genericode04CodeListSerializer().Serialize(aCodeList); Assert.IsNotNull(aDoc); CodeListDocument aCLDoc = new Genericode04CodeListSerializer().Deserialize(aDoc); Assert.IsNotNull(aCLDoc); } } }
private static void testReadAndWriteValid(string aRes) { Genericode04CodeListSerializer serializer = new Genericode04CodeListSerializer(); // Read code list CodeListDocument aCLDoc = serializer.Deserialize(aRes); Assert.IsNotNull(aCLDoc); // Write again string aDoc2 = serializer.Serialize(aCLDoc); Assert.IsNotNull(aDoc2); // Read code list again CodeListDocument aCLDoc2 = serializer.Deserialize(aDoc2); }
public static CodeListDocument ConvertToSimpleCodeList(ISheet aExcelSheet, ExcelReadOptions <UseType> aReadOptions, string sCodeListName, string sCodeListVersion, Uri aCanonicalUri, Uri aCanonicalVersionUri, Uri aLocationURI) { if (aExcelSheet == null) { throw new ArgumentNullException("ExcelSheet"); } if (aReadOptions == null) { throw new ArgumentNullException("ReadOptions"); } CodeListDocument ret = new CodeListDocument(); XmlDocument xmlDocument = new XmlDocument(); // create annotation Annotation aAnnotation = new Annotation(); AnyOtherContent aContent = new AnyOtherContent(); XmlElement xmlElement = xmlDocument.CreateElement(QNAME_ANNOTATION.Name, QNAME_ANNOTATION.Namespace); xmlElement.InnerText = "Automatically created by ph-genericode-net. Do NOT edit."; aContent.Any.Add(xmlElement); aAnnotation.AppInfo = aContent; ret.Annotation = aAnnotation; // create identification Identification aIdentification = new Identification(); aIdentification.ShortName = Genericode04Helper.CreateShortName(sCodeListName); aIdentification.Version = sCodeListVersion; aIdentification.CanonicalUri = aCanonicalUri.ToString(); aIdentification.CanonicalVersionUri = aCanonicalVersionUri.ToString(); if (aLocationURI != null) { aIdentification.LocationUri.Add(aLocationURI.ToString()); } ret.Identification = aIdentification; // create columns IList <ExcelReadColumn <UseType> > aExcelColumns = aReadOptions.GetAllColumns(); ColumnSet aColumnSet = new ColumnSet(); foreach (ExcelReadColumn <UseType> aExcelColumn in aExcelColumns) { // Read short name (required) string sShortName = aExcelSheet.GetRow(aReadOptions.GetLineIndexShortName()) .GetCell(aExcelColumn.GetIndex()) .StringCellValue; // Read long name (optional) String sLongName = null; if (aReadOptions.GetLineIndexLongName() >= 0) { sLongName = aExcelSheet.GetRow(aReadOptions.GetLineIndexLongName()) .GetCell(aExcelColumn.GetIndex()) .StringCellValue; } // Create Genericode column set Column aColumn = Genericode04Helper.CreateColumn(aExcelColumn.GetColumnID(), aExcelColumn.GetUseType(), sShortName, sLongName, aExcelColumn.GetDataType()); // add column aColumnSet.Items.Add(aColumn); if (aExcelColumn.IsKeyColumn()) { // Create key definition Key aKey = Genericode04Helper.CreateKey(aExcelColumn.GetColumnID() + "Key", sShortName, sLongName, aColumn); // Add key aColumnSet.Items1.Add(aKey); } } ret.ColumnSet = aColumnSet; // Read items SimpleCodeList aSimpleCodeList = new SimpleCodeList(); // Determine the row where reading should start int nRowIndex = aReadOptions.GetLinesToSkip(); while (true) { // Read a single excel row IRow aExcelRow = aExcelSheet.GetRow(nRowIndex++); if (aExcelRow == null) { break; } // Create Genericode row Row aRow = new Row(); foreach (ExcelReadColumn <UseType> aExcelColumn in aExcelColumns) { var cell = aExcelRow.GetCell(aExcelColumn.GetIndex()); string sValue = ""; switch (cell.CellType) { case CellType.Numeric: sValue = cell.NumericCellValue.ToString(); break; case CellType.String: sValue = cell.StringCellValue; break; case CellType.Boolean: sValue = cell.BooleanCellValue.ToString(); break; case CellType.Error: sValue = cell.ErrorCellValue.ToString(); break; case CellType.Blank: sValue = ""; break; case CellType.Formula: sValue = cell.CellFormula; break; case CellType.Unknown: sValue = ""; break; default: break; } if (!string.IsNullOrEmpty(sValue) || aExcelColumn.GetUseType() == UseType.required) { // Create a single value in the current row Value aValue = new Value(); aValue.ColumnRef = Genericode04Helper.GetColumnOfID(aColumnSet, aExcelColumn.GetColumnID()).Id; aValue.SimpleValue = Genericode04Helper.CreateSimpleValue(sValue); aRow.Value.Add(aValue); } } aSimpleCodeList.Row.Add(aRow); } ret.SimpleCodeList = aSimpleCodeList; return(ret); }