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);
        }