internal string GetFormat(OXCell c)
{
if (StyleSheet != null &&
StyleSheet.CellXFS != null &&
StyleSheet.CellXFS.XFS != null)
{
if (c.StyleIndex < StyleSheet.CellXFS.XFS.Length)
{
OXXf xf = StyleSheet.CellXFS.XFS[c.StyleIndex];
if (xf.ApplyNumberFormat == 1)
{
string f = OXBuiltInFormats.GetFormat(xf.NumFormatID);
if (string.IsNullOrEmpty(f))
{
if (StyleSheet.NumberFormats != null &&
StyleSheet.NumberFormats.NumberFormatEntries != null)
{
if (_formats == null)
{
_formats = new Dictionary <uint, string>();
foreach (OXNumberFormat nf in StyleSheet.NumberFormats.NumberFormatEntries)
{
if (!_formats.ContainsKey(nf.Id))
{
_formats.Add(nf.Id, nf.Format);
}
else
{
#if DEBUG
throw new Exception(string.Format("Duplicate number format id {0}", nf.Id));
#endif
}
}
}
if (!_formats.TryGetValue(xf.NumFormatID, out f))
{
#if DEBUG
throw new Exception(string.Format("Invalid number format id {0} for cell {1}", xf.NumFormatID, c.Reference));
#endif
}
}
}
return(f ?? "");
}
}
else
{
#if DEBUG
throw new Exception(string.Format("Cell {0} has non existent cell xf {1}", c.Reference, c.StyleIndex));
#endif
}
}
return("");
}
private void InitValue()
{
if (!_valueInit)
{
OXCell cell = Worksheet.CellMap.GetCell(Row.Index, Index);
if (cell != null)
{
switch (cell.Type)
{
case OXCellType.Boolean:
_value = cell.Value;
_typedValue = _value == "1";
break;
case OXCellType.Number:
_value = cell.Value;
_typedValue = decimal.Parse(cell.Value, NumberStyles.Number | NumberStyles.AllowExponent);
break;
default:
_typedValue =
_value = Worksheet.GetCellValue(cell);
break;
}
}
_valueInit = true;
}
}
public string GetFormattedValue(uint row, uint cell)
{
OXCell c = CellMap.GetCell(row, cell);
if (c != null)
{
string value = GetCellValue(c);
string format = GetFormat(c);
if (!string.IsNullOrEmpty(format))
{
object fvalue = value;
string output = null;
int ret = VarFormat(ref fvalue, format, 0, 0, 0, ref output);
if (ret < 0)
{
#if DEBUG
Marshal.ThrowExceptionForHR(ret);
#else
return value;
#endif
}
return output;
}
return value;
}
return "";
}
private string GetFormat(OXCell c)
{
if (Workbook != null)
return Workbook.GetFormat(c);
return "";
}
public DataSet AsDataSet(bool firstRowAsColumnNames)
{
DataSet set = new DataSet();
foreach (var sheet in this.GetWorksheets())
{
DataTable table = set.Tables.Add(sheet.Name);
uint startRow = sheet.CellMap.FirstRow;
if (firstRowAsColumnNames)
{
for (uint i = sheet.CellMap.FirstCol; i <= sheet.CellMap.LastCol; i++)
{
OXCell cell = sheet.CellMap.GetCell(startRow, i);
if (cell != null)
{
table.Columns.Add(sheet.GetCellValue(cell), typeof(object));
}
else
{
table.Columns.Add(null, typeof(object));
}
}
startRow++;
}
else
{
for (uint i = sheet.CellMap.FirstCol; i <= sheet.CellMap.LastCol; i++)
{
table.Columns.Add(null, typeof(object));
}
}
for (uint i = startRow; i <= sheet.CellMap.LastRow; i++)
{
DataRow newRow = table.NewRow();
for (uint j = sheet.CellMap.FirstCol; j <= sheet.CellMap.LastCol; j++)
{
OXCell cell = sheet.CellMap.GetCell(i, j);
if (cell != null)
{
switch (cell.Type)
{
case OXCellType.SharedString:
newRow[(int)j] = sheet.GetCellValue(cell);
break;
default:
newRow[(int)j] = cell.Value;
break;
}
}
}
table.Rows.Add(newRow);
}
}
return(set);
}
internal OXCell GetCell(uint row, uint col)
{
OXCell ret = null;
ColMap cols;
if (_rows.TryGetValue(row, out cols))
{
cols.TryGetValue(col, out ret);
}
return(ret);
}
public bool IsEmpty(uint row)
{
if (row >= CellMap.FirstRow && row <= CellMap.LastRow)
{
for (uint col = CellMap.FirstCol; col <= CellMap.LastCol; ++col)
{
OXCell c = CellMap.GetCell(row, col);
if (c != null && !string.IsNullOrEmpty(GetCellValue(c)))
return false;
}
}
return true;
}
internal string GetCellValue(OXCell c)
{
switch (c.Type)
{
case OXCellType.InlineString:
if (c.FormattedString == null)
{
#if DEBUG
throw new Exception("Cell type is InlineString but FormattedString is null");
#else
return "";
#endif
}
return c.FormattedString.ToString();
case OXCellType.SharedString:
bool valid = false;
uint idx;
valid = uint.TryParse(c.Value, out idx);
if (Workbook.SST == null ||
!valid ||
idx >= Workbook.SST.Entries.Length)
{
#if DEBUG
throw new Exception(string.Format("Cell type is SharedString and refers to an invalid shared string table reference {0} for cell {1}", idx, c.Reference));
#else
return "";
#endif
}
return Workbook.SST.Entries[idx].ToString();
default:
return c.Value;
}
}
internal string GetFormat(OXCell c)
{
if (StyleSheet != null &&
StyleSheet.CellXFS != null &&
StyleSheet.CellXFS.XFS != null)
{
if (c.StyleIndex < StyleSheet.CellXFS.XFS.Length)
{
OXXf xf = StyleSheet.CellXFS.XFS[c.StyleIndex];
if (xf.ApplyNumberFormat == 1)
{
string f = OXBuiltInFormats.GetFormat(xf.NumFormatID);
if (string.IsNullOrEmpty(f))
{
if (StyleSheet.NumberFormats != null &&
StyleSheet.NumberFormats.NumberFormatEntries != null)
{
if (_formats == null)
{
_formats = new Dictionary<uint, string>();
foreach (OXNumberFormat nf in StyleSheet.NumberFormats.NumberFormatEntries)
{
if (!_formats.ContainsKey(nf.Id))
_formats.Add(nf.Id, nf.Format);
else
{
#if DEBUG
throw new Exception(string.Format("Duplicate number format id {0}", nf.Id));
#endif
}
}
}
if (!_formats.TryGetValue(xf.NumFormatID, out f))
{
#if DEBUG
throw new Exception(string.Format("Invalid number format id {0} for cell {1}", xf.NumFormatID, c.Reference));
#endif
}
}
}
return f ?? "";
}
}
else
{
#if DEBUG
throw new Exception(string.Format("Cell {0} has non existent cell xf {1}", c.Reference, c.StyleIndex));
#endif
}
}
return "";
}
