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 fpSheetEditor1_CellTypeSetting(object sender, CellTypeEventArgs e)
{
if (e.CellType is ChartCellType)
{
ChartCellType ChartCellType = e.CellType as ChartCellType;
ChartCellType.ActiveSheet = FpSpread.ActiveSheet;
}
IGetFieldType FieldTypeGetter = e.CellType as IGetFieldType;
CellRange[] CellRanges = FpSpread.ActiveSheet.GetSelections();
foreach (CellRange Range in CellRanges)
{
int Row = Range.Row;
int RowCount = Range.RowCount;
int Column = Range.Column;
int ColumnCount = Range.ColumnCount;
if (Row < 0)
{
Row = 0;
}
if (Column < 0)
{
Column = 0;
}
if (RowCount < 0)
{
RowCount = FpSpread.ActiveSheet.RowCount;
}
if (ColumnCount < 0)
{
ColumnCount = FpSpread.ActiveSheet.ColumnCount;
}
Cell cell = FpSpread.ActiveSheet.Cells[Row, Column, Row + RowCount - 1, Column + ColumnCount - 1];
if (cell != null)
{
cell.CellType = e.CellType;
}
}
}
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);
}
/// <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
}
}
}
private void ProcessDoc(JZDocument doc, FpSpread sp, List <JZFormulaData> CrossSheetLineFormulaInfos)
{
foreach (SheetView sheet in sp.Sheets)
{
for (int i = 0; i < sheet.RowCount; i++)
{
for (int j = 0; j < sheet.ColumnCount; j++)
{
Cell cell = sheet.Cells[i, j];
cell.Formula = "";
cell.Value = JZCommonHelper.GetCellValue(doc, new Guid(sheet.Tag.ToString()), cell.Column.Label + cell.Row.Label);
}
}
}
sp.LoadFormulas(true);
foreach (JZFormulaData formula in CrossSheetLineFormulaInfos)
{
SheetView Sheet = GetSheetByID(sp, formula.SheetIndex);
if (Sheet != null)
{
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 (Exception ex)
{
logger.Error(ex.Message);
}
}
}
}
sp.LoadFormulas(true);
foreach (JZSheet sheet in doc.Sheets)
{
SheetView view = GetSheetByID(sp, sheet.ID);
if (view == null)
{
continue;
}
foreach (JZCell dataCell in sheet.Cells)
{
Cell cell = view.Cells[dataCell.Name];
if (cell != null)
{
if (String.IsNullOrEmpty(cell.Formula))
{
continue;
}
IGetFieldType FieldTypeGetter = cell.CellType as IGetFieldType;
if (FieldTypeGetter != null && FieldTypeGetter.FieldType.Description == "图片")
{
continue;
}
else if (FieldTypeGetter != null && FieldTypeGetter.FieldType.Description == "数字")
{
if (cell.Value != null)
{
Decimal d;
if (Decimal.TryParse(cell.Value.ToString().Trim(' ', '\r', '\n'), out d))
{
dataCell.Value = d;
}
else
{
dataCell.Value = null;
}
}
}
else
{
dataCell.Value = cell.Value;
}
if (dataCell.Value != null && dataCell.Value is String)
{
dataCell.Value = dataCell.Value.ToString().Trim(' ', '\r', '\n');
}
}
}
}
}