private void btnExportExecl_Click(object sender, EventArgs e)
{
if (grvData.FocusedRowHandle < 0)
{
return;
}
string path = "";
FolderBrowserDialog f = new FolderBrowserDialog();
if (f.ShowDialog() == DialogResult.OK)
{
path = f.SelectedPath;
}
else
{
return;
}
string _pPath = Application.StartupPath + "\\ResultCheckTemplate.xls";
string orderCode = Lib.ToString(grvData.GetFocusedRowCellValue(colOrderCode));
string productCode = Lib.ToString(grvData.GetFocusedRowCellValue(colPID));
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo file kết quả kiểm tra!"))
{
DataSet ds = LibIE.GetListDataFromSP("spGetProductWorkingInfo_ByOrder", new string[] { "@OrderCode", "@ProductCode" }, new object[] { orderCode, productCode });
// if (ds.Tables.Count <= 1) return;
DataTable dtProduct = ds.Tables[0];
if (ds.Tables.Count <= 1)
{
return;
}
DataTable dt = ds.Tables[1];
DataRow r1 = dt.NewRow();
dt.Rows.InsertAt(r1, 0);
int count = dt.Rows.Count;
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
string fileNameHSTK = "KetQuaKiemTra_" + orderCode + "_" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xls";
try
{
File.Copy(_pPath, path + "\\" + fileNameHSTK, true);
app = new Excel.Application();
app.Workbooks.Open(path + "\\" + fileNameHSTK);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
app.DisplayAlerts = false;
string congDoan = "";
int position = 0;
//workSheet.Cells[2, 3] = "";
workSheet.Cells[3, 2] = Lib.ToString(dtProduct.Rows[0]["ProductName"]); //tên
workSheet.Cells[5, 6] = Lib.ToString(dtProduct.Rows[0]["MotorCode"]); //mã motor
workSheet.Cells[6, 5] = Lib.ToString(dtProduct.Rows[0]["LoaiMo"]); //loại mỡ
workSheet.Cells[6, 8] = Lib.ToString(dtProduct.Rows[0]["LuongMo"]); //lượng mỡ
workSheet.Cells[2, 7] = grvData.GetFocusedRowCellDisplayText(colDateLR); //ngày lắp ráp
workSheet.Cells[7, 7] = orderCode + "-1 " + productCode;
workSheet.Cells[9, 10] = dt.Columns.Contains("1") ? orderCode + "-1" : "";
workSheet.Cells[9, 11] = dt.Columns.Contains("2") ? orderCode + "-2" : "";
workSheet.Cells[9, 12] = dt.Columns.Contains("3") ? orderCode + "-3" : "";
workSheet.Cells[9, 13] = dt.Columns.Contains("4") ? orderCode + "-4" : "";
workSheet.Cells[9, 14] = dt.Columns.Contains("5") ? orderCode + "-5" : "";
workSheet.Cells[9, 15] = dt.Columns.Contains("6") ? orderCode + "-6" : "";
for (int i = count - 1; i >= 0; i--)
{
DataRow r = dt.Rows[i];
string maCongDoan = Lib.ToString(r["ProductStepCode"]);
if (congDoan != maCongDoan)
{
((Excel.Range)workSheet.Rows[11]).Insert();
((Excel.Range)workSheet.Rows[11]).Insert();
workSheet.get_Range("A10", "Q10").Copy(workSheet.get_Range("A11", "Q11"));
if (position > 1)
{
workSheet.get_Range("A14", "A" + (13 + position)).Merge(false);
workSheet.get_Range("B14", "B" + (13 + position)).Merge(false);
}
position = 0;
congDoan = maCongDoan;
workSheet.Cells[12, 1] = maCongDoan;
workSheet.Cells[12, 2] = "";
workSheet.Cells[12, 3] = "";
workSheet.Cells[12, 8] = "";
workSheet.Cells[12, 9] = "Đánh giá";
workSheet.Cells[12, 10] = dt.Columns.Contains("StatusResult1") ? Lib.ToString(r["StatusResult1"]) : "";
workSheet.Cells[12, 11] = dt.Columns.Contains("StatusResult2") ? Lib.ToString(r["StatusResult2"]) : "";
workSheet.Cells[12, 12] = dt.Columns.Contains("StatusResult3") ? Lib.ToString(r["StatusResult3"]) : "";
workSheet.Cells[12, 13] = dt.Columns.Contains("StatusResult4") ? Lib.ToString(r["StatusResult4"]) : "";
workSheet.Cells[12, 14] = dt.Columns.Contains("StatusResult5") ? Lib.ToString(r["StatusResult5"]) : "";
workSheet.Cells[12, 15] = dt.Columns.Contains("StatusResult6") ? Lib.ToString(r["StatusResult6"]) : "";
workSheet.get_Range("C12", "I12").Merge(false);
workSheet.get_Range("A12", "Q12").Font.Size = 15;
workSheet.get_Range("A12", "Q12").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
position++;
}
((Excel.Range)workSheet.Rows[11]).Insert();
workSheet.get_Range("A10", "Q10").Copy(workSheet.get_Range("A11", "Q11"));
workSheet.Cells[12, 1] = maCongDoan;
workSheet.Cells[12, 2] = Lib.ToString(r["ProductStepName"]);
workSheet.Cells[12, 3] = Lib.ToString(r["WorkingName"]);
workSheet.Cells[12, 8] = Lib.ToString(r["PeriodValue"]);
workSheet.Cells[12, 9] = Lib.ToString(r["ValueTypeName"]);
workSheet.Cells[12, 10] = dt.Columns.Contains("1") ? Lib.ToString(r["1"]) : "";
workSheet.Cells[12, 11] = dt.Columns.Contains("2") ? Lib.ToString(r["2"]) : "";
workSheet.Cells[12, 12] = dt.Columns.Contains("3") ? Lib.ToString(r["3"]) : "";
workSheet.Cells[12, 13] = dt.Columns.Contains("4") ? Lib.ToString(r["4"]) : "";
workSheet.Cells[12, 14] = dt.Columns.Contains("5") ? Lib.ToString(r["5"]) : "";
workSheet.Cells[12, 15] = dt.Columns.Contains("6") ? Lib.ToString(r["6"]) : "";
position++;
}
for (int i = 0; i < 5; i++)
{
((Excel.Range)workSheet.Rows[10]).Delete();
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: " + ex.Message);
}
finally
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
Process.Start(path + "\\" + fileNameHSTK);
}
}
}