// private void WriteWorkSheet_sheetData_c_byCellType(
private void WriteWorkSheet_sheetData_c_style(XElement element, Cell cell)
{
#region "S" Style
if (cell._Style != null)
{
element.Add(new XAttribute("s", GetStyleId(cell.Style)));
}
else if (cell.Row._Style != null)
{
element.Add(new XAttribute("s", GetStyleId(cell.Row._Style)));
}
else if (cell.Column._Style != null)
{
element.Add(new XAttribute("s", GetStyleId(cell.Column._Style)));
}
#endregion
}
/// <summary>
///FileName:sheet.xml
/// <para>NodePath:worksheet/sheetData/row/c/f</para>
/// </summary>
private XElement WriteWorkSheet_sheetData_c_f(WorkSheet sheet, Cell cell)
{
XElement result = null;
if (cell.Formula.IsNotEmpty() && cell.FormulaSharedIndex == int.MinValue)
{
result = new XElement(ExcelCommon.Schema_WorkBook_Main + "f");
result.Value = cell.Formula;
}
else
{
if (cell.FormulaSharedIndex > 0)
{
result = new XElement(ExcelCommon.Schema_WorkBook_Main + "f");
Formulas currentF = sheet._SharedFormulas.FirstOrDefault(f => f.Value.Index == cell.FormulaSharedIndex).Value;
result.Add(new XAttribute("t", "shared"));
result.Add(new XAttribute("si", cell.FormulaSharedIndex.ToString(CultureInfo.InvariantCulture)));
if (cell.Row.Index == currentF.Address.StartRow && cell.Column.Index == currentF.Address.StartColumn)
{
result.Add(new XAttribute("ref", currentF.Address.ToAddress()));
result.Value = currentF.Formula;
}
}
else if (cell.HasDataTable)
{
result = new XElement(ExcelCommon.Schema_WorkBook_Main + "f");
var currentF = sheet._DataTableFormulas.Where(f => f.Key == cell).Select(r => r.Value).FirstOrDefault();
currentF.IsNotNull(o =>
{
var f = o as Formulas;
result.Add(new XAttribute("t", "dataTable"));
f.Dt2D.IsNotWhiteSpace(v => result.Add(new XAttribute("dt2D", v)));
f.Dtr.IsNotWhiteSpace(v => result.Add(new XAttribute("dtr", f.Dtr)));
f.R1.IsNotWhiteSpace(v => result.Add(new XAttribute("r1", f.R1)));
f.R2.IsNotWhiteSpace(v => result.Add(new XAttribute("r2", f.R2)));
f.Ca.IsNotWhiteSpace(v => result.Add(new XAttribute("ca", f.Ca)));
result.Add(new XAttribute("ref", f.Address.ToAddress()));
});
}
}
return result;
}
internal Comment Clone(Cell cell)
{
Comment cloneComment = new Comment(cell);
cloneComment._Anchor = this._Anchor;
cloneComment._BackgroundColor = this._BackgroundColor;
cloneComment._From = this._From;
cloneComment._HorizontalAlignment = this._HorizontalAlignment;
cloneComment._LineColor = this._LineColor;
cloneComment._LineStyle = this._LineStyle;
cloneComment._LineWidth = this._LineWidth;
cloneComment._RichText = this._RichText;
cloneComment._Style = this._Style;
cloneComment._To = this._To;
cloneComment._VerticalAlignment = this._VerticalAlignment;
cloneComment._Visible = this._Visible;
return cloneComment;
}
/// <summary>
/// FileName:sheet.xml
/// <para>NodePath:worksheet/sheetData/row/c</para>
/// </summary>
private void WriteWorkSheet_sheetData_c(WorkSheet sheet, SortedDictionary<int, XElement> cellDict, Cell cell)
{
XElement element = new XElement(ExcelCommon.Schema_WorkBook_Main + "c");
element.Add(new XAttribute("r", cell.ToString()));
string str = cell.Value as string;
if (str.IsNullOrEmpty() && cell.Value == null)
{
if (cell.Formula.IsNotEmpty() || cell.FormulaSharedIndex > 0)
{
XElement formula = WriteWorkSheet_sheetData_c_f(sheet, cell);
if (formula != null)
{
element.Add(formula);
cellDict.Add(cell.Column.Index, element);
}
}
else
{
if (cell.CheckStyleIsEmpty() == false)
{
WriteWorkSheet_sheetData_c_style(element, cell);
cellDict.Add(cell.Column.Index, element);
}
}
return;
}
if ((cell.Value.GetType().IsPrimitive || cell.Value is double || cell.Value is decimal || cell.Value is DateTime || cell.Value is TimeSpan))
{
try
{
if (cell.Value is DateTime)
{
str = ((DateTime)cell.Value).ToOADate().ToString(CultureInfo.InvariantCulture);
//样式为空
if (cell.CheckStyleIsEmpty())
{
cell._Style = new CellStyleXmlWrapper();
cell._Style.NumberFormat.Format = "mm-dd-yy";
}
else
{ //样式设置在列或行上，但主体单元格没有设置
if ((cell._Style == null && cell.CheckStyleIsEmpty() == false) || (string.IsNullOrEmpty(cell.Style.NumberFormat.Format)))
cell.Style.NumberFormat.Format = "mm-dd-yy";
}
}
else if (cell.Value is TimeSpan)
{
str = new DateTime(((TimeSpan)cell.Value).Ticks).ToOADate().ToString(CultureInfo.InvariantCulture);
if (cell._Style == null)
{
cell._Style = new CellStyleXmlWrapper();
}
if (string.IsNullOrEmpty(cell._Style.NumberFormat.Format))
{
cell.Style.NumberFormat.Format = "h:mm";
}
}
else
{
if (cell.Value is double && double.IsNaN((double)cell.Value))
{
str = "0";
}
else
{
str = Convert.ToDouble(cell.Value, CultureInfo.InvariantCulture).ToString("g15", CultureInfo.InvariantCulture);
}
}
}
catch
{
str = "0";
}
if (cell.Value is bool)
{
element.Add(new XAttribute("t", "b"));
}
var formula = WriteWorkSheet_sheetData_c_f(sheet, cell);
if (formula != null)
{
element.Add(formula);
}
element.Add(new XElement(ExcelCommon.Schema_WorkBook_Main + "v", str));
}
else if (str.IsNotEmpty())
{
element.Add(new XAttribute("t", "s"));
var formula = WriteWorkSheet_sheetData_c_f(sheet, cell);
if (formula != null)
{
element.Add(formula);
}
if (this.Context.SharedStrings.ContainsKey(str))
{
element.Add(new XElement(ExcelCommon.Schema_WorkBook_Main + "v", this.Context.SharedStrings[str].Pos.ToString(CultureInfo.InvariantCulture)));
}
else
{
int index = this.Context.SharedStrings.Count;
this.Context.SharedStrings.Add(str, new SharedStringItem() { IsRichText = cell.IsRichText, Pos = index, Text = ExcelHelper.ExcelDecodeString(str) });
element.Add(new XElement(ExcelCommon.Schema_WorkBook_Main + "v", index.ToString(CultureInfo.InvariantCulture)));
}
}
WriteWorkSheet_sheetData_c_style(element, cell);
cellDict.Add(cell.Column.Index, element);
}
public Comment(Cell cell)
{
this._Cell = cell;
}
internal Cell Clone(WorkSheet workSheet)
{
Cell cloneCell = new Cell(workSheet.Rows[this.Row.Index], workSheet.Columns[this.Column.Index]);
cloneCell.IsMerge = this.IsMerge;
cloneCell.IsRichText = this.IsRichText;
cloneCell.Formula = this.Formula;
cloneCell._SharedIndex = this._SharedIndex;
cloneCell.FormulaSharedIndex = this.FormulaSharedIndex;
cloneCell.DataType = this.DataType;
if (this._StyleID != 0)
cloneCell.StyleID = this.StyleID;
if (this._Style != null)
cloneCell._Style = this._Style;
if (this._Hyperlink != null)
cloneCell._Hyperlink = this._Hyperlink;
if (this._Comment != null)
cloneCell._Comment = this._Comment.Clone(cloneCell);
cloneCell.Value = this.Value;
return cloneCell;
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData/row/c/v</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal void ReadWorkSheet_sheetData_row_c_v(Cell target, XElement item)
{
switch (target.DataType)
{
case "s":
int ix = int.Parse(item.Value);
target.Value = this.Context.SharedStrings[ix].Text;
target.IsRichText = this.Context.SharedStrings[ix].IsRichText;
break;
case "str":
target.Value = item.Value;
break;
case "b":
target.Value = item.Value != "0";
break;
case "d":
target.Value = CellValueConvertyDateTime(item.Value);
break;
default:
int n = target.Style.NumberFormat.NumFmtId;
string v = item.Value;
if ((n >= 14 && n <= 22) || (n >= 45 && n <= 47))
{
DateTime cellReslut = CellValueConvertyDateTime(v);
if (cellReslut != default(DateTime))
{
target.DataType = "d";
target.Value = cellReslut;
}
else
{
target.Value = string.Empty;
}
}
else
{
double d;
if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out d))
{
target.Value = d;
//DateTime.FromOADate(d);
}
else
{
target.Value = double.NaN;
}
}
break;
}
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData/row/c/f</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal void ReadWorkSheet_sheetData_row_c_f(WorkSheet worksheet, Cell target, XElement item)
{
string t = string.Empty;
if (item.Attribute("t") != null)
{
t = item.Attribute("t").Value;
}
if (string.IsNullOrEmpty(t))
{
target.Formula = item.Value;
return;
}
switch (t)
{
case "shared":
string si = item.Attribute("si").Value;
if (si.IsNotEmpty())
{
target.FormulaSharedIndex = int.Parse(si);
if (item.Attribute("ref") != null)
{
Formulas f = new Formulas();
f.Address = Range.Parse(worksheet, item.Attribute("ref").Value);
f.Formula = item.Value;
f.Index = target.FormulaSharedIndex;
worksheet._SharedFormulas.Add(item.Value, f);
}
}
break;
case "dataTable":
//todo:ref="D22:N32" dt2D="1" dtr="1" r1="D94" r2="D95" ca="1"
if (item.Attribute("ref") != null)
{
Formulas f = new Formulas();
f.Address = Range.Parse(worksheet, item.Attribute("ref").Value);
item.Attribute("dt2D").IsNotNull(v => f.Dt2D = ((XAttribute)v).Value);
item.Attribute("dtr").IsNotNull(v => f.Dtr = ((XAttribute)v).Value);
item.Attribute("r1").IsNotNull(v => f.R1 = ((XAttribute)v).Value);
item.Attribute("r2").IsNotNull(v => f.R2 = ((XAttribute)v).Value);
item.Attribute("ca").IsNotNull(v => f.Ca = ((XAttribute)v).Value);
target.HasDataTable = true;
worksheet._DataTableFormulas.Add(target, f);
}
break;
case "array":
//todo:
break;
}
}
/// <summary>
/// FileName:sheet1.xml
/// <para>NodePath:worksheet/sheetData/row/c</para>
/// </summary>
/// <param name="bookViewsRoot"></param>
internal Cell ReadWorkSheet_sheetData_row_c(WorkSheet target, XElement item)
{
int rowIndex, columnIndex;
ExcelHelper.GetRowCol(item.Attribute("r").Value, out rowIndex, out columnIndex, true);
Row row = null;
Column col = null;
if (target.Rows.ContainsKey(rowIndex))
{
row = target.Rows[rowIndex];
}
else
{
row = new Row(rowIndex);
target.Rows.Add(row);
}
if (target.Columns.ContainsKey(columnIndex))
{
col = target.Columns[columnIndex];
}
else
{
col = new Column(columnIndex);
target.Columns.Add(col);
}
Cell cell = new Cell(row, col);
if (item.Attribute("t") != null)
{
cell.DataType = item.Attribute("t").Value;
}
if (item.Attribute("s") == null)
{
cell.StyleID = 0;
cell.Style = this.Context.GlobalStyles.CellStyleXfs[0];
}
else
{
cell.StyleID = int.Parse(item.Attribute("s").Value);
cell.Style = this.Context.GlobalStyles.CellXfs[cell.StyleID];
}
return cell;
}
