private void DataItemForm_Load(object sender, EventArgs e)
{
foreach (Cell cell in cells)
{
DataItems.ActiveSheet.Rows.Add(DataItems.ActiveSheet.Rows.Count, 1);
int RowIndex = DataItems.ActiveSheet.Rows.Count - 1;
DataItems.ActiveSheet.Rows[RowIndex].Tag = cell;
DataItems.ActiveSheet.Cells[RowIndex, 0].Value = cell.Column.Label + cell.Row.Label;
JZCellProperty property = cell.Tag as JZCellProperty;
IGetFieldType FieldTypeGetter = cell.CellType as IGetFieldType;
if (FieldTypeGetter != null)
{
DataItems.ActiveSheet.Cells[RowIndex, 2].Value = FieldTypeGetter.FieldType.Description;
}
if (property == null)
{
property = new JZCellProperty();
cell.Tag = property;
}
DataItems.ActiveSheet.Cells[RowIndex, 1].Value = property.Description;
DataItems.ActiveSheet.Cells[RowIndex, 3].Value = property.IsUnique;
DataItems.ActiveSheet.Cells[RowIndex, 4].Value = property.IsNotNull;
DataItems.ActiveSheet.Cells[RowIndex, 5].Value = property.IsNotCopy;
DataItems.ActiveSheet.Cells[RowIndex, 6].Value = property.IsPingxing;
DataItems.ActiveSheet.Cells[RowIndex, 7].Value = property.IsReadOnly;
DataItems.ActiveSheet.Cells[RowIndex, 8].Value = property.IsKey;
}
}
private void TableList_SelectedIndexChanged(object sender, EventArgs e)
{
foreach (Cell cell in cells)
{
DataItems_Sheet1.Rows.Add(DataItems_Sheet1.Rows.Count, 1);
int RowIndex = DataItems_Sheet1.Rows.Count - 1;
DataItems_Sheet1.Rows[RowIndex].Tag = cell;
DataItems_Sheet1.Cells[RowIndex, 0].Value = cell.Column.Label + cell.Row.Label;
JZCellProperty property = cell.Tag as JZCellProperty;
IGetFieldType FieldTypeGetter = cell.CellType as IGetFieldType;
if (FieldTypeGetter != null)
{
DataItems_Sheet1.Cells[RowIndex, 2].Value = FieldTypeGetter.FieldType.Description;
}
if (property != null)
{
DataItems_Sheet1.Cells[RowIndex, 1].Value = property.Description;
DataItems_Sheet1.Cells[RowIndex, 3].Value = property.IsUnique;
DataItems_Sheet1.Cells[RowIndex, 4].Value = property.IsNotNull;
DataItems_Sheet1.Cells[RowIndex, 5].Value = property.IsNotCopy;
DataItems_Sheet1.Cells[RowIndex, 6].Value = property.IsPingxing;
DataItems_Sheet1.Cells[RowIndex, 7].Value = property.IsReadOnly;
DataItems_Sheet1.Cells[RowIndex, 8].Value = property.IsKey;
}
else
{
DataItems_Sheet1.Rows[RowIndex].BackColor = Color.Red;
}
}
}
private void Button_ok_Click(object sender, EventArgs e)
{
for (int i = 0; i < DataItems.ActiveSheet.Rows.Count; i++)
{
Cell cell = DataItems.ActiveSheet.Rows[i].Tag as Cell;
if (cell != null)
{
JZCellProperty property = cell.Tag as JZCellProperty;
if (property == null)
{
property = new JZCellProperty();
cell.Tag = property;
}
if (DataItems.ActiveSheet.Cells[i, 1].Value != null)
{
property.Description = DataItems.ActiveSheet.Cells[i, 1].Value.ToString();
}
property.IsUnique = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 3].Value);
property.IsNotNull = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 4].Value);
property.IsNotCopy = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 5].Value);
property.IsPingxing = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 6].Value);
property.IsReadOnly = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 7].Value);
property.IsKey = System.Convert.ToBoolean(DataItems.ActiveSheet.Cells[i, 8].Value);
if (cell.CellType != null && DataItems_Sheet1.Cells[i, 2].Value.ToString() == cell.CellType.ToString())
{
}
else
{
IGetFieldType FieldTypeGetter = CellTypeFactory.CreateCellType(DataItems_Sheet1.Cells[i, 2].Value.ToString()) as IGetFieldType;
cell.CellType = FieldTypeGetter as ICellType;
}
AddDataAreaCell(cell);
}
}
Close();
String Message = "设置完毕，点击保存按钮后生效";
MessageBoxIcon Icon = MessageBoxIcon.Information;
MessageBox.Show(Message, "提示", MessageBoxButtons.OK, Icon);
}
private void SheetDataCheckZone()
{
String msg = "未发现问题！";
foreach (Cell cell in dataCells)
{
JZCellProperty p = cell.Tag as JZCellProperty;
if (p == null || cell.CellType == null)
{
cell.BackColor = Color.Red;
msg = "红色单元格未设置类型属性!";
}
else
{
cell.BackColor = Color.White;
}
}
MessageBox.Show(msg);
}
/// <summary>
/// 导出到Excel工作簿
/// </summary>
private void ExportToExcel()
{
SaveFileDialog FileDialog = new SaveFileDialog();
FileDialog.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls|Excel 2007 工作簿(*.xlsx)|*.xlsx";
FileDialog.FilterIndex = 1;
FileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
FileDialog.FileName = GetFileName(FileDialog.InitialDirectory, Text, FileDialog.FilterIndex == 1 ? ".xls" : ".xlsx");
FileDialog.RestoreDirectory = true;
if (FileDialog.ShowDialog() == DialogResult.OK)
{
if (FileDialog.FilterIndex == 1)
{
Bitmap bitmap = new Bitmap(130, 170);
int ImgRowIndex = -1;
int ImgColumnIndex = -1;
string strImageFileName = "";
SheetView view = fpSpread.ActiveSheet;
string strSheetID = view.Tag.ToString();
bool bHasImg = false;
string strRangeName = string.Empty;
#region 循环处理单元格
for (int i = 0; i < view.RowCount; i++)
{
for (int j = 0; j < view.ColumnCount; j++)
{
Cell cell = view.Cells[i, j];
if (cell != null)
{
IGetFieldType FieldTypeGetter = cell.CellType as IGetFieldType;
JZCellProperty property = cell.Tag as JZCellProperty;
if (property != null)
{
if (FieldTypeGetter != null && FieldTypeGetter.FieldType.Description == "图片")
{
if (cell.Value != null && cell.Value is Bitmap)
{
strImageFileName = FileDialog.FileName;
strImageFileName = strImageFileName.Replace(".xls", ".jpg");
ImgRowIndex = i;
ImgColumnIndex = j;
//创建一个bitmap类型的bmp变量来读取文件。
bitmap = cell.Value as Bitmap;
//新建第二个bitmap类型的bmp2变量，我这里是根据我的程序需要设置的。
Bitmap bitmap2 = new Bitmap(bitmap.Width, bitmap.Height, PixelFormat.Format16bppRgb555);//PixelFormat.Format16bppRgb555
//将第一个bmp拷贝到bmp2中
Graphics draw = Graphics.FromImage(bitmap2);
draw.DrawImage(bitmap, 0, 0);
bitmap2.Save(strImageFileName, ImageFormat.Jpeg);
cell.Value = null;
bHasImg = true;
strRangeName = ((char)('A' + ImgColumnIndex)).ToString() + (ImgRowIndex + 1).ToString() + ":" + ((char)('A' + ImgColumnIndex + cell.ColumnSpan - 1)).ToString() + (ImgRowIndex + cell.RowSpan).ToString();
break;
}
}
}
}
}
if (bHasImg == true)
{
break;
}
}
#endregion
fpSpread.SaveExcel(FileDialog.FileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas | FarPoint.Excel.ExcelSaveFlags.SaveAsViewed);
#region 添加图片到Excel
if (bHasImg == true)
{
view.Cells[ImgRowIndex, ImgColumnIndex].Value = bitmap;
PictureToExcel pictrue2excel = new PictureToExcel();
pictrue2excel.Open(FileDialog.FileName);
if (!string.IsNullOrEmpty(strRangeName))
{
pictrue2excel.InsertPicture(strRangeName, strImageFileName);//"L6:M9"
if (File.Exists(strImageFileName))
{
File.Delete(strImageFileName);
}
}
pictrue2excel.SaveFile(FileDialog.FileName);
pictrue2excel.Dispose();
}
#endregion
}
else if (FileDialog.FilterIndex == 2)
{
//fpSpread.SaveExcel(FileDialog.FileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas | FarPoint.Excel.ExcelSaveFlags.SaveAsViewed | FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
Bitmap bitmap = new Bitmap(130, 170);
int ImgRowIndex = -1;
int ImgColumnIndex = -1;
string strImageFileName = "";
SheetView view = fpSpread.ActiveSheet;
string strSheetID = view.Tag.ToString();
bool bHasImg = false;
string strRangeName = string.Empty;
#region 循环处理单元格
for (int i = 0; i < view.RowCount; i++)
{
for (int j = 0; j < view.ColumnCount; j++)
{
Cell cell = view.Cells[i, j];
if (cell != null)
{
IGetFieldType FieldTypeGetter = cell.CellType as IGetFieldType;
JZCellProperty property = cell.Tag as JZCellProperty;
if (property != null)
{
if (FieldTypeGetter != null && FieldTypeGetter.FieldType.Description == "图片")
{
if (cell.Value != null && cell.Value is Bitmap)
{
strImageFileName = FileDialog.FileName;
strImageFileName = strImageFileName.Replace(".xls", ".jpg");
ImgRowIndex = i;
ImgColumnIndex = j;
//创建一个bitmap类型的bmp变量来读取文件。
bitmap = cell.Value as Bitmap;
//新建第二个bitmap类型的bmp2变量，我这里是根据我的程序需要设置的。
Bitmap bitmap2 = new Bitmap(bitmap.Width, bitmap.Height, PixelFormat.Format16bppRgb555);//PixelFormat.Format16bppRgb555
//将第一个bmp拷贝到bmp2中
Graphics draw = Graphics.FromImage(bitmap2);
draw.DrawImage(bitmap, 0, 0);
bitmap2.Save(strImageFileName, ImageFormat.Jpeg);
cell.Value = null;
bHasImg = true;
strRangeName = ((char)('A' + ImgColumnIndex)).ToString() + (ImgRowIndex + 1).ToString() + ":" + ((char)('A' + ImgColumnIndex + cell.ColumnSpan - 1)).ToString() + (ImgRowIndex + cell.RowSpan).ToString();
break;
}
}
}
}
}
if (bHasImg == true)
{
break;
}
}
#endregion
fpSpread.SaveExcel(FileDialog.FileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas | FarPoint.Excel.ExcelSaveFlags.SaveAsViewed | FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
#region 添加图片到Excel
if (bHasImg == true)
{
view.Cells[ImgRowIndex, ImgColumnIndex].Value = bitmap;
PictureToExcel pictrue2excel = new PictureToExcel();
pictrue2excel.Open(FileDialog.FileName);
if (!string.IsNullOrEmpty(strRangeName))
{
pictrue2excel.InsertPicture(strRangeName, strImageFileName);//"L6:M9"
if (File.Exists(strImageFileName))
{
File.Delete(strImageFileName);
}
}
pictrue2excel.SaveFile(FileDialog.FileName);
pictrue2excel.Dispose();
}
#endregion
}
}
}
/// <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);
}
}
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;
}
}
}
/// <summary>
/// 单元格的值是否唯一，返回不唯一的模板名
/// </summary>
/// <param name="cell"></param>
/// <param name="sheetID"></param>
/// <param name="moduleID"></param>
/// <returns></returns>
public static DataTable IsUniqueAndReturnDT(JZCellProperty property, JZCell cell, Guid sheetID, Guid moduleID, String testRoomCode, Guid documentID)
{
return(Agent.CallService("Yqun.BO.BusinessManager.dll", "IsUniqueAndReturnDT", new object[] { property, cell, sheetID, moduleID, testRoomCode, documentID }) as DataTable);
}
/// <summary>
/// 未保存单元格数据值，此单元格所在模板与表单
/// </summary>
/// <param name="cell"></param>
/// <param name="sheetID"></param>
/// <param name="moduleID"></param>
/// <returns></returns>
public static Boolean IsUnique(JZCellProperty property, JZCell cell, Guid sheetID, Guid moduleID, String testRoomCode, Guid documentID)
{
return(Convert.ToBoolean(Agent.CallService("Yqun.BO.BusinessManager.dll", "IsUnique", new object[] { property, cell, sheetID, moduleID, testRoomCode, documentID })));
}
