public void InsertImage(AkbExcelCellCoordinate cell, Image image) { try { SetSelect(cell.Row, cell.Column); Clipboard.SetImage(image); PasteObject(); } catch (Exception e) { XWriteLogs(MethodBase.GetCurrentMethod().Name, e.Message); } }
/// <summary> /// 2017/08/11 AKB Nguyen Thanh Tung /// </summary> /// <param name="cellStart"></param> /// <param name="objValue"></param> /// <returns></returns> public string SetRangeValue(AkbExcelCellCoordinate cellStart, object[,] objValue) { string strRange = string.Empty; try { if (objValue == null) { return(null); } int countRow = objValue.GetLength(0) - 1; int countCol = objValue.GetLength(1) - 1; string NameColStart = GetExcelColumnName(cellStart.Column); string NameColEnd = GetExcelColumnName(cellStart.Column + countCol); int rowEnd = cellStart.Row + countRow; strRange = NameColStart + cellStart.Row + ":" + NameColEnd + rowEnd; _parameters = new Object[1]; _parameters[0] = strRange; _mobjRange = GetProperty(_mobjWorkSheet, "Range", _parameters); SetProperty(_mobjRange, "Value2", objValue); } catch (Exception e) { strRange = string.Empty; XWriteLogs(MethodBase.GetCurrentMethod().Name, e.Message); } finally { FreeMemory(); } return(strRange); }
public void SetCellString(AkbExcelCellCoordinate coor, object val) { var cellVal = "'" + val; SetValue(coor.Row, coor.Column, cellVal); }
public object GetCellValue(AkbExcelCellCoordinate coor) { return(GetValue(coor.Row, coor.Column)); }
public void SetCellValue(AkbExcelCellCoordinate coor, object val) { SetValue(coor.Row, coor.Column, val); }
private void btnExport_Click(object sender, EventArgs e) { if (dgvMeasure.SelectedRows.Count == 1) { var targetPath = string.Empty; // Get value for export //var dtStart = new DateTime(dtpStart.Value.Year, dtpStart.Value.Month, dtpStart.Value.Day, 0, 0, 0); //var dtEnd = new DateTime(dtpEnd.Value.Year, dtpEnd.Value.Month, dtpEnd.Value.Day, 23, 59, 59); var strSDate = dgvMeasure.CurrentRow.Cells[StartTime.Index].Value.ToString(); var strEDate = dgvMeasure.CurrentRow.Cells[EndTime.Index].Value.ToString(); int intUser = Common.CnvNullToInt(cmbUser.SelectedValue); int intType = Common.CnvNullToInt(((dynamic)cmbType.SelectedItem).Value); int intResult = Common.CnvNullToInt(((dynamic)cmbResult.SelectedItem).Value); var intMeasureId = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[MeasureID.Index].Value); var strDeviceName = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[DeviceName.Index].Value); var intMeasureType = Common.CnvNullToInt(dgvMeasure.CurrentRow.Cells[MeasureType.Index].Value); var strType = string.Empty; if (intMeasureType == (int)clsDBUltity.emMeasureType.AlarmTest) { strType = "Alarm Test"; } else if (intMeasureType == (int)clsDBUltity.emMeasureType.WalkingTest) { strType = "Walking Test"; } var strResult = Common.CnvNullToString(Common.MeasureResultDisplay(Common.CnvNullToInt(dgvMeasure.CurrentRow.Cells[Result.Index].Value))); var strUser = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[User.Index].Value); // Export excel var exportExcel = new AkbExcel(); TempPath = (intMeasureType == 0) ? PathBase + SOURCE_PATH + File_Name : PathBase + SOURCE_PATH + File_Name_Walking; TargetPath = PathBase + SOURCE_PATH; if (!File.Exists(TempPath)) { Common.ShowMsg(MessageBoxIcon.Warning, "Not excel template"); return; } var flag = false; try { if (intMeasureType == 0) { var strAlarmValue = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[AlarmValue.Index].Value); string dtNow = DateTime.Now.ToString(clsDBUltity.cstrDateTimeFormatNoMiliSecond); var sourcePath = TargetPath + "Report.xlsx"; targetPath = ValidateExportExcel(sourcePath, DateTime.Now.ToString("yyyyMMddHHmmss") + "_Report"); if (string.IsNullOrEmpty(targetPath)) { return; } exportExcel.OpenExcel(targetPath, false, false, sourcePath); using (var objDb = new clsDBUltity()) { // Binding grid measure detail var dataExport = objDb.GetTBLMeasureDetail(intMeasureId, chkView.Checked); if (Common.TableIsNullOrEmpty(dataExport)) { return; } var cellTimeExport = new AkbExcelCellCoordinate(3, 5); //Time export var cellMeasureID = new AkbExcelCellCoordinate(4, 3); //Measure ID var cellStart = new AkbExcelCellCoordinate(5, 3); //Start var cellEnd = new AkbExcelCellCoordinate(6, 3); //End var cellAlarmValue = new AkbExcelCellCoordinate(7, 3); //Alarm Value var cellUser = new AkbExcelCellCoordinate(4, 5); //Alarm Value var cellType = new AkbExcelCellCoordinate(5, 5); //Type var cellResult = new AkbExcelCellCoordinate(6, 5); //Result var cellDeviceName = new AkbExcelCellCoordinate(7, 5); //Device Name var cellStartBindingGrid = new AkbExcelCellCoordinate(11, 2); //Starting row to binding data exportExcel.SetCellValue(cellTimeExport, dtNow); exportExcel.SetCellValue(cellMeasureID, intMeasureId); exportExcel.SetCellValue(cellStart, strSDate); exportExcel.SetCellValue(cellEnd, strEDate); exportExcel.SetCellValue(cellAlarmValue, strAlarmValue); exportExcel.SetCellValue(cellUser, strUser); exportExcel.SetCellValue(cellType, strType); exportExcel.SetCellValue(cellResult, strResult); exportExcel.SetCellValue(cellDeviceName, strDeviceName); var objData = new object[dataExport.Rows.Count, 4]; int i = 0; var no = 0; foreach (DataRow item in dataExport.Rows) { objData[i, 0] = Common.CnvNullToString(++no); objData[i, 1] = Common.CnvNullToString(item["samples_time"]); objData[i, 2] = Common.CnvNullToString(item["actual_delegate"]); objData[i, 3] = Common.CnvNullToString(Common.MeasureResultDisplay(Common.CnvNullToInt(item["result"]))); i++; } string strRange = exportExcel.SetRangeValue(cellStartBindingGrid, objData); exportExcel.SetBorderAllRange(strRange, Color.Black); if (!exportExcel.Save()) { Common.ShowMsg(MessageBoxIcon.Warning, "Error exporting excel data"); return; } } } else { var strFailLevel = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[FailLevel.Index].Value); var strPeriod = Common.CnvNullToString(dgvMeasure.CurrentRow.Cells[colPeriod.Index].Value); string dtNow = DateTime.Now.ToString(clsDBUltity.cstrDateTimeFormatMiliSecond); var sourcePath = TargetPath + "ReportWalking.xlsx"; targetPath = ValidateExportExcel(sourcePath, DateTime.Now.ToString("yyyyMMddHHmmss") + "_ReportWalking"); if (string.IsNullOrEmpty(targetPath)) { return; } exportExcel.OpenExcel(targetPath, false, false, sourcePath); using (var objDb = new clsDBUltity()) { // Get value measure detail limit var dataLimit = objDb.GetTBLMeasureDetailLimit(intMeasureId); // Get value measure detail var dataExport = objDb.GetTBLMeasureDetail(intMeasureId, chkView.Checked); if (Common.TableIsNullOrEmpty(dataLimit) && Common.TableIsNullOrEmpty(dataExport)) { return; } var cellTimeExport = new AkbExcelCellCoordinate(3, 5); //Time export var cellMeasureID = new AkbExcelCellCoordinate(4, 3); //Measure ID var cellStart = new AkbExcelCellCoordinate(5, 3); //Start var cellEnd = new AkbExcelCellCoordinate(6, 3); //End var cellAlarmValue = new AkbExcelCellCoordinate(7, 3); //Alarm Value var cellUser = new AkbExcelCellCoordinate(4, 5); //Alarm Value var cellType = new AkbExcelCellCoordinate(5, 5); //Type var cellResult = new AkbExcelCellCoordinate(6, 5); //Result var cellDeviceName = new AkbExcelCellCoordinate(7, 5); //Device Name var cellFailLevel = new AkbExcelCellCoordinate(8, 3); //Fail Level var cellStartBindingLimit = new AkbExcelCellCoordinate(12, 2); //Fail Level var cellStartBindingGrid = new AkbExcelCellCoordinate(20, 2); //Starting row to binding data exportExcel.SetCellValue(cellTimeExport, dtNow); exportExcel.SetCellValue(cellMeasureID, intMeasureId); exportExcel.SetCellValue(cellStart, strSDate); exportExcel.SetCellValue(cellEnd, strEDate); exportExcel.SetCellValue(cellAlarmValue, strPeriod); exportExcel.SetCellValue(cellUser, strUser); exportExcel.SetCellValue(cellType, strType); exportExcel.SetCellValue(cellResult, strResult); exportExcel.SetCellValue(cellFailLevel, strFailLevel); exportExcel.SetCellValue(cellDeviceName, strDeviceName); var objLimit = new object[dataLimit.Rows.Count, 3]; var j = 0; var num = 0; foreach (DataRow item in dataLimit.Rows) { DateTime samplesTime; objLimit[j, 0] = Common.CnvNullToString(++num); if (DateTime.TryParse(Common.CnvNullToString(item["samples_time"]), out samplesTime)) { objLimit[j, 1] = ((DateTime)item["samples_time"]).ToString(clsDBUltity.cstrDateTimeFormatMiliSecond); } else { objLimit[j, 1] = string.Empty; } objLimit[j, 2] = Common.CnvNullToString(item["actual_delegate"]); j++; } string strRangeLimit = exportExcel.SetRangeValue(cellStartBindingLimit, objLimit); exportExcel.SetBorderAllRange(strRangeLimit, Color.Black); var objData = new object[dataExport.Rows.Count, 3]; int i = 0; var no = 0; foreach (DataRow item in dataExport.Rows) { DateTime samplesTime; objData[i, 0] = Common.CnvNullToString(++no); if (DateTime.TryParse(Common.CnvNullToString(item["samples_time"]), out samplesTime)) { objData[i, 1] = ((DateTime)item["samples_time"]).ToString(clsDBUltity.cstrDateTimeFormatMiliSecond); } else { objData[i, 1] = string.Empty; } objData[i, 2] = Common.CnvNullToString(item["actual_value"]); i++; } string strRange = exportExcel.SetRangeValue(cellStartBindingGrid, objData); exportExcel.SetBorderAllRange(strRange, Color.Black); if (!exportExcel.Save()) { Common.ShowMsg(MessageBoxIcon.Warning, "Error exporting excel data"); return; } } } flag = true; } catch (Exception ex) { } finally { if (exportExcel != null) { exportExcel.CloseExcel(); } exportExcel = null; // Open file saved if (flag && Common.ComfirmMsg("Do you want open file?") == true) { if (File.Exists(targetPath)) { System.Diagnostics.Process.Start(targetPath); } else { Common.ShowMsg(MessageBoxIcon.Error, "File export not found"); } } } } else { Common.ShowMsg(MessageBoxIcon.Warning, "Please select 1 row data to export excel"); return; } }