Пример #1
0
 internal void Save()
 {
     wbView.GetLock();
     try
     {
         workbook.SaveAs(m_filename, m_fileFormat);
         //workbook.Save();
     }
     finally
     {
         wbView.ReleaseLock();
     }
 }
Пример #2
0
        private void LoadWorkbookView()
        {
            //workbook = SpreadsheetGear.Factory.GetWorkbook(this.tmpXlsName);
            workbook  = SpreadsheetGear.Factory.GetWorkbook();
            worksheet = workbook.ActiveWorksheet;

            wbView = new SpreadsheetGear.Windows.Forms.WorkbookView(workbook);
            wbView.GetLock();
            try
            {
                SpreadsheetGear.IWorkbookWindowInfo windowInfo = wbView.ActiveWorkbookWindowInfo;
                windowInfo.DisplayWorkbookTabs = false;
                worksheet.WindowInfo.Zoom      = 100;
                wbView.Parent = this;;
                wbView.BringToFront();
                wbView.Dock                   = DockStyle.Fill;
                wbView.CellBeginEdit         += new CellBeginEditEventHandler(wbView_CellBeginEdit);
                wbView.RangeChanged          += new RangeChangedEventHandler(wbView_RangeChanged);
                wbView.RangeSelectionChanged += wbView_RangeSelectionChanged;
            }
            finally
            {
                wbView.ReleaseLock();
            }
        }
Пример #3
0
        private void efBtn_excel_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();

            openFileDialog.InitialDirectory = "c:\\";
            openFileDialog.Filter           = "txt files (*.xls)|*.xls";
            openFileDialog.FilterIndex      = 2;
            openFileDialog.Multiselect      = false;
            openFileDialog.RestoreDirectory = true;

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    efGroupBox3.Controls.Clear();
                    m_workbookView = new WorkbookView();
                    m_workbookView.GetLock();
                    efGroupBox3.Controls.Add(m_workbookView);
                    m_workbookView.Parent = efGroupBox3;
                    m_workbookView.Dock   = DockStyle.Fill;
                    SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(openFileDialog.FileName, System.Globalization.CultureInfo.CurrentCulture);
                    m_workbookView.ActiveWorkbook = workbook;
                    m_workbookView.ReleaseLock();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }
        }
Пример #4
0
        /// <summary>
        /// Sets source data and type of data.
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="db"></param>
        public void SetDataTable(DataTable tbl)
        {
            m_dataTable = tbl;
            LoadWorkbookView();
            SetupContextMenus();
            wbView.GetLock();
            try
            {
                m_suspendUpdates = true;
                worksheet.UsedRange.Clear();
                worksheet.WindowInfo.ScrollColumn = 0;
                worksheet.WindowInfo.ScrollRow    = 0;
                worksheet.WindowInfo.FreezePanes  = true;


                var range = worksheet.Cells["A1"];
                range.EntireColumn.ColumnWidth = 1;
                range.Select();
                range.CopyFromDataTable(tbl, SpreadsheetGear.Data.SetDataFlags.None);
                worksheet.UsedRange.Columns.AutoFit();
                // range.EntireColumn.ColumnWidth = 0;
            }
            finally
            {
                wbView.ReleaseLock();
                m_suspendUpdates = false;
            }
        }
Пример #5
0
 public void WorkbookViewSheetClear(WorkbookView wbv)
 {
     wbv.GetLock();
     wbv.ActiveWorksheet.Cells.Clear();
     foreach (IShape shape in wbv.ActiveWorksheet.Shapes)
     {
         shape.Delete();
     }
     wbv.ReleaseLock();
 }
Пример #6
0
 public void SheetClear(WorkbookView wbv)
 {
     wbv.GetLock();
     wbv.ActiveWorksheet.Cells.Clear();
     wbv.ActiveWorksheet.Cells.Borders.Color = SpreadsheetGear.Colors.White;
     foreach (IShape shape in wbv.ActiveWorksheet.Shapes)
     {
         shape.Delete();
     }
     wbv.ReleaseLock();
 }
Пример #7
0
 public void Clear()
 {
     wbView.GetLock();
     try
     {
         m_suspendUpdates = true;
         worksheet.UsedRange.Clear();
     }
     finally
     {
         m_suspendUpdates = false;
         wbView.ReleaseLock();
     }
 }
Пример #8
0
        private void efBtn_import_preview_Click(object sender, EventArgs e)
        {
            try
            {
                if (m_workbookView != null)
                {
                    m_workbookView.GetLock();
                    System.Data.DataSet ds = null;
                    DataTable           dt = null;
                    if (efRB_col_seq.Checked)
                    {
                        ds = m_workbookView.ActiveWorkbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText | SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
                        dt = ds.Tables[m_workbookView.ActiveSheet.Name.Trim()];
                        dt.Rows.RemoveAt(0);
                    }
                    else
                    {
                        ds = m_workbookView.ActiveWorkbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText);
                        dt = ds.Tables[m_workbookView.ActiveSheet.Name.Trim()];
                    }
                    m_workbookView.ReleaseLock();

                    /////////
#if (Devxpress)
                    if (m_ctrlGrid is  GridControl)
                    {
                        if (SetGridData(( GridControl)m_ctrlGrid, dt) != 0)
                        {
                            throw new Exception("预览导入失败");
                        }
                    }
#endif
                    if (m_ctrlGrid is  DataGridView)
                    {
                        if (SetGridData(( DataGridView)m_ctrlGrid, dt) != 0)
                        {
                            throw new Exception("预览导入失败");
                        }
                    }
                }
                else
                {
                    throw new Exception("请先打开EXCEL文件");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #9
0
        private void LoadWorkbookView()
        {
            //workbook = SpreadsheetGear.Factory.GetWorkbook(this.tmpXlsName);
            // workbook = SpreadsheetGear.Factory.GetWorkbook();
            worksheet = workbook.ActiveWorksheet;

            wbView = new SpreadsheetGear.Windows.Forms.WorkbookView(workbook);
            wbView.GetLock();
            try
            {
                //SpreadsheetGear.IWorkbookWindowInfo windowInfo = wbView.ActiveWorkbookWindowInfo;
                //windowInfo.DisplayWorkbookTabs = false;
                worksheet.WindowInfo.Zoom = 100;
                wbView.Parent             = this;;
                wbView.BringToFront();
                wbView.Dock = DockStyle.Fill;
            }
            finally
            {
                wbView.ReleaseLock();
            }
        }
        private void PasteData()
        {
            List <Gen_Company> listofCompany = General.GetQueryable <Gen_Company>(null).ToList();

            objWBView.GetLock();


            int rowsCnt = objWBView.ActiveWorksheet.UsedRange.RowCount;
            int cellCnt = objWBView.ActiveWorksheet.UsedRange.CellCount.ToInt();

            if (cellCnt < 2)
            {
                return;
            }



            try
            {
                grdBookings.RowCount = rowsCnt;
                for (int i = 0; i < rowsCnt; i++)
                {
                    //for (int j = 0; j < 1; j++)
                    //{
                    DateTime pickupdate = objWBView.ActiveWorksheet.Cells[i, 0].Value.ToDateTime();
                    string   vehicle    = objWBView.ActiveWorksheet.Cells[i, 1].Value.ToStr().Trim();



                    string phone     = objWBView.ActiveWorksheet.Cells[i, 2].Value.ToStr().Trim().ToLower();
                    string passenger = objWBView.ActiveWorksheet.Cells[i, 3].Value.ToStr().Trim();
                    string from      = objWBView.ActiveWorksheet.Cells[i, 4].Value.ToStr().Trim().ToLower();
                    string to        = objWBView.ActiveWorksheet.Cells[i, 5].Value.ToStr().Trim();
                    string comment   = objWBView.ActiveWorksheet.Cells[i, 6].Value.ToStr().Trim().ToLower();
                    string account   = objWBView.ActiveWorksheet.Cells[i, 7].Value.ToStr().Trim();



                    if (!string.IsNullOrEmpty(account) && account != "Cash" && listofCompany.Count(c => c.CompanyName.ToLower() == account.ToLower()) == 0)
                    {
                        CompanyBO objCompanyMaster = new CompanyBO();
                        try
                        {
                            objCompanyMaster.New();

                            objCompanyMaster.Current.CompanyName = account;
                            objCompanyMaster.Current.Email       = "*****@*****.**";
                            objCompanyMaster.Current.ContactName = account;
                            objCompanyMaster.Current.Address     = "unknown";
                            objCompanyMaster.Current.TelephoneNo = string.Empty;
                            objCompanyMaster.Current.MobileNo    = string.Empty;
                            objCompanyMaster.Current.WebsiteUrl  = string.Empty;
                            objCompanyMaster.Current.Fax         = string.Empty;

                            objCompanyMaster.Current.IsClosed = false;

                            objCompanyMaster.Current.HasOrderNo       = false;
                            objCompanyMaster.Current.HasPupilNo       = false;
                            objCompanyMaster.Current.AdminFees        = 0;
                            objCompanyMaster.Current.HasComcabCharges = false;

                            objCompanyMaster.Current.DiscountPercentage = 0;

                            objCompanyMaster.Current.FareDeductionPercent = 0;

                            objCompanyMaster.Current.HasVat = false;

                            objCompanyMaster.Current.HasBookedBy = false;

                            objCompanyMaster.Current.CompanyCode = string.Empty;

                            objCompanyMaster.Current.AccountTypeId = Enums.ACCOUNT_TYPE.ACCOUNT;


                            objCompanyMaster.Current.AccountNo        = string.Empty;
                            objCompanyMaster.Current.WebLoginId       = string.Empty;
                            objCompanyMaster.Current.WebLoginPassword = string.Empty;
                            objCompanyMaster.Current.IsWebLoginActive = false;


                            objCompanyMaster.Current.BackgroundColor = string.Empty;
                            objCompanyMaster.Current.TextColor       = string.Empty;
                            objCompanyMaster.Current.PasswordAccount = string.Empty;
                            objCompanyMaster.Current.PasswordEnable  = false;

                            objCompanyMaster.Save();



                            listofCompany.Add(objCompanyMaster.Current);
                        }
                        catch (Exception ex)
                        {
                            if (objCompanyMaster.Errors.Count > 0)
                            {
                                MessageBox.Show(objCompanyMaster.ShowErrors());
                            }

                            break;
                        }
                    }



                    string via      = objWBView.ActiveWorksheet.Cells[i, 8].Value.ToStr().Trim();
                    string flightno = objWBView.ActiveWorksheet.Cells[i, 9].Value.ToStr().Trim();



                    grdBookings.Rows[i].Cells[COLS.PickupDateTime].Value = pickupdate;



                    int vehicleTypeId = General.GetObject <Fleet_VehicleType>(c => c.VehicleType.ToLower() == vehicle.ToLower()).DefaultIfEmpty().Id;

                    if (vehicleTypeId == 0)
                    {
                        vehicleTypeId = AppVars.objPolicyConfiguration.DefaultVehicleTypeId.ToInt();
                    }

                    grdBookings.Rows[i].Cells[COLS.VehicleType].Value = vehicleTypeId;


                    grdBookings.Rows[i].Cells[COLS.PhoneNo].Value   = phone;
                    grdBookings.Rows[i].Cells[COLS.Passenger].Value = passenger;
                    grdBookings.Rows[i].Cells[COLS.From].Value      = from;
                    grdBookings.Rows[i].Cells[COLS.To].Value        = to;
                    grdBookings.Rows[i].Cells[COLS.Notes].Value     = comment;
                    grdBookings.Rows[i].Cells[COLS.Account].Value   = account;



                    grdBookings.Rows[i].Cells[COLS.Via].Value      = via;
                    grdBookings.Rows[i].Cells[COLS.FlightNo].Value = flightno;



                    //   }
                }
            }
            catch (Exception ex)
            {
            }

            finally
            {
                objWBView.ActiveWorksheet.Cells.Delete();

                objWBView.ReleaseLock();
            }
        }
Пример #11
0
		private void efBtn_excel_Click(object sender, EventArgs e)
		{
			OpenFileDialog openFileDialog = new OpenFileDialog();
			openFileDialog.InitialDirectory = "c:\\";
			openFileDialog.Filter = "txt files (*.xls)|*.xls";
			openFileDialog.FilterIndex = 2;
			openFileDialog.Multiselect = false;
			openFileDialog.RestoreDirectory = true;

			if (openFileDialog.ShowDialog() == DialogResult.OK)
			{
				try
				{
					efGroupBox3.Controls.Clear();
					m_workbookView = new WorkbookView();
					m_workbookView.GetLock();
					efGroupBox3.Controls.Add(m_workbookView);
					m_workbookView.Parent = efGroupBox3;
					m_workbookView.Dock = DockStyle.Fill;
					SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(openFileDialog.FileName, System.Globalization.CultureInfo.CurrentCulture);
					m_workbookView.ActiveWorkbook = workbook;
					m_workbookView.ReleaseLock();
				}
				catch (Exception ex)
				{
					MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
				}
			}
		}
Пример #12
0
        /// <summary>
        /// 生成二维码
        /// </summary>
        /// <param name="wbv">传入WorkbookView控件</param>
        /// <param name="Title">标题</param>
        /// <param name="ColumnNames">表头列表</param>
        /// <param name="Values">值列表</param>
        /// <param name="QRCodeValue">二维码值</param>
        public void LoadBarcode(WorkbookView wbv, string Title,
                                string[] ColumnNames, string[] Values, string QRCodeValue, string[] Checkouts)
        {
            double Proportion = 0.6;

            WorkbookViewSheetClear(wbv);

            wbv.GetLock();
            IRange range = wbv.ActiveWorksheet.Cells;

            range[0, 0].RowHeight     = 42 * Proportion;
            range.Borders.LineStyle   = LineStyle.None;
            range.Font.Name           = "楷体";
            range.Font.Bold           = true;
            range.HorizontalAlignment = HAlign.Left;
            range.Font.Size           = 12 * Proportion;
            range.ColumnWidth         = 10 * Proportion;
            range.RowHeight           = 16 * Proportion;
            range[0, 0, 0, 4].Merge();
            range[0, 0].RowHeight           = 32 * Proportion;
            range[0, 0].HorizontalAlignment = HAlign.Center;
            range[0, 0].VerticalAlignment   = VAlign.Center;
            range[0, 0].Value     = Title;
            range[0, 0].Font.Size = 24 * Proportion;
            range[2, 0, 2 + ColumnNames.Length - 1, 0].RowHeight = 20 * Proportion;

            range[1, 0].Value = ColumnNames[0] + ":";
            range[1, 1, 1, 5].Merge();
            range[1, 1].Value = Values[0];
            range[2, 0].Value = ColumnNames[6] + ":";
            range[2, 1].Value = Values[6];
            range[2, 1, 2, 5].Merge();
            range[3, 0].Value = ColumnNames[2] + ":";
            range[3, 1, 3, 2].Merge();
            range[3, 1].Value = Values[2];
            range[3, 3].Value = ColumnNames[3] + ":";
            range[3, 4].Value = Values[3];
            range[4, 2].Value = ColumnNames[1] + ":";
            range[4, 3].Value = Values[1];
            range[4, 0].Value = ColumnNames[7] + ":";
            range[4, 1].Value = Values[7];
            //range[4, 0].Value = ColumnNames[4] + ":";
            //range[4, 1].Value = Values[4];
            //range[4, 3].Value = ColumnNames[5] + ":";
            //range[4, 4].Value = Values[5];

            for (int i = 0; i < Checkouts.Length; i++)
            {
                range[5 + (i / 6 * 2), 0 + i % 6].Value     = Checkouts[i];
                range[6 + (i / 6 * 2), 0 + i % 6].Value     = "□";
                range[6 + (i / 6 * 2), 0 + i % 6].Font.Size = range[6 + (i / 6 * 2), 0 + i % 6].Font.Size * 1.2;
            }


            range[0, 0, 12, 5].Borders.Color = SpreadsheetGear.Colors.Black;
            range[0, 0, 12, 5].Borders[SpreadsheetGear.BordersIndex.InsideHorizontal].LineStyle = LineStyle.None;
            range[0, 0, 12, 5].Borders[SpreadsheetGear.BordersIndex.InsideVertical].LineStyle   = LineStyle.None;

            range[3 + ColumnNames.Length, 0].Select();

            MemoryStream ms = new MemoryStream();

            try
            {
                QRCodeEncoder qrCodeEncoder = new QRCodeEncoder();
                qrCodeEncoder.QRCodeEncodeMode   = QRCodeEncoder.ENCODE_MODE.BYTE;
                qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;
                qrCodeEncoder.QRCodeVersion      = 0;
                qrCodeEncoder.QRCodeScale        = 3;
                //生成图像
                qrCodeEncoder.Encode(QRCodeValue, Encoding.UTF8).
                Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);;
            }
            catch (Exception ex)
            {
                ReturnValue.ShowMessage(new ReturnValue(false, -1, ex.Message));
            }

            byte[] bytes = ms.GetBuffer();
            wbv.ActiveWorksheet.Shapes.AddPicture(bytes, 335 * Proportion,
                                                  2 * Proportion, 80 * Proportion, 80 * Proportion);

            wbv.ActiveWorksheetWindowInfo.Zoom = (int)(100 / Proportion);
            wbv.ReleaseLock();
        }
        private void PasteFixFares()
        {
            int testi = 0;

            try
            {
                this.objWBView.Paste();

                objWBView.GetLock();


                int rowsCnt = objWBView.ActiveWorksheet.UsedRange.RowCount;
                int cellCnt = objWBView.ActiveWorksheet.UsedRange.CellCount.ToInt();
                if (cellCnt < 4)
                {
                    return;
                }

                int lastRow = 0;

                lastRow = grdFixFares.RowCount;

                grdFixFares.RowCount += rowsCnt;

                for (int i = lastRow; i < grdFixFares.RowCount; i++)
                {
                    testi = i;
                    string vehicle = objWBView.ActiveWorksheet.Cells[i, 0].Value.ToStr().Trim().ToLower();

                    if (vehicle == "vehicle")
                    {
                        continue;
                    }

                    string from = objWBView.ActiveWorksheet.Cells[i, 1].Value.ToStr().Trim();
                    string to   = objWBView.ActiveWorksheet.Cells[i, 2].Value.ToStr().Trim();

                    decimal fares = 0.00m;

                    if (objWBView.ActiveWorksheet.Cells[i, 3].Value.ToStr().StartsWith("£"))
                    {
                        fares = objWBView.ActiveWorksheet.Cells[i, 3].Value.ToStr().Substring(1).ToDecimal();
                    }
                    else
                    {
                        fares = objWBView.ActiveWorksheet.Cells[i, 3].Value.ToDecimal();
                    }

                    grdFixFares.Rows[i].Cells[COLS.VehicleName].Value = vehicle;
                    grdFixFares.Rows[i].Cells[COLS.From].Value        = from;
                    grdFixFares.Rows[i].Cells[COLS.To].Value          = to;
                    grdFixFares.Rows[i].Cells[COLS.Fare].Value        = fares;
                }


                objWBView.ActiveWorksheet.Cells.Delete();

                objWBView.ReleaseLock();
            }
            catch (Exception ex)
            {
                ENUtils.ShowMessage(ex.Message + " " + testi);
            }
        }
Пример #14
0
        private static void Paste(WorkbookView wbView)
        {

            // check to see if clipboard contains text
            if (!Clipboard.ContainsText())
                return;

            // Confirm the pending changes before going on
            //ConfirmChanges();

            // Get the selected start row and col 
            IRange selectedRng = wbView.RangeSelection;
            int startRow = selectedRng.Row;
            int startCol = selectedRng.Column;

            // Do not paste if the row and column is out of the data area
            // If nRow < LOCATION_ROW_OFFSET OrElse nCol <= ColumnIndex.Order OrElse nCol > ColumnIndex.InspectionRequest Then Return
            //if (startCol > ColumnIndex.InspectionRequest)
            //    return;

            //  Get content from clipboard
            string[] lines = Clipboard.GetText().Split('\r');

            if (lines.Length < 65535)
            {
                wbView.PasteSpecial(PasteType.Values, PasteOperation.Add, false, false);
                return;
            }

            // get lock before pasting.
            wbView.GetLock();


            try
            {
                IRange cells = wbView.ActiveWorksheet.Cells;

                for (int k = 0; k < lines.Length; k++)
                {
                    if (!string.IsNullOrEmpty(lines[k]))
                    {
                        // get current row index
                        int row = startRow + k;

                        // split the line
                        string[] aValue = lines[k].Split('\t');

                        for (int i = 0; i < aValue.Length; i++)
                        {
                            // get current column index
                            int col = startCol + i;
                            cells[row, col].Value = aValue[col];
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // If showProgressBar AndAlso frmBar IsNot Nothing Then frmBar.Close()
                wbView.ReleaseLock();
            }

        }
        private void PasteData()
        {
            GridViewRowInfo row = null;

            objWBView.GetLock();
            GridViewColumn col = null;


            int rowsCnt = objWBView.ActiveWorksheet.UsedRange.RowCount;
            int cellCnt = objWBView.ActiveWorksheet.UsedRange.CellCount.ToInt();

            if (cellCnt < 2)
            {
                return;
            }


            row = grdBookings.Rows.AddNew();
            for (int i = 0; i < rowsCnt; i++)
            {
                for (int j = 0; j < 1; j++)
                {
                    string cellHeading = objWBView.ActiveWorksheet.Cells[i, j].Value.ToStr().Trim().ToLower();
                    string cellValue   = objWBView.ActiveWorksheet.Cells[i, j + 1].Value.ToStr().Trim();

                    if (string.IsNullOrEmpty(cellHeading))
                    {
                        row = grdBookings.Rows.AddNew();
                    }

                    col = grdBookings.Columns.FirstOrDefault(c => c.FieldName.ToLower() == cellHeading);

                    if (col != null)
                    {
                        if (col is GridViewDateTimeColumn)
                        {
                            if (cellValue.ToLower().Contains("today"))
                            {
                                cellValue = cellValue.ToLower().Strip("today").Trim();
                            }
                            DateTime tempDate = new DateTime();

                            bool canParse = DateTime.TryParse(cellValue, out tempDate);



                            cellValue = string.Format("{0:HH:mm}", cellValue);
                            TimeSpan pickupTime = TimeSpan.Zero;

                            TimeSpan.TryParse(cellValue, out pickupTime);



                            DateTime pickupDate = DateTime.Now.ToDate();
                            pickupDate = pickupDate + pickupTime;
                            row.Cells[col.Name].Value = pickupDate;
                        }

                        else if (col is GridViewComboBoxColumn)
                        {
                            int vehicleTypeId = General.GetObject <Fleet_VehicleType>(c => c.VehicleType.ToLower() == cellValue.ToLower()).DefaultIfEmpty().Id;

                            if (vehicleTypeId == 0)
                            {
                                vehicleTypeId = AppVars.objPolicyConfiguration.DefaultVehicleTypeId.ToInt();
                            }

                            row.Cells[col.Name].Value = vehicleTypeId;
                        }
                        else
                        {
                            if (col is GridViewDecimalColumn && cellValue.IsNumeric() == false)
                            {
                                cellValue = "0.00";
                            }


                            if (col.FieldName == "Card")
                            {
                                cellValue = "Bank Account";
                            }


                            if (col.FieldName == "Booking Account")
                            {
                                cellValue = "Comcab";
                            }


                            row.Cells[col.Name].Value = cellValue;
                        }
                    }
                }
            }


            objWBView.ActiveWorksheet.Cells.Delete();

            objWBView.ReleaseLock();
        }