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