internal void Save() { wbView.GetLock(); try { workbook.SaveAs(m_filename, m_fileFormat); //workbook.Save(); } finally { wbView.ReleaseLock(); } }
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(); } }
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); } } }
/// <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; } }
public void WorkbookViewSheetClear(WorkbookView wbv) { wbv.GetLock(); wbv.ActiveWorksheet.Cells.Clear(); foreach (IShape shape in wbv.ActiveWorksheet.Shapes) { shape.Delete(); } wbv.ReleaseLock(); }
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(); }
public void Clear() { wbView.GetLock(); try { m_suspendUpdates = true; worksheet.UsedRange.Clear(); } finally { m_suspendUpdates = false; wbView.ReleaseLock(); } }
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); } }
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(); } }
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); } } }
/// <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); } }
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(); }