Exemple #1
0
 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;
                }
            }
        }
Exemple #3
0
        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;
                }
            }
        }
Exemple #4
0
        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);
        }
Exemple #5
0
        /// <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
                }
            }
        }
Exemple #6
0
        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');
                        }
                    }
                }
            }
        }