/// <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 ExportToExceltoolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRowCollection drRows = this.dgv.Rows;
if (drRows.Count == 0)
{
return;
}
//1. 先确定 image path
if (CmdHelper.ifExistsTheProcessByName("excel") || CmdHelper.ifExistsTheProcessByName("et"))
{
Tools.FrmPrompt frmPrompt = new Tools.FrmPrompt("excel.exe|et.exe");
frmPrompt.ShowDialog();
}
//2. 打开Excel
//先获取时间字符串
string currTimeStr = TimeHelper.getCurrentTimeStr();
string xlsFileName = "成衣成本汇总_" + currTimeStr + ".xls";
string srcFilePath = Application.StartupPath + "\\成本汇总\\ProductsCostSummaryTemplate.xls";
string destDir = Application.StartupPath + "\\成本汇总\\";
string destFileName = xlsFileName;
CmdHelper.copyFileToDestDirWithNewFileName(srcFilePath, destDir, destFileName);
//目的文件名为：
destFilePath = destDir + xlsFileName;
MyExcel myExcel = new MyExcel(destFilePath);
myExcel.open(true);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(myExcel.getFirstWorkSheetAfterOpen());
for (int index = 0; index <= drRows.Count - 1; index++)
{
DataGridViewRow currRow = drRows[index];
string product_name = currRow.Cells["Product_Name"].Value.ToString();
uEHelper.setSpecificCellValue("A" + (2 + index).ToString(), product_name);
//(drRow.Cells["product_name"]
uEHelper.setSpecificCellValue("B" + (2 + index).ToString(), currRow.Cells["total_man_hours"].Value.ToString());
uEHelper.setSpecificCellValue("C" + (2 + index).ToString(), currRow.Cells["total_labour_cost"].Value.ToString());
uEHelper.setSpecificCellValue("D" + (2 + index).ToString(), currRow.Cells["supplier"].Value.ToString());
uEHelper.setSpecificCellValue("E" + (2 + index).ToString(), currRow.Cells["latest_update_time"].Value.ToString());
//picture
//保存此图片
//Image image = ((Image)(currRow.Cells["picture"].Value));
byte[] pictureByteArray = (byte[])currRow.Cells["picture"].Value;
Image image = PictureHelper.ReturnPhoto(pictureByteArray);
string picPath = string.Format(@"{0}\{1}.jpg", destDir, product_name);
image.Save(picPath, image.RawFormat);
Microsoft.Office.Interop.Excel.Range range = uEHelper.getRange("F" + (2 + index).ToString(), "F" + (2 + index).ToString());
uEHelper.pastePicture(range, picPath);
}
myExcel.saveWithoutAutoFit();
myExcel.close();
ShowResult.show(lblResult, string.Format(@"导出完毕，存于：{0}", destFilePath), true);
timerRestoreLabel.Start();
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Of_First_Sheet()
{
//确认关闭已经打开的Excel
if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
{
Tools.FrmPrompt frmCloseExcel = new Tools.FrmPrompt("excel.exe|et.exe");
frmCloseExcel.ShowDialog();
}
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录：", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
MSG msg = null;
//非成本文件，则忽略。
if (!(msg = isProductsCostRecord(firstWS)).Flag)
{
lblPromptForPB.Visible = false;
ShowResult.show(lblResult, msg.Msg, false);
myExcel.close();
return;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
myExcel.close();
return;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
myExcel.close();
return;
}
//判断是否存在该成品的记录
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
//最大行，取
//从第二行开始，取第三列连续出现工序的最大行
int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(2, 3);
//int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
string seq_p_c_record = string.Empty;
string summary_process = string.Empty;
string specific_process = string.Empty;
string man_hours = string.Empty;
string labour_cost = string.Empty;
pb.Maximum = rowMaxIndex - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
//先删除Check_Products_Cost_Doc中的所有记录。
CheckProductsCostDoc.deleteAllRecord();
//检查工时数据，成本数据是否正确。
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
//判断man_hours是否为decimal;
decimal _man_hours_d;
decimal _labour_cost_d;
if (!decimal.TryParse(man_hours, out _man_hours_d))
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"第{0}行第4列，非工时数据,请检查！", rowIndex), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
if (!decimal.TryParse(labour_cost, out _labour_cost_d))
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"第{0}行第5列，非单价数据，请检查！", rowIndex), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
CheckProductsCostDoc checkProductsCostDoc = new CheckProductsCostDoc(rowIndex, summary_process, specific_process);
msg = checkProductsCostDoc.ifExistsSameProcess();
if (msg.Flag)
{
lblPromptForPB.Visible = false;
pb.Visible = false;
ShowResult.show(lblResult, string.Format(@"序号:{0}行与{1}，工序相同，请检查。", seq_p_c_record, msg.Msg), false);
timerRestoreLblResult.Enabled = true;
myExcel.close();
return;
}
checkProductsCostDoc.addProcess();
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 检查中: ";
pb.Value++;
}
pb.Value = 0;
//保存工序数据到后台。
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
return;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 导入中: ";
pb.Value++;
continue;
}
tran.Commit();
theConnForTransaction.Close();
pb.Visible = false;
lblPromptForPB.Visible = false;
lblPromptForPB.Text = "";
lblResult.Visible = true;
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
myExcel.close();
ShowResult.show(lblResult, "已经提交！", true);
timerRestoreLblResult.Enabled = true;
lblPromptForPB.Visible = false;
//默认提交 pictures下的 waiting.jpg图片。
//保存或更新其对应的图片。
if (!FrmProductsCostSummary.ifExistsRecordOfProducts_Picture(PN))
{
FrmProductsCostSummary.addPictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
}
else
{
FrmProductsCostSummary.updatePictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg");
}
}