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