/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cbPN.Text.Trim()))
{
MessageBox.Show("您未选中成衣名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//加载图片
cbPN_SelectedIndexChanged(sender, e);
if (CmdHelper.ifExistsTheProcessByName("EXCEL") || CmdHelper.ifExistsTheProcessByName("et"))
{
Tools.FrmPrompt frmCloseExcel = new Tools.FrmPrompt("excel.exe|et.exe");
frmCloseExcel.ShowDialog();
}
//导出到Excel中
string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\成本汇总";
string _fileName = cbPN.Text.Trim() + "_成本汇总.xls";
xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("成衣汇总：", "*.xls|*.xls", _defaultDir, _fileName);
if (!xlsFilePath.Contains(@"\"))
{
return;
}
//将图片存于粘贴板中。
Clipboard.SetImage(this.pictureBox.Image);
System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource;
string PN = dt.Rows[0]["成衣名称"].ToString();
ExcelHelper.saveDtToExcelWithProgressBar((System.Data.DataTable)dgv.DataSource, xlsFilePath, pb);
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
//获取第二个Sheet.
myExcel.App.Visible = false;
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
int maxRowCount = wS.UsedRange.Rows.Count;
uEHelper.insertAboveTheSpecificRow(1);
uEHelper.setSpecificCellValue("A1", PN);
Range range = (wS.Range)[wS.Cells[2, 2], wS.Cells[maxRowCount, 2]];
uEHelper.delTheSpecificContentShiftToLeft(range);
myExcel.App.DisplayAlerts = false;
uEHelper.merge("A1", "G1");
uEHelper.MergeTheSpecificColumnWithoutBlankContent(2);
//保存图片到I1.
Range rangeI1 = uEHelper.getRange("I1", "I1");
/*
* ActiveSheet.Range("A1").PasteSpecial(
* Excel.Enums.XlPasteType.xlPasteAll,
* Excel.Enums.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
* false, false);
*/
rangeI1.Select();
wS.PasteSpecial();
//sheet2中写入文件.
IShowProductsCostSummary iShowProductsCostSummary = new ShowProductsCostSummaryImpl();
dt = iShowProductsCostSummary.get_P_C_Each_Port(cbPN.Text.Trim());
//序号, 成衣名称, 概要工序, 指定工序, 指定每部位工序, 工时, 件数, 单价, 最终单价.
/*
* seq_p_c_record,
* product_name,
* summary_process,
* specific_process,
* specific_each_process,
* man_hours,
* amount,
* each_cost,
* final_labour_cost
*/
//第二个表格.
myExcel.AddSheetToLastIndex("每部件成本汇总");
Worksheet secondWS = myExcel.getSecondWorksheetAfterOpen();
//先写标题。
secondWS.Cells[1, 1] = "序号";
secondWS.Cells[1, 2] = "成衣名称";
secondWS.Cells[1, 3] = "部位";
secondWS.Cells[1, 4] = "工序";
secondWS.Cells[1, 5] = "单部件";
secondWS.Cells[1, 6] = "单部件_件数";
//secondWS.Cells[1, 6] = "工时";
secondWS.Cells[1, 7] = "单部件_工价";
//secondWS.Cells[1, 7] = "件数";
secondWS.Cells[1, 8] = "整部位_工时";
//secondWS.Cells[1, 8] = "单价";
secondWS.Cells[1, 9] = "整部位_工价";
secondWS.Cells[1, 10] = "最终_工价";
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
secondWS.Cells[2 + i, 1] = dt.Rows[i]["seq_p_c_record"].ToString();
secondWS.Cells[2 + i, 2] = dt.Rows[i]["product_name"].ToString();
secondWS.Cells[2 + i, 3] = dt.Rows[i]["summary_process"].ToString();
secondWS.Cells[2 + i, 4] = dt.Rows[i]["specific_process"].ToString();
secondWS.Cells[2 + i, 5] = dt.Rows[i]["specific_each_process"].ToString();
secondWS.Cells[2 + i, 6] = dt.Rows[i]["amount"].ToString();
secondWS.Cells[2 + i, 7] = dt.Rows[i]["each_cost"].ToString();
secondWS.Cells[2 + i, 8] = dt.Rows[i]["man_hours"].ToString();
secondWS.Cells[2 + i, 9] = dt.Rows[i]["labour_cost"].ToString();
secondWS.Cells[2 + i, 10] = dt.Rows[i]["final_labour_cost"].ToString();
}
//获取最终单价的区域.
Range _range_final_labour_cost;
uEHelper = new Usual_Excel_Helper(secondWS);
_range_final_labour_cost = uEHelper.getRange("J2", "J" + secondWS.UsedRange.Rows.Count);
uEHelper.setFormulaR1C1ForRange(_range_final_labour_cost, "=IF(OR(ISBLANK(RC[-4]),ISBLANK(RC[-3])),RC[-1],RC[-4]*RC[-3])");
secondWS.UsedRange.EntireColumn.AutoFit();
myExcel.save();
myExcel.close();
//((FrmMainOfProductsCost)this.ParentForm).notifyIcon.ShowBalloonTip(7000, "提示：","汇总保存于: " + xlsFilePath, ToolTipIcon.Info);
ShowResult.show(lblResult, "汇总保存于: " + xlsFilePath, true);
timerRestoreLblResult.Start();
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
OracleHelper oH = OracleHelper.getBaseDao();
if (string.IsNullOrEmpty(cbPN.Text.Trim()))
{
return;
}
if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
{
return;
}
//获取该成品对应的工序。
System.Data.DataTable dt = null;
OracleParameter paramPN = new OracleParameter("v_PN", OracleDbType.NVarchar2, 20);
OracleParameter paramCur = new OracleParameter("retureCur", OracleDbType.RefCursor);
paramPN.Direction = ParameterDirection.Input;
paramCur.Direction = ParameterDirection.ReturnValue;
paramPN.Value = cbPN.Text.Trim();
OracleParameter[] parameters = new OracleParameter[2] {
paramCur, paramPN
};
string procName = "Show_Products_Cost.get_P_C_Info_To_Piecework";
dt = oH.getDT(procName, parameters);
_fileName = cbPN.Text.Trim() + ".xls";
DirectoryHelper.createDirecotry(_defaultDir);
xlsFilePath = _defaultDir + _fileName;
//获取一个Excel
V_New_Excel v_Excel = new V_New_Excel(xlsFilePath);
Worksheet wS = v_Excel.WS;
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
uEHelper.setSpecificCellValue(1, 1, cbPN.Text.Trim() + "报工单", 11, true);
//写序号标题
uEHelper.setSpecificCellValue(2, 3, "姓名");
uEHelper.setSpecificCellValue(3, 1, "序号");
uEHelper.setSpecificCellValue(3, 2, "部位");
uEHelper.setSpecificCellValue(3, 3, "工序");
//姓名占据两列
//uEHelper.merge("C2", "D2");
//uEHelper.merge("C3","D3");
//Range range = wS.Range[wS.Cells[2, 3], wS.Cells[2, 4]];
//uEHelper.textAlighRight(range);
//工序名称，单据时间靠右
//range = wS.Range[wS.Cells[3, 4], wS.Cells[3, 5]];
//uEHelper.textAlighRight(range);
//设置第三列宽度
uEHelper.setColumnWidth("C", 42.25M);
uEHelper.setColumnWidth("A", 5.13M);
uEHelper.setRowHeight(1, 31);
uEHelper.setRowHeight(2, 31);
pb.Visible = true;
pb.Value = 0;
pb.Maximum = dt.Rows.Count;
lblResult.Visible = false;
lblPrompt.Visible = true;
lblPrompt.Text = "请等待！";
//开始写信息
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
uEHelper.setSpecificCellValue(i + 4, 1, dt.Rows[i]["row_num"].ToString());
uEHelper.setSpecificCellValue(i + 4, 2, dt.Rows[i]["Summary_Process"].ToString());
uEHelper.setSpecificCellValue(i + 4, 3, dt.Rows[i]["specific_Process"].ToString());
pb.Value++;
}
pb.Visible = false;
pb.Value = 0;
lblResult.Visible = true;
lblPrompt.Visible = false;
lblPrompt.Text = "";
for (int columnIndex = 4; columnIndex <= 30; columnIndex++)
{
uEHelper.setColumnWidthByColumnIndex(columnIndex, 4.5M);
}
Range range = wS.Range[wS.Cells[2 + dt.Rows.Count, 30], wS.Cells[2 + dt.Rows.Count, 30]];
uEHelper.setAllTheBoxLine(range);
uEHelper.merge("A1", "AD1");
v_Excel.displayAlert();
uEHelper.MergeTheSpecificColumnWithoutBlankContent(2);
//设置边框。
uEHelper.setAllTheBoxLine();
v_Excel.saveWithoutAutoFit();
v_Excel.closeTheApp();
ShowResult.show(lblResult, cbPN.Text.Trim() + " 产量统计模板保存于: " + xlsFilePath, true);
timerRestoreLblResult.Start();
}