Ejemplo n.º 1
0
        /// <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);
        }
Ejemplo n.º 2
0
        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);
        }