private void ReadWorkbookRels(Stream xmlFileStream) { using (XmlReader reader = XmlReader.Create(xmlFileStream)) { while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element && reader.LocalName == N_rel) { string rid = reader.GetAttribute(A_id); for (int i = 0; i < sheets.Count; i++) { XlsxWorksheet tempSheet = sheets[i]; if (tempSheet.RID == rid) { tempSheet.Path = reader.GetAttribute(A_target); sheets[i] = tempSheet; break; } } } } xmlFileStream.Close(); } }
private bool ReadSheetRow(XlsxWorksheet sheet) { if (sheet.ColumnsCount < 0) { return false; } if (null == m_xmlReader) return false; if (m_emptyRowCount != 0) { m_cellsValues = new object[sheet.ColumnsCount]; m_emptyRowCount--; m_depth++; return true; } if (m_savedCellsValues != null) { m_cellsValues = m_savedCellsValues; m_savedCellsValues = null; m_depth++; return true; } bool isRow = false; bool isSheetData = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_sheetData); if (isSheetData) { isRow = m_xmlReader.ReadToFollowing(XlsxWorksheet.N_row, m_namespaceUri); } else { if (m_xmlReader.LocalName == XlsxWorksheet.N_row && m_xmlReader.NodeType == XmlNodeType.EndElement) { m_xmlReader.Read(); } isRow = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row); } if (isRow) { m_cellsValues = new object[sheet.ColumnsCount]; if (sheet.ColumnsCount > 13) { int i = sheet.ColumnsCount; } var rowIndexText = m_xmlReader.GetAttribute(XlsxWorksheet.A_r); Debug.Assert(rowIndexText!=null); int rowIndex=int.Parse(rowIndexText); if (rowIndex != (m_depth + 1)) { m_emptyRowCount = rowIndex - m_depth - 1; } bool hasValue = false; string a_s = String.Empty; string a_t = String.Empty; string a_r = String.Empty; int col = 0; int row = 0; while (m_xmlReader.Read()) { if (m_xmlReader.Depth == 2) { break; } if (m_xmlReader.NodeType == XmlNodeType.Element) { hasValue = false; if (m_xmlReader.LocalName == XlsxWorksheet.N_c) { a_s = m_xmlReader.GetAttribute(XlsxWorksheet.A_s); a_t = m_xmlReader.GetAttribute(XlsxWorksheet.A_t); a_r = m_xmlReader.GetAttribute(XlsxWorksheet.A_r); XlsxDimension.XlsxDim(a_r, out col, out row); } else if (m_xmlReader.LocalName == XlsxWorksheet.N_v || m_xmlReader.LocalName == XlsxWorksheet.N_t) { hasValue = true; } else { // Ignore } } if (m_xmlReader.NodeType == XmlNodeType.Text && hasValue) { double number; object o = m_xmlReader.Value; #region Read Cell Value if (double.TryParse(o.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out number)) { // numeric o=number; } if (null!=a_t&&a_t==XlsxWorksheet.A_s) { // string o=m_workbook.SST[int.Parse(o.ToString())].ConvertEscapeChars(); } else if (null!=a_t&&a_t==XlsxWorksheet.N_inlineStr) { // string inline o=o.ToString().ConvertEscapeChars(); } else if (a_t=="b") { // boolean o=m_xmlReader.Value=="1"; } else if (a_t=="str") { // string o=m_xmlReader.Value; } else if (null!=a_s) { //something else XlsxXf xf=m_workbook.Styles.CellXfs[int.Parse(a_s)]; if (xf.ApplyNumberFormat&&o!=null&&o.ToString()!=string.Empty&& IsDateTimeStyle(xf.NumFmtId)) { o=number.ConvertFromOATime(); } else if (xf.NumFmtId==49) { o=o.ToString(); } } #endregion if (col - 1 < m_cellsValues.Length) { m_cellsValues[col - 1] = o; } } } if (m_emptyRowCount > 0) { m_savedCellsValues = m_cellsValues; return ReadSheetRow(sheet); } m_depth++; return true; } else { return false; } }
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table) { // ReadTo HyperLinks Node if (m_xmlReader == null) { return false; } m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks); if (m_xmlReader.IsEmptyElement) { return false; } // Read Realtionship Table Stream sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path); var hyperDict = new Dictionary<string, string>(); if (sheetRelStream != null) { using (XmlReader reader = XmlReader.Create(sheetRelStream)) { while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel) { string rid = reader.GetAttribute(XlsxWorkbook.A_id); Debug.Assert(rid!=null); hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target); } } sheetRelStream.Close(); } } // Read All HyperLink Node while (m_xmlReader.Read()) { if (m_xmlReader.NodeType != XmlNodeType.Element) break; if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink) break; string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref); string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display); string rid = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid); string hyperlink = display; Debug.Assert(rid!=null); if (hyperDict.ContainsKey(rid)) { hyperlink = hyperDict[rid]; } int col = -1; int row = -1; XlsxDimension.XlsxDim(aref, out col, out row); if (col >= 1 && row >= 1) { row = row - 1; col = col - 1; if (row == 0 && m_isFirstRowAsColumnNames) { // TODO(fanfeilong): string value = table.Columns[col].ColumnName; var cell = new XlsCell(value); cell.SetHyperLink(hyperlink); table.Columns[col].DefaultValue = cell; } else { object value = table.Rows[row][col]; var cell = new XlsCell(value); cell.SetHyperLink(hyperlink); table.Rows[row][col] = cell; } } } // Close m_xmlReader.Close(); if (m_sheetStream != null) { m_sheetStream.Close(); } return true; }
private void ReadSheetGlobals(XlsxWorksheet sheet) { if (!ResetSheetReader(sheet)) { return; } //count rows and cols in case there is no dimension elements m_namespaceUri = null; int rows = 0; int cols = 0; int biggestColumn = 0; while (m_xmlReader.Read()) { if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_worksheet) { //grab the namespaceuri from the worksheet element m_namespaceUri = m_xmlReader.NamespaceURI; } if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_dimension) { string dimValue = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref); sheet.Dimension = new XlsxDimension(dimValue); break; } if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row) { rows++; } // check cells so we can find size of sheet if can't work it out from dimension or // col elements (dimension should have been set before the cells if it was available) // ditto for cols if (sheet.Dimension == null && cols == 0 && m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_c) { string refAttribute = m_xmlReader.GetAttribute(XlsxWorksheet.A_r); if (refAttribute != null) { int[] thisRef = refAttribute.ReferenceToColumnAndRow(); if (thisRef[1] > biggestColumn) { biggestColumn = thisRef[1]; } } } } // if we didn't get a dimension element then use the calculated rows/cols to create it if (sheet.Dimension == null) { if (cols == 0) { cols = biggestColumn; } if (rows == 0 || cols == 0) { sheet.IsEmpty = true; return; } sheet.Dimension = new XlsxDimension(rows, cols); //we need to reset our position to sheet data if (!ResetSheetReader(sheet)) { return; } } // read up to the sheetData element. if this element is empty then // there aren't any rows and we need to null out dimension Debug.Assert(m_namespaceUri!=null); m_xmlReader.ReadToFollowing(XlsxWorksheet.N_sheetData, m_namespaceUri); if (m_xmlReader.IsEmptyElement) { sheet.IsEmpty=true; } }
private bool ResetSheetReader(XlsxWorksheet sheet) { if (m_sheetStream != null) { m_sheetStream.Close(); m_sheetStream = null; } if (m_xmlReader != null) { m_xmlReader.Close(); m_xmlReader = null; } m_sheetStream = m_zipWorker.GetWorksheetStream(sheet.Path); if (null == m_sheetStream) { return false; } m_xmlReader = XmlReader.Create(m_sheetStream); if (null == m_xmlReader) { return false; } return true; }
private bool ReadSheetRow(XlsxWorksheet sheet) { if (null == m_xmlReader) return false; if (m_emptyRowCount != 0) { m_cellsValues = new object[sheet.ColumnsCount]; m_emptyRowCount--; m_depth++; return true; } if (m_savedCellsValues != null) { m_cellsValues = m_savedCellsValues; m_savedCellsValues = null; m_depth++; return true; } bool isRow = false; bool isSheetData = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_sheetData); if(isSheetData) { isRow = m_xmlReader.ReadToFollowing(XlsxWorksheet.N_row, m_namespaceUri); } else { if (m_xmlReader.LocalName == XlsxWorksheet.N_row && m_xmlReader.NodeType == XmlNodeType.EndElement) { m_xmlReader.Read(); } isRow = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row); //Console.WriteLine("isRow:{0}/{1}/{2}", isRow,m_xmlReader.NodeType, m_xmlReader.LocalName); } if (isRow) { m_cellsValues = new object[sheet.ColumnsCount]; int rowIndex = int.Parse(m_xmlReader.GetAttribute(XlsxWorksheet.A_r)); if (rowIndex != (m_depth + 1)) { m_emptyRowCount = rowIndex - m_depth - 1; } bool hasValue = false; string a_s = String.Empty; string a_t = String.Empty; string a_r = String.Empty; int col = 0; int row = 0; while (m_xmlReader.Read()) { if (m_xmlReader.Depth == 2) break; if (m_xmlReader.NodeType == XmlNodeType.Element) { hasValue = false; if (m_xmlReader.LocalName == XlsxWorksheet.N_c) { a_s = m_xmlReader.GetAttribute(XlsxWorksheet.A_s); a_t = m_xmlReader.GetAttribute(XlsxWorksheet.A_t); a_r = m_xmlReader.GetAttribute(XlsxWorksheet.A_r); XlsxDimension.XlsxDim(a_r, out col, out row); } else if (m_xmlReader.LocalName == XlsxWorksheet.N_v || m_xmlReader.LocalName == XlsxWorksheet.N_t) { hasValue = true; } } if (m_xmlReader.NodeType == XmlNodeType.Text && hasValue) { double number; object o = m_xmlReader.Value; var style = NumberStyles.Any; var culture = CultureInfo.InvariantCulture; if (double.TryParse(o.ToString(), style, culture, out number)) o = number; #region Read Cell Value if (null != a_t && a_t == XlsxWorksheet.A_s) //if string { o = m_workbook.SST[int.Parse(o.ToString())].ConvertEscapeChars(); } // Requested change 4: missing (it appears that if should be else if) else if (null != a_t && a_t == XlsxWorksheet.N_inlineStr) //if string inline { o = o.ToString().ConvertEscapeChars(); } else if (a_t == "b") //boolean { o = m_xmlReader.Value == "1"; } else if (null != a_s) //if something else { XlsxXf xf = m_workbook.Styles.CellXfs[int.Parse(a_s)]; if (xf.ApplyNumberFormat && o != null && o.ToString() != string.Empty && IsDateTimeStyle(xf.NumFmtId)) { o = number.ConvertFromOATime(); } else if (xf.NumFmtId == 49) { o = o.ToString(); } } #endregion if (col - 1 < m_cellsValues.Length) { m_cellsValues[col - 1] = o; } } } if (m_emptyRowCount > 0) { m_savedCellsValues = m_cellsValues; return ReadSheetRow(sheet); } m_depth++; return true; } else { //Console.WriteLine(m_xmlReader.LocalName.ToString()); return false; } }
private void ReadSheetGlobals(XlsxWorksheet sheet) { if (m_xmlReader != null) m_xmlReader.Close(); if (m_sheetStream != null) m_sheetStream.Close(); m_sheetStream = m_zipWorker.GetWorksheetStream(sheet.Path); if (null == m_sheetStream) return; m_xmlReader = XmlReader.Create(m_sheetStream); //count rows and cols in case there is no dimension elements int rows = 0; int cols = 0; m_namespaceUri = null; int biggestColumn = 0; //used when no col elements and no dimension while (m_xmlReader.Read()) { if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_worksheet) { //grab the namespaceuri from the worksheet element m_namespaceUri = m_xmlReader.NamespaceURI; } if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_dimension) { string dimValue = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref); sheet.Dimension = new XlsxDimension(dimValue); break; } //removed: Do not use col to work out number of columns as this is really for defining formatting, so may not contain all columns //if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_col) // cols++; if (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row) rows++; //check cells so we can find size of sheet if can't work it out from dimension or col elements (dimension should have been set before the cells if it was available) //ditto for cols if (sheet.Dimension == null && cols == 0 && m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_c) { var refAttribute = m_xmlReader.GetAttribute(XlsxWorksheet.A_r); if (refAttribute != null) { var thisRef = refAttribute.ReferenceToColumnAndRow(); if (thisRef[1] > biggestColumn) biggestColumn = thisRef[1]; } } } //if we didn't get a dimension element then use the calculated rows/cols to create it if (sheet.Dimension == null) { if (cols == 0) cols = biggestColumn; if (rows == 0 || cols == 0) { sheet.IsEmpty = true; return; } sheet.Dimension = new XlsxDimension(rows, cols); //we need to reset our position to sheet data m_xmlReader.Close(); m_sheetStream.Close(); m_sheetStream = m_zipWorker.GetWorksheetStream(sheet.Path); m_xmlReader = XmlReader.Create(m_sheetStream); } //read up to the sheetData element. if this element is empty then there aren't any rows and we need to null out dimension m_xmlReader.ReadToFollowing(XlsxWorksheet.N_sheetData, m_namespaceUri); if (m_xmlReader.IsEmptyElement) { sheet.IsEmpty = true; } }
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table) { // ReadTo HyperLinks Node if (m_xmlReader == null) { //Console.WriteLine("m_xmlReader is null"); return false; } //Console.WriteLine(m_xmlReader.Depth.ToString()); m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks); if (m_xmlReader.IsEmptyElement) { //Console.WriteLine("not find hyperlink"); return false; } // Read All HyperLink Node while (m_xmlReader.Read()) { if (m_xmlReader.NodeType != XmlNodeType.Element) break; if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink) break; string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref); string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display); ////Console.WriteLine("{0}:{1}", aref.Substring(1), display); int col = -1; int row = -1; XlsxDimension.XlsxDim(aref, out col,out row); //Console.WriteLine("{0}:[{1},{2}]",aref, row, col); if (col >=1 && row >=1) { row = row - 1; col = col - 1; if (row==0 &&m_isFirstRowAsColumnNames) { // TODO(fanfeilong): var value = table.Columns[col].ColumnName; XlsCell cell = new XlsCell(value); cell.SetHyperLink(display); table.Columns[col].DefaultValue = cell; } else { var value = table.Rows[row][col]; var cell = new XlsCell(value); cell.SetHyperLink(display); //Console.WriteLine(cell.MarkDownText); table.Rows[row][col] = cell; } } } // Close m_xmlReader.Close(); if (m_sheetStream != null) { m_sheetStream.Close(); } return true; }
private void ReadWorkbook(Stream xmlFileStream) { sheets=new List<XlsxWorksheet>(); using (XmlReader reader=XmlReader.Create(xmlFileStream)) { while (reader.Read()) { if (reader.NodeType==XmlNodeType.Element&&reader.LocalName==N_sheet){ var sheetIdText = reader.GetAttribute(A_sheetId); Debug.Assert(sheetIdText!=null); var sheetName=reader.GetAttribute(A_name); var sheetId=int.Parse(sheetIdText); var rid=reader.GetAttribute(A_rid); var sheet=new XlsxWorksheet(sheetName, sheetId, rid); sheets.Add(sheet); } } xmlFileStream.Close(); } }
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table) { // ReadTo HyperLinks Node if (m_xmlReader == null) { //Console.WriteLine("m_xmlReader is null"); return false; } //Console.WriteLine(m_xmlReader.Depth.ToString()); m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks); if (m_xmlReader.IsEmptyElement) { //Console.WriteLine("not find hyperlink"); return false; } // Read Realtionship Table //Console.WriteLine("sheetrel:{0}", sheet.Path); var sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path); var hyperDict = new Dictionary<string, string>(); if (sheetRelStream != null) { using (var reader = XmlReader.Create(sheetRelStream)) { while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel) { string rid = reader.GetAttribute(XlsxWorkbook.A_id); hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target); } } sheetRelStream.Close(); } } // Read All HyperLink Node while (m_xmlReader.Read()) { if (m_xmlReader.NodeType != XmlNodeType.Element) break; if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink) break; string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref); string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display); string rid = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid); ////Console.WriteLine("{0}:{1}", aref.Substring(1), display); //Console.WriteLine("hyperlink:{0}",hyperDict[rid]); var hyperlink = display; if (hyperDict.ContainsKey(rid)) { hyperlink = hyperDict[rid]; } int col = -1; int row = -1; XlsxDimension.XlsxDim(aref, out col,out row); //Console.WriteLine("{0}:[{1},{2}]",aref, row, col); if (col >=1 && row >=1) { row = row - 1; col = col - 1; if (row==0 &&m_isFirstRowAsColumnNames) { // TODO(fanfeilong): var value = table.Columns[col].ColumnName; XlsCell cell = new XlsCell(value); cell.SetHyperLink(hyperlink); table.Columns[col].DefaultValue = cell; } else { var value = table.Rows[row][col]; var cell = new XlsCell(value); cell.SetHyperLink(hyperlink); //Console.WriteLine(cell.MarkDownText); table.Rows[row][col] = cell; } } } // Close m_xmlReader.Close(); if (m_sheetStream != null) { m_sheetStream.Close(); } return true; }