/// <summary>
/// DeSerialise the date held in the sheet into a COBieWorkbook
/// </summary>
/// <returns>COBieWorkbook with date imported from XLS file</returns>
public COBieWorkbook Deserialise()
{
try
{
GetXLSFileData(); //Read XLS file into the HSSFWorkbook object
foreach (string sheetname in SheetNames)
{
ISheet excelSheet = XlsWorkbook.GetSheet(sheetname); //get sheet name in XLS file
if (excelSheet != null)
{
ICOBieSheet <COBieRow> thisSheet = GetSheetType(sheetname);
int COBieColumnCount = thisSheet.Columns.Count;
//no checking on Sheet column count to XLS sheet column count, just extract up to the column number in the COBieSheet/Row
int rownumber = 0;
int columnCount = 0;
foreach (IRow row in excelSheet)
{
if (rownumber == 0) //this will be the headers so get how many we have
{
foreach (ICell cell in row)
{
columnCount++;
}
}
else
{
bool addRow = false;
//check we have some data on the row
for (int i = 0; i < columnCount; i++)
{
ICell cell = row.GetCell(i);
if ((cell != null) && (cell.CellType != CellType.Blank))
{
addRow = true;
break;
}
}
//add a none blank row
if (addRow)
{
COBieRow sheetRow = thisSheet.AddNewRow(); //add a new empty COBie row to the sheet
for (int i = 0; i < thisSheet.Columns.Count(); i++) //changed from columnCount to supported column count of the sheet
{
string cellValue = ""; //default value
ICell cell = row.GetCell(i);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Numeric:
if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODate)
{
cellValue = cell.DateCellValue.ToString(Constants.DATE_FORMAT);
}
else if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODateTime)
{
DateTime date = DateTime.Now;
try
{
date = cell.DateCellValue;
}
catch
{
// If we can't read a valid date, just use the current date.
date = DateTime.Now;
}
cellValue = date.ToString(Constants.DATETIME_FORMAT);
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
cellValue = cell.ErrorCellValue.ToString();
break;
case CellType.Blank:
case CellType.Formula:
case CellType.Unknown:
cellValue = cell.StringCellValue;
break;
default:
break;
}
}
if (i < COBieColumnCount) //check we are in the column range of the COBieRow and add value
{
COBieColumn cobieColumn = thisSheet.Columns.Where(idxcol => idxcol.Key == i).Select(idxcol => idxcol.Value).FirstOrDefault();
sheetRow[i] = new COBieCell(cellValue, cobieColumn);
}
}
}
}
rownumber++;
}
WorkBook.Add(thisSheet);
}
}
}
catch (FileNotFoundException)
{
//TODO: Report this
throw;
}
catch (Exception)
{
throw;
}
WorkBook.CreateIndices();
return(WorkBook);
}
private ulong createSheet(string name)
{
var Id = WorkBook.Count > 0 ? WorkBook.Max(x => x.Id) + 1 : 1;
// 向缓存中添加该Id
WorkBook.Add(new WorkBook
{
Id = Id,
Name = name,
//修复创建Sheet的无法找到Root的Bug
Target = $"worksheets/sheet{Id}.xml"
});
// 添加sheetX.xml
var e = ZipArchive.CreateEntry($"xl/worksheets/sheet{Id}.xml", CompressionLevel.Optimal);
using (var stream = e.Open())
using (var sw = new StreamWriter(stream))
{
sw.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"" xmlns:xdr=""http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"" xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"">
<sheetPr />
<dimension ref=""A1"" />
<sheetViews>
<sheetView workbookViewId=""0"">
<selection activeCell=""A1"" sqref=""A1"" />
</sheetView>
</sheetViews>
<sheetFormatPr defaultColWidth=""9"" defaultRowHeight=""13.5"" />
<sheetData>
</sheetData>
<pageMargins left=""0.75"" right=""0.75"" top=""1"" bottom=""1"" header=""0.511805555555556"" footer=""0.511805555555556"" />
<headerFooter />
</worksheet>");
}
// 向[Content_Types].xml中添加sheetX.xml
var e2 = ZipArchive.GetEntry("[Content_Types].xml");
using (var stream = e2.Open())
using (var sr = new StreamReader(stream))
{
var xd = new XmlDocument();
xd.LoadXml(sr.ReadToEnd());
var element = xd.CreateElement("Override", xd.DocumentElement.NamespaceURI);
element.SetAttribute("PartName", $"/xl/worksheets/sheet{Id}.xml");
element.SetAttribute("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
var tmp = xd.GetElementsByTagName("Types")
.Cast <XmlNode>()
.First()
.AppendChild(element);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
string identifier = "rId";
// 向xl/rels中添加
var e5 = ZipArchive.GetEntry("xl/_rels/workbook.xml.rels");
using (var stream = e5.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var relationships = xd.GetElementsByTagName("Relationships")
.Cast <XmlNode>()
.First();
identifier += (relationships.ChildNodes.Count + 1).ToString();
var element = xd.CreateElement("Relationship", xd.DocumentElement.NamespaceURI);
element.SetAttribute("Target", $"worksheets/sheet{Id}.xml");
element.SetAttribute("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
element.SetAttribute("Id", identifier);
relationships.AppendChild(element);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 向workbook.xml添加sheetX.xml
var e3 = ZipArchive.GetEntry("xl/workbook.xml");
using (var stream = e3.Open())
using (var sr = new StreamReader(stream))
{
var xd = new XmlDocument();
xd.LoadXml(sr.ReadToEnd());
var tmp = xd.GetElementsByTagName("sheets")
.Cast <XmlNode>()
.First();
var element = xd.CreateElement("sheet", xd.DocumentElement.NamespaceURI);
tmp.AppendChild(element);
var attr = xd.CreateAttribute("r", "id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
attr.Value = identifier;
element.Attributes.Append(attr);
element.SetAttribute("sheetId", Id.ToString());
element.SetAttribute("name", name);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 向app.xml中添加sheetX.xml
var e4 = ZipArchive.GetEntry("docProps/app.xml");
using (var stream = e4.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var element = xd.CreateElement("vt:lpstr", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes");
element.InnerText = name;
var tmp = xd
.GetElementsByTagName("vt:vector")
.Cast <XmlNode>()
.Single(x => x.Attributes["baseType"].Value == "lpstr");
tmp.AppendChild(element);
tmp.Attributes["size"].Value = (Convert.ToInt32(tmp.Attributes["size"].Value) + 1).ToString();
var tmp2 = xd.GetElementsByTagName("vt:i4")
.Cast <XmlNode>()
.Single();
tmp2.InnerText = (Convert.ToInt32(tmp2.InnerText) + 1).ToString();
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
return(Id);
}
