private DataTable CreateTableSchema(int sheetIndex)
{
XlsxWorksheet worksheet = _workbook.Sheets[sheetIndex];
DataTable dataTable = new DataTable(worksheet.Name);
dataTable.ExtendedProperties.Add("visiblestate", worksheet.VisibleState);
dataTable.ExtendedProperties.Add("SkipRows", _skipRows);
dataTable.ExtendedProperties.Add("IsFirstRowAsColumnNames", _isFirstRowAsColumnNames);
//DataTable columns
if (!_isFirstRowAsColumnNames)
{
for (int i = 0, columns = worksheet.ColumnsCount; i < columns; i++)
{
dataTable.Columns.Add(null, typeof(Object));
}
}
else if (_cellsValues != null)
{
for (int index = 0; index < _cellsValues.Length; index++)
{
object cellValues = _cellsValues[index];
if (cellValues != null && cellValues.ToString().Length > 0)
{
Helpers.AddColumnHandleDuplicate(dataTable, cellValues.ToString());
}
else
{
Helpers.AddColumnHandleDuplicate(dataTable, string.Concat(COLUMN, index));
}
}
}
return(dataTable);
}
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 void ReadSheetGlobals(XlsxWorksheet sheet)
{
if (this._xmlReader != null)
{
this._xmlReader.Close();
}
if (this._sheetStream != null)
{
this._sheetStream.Close();
}
this._sheetStream = this._zipWorker.GetWorksheetStream(sheet.Path);
if (this._sheetStream != null)
{
this._xmlReader = XmlReader.Create(this._sheetStream);
while (this._xmlReader.Read())
{
if ((this._xmlReader.NodeType == XmlNodeType.Element) && (this._xmlReader.Name == "dimension"))
{
string attribute = this._xmlReader.GetAttribute("ref");
sheet.Dimension = new XlsxDimension(attribute);
break;
}
}
this._xmlReader.ReadToFollowing("sheetData");
if (this._xmlReader.IsEmptyElement)
{
sheet.IsEmpty = true;
}
}
}
private void ReadSheetGlobals(XlsxWorksheet sheet)
{
_sheetStream = _zipWorker.GetWorksheetStream(sheet.Path);
if (null == _sheetStream)
{
return;
}
_xmlReader = XmlReader.Create(_sheetStream);
while (_xmlReader.Read())
{
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_dimension)
{
var dimValue = _xmlReader.GetAttribute(XlsxWorksheet.A_ref);
if (dimValue.IndexOf(':') > 0)
{
sheet.Dimension = new XlsxDimension(dimValue);
}
else
{
_xmlReader.Close();
_sheetStream.Close();
}
break;
}
}
}
private static IEnumerable <OpenXmlElement> MakeColumns(XlsxWorksheet worksheet)
{
uint i = 0;
return(worksheet.Layout.Columns.Select(column => new Column {
Min = i + 1,
Max = i += (uint)column.Repeat,
Style = column.StyleIndex.HasValue ? new UInt32Value((uint)column.StyleIndex) : null,
Hidden = column.Hidden ? new BooleanValue(true) : null,
CustomWidth = column.Size.HasValue ? new BooleanValue(true) : null,
Width = column.Size,
//BestFit = http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.aspx
}));
}
private DataSet ReadDataSet()
{
var dataset = new DataSet();
Dictionary <int, XlsxDimension> demensionDict = DetectDemension();
for (int sheetIndex = 0; sheetIndex < m_workbook.Sheets.Count; sheetIndex++)
{
XlsxWorksheet sheet = m_workbook.Sheets[sheetIndex];
var table = new DataTable(m_workbook.Sheets[sheetIndex].Name);
ReadSheetGlobals(sheet);
sheet.Dimension = demensionDict[sheetIndex];
if (sheet.Dimension == null)
{
continue;
}
m_depth = 0;
m_emptyRowCount = 0;
// Reada Columns
for (int i = 0; i < sheet.ColumnsCount; i++)
{
table.Columns.Add(i.ToString(CultureInfo.InvariantCulture), typeof(Object));
}
// Read Sheet Rows
table.BeginLoadData();
while (ReadSheetRow(sheet))
{
table.Rows.Add(m_cellsValues);
}
if (table.Rows.Count > 0)
{
dataset.Tables.Add(table);
}
// Read HyperLinks
ReadHyperLinks(sheet, table);
table.EndLoadData();
}
dataset.AcceptChanges();
dataset.FixDataTypes();
return(dataset);
}
private void ReadSheetGlobals(XlsxWorksheet sheet)
{
if (_xmlReader != null)
{
_xmlReader.Close();
}
if (_sheetStream != null)
{
_sheetStream.Close();
}
_sheetStream = _zipWorker.GetWorksheetStream(sheet.Path);
if (null == _sheetStream)
{
return;
}
_xmlReader = XmlReader.Create(_sheetStream);
while (_xmlReader.Read())
{
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_dimension)
{
string dimValue = _xmlReader.GetAttribute(XlsxWorksheet.A_ref);
// if (dimValue.IndexOf(':') > 0)
// {
sheet.Dimension = new XlsxDimension(dimValue);
// }
// else
// {
// _xmlReader.Close();
// _sheetStream.Close();
// }
break;
}
}
//read up to the sheetData element. if this element is empty then there aren't any rows and we need to null out dimension
_xmlReader.ReadToFollowing(XlsxWorksheet.N_sheetData);
if (_xmlReader.IsEmptyElement)
{
sheet.IsEmpty = true;
}
}
private Dictionary <int, XlsxDimension> DetectDemension()
{
var dict = new Dictionary <int, XlsxDimension>();
for (int sheetIndex = 0; sheetIndex < m_workbook.Sheets.Count; sheetIndex++)
{
XlsxWorksheet sheet = m_workbook.Sheets[sheetIndex];
ReadSheetGlobals(sheet);
if (sheet.Dimension != null)
{
m_depth = 0;
m_emptyRowCount = 0;
// ����100��
int detectRows = Math.Min(sheet.Dimension.LastRow, 100);
int maxColumnCount = 0;
while (detectRows > 0)
{
ReadSheetRow(sheet);
maxColumnCount = Math.Max(LastIndexOfNonNull(m_cellsValues) + 1, maxColumnCount);
detectRows--;
}
// ����ʵ�ʼ����������и���С��Ԫ��������������
if (maxColumnCount < sheet.Dimension.LastCol)
{
dict[sheetIndex] = new XlsxDimension(sheet.Dimension.LastRow, maxColumnCount);
}
else
{
dict[sheetIndex] = sheet.Dimension;
}
}
else
{
dict[sheetIndex] = sheet.Dimension;
}
}
return(dict);
}
private bool InitializeSheetBatchRead()
{
if (ResultsCount <= 0)
{
return(false);
}
XlsxWorksheet worksheet = _workbook.Sheets[_sheetIndex];
ReadSheetGlobals(worksheet);
if (worksheet.Dimension == null)
{
return(false);
}
if (_skipRows >= worksheet.RowsCount)
{
throw new Exception(string.Format(@"Sheet '{0}' contains less rows than SkipRows property of IExcelDataReader.", _sheetName));
}
_depth = 0;
_emptyRowCount = 0;
if (_skipRows > 0)
{
_xmlReader.ReadToFollowing(XlsxWorksheet.N_row, _namespaceUri);
for (int i = 0; i < _skipRows; i++)
{
_xmlReader.Skip();
}
_depth = _skipRows;
}
if (_isFirstRowAsColumnNames)
{
ReadSheetBatchRow(worksheet);
}
_isFirstRead = false;
return(true);
}
private void ReadWorkbookRels(Stream xmlFileStream, List <XlsxWorksheet> sheets)
{
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;
}
}
}
}
}
}
private void ReadSheetGlobals(XlsxWorksheet sheet)
{
if (_xmlReader != null)
{
_xmlReader.Dispose();
}
if (_sheetStream != null)
{
_sheetStream.Dispose();
}
_sheetStream = _zipWorker.GetWorksheetStream(sheet.Path);
if (null == _sheetStream)
{
return;
}
_xmlReader = XmlReader.Create(_sheetStream);
while (_xmlReader.Read())
{
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_dimension)
{
string dimValue = _xmlReader.GetAttribute(XlsxWorksheet.A_ref);
sheet.Dimension = new XlsxDimension(dimValue);
break;
}
}
_xmlReader.ReadToFollowing(XlsxWorksheet.N_sheetData);
if (_xmlReader.IsEmptyElement)
{
sheet.IsEmpty = true;
}
}
public WorksheetPart Do(WorkbookPart workbookPart, XlsxWorksheet documentWorksheet, string id)
{
var worksheet = new Worksheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var data = MakeData(documentWorksheet).ToArray();
var rows = data.OfType <Row>().OfType <OpenXmlElement>().ToArray();
var mergeCells = data.OfType <MergeCell>().OfType <OpenXmlElement>().ToArray();
var rowBreaks = data.OfType <Break>().OfType <OpenXmlElement>().ToArray();
var selection = new Selection()
{
ActiveCell = "A1",
SequenceOfReferences = new ListValue <StringValue> {
InnerText = "A1"
}
};
worksheet.Append(
new SheetProperties(new PageSetupProperties()
{
FitToPage = true
}),
new SheetDimension {
Reference = "A1:F6"
},
new SheetViews(new SheetView(selection)
{
WorkbookViewId = 0U,
TabSelected = documentWorksheet.IsActive,
ZoomScale = (uint)documentWorksheet.ZoomScale,
ZoomScaleNormal = 100U
}),
documentWorksheet.RowFormat,
new Columns(MakeColumns(documentWorksheet)),
new SheetData(rows),
mergeCells.Any() ? new MergeCells(mergeCells)
{
Count = (uint)mergeCells.Count()
} : null,
documentWorksheet.PrintOptions,
documentWorksheet.PageMargins,
new PageSetup {
PaperSize = 9U, FitToHeight = 0U, Orientation = documentWorksheet.Orientation
},
new RowBreaks(rowBreaks)
{
Count = (uint)rowBreaks.Count(), ManualBreakCount = (uint)rowBreaks.Count()
}
);
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(id);
worksheetPart.Worksheet = worksheet;
return(worksheetPart);
}
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);
}
private bool ReadSheetRow(XlsxWorksheet sheet)
{
if (null == _xmlReader)
{
return(false);
}
if (_emptyRowCount != 0)
{
_cellsValues = new object[sheet.ColumnsCount];
_emptyRowCount--;
_depth++;
return(true);
}
if (_savedCellsValues != null)
{
_cellsValues = _savedCellsValues;
_savedCellsValues = null;
_depth++;
return(true);
}
if ((_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_row) ||
_xmlReader.ReadToFollowing(XlsxWorksheet.N_row))
{
_cellsValues = new object[sheet.ColumnsCount];
var rowIndex = int.Parse(_xmlReader.GetAttribute(XlsxWorksheet.A_r));
if (rowIndex != (_depth + 1))
{
_emptyRowCount = rowIndex - _depth - 1;
}
var hasValue = false;
var a_s = String.Empty;
var a_t = String.Empty;
var a_r = String.Empty;
var col = 0;
var row = 0;
while (_xmlReader.Read())
{
if (_xmlReader.Depth == 2)
{
break;
}
if (_xmlReader.NodeType == XmlNodeType.Element)
{
hasValue = false;
if (_xmlReader.Name == XlsxWorksheet.N_c)
{
a_s = _xmlReader.GetAttribute(XlsxWorksheet.A_s);
a_t = _xmlReader.GetAttribute(XlsxWorksheet.A_t);
a_r = _xmlReader.GetAttribute(XlsxWorksheet.A_r);
XlsxDimension.XlsxDim(a_r, out col, out row);
}
else if (_xmlReader.Name == XlsxWorksheet.N_v)
{
hasValue = true;
}
}
if (_xmlReader.NodeType == XmlNodeType.Text && hasValue)
{
object o = _xmlReader.Value;
if (null != a_t && a_t == XlsxWorksheet.A_s)
{
o = _workbook.SST[Convert.ToInt32(o)];
}
else if (null != a_s)
{
var xf = _workbook.Styles.CellXfs[int.Parse(a_s)];
if (xf.ApplyNumberFormat && IsDateTimeStyle(xf.NumFmtId) && o != null && o.ToString() != string.Empty)
{
try {
o = DateTime.FromOADate(Convert.ToDouble(o, CultureInfo.InvariantCulture));
} catch {
// just don't try and convert it then
}
}
}
if (col - 1 < _cellsValues.Length)
{
_cellsValues[col - 1] = o;
}
}
}
if (_emptyRowCount > 0)
{
_savedCellsValues = _cellsValues;
return(ReadSheetRow(sheet));
}
else
{
_depth++;
}
return(true);
}
_xmlReader.Close();
if (_sheetStream != null)
{
_sheetStream.Close();
}
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 ReadSheetRow(XlsxWorksheet sheet)
{
if (sheet.ColumnsCount < 0)
{
//Console.WriteLine("Columons Count Can NOT BE Negative");
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);
//Console.WriteLine("isRow:{0}/{1}/{2}", isRow,m_xmlReader.NodeType, m_xmlReader.LocalName);
}
if (isRow)
{
m_cellsValues = new object[sheet.ColumnsCount];
if (sheet.ColumnsCount > 13)
{
int i = 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;
}
else
{
//Console.WriteLine("Error");
}
}
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 (a_t == "str")
{
o = m_xmlReader.Value.ToString();
}
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)
{
//Console.WriteLine(o);
if (string.IsNullOrEmpty(o.ToString()))
{
//Console.WriteLine("Error");
}
m_cellsValues[col - 1] = o;
}
else
{
//Console.WriteLine("Error");
}
}
else
{
if (m_xmlReader.LocalName == XlsxWorksheet.N_v)
{
//Console.WriteLine("No Value");
}
}
}
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 bool ReadSheetRow(XlsxWorksheet sheet)
{
if (this._xmlReader == null)
{
return(false);
}
if (this._emptyRowCount != 0)
{
this._cellsValues = new object[sheet.ColumnsCount];
this._emptyRowCount--;
this._depth++;
return(true);
}
if (this._savedCellsValues != null)
{
this._cellsValues = this._savedCellsValues;
this._savedCellsValues = null;
this._depth++;
return(true);
}
if (((this._xmlReader.NodeType != XmlNodeType.Element) || (this._xmlReader.Name != "row")) && !this._xmlReader.ReadToFollowing("row"))
{
this._xmlReader.Close();
if (this._sheetStream != null)
{
this._sheetStream.Close();
}
return(false);
}
this._cellsValues = new object[sheet.ColumnsCount];
int num = int.Parse(this._xmlReader.GetAttribute("r"));
if (num != (this._depth + 1))
{
this._emptyRowCount = (num - this._depth) - 1;
}
bool flag = false;
string s = string.Empty;
string attribute = string.Empty;
int num2 = 0;
int num3 = 0;
while (this._xmlReader.Read())
{
if (this._xmlReader.Depth == 2)
{
break;
}
if (this._xmlReader.NodeType == XmlNodeType.Element)
{
flag = false;
if (this._xmlReader.Name == "c")
{
s = this._xmlReader.GetAttribute("s");
attribute = this._xmlReader.GetAttribute("t");
XlsxDimension.XlsxDim(this._xmlReader.GetAttribute("r"), out num2, out num3);
}
else if (this._xmlReader.Name == "v")
{
flag = true;
}
}
if ((this._xmlReader.NodeType == XmlNodeType.Text) && flag)
{
double num4;
object obj2 = this._xmlReader.Value;
if (double.TryParse(obj2.ToString(), out num4))
{
obj2 = num4;
}
if ((attribute != null) && (attribute == "s"))
{
obj2 = Helpers.ConvertEscapeChars(this._workbook.SST[int.Parse(obj2.ToString())]);
}
else if (attribute == "b")
{
obj2 = this._xmlReader.Value == "1";
}
else if (s != null)
{
XlsxXf xf = this._workbook.Styles.CellXfs[int.Parse(s)];
if ((xf.ApplyNumberFormat && (obj2 != null)) && ((obj2.ToString() != string.Empty) && this.IsDateTimeStyle(xf.NumFmtId)))
{
obj2 = Helpers.ConvertFromOATime(num4);
}
else if (xf.NumFmtId == 0x31)
{
obj2 = obj2.ToString();
}
}
if ((num2 - 1) < this._cellsValues.Length)
{
this._cellsValues[num2 - 1] = obj2;
}
}
}
if (this._emptyRowCount > 0)
{
this._savedCellsValues = this._cellsValues;
return(this.ReadSheetRow(sheet));
}
this._depth++;
return(true);
}
private void ReadSheetGlobals(XlsxWorksheet sheet)
{
if (_xmlReader != null)
{
_xmlReader.Close();
}
if (_sheetStream != null)
{
_sheetStream.Close();
}
_sheetStream = _zipWorker.GetWorksheetStream(sheet.Path);
if (null == _sheetStream)
{
return;
}
_xmlReader = XmlReader.Create(_sheetStream);
//count rows and cols in case there is no dimension elements
int rows = 0;
int cols = 0;
_namespaceUri = null;
while (_xmlReader.Read())
{
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_worksheet)
{
//grab the namespaceuri from the worksheet element
_namespaceUri = _xmlReader.NamespaceURI;
}
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_dimension)
{
string dimValue = _xmlReader.GetAttribute(XlsxWorksheet.A_ref);
sheet.Dimension = new XlsxDimension(dimValue);
break;
}
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_col)
{
cols++;
}
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_row)
{
rows++;
}
}
//if we didn't get a dimension element then use the calculated rows/cols to create it
if (sheet.Dimension == null)
{
if (rows == 9 || cols == 0)
{
sheet.IsEmpty = true;
return;
}
sheet.Dimension = new XlsxDimension(rows, cols);
//we need to reset our position to sheet data
_xmlReader.Close();
_sheetStream.Close();
_sheetStream = _zipWorker.GetWorksheetStream(sheet.Path);
_xmlReader = XmlReader.Create(_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
_xmlReader.ReadToFollowing(XlsxWorksheet.N_sheetData, _namespaceUri);
if (_xmlReader.IsEmptyElement)
{
sheet.IsEmpty = true;
}
}
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 < 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 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 ReadSheetRow(XlsxWorksheet sheet)
{
if (null == _xmlReader)
{
return(false);
}
if (_emptyRowCount != 0)
{
_cellsValues = new object[sheet.ColumnsCount];
_emptyRowCount--;
_depth++;
return(true);
}
if (_savedCellsValues != null)
{
_cellsValues = _savedCellsValues;
_savedCellsValues = null;
_depth++;
return(true);
}
if ((_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_row) ||
_xmlReader.ReadToFollowing(XlsxWorksheet.N_row))
{
_cellsValues = new object[sheet.ColumnsCount];
int rowIndex = int.Parse(_xmlReader.GetAttribute(XlsxWorksheet.A_r));
if (rowIndex != (_depth + 1))
{
_emptyRowCount = rowIndex - _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 (_xmlReader.Read())
{
if (_xmlReader.Depth == 2)
{
break;
}
if (_xmlReader.NodeType == XmlNodeType.Element)
{
hasValue = false;
if (_xmlReader.Name == XlsxWorksheet.N_c)
{
a_s = _xmlReader.GetAttribute(XlsxWorksheet.A_s);
a_t = _xmlReader.GetAttribute(XlsxWorksheet.A_t);
a_r = _xmlReader.GetAttribute(XlsxWorksheet.A_r);
XlsxDimension.XlsxDim(a_r, out col, out row);
}
else if (_xmlReader.Name == XlsxWorksheet.N_v)
{
hasValue = true;
}
}
if (_xmlReader.NodeType == XmlNodeType.Text && hasValue)
{
double number;
object o = _xmlReader.Value;
if (double.TryParse(o.ToString(), out number))
{
o = number;
}
if (null != a_t && a_t == XlsxWorksheet.A_s) //if string
{
o = Helpers.ConvertEscapeChars(_workbook.SST[int.Parse(o.ToString())]);
}
else if (a_t == "b") //boolean
{
o = _xmlReader.Value == "1";
}
else if (null != a_s) //if something else
{
XlsxXf xf = _workbook.Styles.CellXfs[int.Parse(a_s)];
if (xf.ApplyNumberFormat && o != null && o.ToString() != string.Empty && IsDateTimeStyle(xf.NumFmtId))
{
o = Helpers.ConvertFromOATime(number);
}
else if (xf.NumFmtId == 49)
{
o = o.ToString();
}
}
if (col - 1 < _cellsValues.Length)
{
_cellsValues[col - 1] = o;
}
}
}
if (_emptyRowCount > 0)
{
_savedCellsValues = _cellsValues;
return(ReadSheetRow(sheet));
}
_depth++;
return(true);
}
_xmlReader.Close();
if (_sheetStream != null)
{
_sheetStream.Close();
}
return(false);
}
private static IEnumerable <OpenXmlElement> MakeData(XlsxWorksheet worksheet)
{
var lastRowIndex = 0;
foreach (var section in worksheet.Data)
{
var data = section.Data.OrderBy(x => x.Row).Last();
var rows = worksheet.Layout.Rows.SelectMany(x => Enumerable.Repeat(x, x.Repeat)).ToArray();
var rc = data.Row - rows.Length;
if (rc > 0)
{
rows = rows.Concat(Enumerable.Repeat(DocumentLayoutUnit.Auto, rc)).ToArray();
}
var rowsIndo = rows.Select((x, i) => new { Index = i + 1, Props = x });
var items = rowsIndo.GroupJoin(section.Data, x => x.Index, x => x.Row, (i, o) => new { Row = i, Data = o });
foreach (var item in items)
{
var rowIndex = ++lastRowIndex;
if (!item.Row.Props.IsDefault && !item.Data.Any())
{
continue;
}
var cells = item.Data
.OrderBy(x => x.Column)
.Select(x => Enumerable.Repeat(x, x.ColumnSpan))
.SelectMany(x => x.Select((p, i) => i == 0
? MakeCell(p, p.Column, rowIndex)
: EmptyCell(p, p.Column + i, rowIndex)
));
var merges = item.Data
.Where(x => x.ColumnSpan > 1)
.Select(x => new MergeCell()
{
Reference = string.Format("{1}{0}:{2}{0}",
rowIndex,
ColumnA1Reference(x.Column),
ColumnA1Reference(x.Column + x.ColumnSpan - 1)
)
});
foreach (var merge in merges)
{
yield return(merge);
}
var row = new Row(cells)
{
RowIndex = (uint)rowIndex,
StyleIndex = item.Row.Props.StyleIndex.HasValue
? new UInt32Value((uint)item.Row.Props.StyleIndex)
: null,
//Spans = new ListValue<StringValue>() { InnerText = "1:" + page.GridSystem.Columns.Count() },
Hidden = item.Row.Props.Hidden ? new BooleanValue(true) : null,
CustomHeight = item.Row.Props.Size.HasValue ? new BooleanValue(true) : null,
Height = item.Row.Props.Size,
DyDescent = worksheet.RowFormat.DyDescent
};
yield return(row);
}
yield return(new Break {
Id = (uint)++lastRowIndex, ManualPageBreak = true
});
}
}
private void ReadSheetGlobals(XlsxWorksheet sheet)
{
//_SheetStream = new MemoryStream(_ZipWorker.GetWorksheetByteArray(sheet.Id));
_sheetStream = new MemoryStream(_zipWorker.GetWorksheetByteArray(sheet.Path));
if (null == _sheetStream) return;
_xmlReader = XmlReader.Create(_sheetStream);
while (_xmlReader.Read())
{
if (_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_dimension)
{
string dimValue = _xmlReader.GetAttribute(XlsxWorksheet.A_ref);
if (dimValue.IndexOf(':') > 0)
sheet.Dimension = new XlsxDimension(dimValue);
else
{
_xmlReader.Close();
_sheetStream.Close();
}
break;
}
}
}
private bool ReadSheetRow(XlsxWorksheet sheet)
{
if (null == _xmlReader) return false;
if (_emptyRowCount != 0)
{
_cellsValues = new object[sheet.ColumnsCount];
_emptyRowCount--;
Depth++;
return true;
}
if (_savedCellsValues != null)
{
_cellsValues = _savedCellsValues;
_savedCellsValues = null;
Depth++;
return true;
}
if ((_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.Name == XlsxWorksheet.N_row) ||
_xmlReader.ReadToFollowing(XlsxWorksheet.N_row))
{
_cellsValues = new object[sheet.ColumnsCount];
int rowIndex = int.Parse(_xmlReader.GetAttribute(XlsxWorksheet.A_r));
if (rowIndex != (Depth + 1))
{
_emptyRowCount = rowIndex - 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 (_xmlReader.Read())
{
if (_xmlReader.Depth == 2) break;
if (_xmlReader.NodeType == XmlNodeType.Element)
{
hasValue = false;
if (_xmlReader.Name == XlsxWorksheet.N_c)
{
a_s = _xmlReader.GetAttribute(XlsxWorksheet.A_s);
a_t = _xmlReader.GetAttribute(XlsxWorksheet.A_t);
a_r = _xmlReader.GetAttribute(XlsxWorksheet.A_r);
XlsxDimension.XlsxDim(a_r, out col, out row);
}
else if (_xmlReader.Name == XlsxWorksheet.N_v)
{
hasValue = true;
}
}
if (_xmlReader.NodeType == XmlNodeType.Text && hasValue)
{
object o = _xmlReader.Value;
if (null != a_t && a_t == XlsxWorksheet.A_s)
{
o = _workbook.SST[Convert.ToInt32(o)];
}
else if (null != a_s)
{
XlsxXf xf = _workbook.Styles.CellXfs[int.Parse(a_s)];
if (xf.ApplyNumberFormat && IsDateTimeStyle(xf.NumFmtId) && o != null && o.ToString() != string.Empty)
{
o = DateTime.FromOADate(Convert.ToDouble(o, CultureInfo.InvariantCulture));
}
}
if (col - 1 < _cellsValues.Length)
_cellsValues[col - 1] = o;
}
}
if (_emptyRowCount > 0)
{
_savedCellsValues = _cellsValues;
return ReadSheetRow(sheet);
}
else
Depth++;
return true;
}
_xmlReader.Close();
if (_sheetStream != null) _sheetStream.Close();
return false;
}
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 ReadSheetRow(XlsxWorksheet sheet)
{
if (null == _xmlReader)
{
return(false);
}
if (_emptyRowCount != 0)
{
_cellsValues = new object[sheet.ColumnsCount];
_emptyRowCount--;
_depth++;
return(true);
}
if (_savedCellsValues != null)
{
_cellsValues = _savedCellsValues;
_savedCellsValues = null;
_depth++;
return(true);
}
if ((_xmlReader.NodeType == XmlNodeType.Element && _xmlReader.LocalName == XlsxWorksheet.N_row) ||
_xmlReader.ReadToFollowing(XlsxWorksheet.N_row, _namespaceUri))
{
_cellsValues = new object[sheet.ColumnsCount];
int rowIndex = int.Parse(_xmlReader.GetAttribute(XlsxWorksheet.A_r));
if (rowIndex != (_depth + 1))
{
if (rowIndex != (_depth + 1))
{
_emptyRowCount = rowIndex - _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 (_xmlReader.Read())
{
if (_xmlReader.Depth == 2)
{
break;
}
if (_xmlReader.NodeType == XmlNodeType.Element)
{
hasValue = false;
if (_xmlReader.LocalName == XlsxWorksheet.N_c)
{
a_s = _xmlReader.GetAttribute(XlsxWorksheet.A_s);
a_t = _xmlReader.GetAttribute(XlsxWorksheet.A_t);
a_r = _xmlReader.GetAttribute(XlsxWorksheet.A_r);
XlsxDimension.XlsxDim(a_r, out col, out row);
}
else if (_xmlReader.LocalName == XlsxWorksheet.N_v || _xmlReader.LocalName == XlsxWorksheet.N_t)
{
hasValue = true;
}
}
if (_xmlReader.NodeType == XmlNodeType.Text && hasValue)
{
double number;
object o = _xmlReader.Value;
var style = NumberStyles.Any;
var culture = CultureInfo.InvariantCulture;
if (double.TryParse(o.ToString(), style, culture, out number))
{
o = number;
}
if (null != a_t && a_t == XlsxWorksheet.A_s) //if string
{
o = Helpers.ConvertEscapeChars(_workbook.SST[int.Parse(o.ToString())]);
} // 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 = Helpers.ConvertEscapeChars(o.ToString());
}
else if (a_t == "b") //boolean
{
o = _xmlReader.Value == "1";
}
else if (null != a_s) //if something else
{
XlsxXf xf = _workbook.Styles.CellXfs[int.Parse(a_s)];
if (o != null && o.ToString() != string.Empty && IsDateTimeStyle(xf.NumFmtId))
{
o = Helpers.ConvertFromOATime(number);
}
else if (xf.NumFmtId == 49)
{
o = o.ToString();
}
}
if (col - 1 < _cellsValues.Length)
{
_cellsValues[col - 1] = o;
}
}
}
if (_emptyRowCount > 0)
{
_savedCellsValues = _cellsValues;
return(ReadSheetRow(sheet));
}
_depth++;
return(true);
}
((IDisposable)_xmlReader).Dispose();
if (_sheetStream != null)
{
_sheetStream.Dispose();
}
return(false);
}