public List <JZFormulaData> GetFormulaByModuleIndex(Guid moduleIndex)
{
List <JZFormulaData> list = new List <JZFormulaData>();
String sql = "SELECT * FROM dbo.sys_formulas WHERE IsActive=1 and ModuleID='" + moduleIndex + "'";
DataTable dt = GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
JZFormulaData formula = new JZFormulaData();
formula.ColumnIndex = Convert.ToInt32(row["ColumnIndex"]);
formula.Formula = row["Formula"].ToString();
formula.ModelIndex = new Guid(row["ModuleID"].ToString());
formula.SheetIndex = new Guid(row["SheetID"].ToString());
formula.RowIndex = Convert.ToInt32(row["RowIndex"]);
list.Add(formula);
}
}
return(list);
}
public List <JZFormulaData> GetLineFormulaByModuleIndex(Guid moduleIndex)
{
List <JZFormulaData> list = new List <JZFormulaData>();
String sql = "exec sp_getFormulas @moduleID='" + moduleIndex + "'";
DataTable dt = GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
JZFormulaData formula = new JZFormulaData();
formula.ColumnIndex = Convert.ToInt32(row["ColumnIndex"]);
formula.Formula = row["Formula"].ToString();//.Replace("'","")
formula.ModelIndex = new Guid(row["ModuleID"].ToString());
formula.SheetIndex = new Guid(row["SheetID"].ToString());
formula.RowIndex = Convert.ToInt32(row["RowIndex"]);
formula.FormulaType = Convert.ToInt32(row["fromLine"]);
list.Add(formula);
}
}
return(list);
}
void fpSpreadEditor1_UserFormulaEntered(object sender, UserFormulaEnteredEventArgs e)
{
SheetView Sheet = FpSpread.Sheets[e.View.ActiveSheetIndex];
string key = string.Format("{0}_{1}_{2}", Sheet.Tag.ToString(), e.Row, e.Column);
if (string.IsNullOrEmpty(Sheet.Cells[e.Row, e.Column].Formula))
{
if (CrossSheetFormulaCache.ContainsKey(key))
{
CrossSheetFormulaCache.Remove(key);
FpSpread.ActiveSheet.ActiveCell.BackColor = Color.White;
}
return;
}
if (!CrossSheetFormulaCache.ContainsKey(key))
{
JZFormulaData Formula = new JZFormulaData();
Formula.ModelIndex = moduleID;
Formula.SheetIndex = new Guid(Sheet.Tag.ToString());
Formula.RowIndex = e.Row;
Formula.ColumnIndex = e.Column;
Formula.Formula = Sheet.Cells[e.Row, e.Column].Formula;
CrossSheetFormulaCache.Add(key, Formula);
if (ForLine)
{
FpSpread.ActiveSheet.ActiveCell.BackColor = Color.Blue;
}
}
else
{
JZFormulaData Formula = CrossSheetFormulaCache[key];
Formula.Formula = Sheet.Cells[e.Row, e.Column].Formula;
}
}
/// <summary>
/// 保存表单样式
/// </summary>
private void SaveSheetStyle()
{
try
{
Sys_Sheet sheet = new Sys_Sheet();
sheet.ID = sheetID;
sheet.Name = SheetName;
sheet.SheetXML = JZCommonHelper.GZipCompressString(Editor.GetActiveSheetXml());
List <JZCell> cells = new List <JZCell>();
List <CellLogic> cellLogicList = new List <CellLogic>();
List <JZFormulaData> cellFormulasList = new List <JZFormulaData>();
//bool bIsKeyDescEmpty = false;
#region CellLogic
foreach (Cell cell in dataCells)
{
if (cell == null)
{
continue;
}
JZCell c = new JZCell();
c.Name = cell.Column.Label + cell.Row.Label;
c.Value = cell.Value;
bool bHasExist = false;
foreach (JZCell cc in cells)
{
if (cc.Name == c.Name)
{
bHasExist = true;
break;
}
}
if (bHasExist == true)
{
continue;
}
cells.Add(c);
CellLogic cl = new CellLogic();
cl.Name = c.Name;
JZCellProperty p = cell.Tag as JZCellProperty;
if (p != null)
{
cl.Description = p.Description;
cl.IsKey = p.IsKey;
cl.IsNotCopy = p.IsNotCopy;
cl.IsNotNull = p.IsNotNull;
cl.IsPingxing = p.IsPingxing;
cl.IsReadOnly = p.IsReadOnly;
cl.IsUnique = p.IsUnique;
if (cl.IsKey == true && string.IsNullOrEmpty(cl.Description))
{
//bIsKeyDescEmpty = true;
MessageBox.Show(string.Format("单元格{0}的描述不能为空", c.Name));
return;
}
}
cellLogicList.Add(cl);
}
#endregion
#region Formulas
if (FpSpread.Sheets[0] != null)
{
for (int j = 0; j < FpSpread.Sheets[0].ColumnCount; j++)
{
for (int m = 0; m < FpSpread.Sheets[0].RowCount; m++)
{
Cell c = FpSpread.Sheets[0].Cells[m, j];
JZFormulaData cl = new JZFormulaData();
if (c != null)
{
if (!string.IsNullOrEmpty(c.Formula))
{
//logger.Error(string.Format("Name:{0} ColumnIndex:{1} RowIndex:{2} Formula:{3} ", ((char)('A' + c.Column.Index)).ToString() + (c.Row.Index + 1).ToString(), c.Column.Index, c.Row.Index, c.Formula));
cl.ColumnIndex = c.Column.Index;
cl.RowIndex = c.Row.Index;
cl.Formula = c.Formula;
cellFormulasList.Add(cl);
}
}
}
}
}
#endregion
sheet.SheetData = Newtonsoft.Json.JsonConvert.SerializeObject(cells);
sheet.CellLogic = JZCommonHelper.GZipCompressString(Newtonsoft.Json.JsonConvert.SerializeObject(cellLogicList));
sheet.Formulas = JZCommonHelper.GZipCompressString(Newtonsoft.Json.JsonConvert.SerializeObject(cellFormulasList));
if (ModuleHelperClient.SaveSheet(sheet))
{
MessageBox.Show("保存成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("保存失败。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("保存失败。" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
