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();
}