public CellStyleSetting(Guid _SheetID, string _CellName, JZCellStyle _JZCellStyle)
{
SheetID = _SheetID;
CellName = _CellName;
CellStyle = _JZCellStyle;
InitializeComponent();
propertyGrid1.SelectedObject = CellStyle;
}
/// <summary>
/// 保存表单的单元格样式
/// </summary>
/// <param name="moduleIDs"></param>
/// <param name="IsModule"></param>
/// <returns></returns>
public bool SaveCellStyle(Guid SheetID, string CellName, JZCellStyle CellStyle)
{
bool bSuccess = false;
String sql = "SELECT CSID ,SheetID ,CellName ,CellStyle FROM dbo.sys_line_cellstyle WHERE SheetID ='" + SheetID + "' and CellName='" + CellName + "' ";
DataTable dt = null;
try
{
dt = GetDataTable(sql);
if (dt == null)
{
logger.Error("保存单元格样式时出错: " + sql);
}
else
{
DataRow row = null;
if (dt.Rows.Count == 0)
{
//new
row = dt.NewRow();
dt.Rows.Add(row);
row["SheetID"] = SheetID;
row["CellName"] = CellName;
}
else
{
//update
row = dt.Rows[0];
}
row["CellStyle"] = Newtonsoft.Json.JsonConvert.SerializeObject(CellStyle);
int r = Update(dt);
if (r == 1)
{
bSuccess = true;
}
else
{
bSuccess = false;
}
}
}
catch (Exception e)
{
logger.Error("GetCellStyleBySheetID error:" + e.Message + "; error sql=" + sql);
}
return(bSuccess);
}
private void ButtonSave_Click(object sender, EventArgs e)
{
CellStyle = (JZCellStyle)propertyGrid1.SelectedObject;
bool bSuccess = BizComponents.ModuleHelperClient.SaveCellStyle(SheetID, CellName, CellStyle);
if (bSuccess)
{
SheetDesinger sheetDesinger = (SheetDesinger)this.Owner;
sheetDesinger.CurrentCellStyle = CellStyle;
this.DialogResult = DialogResult.OK;
}
else
{
this.DialogResult = DialogResult.Cancel;
}
this.Close();
}
private void ShowCellStyleSettingDialog()
{
string CellName = string.Empty;
CellName = FpSpread.ActiveSheet.ActiveCell.Column.Label + FpSpread.ActiveSheet.ActiveCell.Row.Label;
DataRow[] dr = dtCellStyle.Select(string.Format("CellName='{0}'", CellName));
DataRow row = null;
if (dr != null && dr.Length > 0)
{
row = dr[0];
CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(row["CellStyle"] == null ? null : row["CellStyle"].ToString());
}
else
{
row = dtCellStyle.NewRow();
CurrentCellStyle = new JZCellStyle();
Cell cell = FpSpread.ActiveSheet.Cells[CellName];
CurrentCellStyle.BackColor = cell.BackColor;
if (cell.Font != null)
{
CurrentCellStyle.FamilyName = cell.Font.FontFamily.Name;
CurrentCellStyle.FontSize = cell.Font.Size;
CurrentCellStyle.FontStyle = cell.Font.Style;
CurrentCellStyle.ForColor = cell.ForeColor;
}
dtCellStyle.Rows.Add(row);
}
CellStyleSetting CellStyleSetting = new CellStyleSetting(sheetID, CellName, CurrentCellStyle);
CellStyleSetting.Owner = this;
//CellStyleSetting.SelectObject(FpSpread.ActiveSheet.ActiveCell.CellType);
if (CellStyleSetting.ShowDialog() == DialogResult.OK)
{
Cell cell = FpSpread.ActiveSheet.Cells[CellName];
cell.ForeColor = CurrentCellStyle.ForColor;
cell.BackColor = CurrentCellStyle.BackColor;
cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle);
row["CSID"] = 0;
row["SheetID"] = sheetID;
row["CellName"] = CellName;
row["CellStyle"] = Newtonsoft.Json.JsonConvert.SerializeObject(CurrentCellStyle);
}
}
private void SheetDesinger_Load(object sender, EventArgs e)
{
ProgressScreen.Current.ShowSplashScreen();
this.AddOwnedForm(ProgressScreen.Current);
ProgressScreen.Current.SetStatus = "正在初始化表单...";
try
{
Sys_Sheet sheetItem = ModuleHelperClient.GetSheetItemByID(sheetID);
FpSpread.Sheets.Clear();
if (ModuleHelperClient.IsContainerXml(sheetItem.SheetXML))
{
sheetItem.SheetXML = sheetItem.SheetXML;
}
else
{
sheetItem.SheetXML = JZCommonHelper.GZipDecompressString(sheetItem.SheetXML);
}
SheetView SheetView = Serializer.LoadObjectXml(typeof(SheetView), sheetItem.SheetXML, "SheetView") as SheetView;
SheetView.SheetName = sheetItem.Name;
List <FarPoint.CalcEngine.FunctionInfo> Infos = FunctionItemInfoUtil.getFunctionItemInfos();
foreach (FarPoint.CalcEngine.FunctionInfo Info in Infos)
{
SheetView.AddCustomFunction(Info);
}
List <JZCell> sheetDataAreaCells = Newtonsoft.Json.JsonConvert.DeserializeObject <List <JZCell> >(sheetItem.SheetData);
if (sheetDataAreaCells != null)
{
foreach (JZCell cell in sheetDataAreaCells)
{
dataCells.Add(SheetView.Cells[cell.Name]);
}
}
FpSpread.Sheets.Add(SheetView);
dtCellStyle = ModuleHelperClient.GetCellStyleBySheetID(sheetID);
for (int i = 0; i < dtCellStyle.Rows.Count; i++)
{
if (dtCellStyle.Rows[i]["CellStyle"] != null)
{
JZCellStyle CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(dtCellStyle.Rows[i]["CellStyle"].ToString());
if (CurrentCellStyle != null)
{
string strCellName = dtCellStyle.Rows[i]["CellName"].ToString();
Cell cell = SheetView.Cells[strCellName];
cell.ForeColor = CurrentCellStyle.ForColor;
cell.BackColor = CurrentCellStyle.BackColor;
cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle);
}
}
}
FpSpread.LoadFormulas(true);
fpSheetEditor1.EnableToolStrip(true);
UpdateChart();
UpdateEquation();
}
catch (Exception ex)
{
MessageBox.Show("加载表单出错！\r\n原因：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.RemoveOwnedForm(ProgressScreen.Current);
ProgressScreen.Current.CloseSplashScreen();
this.Activate();
}
}
/// <summary>
/// 保存表单的单元格样式
/// </summary>
/// <param name="moduleIDs"></param>
/// <param name="IsModule"></param>
/// <returns></returns>
public static bool SaveCellStyle(Guid SheetID, string CellName, JZCellStyle CellStyle)
{
return(Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "SaveCellStyle", new object[] { SheetID, CellName, CellStyle })));
}
private void LoadSheets()
{
ProgressScreen.Current.ShowSplashScreen();
this.AddOwnedForm(ProgressScreen.Current);
Dictionary <Guid, SheetView> SheetCollection = new Dictionary <Guid, SheetView>();
List <FarPoint.CalcEngine.FunctionInfo> Infos = FunctionItemInfoUtil.getFunctionItemInfos();
try
{
CrossSheetFormulaCache.Clear();
FpSpread.Sheets.Clear();
List <JZFormulaData> CrossSheetLineFormulaInfos = ModuleHelperClient.GetLineFormulaByModuleIndex(moduleID);
foreach (Sys_Sheet sheet in sheetsList)
{
ProgressScreen.Current.SetStatus = "正在初始化表单‘" + sheet.Name + "’";
String sheetXML = ModuleHelperClient.GetSheetXMLByID(sheet.ID);
SheetView SheetView = Serializer.LoadObjectXml(typeof(SheetView), sheetXML, "SheetView") as SheetView;
SheetView.Tag = sheet.ID;
SheetView.SheetName = sheet.Name;
SheetView.ZoomFactor = 1.0F;
FpSpread.Sheets.Add(SheetView);
DataTable dtCellStyle = ModuleHelperClient.GetCellStyleBySheetID(sheet.ID);
for (int i = 0; i < dtCellStyle.Rows.Count; i++)
{
if (dtCellStyle.Rows[i]["CellStyle"] != null)
{
JZCellStyle CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(dtCellStyle.Rows[i]["CellStyle"].ToString());
if (CurrentCellStyle != null)
{
string strCellName = dtCellStyle.Rows[i]["CellName"].ToString();
Cell cell = SheetView.Cells[strCellName];
cell.ForeColor = CurrentCellStyle.ForColor;
cell.BackColor = CurrentCellStyle.BackColor;
cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle);
}
}
}
foreach (FarPoint.CalcEngine.FunctionInfo Info in Infos)
{
SheetView.AddCustomFunction(Info);
}
if (!SheetCollection.ContainsKey(sheet.ID))
{
SheetCollection.Add(sheet.ID, SheetView);
}
}
FpSpread.LoadFormulas(true);
foreach (JZFormulaData formula in CrossSheetLineFormulaInfos)
{
if (!SheetCollection.ContainsKey(formula.SheetIndex))
{
continue;
}
SheetView Sheet = SheetCollection[formula.SheetIndex];
Cell cell = Sheet.Cells[formula.RowIndex, formula.ColumnIndex];
if (cell != null)
{
if (formula.Formula.ToUpper().Trim() == "NA()")
{
cell.Formula = "";
}
else
{
cell.Formula = formula.Formula;
}
if (formula.FormulaType == 1)
{
cell.BackColor = Color.Blue;
}
string key = string.Format("{0}_{1}_{2}", formula.SheetIndex, formula.RowIndex, formula.ColumnIndex);
if (ForLine)
{//线路公式
if (formula.FormulaType == 1)
{
if (!CrossSheetFormulaCache.ContainsKey(key))
{
CrossSheetFormulaCache.Add(key, formula);
}
else
{
CrossSheetFormulaCache[key] = formula;
}
}
}
else
{
if (!CrossSheetFormulaCache.ContainsKey(key))
{
CrossSheetFormulaCache.Add(key, formula);
}
else
{
CrossSheetFormulaCache[key] = formula;
}
}
}
}
FpSpread.LoadFormulas(true);
fpSpreadEditor1.EnableToolStrip(true);
UpdateChart();
UpdateEquation();
}
catch (Exception ex)
{
MessageBox.Show("加载模板出错！\r\n原因：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
this.RemoveOwnedForm(ProgressScreen.Current);
ProgressScreen.Current.CloseSplashScreen();
this.Activate();
}
}
private void LoadSpread(Guid moduleID)
{
try
{
ProgressScreen.Current.ShowSplashScreen();
ProgressScreen.Current.SetStatus = "正在打开模板...";
FpSpread = new MyCell();
//增加水印、图章
FpSpread.Watermark = ModuleHelperClient.GetWatermarkByModuleID(moduleID);
Dictionary <Guid, SheetView> SheetCollection = new Dictionary <Guid, SheetView>();
List <FarPoint.CalcEngine.FunctionInfo> Infos = FunctionItemInfoUtil.getFunctionItemInfos();
JZDocument defaultDocument = ModuleHelperClient.GetDefaultDocument(moduleID);
List <JZFormulaData> CrossSheetLineFormulaInfos = ModuleHelperClient.GetLineFormulaByModuleIndex(moduleID);
foreach (JZSheet sheet in defaultDocument.Sheets)
{
ProgressScreen.Current.SetStatus = "正在初始化表单‘" + sheet.Name + "’";
String sheetXML = ModuleHelperClient.GetSheetXMLByID(sheet.ID);
SheetView SheetView = Serializer.LoadObjectXml(typeof(SheetView), sheetXML, "SheetView") as SheetView;
SheetView.Tag = sheet.ID;
SheetView.SheetName = sheet.Name;
SheetView.Cells[0, 0].Value = "";
SheetView.Protect = true;
FpSpread.Sheets.Add(SheetView);
DataTable dtCellStyle = ModuleHelperClient.GetCellStyleBySheetID(sheet.ID);
for (int i = 0; i < dtCellStyle.Rows.Count; i++)
{
if (dtCellStyle.Rows[i]["CellStyle"] != null)
{
JZCellStyle CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(dtCellStyle.Rows[i]["CellStyle"].ToString());
if (CurrentCellStyle != null)
{
string strCellName = dtCellStyle.Rows[i]["CellName"].ToString();
Cell cell = SheetView.Cells[strCellName];
cell.ForeColor = CurrentCellStyle.ForColor;
cell.BackColor = CurrentCellStyle.BackColor;
cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle);
}
}
}
SheetCollection.Add(sheet.ID, SheetView);
foreach (FarPoint.CalcEngine.FunctionInfo Info in Infos)
{
SheetView.AddCustomFunction(Info);
}
}
ProgressScreen.Current.SetStatus = "正在初始化跨表公式...";
FpSpread.LoadFormulas(false);
foreach (JZFormulaData formula in CrossSheetLineFormulaInfos)
{
if (!SheetCollection.ContainsKey(formula.SheetIndex))
{
continue;
}
SheetView Sheet = SheetCollection[formula.SheetIndex];
Cell cell = Sheet.Cells[formula.RowIndex, formula.ColumnIndex];
if (cell != null)
{
try
{
if (formula.Formula.ToUpper().Trim() == "NA()")
{
cell.Formula = "";
}
else
{
cell.Formula = formula.Formula;
}
}
catch
{
}
}
}
FpSpread.LoadFormulas(true);
ProgressScreen.Current.SetStatus = "正在显示资料...";
}
catch (Exception ex)
{
logger.Error(ex.ToString());
}
finally
{
try
{
//this.RemoveOwnedForm(ProgressScreen.Current);
ProgressScreen.Current.CloseSplashScreen();
}
catch
{
}
}
}
private void DataDialog_Load(object sender, EventArgs e)
{
ProgressScreen.Current.ShowSplashScreen();
this.AddOwnedForm(ProgressScreen.Current);
Dictionary <Guid, SheetView> SheetCollection = new Dictionary <Guid, SheetView>();
JZDocument document = DocumentHelperClient.GetDocumentByID(DataID);
JZDocument defaultDocument = ModuleHelperClient.GetDefaultDocument(ModuleID);
List <JZFormulaData> CrossSheetFormulaInfos = ModuleHelperClient.GetLineFormulaByModuleIndex(ModuleID);
FpSpread.Sheets.Clear();
LoadSpread(FpSpread, ModuleID);
#region 初始化表单
foreach (JZSheet sheet in defaultDocument.Sheets)
{
SheetView SheetView = GetSheet(sheet.ID);
foreach (JZCell dataCellDefault in sheet.Cells)
{
Cell cell = SheetView.Cells[dataCellDefault.Name];
Object value = JZCommonHelper.GetCellValue(document, sheet.ID, dataCellDefault.Name);
Boolean hasValue = true;
if (value == null || value.ToString() == "")
{
hasValue = false;
}
if (cell != null)
{
#region 处理单元格
cell.Locked = false;
cell.Font = defaultFont;
if (cell.CellType is DownListCellType)
{
DownListCellType CellType = cell.CellType as DownListCellType;
CellType.DropDownButton = false;
CellType.DesignMode = false;
cell.Value = value;
}
else if (cell.CellType is TextCellType)
{
TextCellType CellType = cell.CellType as TextCellType;
if (CellType.FieldType.Description == FieldType.Text.Description)
{
CellType.Multiline = true;
CellType.WordWrap = true;
}
CellType.MaxLength = CellType.FieldType.Length;
if (hasValue)
{
cell.Value = value.ToString().Trim('\r', '\n');;
}
else
{
cell.Value = value;
}
}
else if (cell.CellType is LongTextCellType)
{
LongTextCellType CellType = cell.CellType as LongTextCellType;
if (CellType.FieldType.Description == FieldType.LongText.Description)
{
CellType.Multiline = true;
CellType.WordWrap = true;
}
CellType.MaxLength = CellType.FieldType.Length;
if (hasValue)
{
cell.Value = value.ToString().Trim('\r', '\n');;
}
else
{
cell.Value = value;
}
}
else if (cell.CellType is DateTimeCellType)
{
DateTimeCellType CellType = cell.CellType as DateTimeCellType;
CellType.MinimumDate = new DateTime(1753, 1, 1);
CellType.MaximumDate = new DateTime(9999, 12, 31);
cell.Value = value;
}
else if (cell.CellType is RichTextCellType)
{
RichTextCellType CellType = cell.CellType as RichTextCellType;
CellType.Multiline = false;
CellType.WordWrap = false;
CellType.MaxLength = CellType.FieldType.Length;
if (hasValue)
{
cell.Value = value.ToString().Trim('\r', '\n');
}
else
{
cell.Value = value;
}
}
else if (cell.CellType is DeleteLineCellType)
{//删除线
DeleteLineCellType CellType = cell.CellType as DeleteLineCellType;
CellType.Multiline = true;
CellType.WordWrap = true;
CellType.MaxLength = CellType.FieldType.Length;
cell.CellType = CellType;
object objOld = cell.Text;
if (hasValue)
{
cell.Value = new System.Text.RegularExpressions.Regex("'+").Replace(value.ToString(), "'"); //value.ToString();
if (string.IsNullOrEmpty(cell.Text))
{
RichTextBox rt = new RichTextBox();
rt.Text = objOld == null ? "" : objOld.ToString();
rt.Font = new Font("宋体", 10.5f, FontStyle.Regular);
cell.Value = rt.Rtf;
}
}
else
{
RichTextBox rt = new RichTextBox();
rt.Text = objOld == null ? "" : objOld.ToString();
rt.Font = new Font("宋体", 10.5f, FontStyle.Regular);
cell.Value = rt.Rtf;
}
}
else if (cell.CellType is NumberCellType)
{
NumberCellType CellType = cell.CellType as NumberCellType;
CellType.MaximumValue = 999999999.9999;
CellType.MinimumValue = -999999999.9999;
cell.Value = value;
}
else if (cell.CellType is MaskCellType)
{
MaskCellType CellType = cell.CellType as MaskCellType;
for (int i = CellType.Mask.Length; i < CellType.FieldType.Length; i++)
{
CellType.Mask += "0";
}
if (CellType.CustomMaskCharacters != null && CellType.CustomMaskCharacters.Length > 0)
{
CellType.CustomMaskCharacters[0] = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-()ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ（）复检";
}
if (value == null || value.ToString().Trim() == "")
{
cell.Value = null;
}
else
{
cell.Value = value.ToString().Trim();
}
}
else if (cell.CellType is ImageCellType)
{
if (value != null)
{
cell.Value = JZCommonHelper.StringToBitmap(value.ToString());
}
else
{
cell.Value = null;
}
}
else if (cell.CellType is HyperLinkCellType)
{
if (value != null)
{
List <string> lstLink = new List <string>();
HyperLinkCellType hlnkCell = new HyperLinkCellType();
try
{
lstLink = Newtonsoft.Json.JsonConvert.DeserializeObject <List <string> >(value.ToString());
hlnkCell.Text = lstLink[0];
hlnkCell.Link = lstLink[1];
}
catch
{
hlnkCell.Text = value.ToString();
hlnkCell.Link = "";
}
cell.CellType = hlnkCell;
//cell.Value = value;
}
else
{
HyperLinkCellType hlnkCell = new HyperLinkCellType();
cell.CellType = hlnkCell;
}
}
else
{
cell.Value = value;
}
#endregion
JZCellProperty p = cell.Tag as JZCellProperty;
#region 处理单元格属性
if (p != null)
{
cell.Locked = false;
}
else
{
// logger.Error("未能设置数据区信息：单元格" + dataCellDefault.Name + "，表单：" + sheet.Name);
}
#endregion
}
}
#region 线路单元格样式
DataTable dtCellStyle = ModuleHelperClient.GetCellStyleBySheetID(sheet.ID);
for (int i = 0; i < dtCellStyle.Rows.Count; i++)
{
if (dtCellStyle.Rows[i]["CellStyle"] != null)
{
JZCellStyle CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(dtCellStyle.Rows[i]["CellStyle"].ToString());
if (CurrentCellStyle != null)
{
string strCellName = dtCellStyle.Rows[i]["CellName"].ToString();
Cell cell = SheetView.Cells[strCellName];
cell.ForeColor = CurrentCellStyle.ForColor;
cell.BackColor = CurrentCellStyle.BackColor;
cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle);
}
}
}
#endregion
}
#endregion
UpdateChart();
UpdateEquation();
//设置只读模式
if (ReadOnly)
{
foreach (SheetView sheet in FpSpread.Sheets)
{
sheet.OperationMode = OperationMode.ReadOnly;
}
}
}
public void RunSubStringTest()
{
HNT h = new HNT()
{
ID = Guid.NewGuid(),
BD = "标段",
DW = "单位",
SYS = "试验室",
GCMC = "工程名称",
Line = "线路",
LQ = "龄期",
QDDJ = "强度等级",
QDZ = new List <float>()
{
12.1f, 12.4f, 12.6f
},
SGBW = "施工部位",
SYRQ = DateTime.Now,
ZJRQ = DateTime.Now
};
JZCellStyle cs = new JZCellStyle();
cs.BackColor = Color.White;
cs.FamilyName = "宋体";
cs.FontSize = 17;
cs.FontStyle = FontStyle.Bold;
cs.ForColor = Color.Black;
GJ j = new GJ()
{
ID = Guid.NewGuid(),
BD = "标段",
DW = "单位",
SYS = "试验室",
GCMC = "工程名称",
GGXH = "规格型号",
KLQD = new List <float>()
{
123.1f, 123.2f, 123.3f
},
Line = "线路",
PH = "批号",
QFQD = new List <float>()
{
33.3f, 22.2f, 11.1f
},
SCCJ = "生产厂家",
SGBW = "施工部位",
SYRQ = DateTime.Now,
SYLB = "试验类别",
ZJ = "直径"
};
String json = Newtonsoft.Json.JsonConvert.SerializeObject(h);
String jsonCS = Newtonsoft.Json.JsonConvert.SerializeObject(cs);
//string str = "{"ForColor":"Black","BackgroundColor":"White","FamilyName":"宋体","FontStyle":1,"FontSize":17.0}";
JZCellStyle css = Newtonsoft.Json.JsonConvert.DeserializeObject <JZCellStyle>(jsonCS);
json = Newtonsoft.Json.JsonConvert.SerializeObject(j);
Assert.IsNotEmpty(json);
}