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)
{
//Console.WriteLine("read");
m_xmlReader.Read();
}
isRow = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row);
}
if (!isRow)
{
return(false);
}
//Console.WriteLine("New Row");
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;
bool hasFormula = false;
HyperLinkIndex hyperlinkIndex = null;
string a_s = String.Empty;
string a_t = String.Empty;
string a_r = String.Empty;
string f = String.Empty;
int col = 0;
int row = 0;
while (m_xmlReader.Read())
{
//Console.WriteLine("m_xmlReader.LocalName:{0}",m_xmlReader.LocalName);
//Console.WriteLine("m_xmlReader.Value:{0}",m_xmlReader.Value);
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_f)
{
hasFormula = true;
}
else if (m_xmlReader.LocalName == XlsxWorksheet.N_v || m_xmlReader.LocalName == XlsxWorksheet.N_t)
{
hasValue = true;
hasFormula = false;
}
else
{
//Console.WriteLine("m_xmlReader.LocalName:{0}",m_xmlReader.LocalName);
// Ignore
}
}
bool hasHyperLinkFormula = false;
if (m_xmlReader.NodeType == XmlNodeType.Text && hasFormula)
{
string formula = m_xmlReader.Value.ToString();
if (formula.StartsWith("HYPERLINK("))
{
hyperlinkIndex = this.ReadHyperLinkFormula(sheet.Name, formula);
}
}
if (m_xmlReader.NodeType == XmlNodeType.Text && hasValue)
{
double number;
object o = m_xmlReader.Value;
//Console.WriteLine("O:{0}", o);
if (double.TryParse(o.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out number))
{
// numeric
o = number;
}
if (null != a_t && a_t == XlsxWorksheet.A_s)
{
// string
var sstStr = m_workbook.SST[int.Parse(o.ToString())];
//Console.WriteLine(sstStr);
o = sstStr.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();
}
}
//Console.WriteLine(o);
if (col - 1 < m_cellsValues.Length)
{
if (hyperlinkIndex != null)
{
var co = new XlsCell(o);
co.HyperLinkIndex = hyperlinkIndex;
m_cellsValues[col - 1] = co;
hyperlinkIndex = null;
}
else
{
m_cellsValues[col - 1] = o;
}
}
}
else
{
//Console.WriteLine(m_xmlReader.Value.ToString());
}
}
if (m_emptyRowCount > 0)
{
//Console.WriteLine("Again");
m_savedCellsValues = m_cellsValues;
return(ReadSheetRow(sheet));
}
m_depth++;
return(true);
}
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 < table.Rows.Count)
{
if (col < table.Rows[row].Count)
{
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 pushCellValue(XlsBiffBlankCell cell)
{
double _dValue;
bool hasValue = true;
switch (cell.ID)
{
case BIFFRECORDTYPE.BOOLERR:
if (cell.ReadByte(7) == 0)
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(6) != 0);
}
else
{
hasValue = false;
}
break;
case BIFFRECORDTYPE.BOOLERR_OLD:
if (cell.ReadByte(8) == 0)
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(7) != 0);
}
else
{
hasValue = false;
}
break;
case BIFFRECORDTYPE.INTEGER:
case BIFFRECORDTYPE.INTEGER_OLD:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffIntegerCell)cell).Value);
break;
case BIFFRECORDTYPE.NUMBER:
case BIFFRECORDTYPE.NUMBER_OLD:
_dValue = ((XlsBiffNumberCell)cell).Value;
m_cellsValues[cell.ColumnIndex] =
new XlsCell(_dValue);
break;
case BIFFRECORDTYPE.LABEL:
case BIFFRECORDTYPE.LABEL_OLD:
case BIFFRECORDTYPE.RSTRING:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffLabelCell)cell).Value);
break;
case BIFFRECORDTYPE.LABELSST:
string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex);
m_cellsValues[cell.ColumnIndex] = new XlsCell(tmp);
break;
case BIFFRECORDTYPE.RK:
_dValue = ((XlsBiffRKCell)cell).Value;
m_cellsValues[cell.ColumnIndex] = new XlsCell(_dValue);
break;
case BIFFRECORDTYPE.MULRK:
var _rkCell = (XlsBiffMulRKCell)cell;
bool hasSet = false;
for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++)
{
_dValue = _rkCell.GetValue(j);
m_cellsValues[j] = new XlsCell(_dValue);
hasSet = true;
}
hasValue = hasSet;
break;
case BIFFRECORDTYPE.BLANK:
case BIFFRECORDTYPE.BLANK_OLD:
case BIFFRECORDTYPE.MULBLANK:
// Skip blank cells
hasValue = false;
break;
case BIFFRECORDTYPE.FORMULA:
case BIFFRECORDTYPE.FORMULA_OLD:
object _oValue = ((XlsBiffFormulaCell)cell).Value;
if (!(_oValue is FORMULAERROR))
{
m_cellsValues[cell.ColumnIndex] =
new XlsCell(_oValue);
}
else
{
hasValue = false;
}
break;
default:
hasValue = false;
break;
}
if (hasValue)
{
XlsBiffHyperLink hyperLink = m_globals.GetHyperLink(cell.RowIndex, cell.ColumnIndex);
if (hyperLink != null)
{
m_cellsValues[cell.ColumnIndex].SetHyperLink(hyperLink.Url);
}
}
}