/// <summary>
/// 这个方法，用来生成数据。
/// </summary>
/// <param name="wS"></param>
/// <param name="dtToPrint"></param>
public static void generate_data_to_print(Worksheet wS, System.Data.DataTable dtToPrint, ProgressBar pb)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
Range theAllRange = wS.UsedRange;
pb.Visible = true;
pb.Maximum = dtToPrint.Rows.Count * dtToPrint.Columns.Count;
pb.Value = 0;
//1.模板修正操作.
//依据列字段名来确定
//2.赋值操作.
for (int i = 0; i <= dtToPrint.Rows.Count - 1; i++)
{
//1.替换 Company
//2.先找出uEHelper中有没有Companyi
for (int j = 0; j <= dtToPrint.Columns.Count - 1; j++)
{
string columnName = dtToPrint.Columns[j].ColumnName;
string searchStr = "$" + columnName + (i + 1).ToString() + "$";
uEHelper.replace_Str_in_the_range(theAllRange, searchStr, dtToPrint.Rows[i][columnName].ToString());
pb.Value++;
}
}
pb.Visible = false;
pb.Maximum = 0;
pb.Value = 0;
}
/// <summary>
/// 检查第四行
/// </summary>
/// <returns></returns>
private bool check4thRow(List <String> excelPathList, out int maxColIndex)
{
maxColIndex = 0;
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int checkedColIndex = 0;
if (!AttendanceRHelper.isAllDigit(firstWS, 4, out checkedColIndex))
{
myExcel.close();
lblPrompt.Visible = false;
ShowResult.show(lblResult, fileNameWithoutSuffix + ": 第4行" + checkedColIndex.ToString() + "列非数字; 导入取消。", false);
timerRestoreTheLblResult.Start();
return(false);
}
if (maxColIndex == 0)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
maxColIndex = uEHelper.getMaxColIndexBeforeBlankCellInSepcificRow(4);
}
myExcel.close();
}
return(true);
}
/// <summary>
/// 判断Products_Cost 原始数据中,具体工序是否为空！
/// </summary>
/// <param name="xlsPath"></param>
/// <returns></returns>
public bool check_P_C_Template(string xlsPath, out string msg)
{
MyExcel myExcel = new MyExcel(xlsPath);
myExcel.open();
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
int rowMaxIndex = wS.UsedRange.Rows.Count;
int colMaxIndex = wS.UsedRange.Columns.Count;
//具体 工序在第四列
string specificProcess = string.Empty;
for (int rowIndex = rowMaxIndex; rowIndex >= 1; rowIndex--)
{
specificProcess = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(specificProcess))
{
myExcel.close();
msg = "第" + rowIndex + "行，具体工序不能为空！";
return(false);
}
}
myExcel.close();
msg = "";
return(true);
}
/// <summary>
/// 写模板
/// </summary>
/// <returns></returns>
public MyExcel writeTemplate()
{
path = System.Windows.Forms.Application.StartupPath + "\\Template\\成衣报价.xls";
V_New_Excel v_New_Excel = new V_New_Excel(path);
Worksheet wS = v_New_Excel.WS;
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
uEHelper.setSpecificCellValue("A2", "序号", 16);
uEHelper.setSpecificCellValue("B2", "工序", 16);
uEHelper.setSpecificCellValue("F2", "序号", 16);
uEHelper.setSpecificCellValue("G2", "工序", 16);
uEHelper.merge("A1", "I1");
//设置列宽
uEHelper.setColumnWidth("A", 5.5M);
uEHelper.setColumnWidth("B", 32.75M);
uEHelper.setColumnWidth("C", 4.15M);
uEHelper.setColumnWidth("D", 5.38M);
uEHelper.setColumnWidth("E", 0.15M);
uEHelper.setColumnWidth("F", 5.5M);
uEHelper.setColumnWidth("G", 32.75M);
uEHelper.setColumnWidth("H", 4.15M);
uEHelper.setColumnWidth("I", 5.38M);
v_New_Excel.saveWithoutAutoFit();
v_New_Excel.closeTheApp();
return(new MyExcel(path));
}
/// <summary>
/// 获取第四行中，为天数的最大列索引
/// </summary>
/// <param name="wS"></param>
/// <returns></returns>
public static int getMaxColIndexOfThe4thRowOfAR(Worksheet wS)
{
Stack <int> sDate = new Stack <int>();
sDate.Push(0);
int aDate = 0;
int maxColIndex = wS.UsedRange.Columns.Count;
for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string tempStr = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
if (string.IsNullOrEmpty(tempStr))
{
return(colIndex - 1);
}
aDate = int.Parse(tempStr);
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
return(colIndex - 1);
}
sDate.Push(aDate);
//取其中的最小值。
}
return(maxColIndex);
}
/// <summary>
/// 这个方法，用来确定模板。
/// </summary>
/// <param name="wS"></param>
/// <param name="dt"></param>
public static void defineExcelTemplate(Worksheet wS, List <TemplateProperty> templatePropertyList, ProgressBar pb)
{
//以下依据DataTable 来 确定 模板内容
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
pb.Visible = true;
pb.Maximum = templatePropertyList.Count;
pb.Value = 0;
//打开该文档.
for (int i = 0; i <= templatePropertyList.Count - 1; i++)
{
string property_name = templatePropertyList[i].Property_name;
decimal visible = templatePropertyList[i].Visible;
bool isVisible = (1 == visible) ? true : false;
if (isVisible)
{
pb.Value++;
continue;
}
//获取该属性对应的行号.
int[] rowIndexArray = uEHelper.getRowIndexArrayWhichContainContent(property_name);
for (int j = 0; j <= rowIndexArray.Length - 1; j++)
{
uEHelper.delTheSpecificRowShiftToUp(rowIndexArray[j] - j);
}
pb.Value++;
}
pb.Visible = false;
pb.Maximum = 0;
pb.Value = 0;
}
private void saveCriticalARInfo(List <string> xlsFilePathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
for (int i = 0; i <= xlsFilePathList.Count - 1; i++)
{
string excelPath = xlsFilePathList[i];
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.openWithoutAlerts();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string excelName = Usual_Excel_Helper.getExcelName(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
int pbMaximum = rowMaxIndex - 4;
int pbValue = 0;
//0: 表示 lblResult.Visible
checkNameBGWorker.ReportProgress(0, "lblResult.Visible");
checkNameBGWorker.ReportProgress(pbMaximum, "pb.Maximum");
checkNameBGWorker.ReportProgress(pbValue, "pb.Value");
//lblPrompt.Text = excelName + ": 基本信息采集中...";
checkNameBGWorker.ReportProgress(0, string.Format(@"lblPrompt.Text={0}: 姓名采集中...", excelName));
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
//偶数行为 时间。
if (0 == rowIndex % 2)
{
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(excelName.Substring(excelName.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
/// <summary>
/// 保存 ATTENDANCE_MACHINE_FLAG,
/// row_index,
/// job_number,
/// name
/// 至AR_Temp中
ar_Temp.saveRecord();
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
}
myExcel.close();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private MSG checkExistSameStaffsButDifferentAttendanceMachine(List <String> excelPathList)
{
MSG msg = new MSG();
List <SimpleARInfo> simpleARInfoList = new List <SimpleARInfo>();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间为空 的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
SimpleARInfo simpleARInfo = null;
string fileNameWithout = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
simpleARInfo = new SimpleARInfo();
simpleARInfo.AttendanceMachineFlag = int.Parse(fileNameWithout.Substring(fileNameWithout.Length - 1, 1));
simpleARInfo.Name = name;
simpleARInfo.RowIndex = rowIndex;
SimpleARInfo sARInfo = simpleARInfoList.Find(x => x.Name == simpleARInfo.Name);
if (sARInfo != null)
{
//存在重复的员工.
msg.Msg = string.Format(@"{0} 与 {1} 同名;同一人请汇总为一行,不同人,请姓名相异。", sARInfo.toString(), simpleARInfo.toString());
myExcel.close();
return(msg);
}
simpleARInfoList.Add(simpleARInfo);
}
myExcel.save();
myExcel.close();
}
msg.Flag = true;
msg.Msg = "未发现同名用户";
return(msg);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public MyExcel write_Template_For_Record_P_C()
{
path = System.Windows.Forms.Application.StartupPath + "\\模板\\成衣报价.xls";
string default_dir = System.Windows.Forms.Application.StartupPath + "\\模板";
DirectoryHelper.createDirecotry(default_dir);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql("select * from v_p_c_template");
ExcelHelper.saveDtToExcel(dt, path);
MyExcel myExcel = new MyExcel(path);
myExcel.open();
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
uEHelper.insertAboveTheSpecificRow(1);
uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
uEHelper.merge("A1", "E1");
uEHelper.setSpecificCellValue("A3", "1", 12, true);
myExcel.save();
myExcel.close();
return(myExcel);
/*V_New_Excel v_New_Excel = new V_New_Excel(path);
* Worksheet wS = v_New_Excel.WS;
* Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
* uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
* uEHelper.setSpecificCellValue("A2", "序号", 16);
* uEHelper.setSpecificCellValue("B2", "工序", 16);
* uEHelper.setSpecificCellValue("F2", "序号", 16);
* uEHelper.setSpecificCellValue("G2", "工序", 16);
* uEHelper.merge("A1", "I1");
* //设置列宽
* uEHelper.setColumnWidth("A", 5.5M);
* uEHelper.setColumnWidth("B", 32.75M);
* uEHelper.setColumnWidth("C", 4.15M);
* uEHelper.setColumnWidth("D", 5.38M);
* uEHelper.setColumnWidth("E", 0.15M);
* uEHelper.setColumnWidth("F", 5.5M);
* uEHelper.setColumnWidth("G", 32.75M);
* uEHelper.setColumnWidth("H", 4.15M);
* uEHelper.setColumnWidth("I", 5.38M);
*
* v_New_Excel.saveWithoutAutoFit();
* v_New_Excel.closeTheApp();
* return new MyExcel(path);
*/
}
private void saveCriticalARInfo(List <String> excelPathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
pb.Value = 0;
pb.Maximum = rowMaxIndex - 4;
pb.Visible = true;
lblPrompt.Visible = true;
lblResult.Visible = false;
lblPrompt.Text = fileNameWithoutSuffix + ": 基本信息采集中...";
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
pb.Value++;
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
ar_Temp.saveRecord();
pb.Value++;
}
lblPrompt.Visible = false;
pb.Visible = false;
myExcel.save();
myExcel.close();
}
}
/// <summary>
/// 保存一份文件到Prepared中。
/// </summary>
/// <param name="xlsFilePath"></param>
private void saveTheExcel(string xlsFilePath)
{
//获取该记录表所对应的月份。
MyExcel srcExceFile = new MyExcel(xlsFilePath);
srcExceFile.openWithoutAlerts();
Worksheet firstSheet_Src = srcExceFile.getFirstWorkSheetAfterOpen();
string year_and_month_src_str = new Usual_Excel_Helper(firstSheet_Src).getCellContentByRowAndColIndex(3, 3).Substring(0, 7);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
string attendanceMachineFlag = fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1);
year_and_month_src_str += string.Format(@"_{0}", attendanceMachineFlag);
srcExceFile.close();
//year_and_month_src_str 即为文件名
//string fileName = DirectoryHelper.getFileName(xlsFilePath);
//1.复制该excel 到 Prepared中。
Tools.CmdHelper.copyFileToDestDir(xlsFilePath, System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls");
string destFilePath = System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls";
//打开该文件
MyExcel myExcel = new MyExcel(destFilePath);
myExcel.openWithoutAlerts();
//新增一个sheet.
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uHelper = new Usual_Excel_Helper(firstSheet);
//获取月份.
string C3ContentStr = uHelper.getCellContentByRowAndColIndex(3, 3);
string year_and_month_str = C3ContentStr.Substring(0, 7);
Worksheet theLastestExcel = myExcel.AddSheetToLastIndex(year_and_month_str);
myExcel.copyRangeFromOneToAnotherSheet(firstSheet, theLastestExcel);
int sheetsCount = myExcel.getCountsOfAllSheet();
//删掉之前的表格,保留最后一个。
for (int i = 1; i <= sheetsCount - 1; i++)
{
myExcel.delTheSheet(1);
}
//一定要保存否则，无效。
myExcel.saveAndColumnsAutoFit();
//关闭该文件
myExcel.close();
}
/// <summary>
/// 判断某行是否都为数字。
/// </summary>
/// <param name="wS"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public static bool isAllDigit(Worksheet wS, int rowIndex, out int checkedColIndex)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
int maxColIndex = wS.UsedRange.Columns.Count;
bool flag = false;
int num = 0;
for (int colIndex = 1; colIndex <= maxColIndex; colIndex++)
{
string tempStr = uEHelper.getCellContentByRowAndColIndex(4, colIndex);
flag = int.TryParse(tempStr, out num);
if (!flag)
{
checkedColIndex = colIndex;
return(false);
}
}
checkedColIndex = maxColIndex;
return(true);
}
/// <summary>
/// 这个方法，用来确定模板。
/// </summary>
/// <param name="wS"></param>
/// <param name="dt"></param>
public static void defineExcelTemplate(Worksheet wS, List <TemplateProperty> templatePropertyList, ProgressBar pb)
{
//以下依据DataTable 来 确定 模板内容
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
pb.Visible = true;
pb.Maximum = templatePropertyList.Count;
pb.Value = 0;
//打开该文档.
//每删除一行。Excel 对应的行索引都要减少。
int j = 0;
for (int i = 0; i <= templatePropertyList.Count - 1; i++)
{
string property_name = templatePropertyList[i].Property_name;
string property_chinese_name = templatePropertyList[i].Property_chinese_name;
decimal visible = templatePropertyList[i].Visible;
bool isVisible = (1 == visible) ? true : false;
if (isVisible)
{
pb.Value++;
continue;
}
//若此属性不显示
//获取该属性对应的行号.
HashSet <int> hashSetRownIndex = uEHelper.getRowIndexArrayWhichContainContent(property_chinese_name);
foreach (int rowIndex in hashSetRownIndex)
{
//不显示的属性，进行删除。
uEHelper.delTheSpecificRowShiftToUp(rowIndex - j);
j++;
}
pb.Value++;
}
pb.Visible = false;
pb.Maximum = 0;
pb.Value = 0;
}
/// <summary>
///
/// </summary>
public static void clearSheet(Worksheet firstWS)
{
Queue <Range> rangeToDelQueue = new Queue <Range>();
int rowsMaxCount;
rowsMaxCount = firstWS.UsedRange.Rows.Count;
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
//获取最大列
int maxColIndex = getMaxColIndexOfThe4thRowOfAR(firstWS);
//判断是否有空行。
for (int i = 5; i <= rowsMaxCount; i++)
{
if (uEHelper.isBlankRangeTheSpecificRow(i, 1, maxColIndex))
{
//只要上一列不是
//删除掉此行。
//判断上一行中的A列是否为工号。
string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
if ("工号:".Equals(temp))
{
continue;
}
//获取该行。
Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
//不为工号
rangeToDelQueue.Enqueue(rangeToDel);
}
}
Range rangeToDelete;
//开始删除空行。
while (rangeToDelQueue.Count > 0)
{
rangeToDelete = rangeToDelQueue.Dequeue();
rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
}
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Template_previous()
{
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);
List <Worksheet> wSList = myExcel.getVisualWS();
int notProductsCostFile_Count = 0;
for (int i = 0; i <= wSList.Count - 1; i++)
{
Worksheet wS = wSList[i];
MSG msg = null;
//非成本文件，则忽略。
if (!(msg = isProductsCostRecord(wS)).Flag)
{
notProductsCostFile_Count++;
continue;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
//判断是否存在该成品的记录
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.getTheMaxRowIndexUntilBlankRow();
//int rowMaxIndex = wS.UsedRange.Rows.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();
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(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = wS.Index + "." + wS.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();
if (notProductsCostFile_Count == wSList.Count)
{
ShowResult.show(lblResult, " 共" + wSList.Count + "个表格均非成衣成本文件！依据：第3行4列和第3行5列均非数字！", false);
timerRestoreLblResult.Enabled = true;
lblPromptForPB.Visible = false;
}
}
/// <summary>
/// 将考勤记录导入数据库.
/// </summary>
/// <param name="xlsFilePath"></param>
/// <param name="randomStr"></param>
/// <param name="pb"></param>
/// <returns></returns>
public static MSG ImportAttendanceRecordToDB(string xlsFilePath, string randomStr, BackgroundWorker bgWork)
{
string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}，准备读取：", excelName));
int pbLength = 0;
bgWork.ReportProgress(pbLength, "pb.Maximum");
int pbValue = 0;
bgWork.ReportProgress(pbValue, "pb.Value");
MSG msg = new MSG();
//用于确定本月最后一天.
Stack <int> sDate = new Stack <int>();
//Queue<AttendanceR> qAttendanceR = new Queue<AttendanceR>();
Queue <AttendanceRecordDetail> qARDetail = new Queue <AttendanceRecordDetail>();
AttendanceRecordDetail._random_str = randomStr;
//按指纹日期
string fingerPrintDate = String.Empty;
//行最大值.
int rowsMaxCount = 0;
int colsMaxCount = 0;
Usual_Excel_Helper uEHelper = null;
MyExcel myExcel = new MyExcel(xlsFilePath);
//打开该文档。
myExcel.openWithoutAlerts();
//只获取第一个表格。
Worksheet ws = myExcel.getFirstWorkSheetAfterOpen();
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}，正在读取：", excelName));
AttendanceRecordDetail._file_path = xlsFilePath;
//行;列最大值 赋值.
rowsMaxCount = ws.UsedRange.Rows.Count;
colsMaxCount = ws.UsedRange.Columns.Count;
AttendanceRecordDetail._sheet_name = ws.Name;
//判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");
if (String.IsNullOrEmpty(A1Str))
{
msg.Msg = "工作表的A1单元格不能为空！";
msg.Flag = false;
myExcel.close();
return(msg);
}
//如果A1Str的内容不包含"考勤记录表"5个字。
if (!A1Str.Contains("考勤记录表"))
{
msg.Msg = "A1内容未包含'考勤记录表'";
msg.Flag = false;
myExcel.close();
return(msg);
}
#region 判断名称中是否区分了考勤记录。
string Seq_Attendance_Record = string.Empty;
int indexOfFullStop = xlsFilePath.LastIndexOf(".");
Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
{
msg.Msg = "考勤记录表名称请以数字结尾！";
msg.Flag = false;
myExcel.close();
return(msg);
}
#endregion
AttendanceRecordDetail._prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
// \0: 表空字符.
if (String.IsNullOrEmpty(C3Str))
{
msg.Msg = "异常: 考勤时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//
string[] ArrayC3 = C3Str.Split('~');
if (ArrayC3.Length == 0)
{
msg.Msg = "异常: 考勤时间格式变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
AttendanceRecordDetail._start_date = ArrayC3[0].ToString().Trim().Replace('/', '-');
AttendanceRecordDetail._end_date = ArrayC3[1].ToString().Trim().Replace('/', '-');
//制表时间: L3 3行12列.
string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim().Replace('/', '-');
if (String.IsNullOrEmpty(L3Str))
{
msg.Msg = "异常: 制表时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//制表时间.
AttendanceRecordDetail._tabulation_time = L3Str;
//检查第4行是否为;考勤时间:
string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
{
msg.Msg = "异常: 第四行已变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
uEHelper = new Usual_Excel_Helper(ws);
//此刻不能删除，只是获取行号。
Queue <Range> rangeToDelQueue = new Queue <Range>();
//判断是否有空行。
for (int i = 5; i <= rowsMaxCount; i++)
{
if (uEHelper.isBlankRow(i))
{
//只要上一列不是
//删除掉此行。
//判断上一行中的A列是否为工号。
string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
if ("工号:".Equals(temp))
{
//本行为空，上一行为工号行，则也统计。
continue;
}
//本行，为空，上一行非工号行。则删除本行。
Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
//不为工号
rangeToDelQueue.Enqueue(rangeToDel);
}
;
}
Range rangeToDelete;
//开始删除空行。
while (rangeToDelQueue.Count > 0)
{
rangeToDelete = rangeToDelQueue.Dequeue();
rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
}
;
rowsMaxCount = ws.UsedRange.Rows.Count;
//进度条长度增加。
pbLength += colsMaxCount;
pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1));
bgWork.ReportProgress(pbLength, "pb.Maximum");
//入队列值0
sDate.Push(0);
//显示进度条。
//考勤表中第4行，某月的最大考勤天数。
//lblPrompt.Text = excelName + "，正在读取：";
int actualMaxDay = 0;
//开始循环
for (int i = 1; i <= colsMaxCount; i++)
{
A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
//碰到第4行某列为空，退出循环。
if (String.IsNullOrEmpty(A4Str))
{
break;
}
int aDate = 0;
//对A4Str进行分析.
if (!Int32.TryParse(A4Str, out aDate))
{
msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
msg.Flag = false;
myExcel.close();
return(msg);
}
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
//跳出循环.
break;
}
actualMaxDay++;
sDate.Push(aDate);
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
//取其中的最小值。
colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
//考勤日期
fingerPrintDate = AttendanceRecordDetail._start_date.Substring(0, 7).Replace('/', '-');
string tempStr = string.Empty;
//开始循环
for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
{
//从第5行开始.
//奇数;偶数行共用一个对象.
AttendanceRecordDetail ARDetail = null;
//设定用于填充的对象
AttendanceRecordDetail._prefix_Job_Number = Seq_Attendance_Record.ToCharArray()[0];
for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
{
//如果行数为奇数则为工号行.
if (rowIndex % 2 == 1)
{
//工号行.
//取工号
ARDetail = new AttendanceRecordDetail();
ARDetail.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
//自行拼凑AR.
ARDetail.combine_Job_Number();
//取姓名: K5
ARDetail.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
//取部门: U5
ARDetail.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
//部门为空，则填充为NULL;
ARDetail.Dept = !String.IsNullOrEmpty(ARDetail.Dept) ? ARDetail.Dept : "NULL";
//取日期.填充0;
ARDetail.Fingerprint_date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
}
else
{
//偶数行取考勤结果.
//上班时间. 如B10;
tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
string tempFirstTime = String.Empty;
string tempLastTime = String.Empty;
List <string> strTimeList = null;
msg = getFPTimeReturnMSG(tempStr, out strTimeList);
if (!msg.Flag)
{
msg.Msg = string.Format(@"导入失败,提交数据尚未开始：第{0}行{1}列,{1}！", rowIndex, colIndex, msg.Msg);
myExcel.close();
return(msg);
}
;
//无打卡记录,不提交
if (strTimeList.Count == 0)
{
qARDetail.Enqueue(ARDetail);
}
//有打卡记录
for (int i = 0; i < strTimeList.Count; i++)
{
AttendanceRecordDetail ARDetailTemp = (AttendanceRecordDetail)CloneObject.Clone(ARDetail);
ARDetailTemp.Finger_print_time = ARDetailTemp.Fingerprint_date + " " + strTimeList[i].ToString();
qARDetail.Enqueue(ARDetailTemp);
}
}
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
}
//释放对象
myExcel.close();
System.Threading.Thread.Sleep(2000);
GC.Collect();
GC.WaitForPendingFinalizers();
//lblResult.Text = "";
bgWork.ReportProgress(0, "lblResult.Text = ''");
//lblPrompt.Text = "提交数据: ";
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}, 提交数据:", excelName));
//
bgWork.ReportProgress(qARDetail.Count, "pb.Maximum");
//*******/
pbValue = 0;
bgWork.ReportProgress(pbValue, "pb.Value");
#region
//OracleDaoHelper.noLogging("Attendance_Record");
OracleDaoHelper.noLogging("Attendance_Record_Detail");
OracleConnection conn = OracleConnHelper.getConn();
OracleTransaction tran = conn.BeginTransaction();
//保存对象
while (qARDetail.Count > 0)
{
try
{
AttendanceRecordDetail aRDetail = qARDetail.Dequeue();
affectedCount += aRDetail.saveBySpecificConn(conn);
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + "：导入失败; " + ex.ToString();
msg.Flag = false;
tran.Rollback();
conn.Close();
conn.Dispose();
return(msg);
throw;
}
}
tran.Commit();
conn.Close();
conn.Dispose();
#endregion
//OracleDaoHelper.logging("Attendance_Record");
OracleDaoHelper.logging("Attendance_Record_Detail");
msg.Flag = true;
msg.Msg = String.Format(@"导入完成;总计{0}条.", affectedCount.ToString());
return(msg);
}
/// <summary>
/// 导入成本记录。
/// </summary>
private void importProductsCostRecord()
{
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", 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 wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show("请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
sqlStr = string.Format(@"DELETE FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//判断是否存在该成品的记录
sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
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 rowStartIndex = 3;
int rowMaxIndex = wS.UsedRange.Rows.Count;
string summary_process = string.Empty;
string specific_process = string.Empty;
//summary_process不允许空开。
//先检查第1至4列.
pb.Visible = true;
lblResult.Visible = false;
pb.Maximum = 4 * (rowMaxIndex - rowStartIndex + 1);
pb.Value = 0;
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string man_hour = uEHelper.getSpecificCellValue("C" + rowIndex);
string temp = uEHelper.getSpecificCellValue("B" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//左边到头。
break;
}
if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
{
//为该要工序.
//开始保存
//则process为概要工序
summary_process = uEHelper.getSpecificCellValue("B" + rowIndex);
pb.Value++;
continue;
}
//具体工序时先保存概要工序。
sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
//再检查第6至第9列 F,G,H,I
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
string man_hour = uEHelper.getSpecificCellValue("H" + rowIndex);
string temp = uEHelper.getSpecificCellValue("G" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour))
{
//为该要工序.
//开始保存
//则process为概要工序
summary_process = uEHelper.getSpecificCellValue("G" + rowIndex);
pb.Value++;
continue;
}
//具体工序时先保存概要工序。
sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process)
values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
#region 开始保存 第1至第4列的数据。
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string temp = uEHelper.getSpecificCellValue("B" + rowIndex);
string man_hours = uEHelper.getSpecificCellValue("C" + rowIndex);
string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
{
//为该要工序.
//开始保存
//则temp为具体工序。
specific_process = temp;
sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost(
SEQ,
SEQ_P_C_Record,
Product_Name,
Summary_Process,
Specific_Process,
Man_Hours,
Labour_Cost,
Supplier,
UPDATE_Time
)
SELECT SEQ_PRODUCTS_COST.Nextval,
'{1}', --- Seq_P_C_Record
'{0}',
Summary_Process,
'{2}', --- specific_process
'{3}', --- man_hours
'{4}', --- labour_cost
'{5}', --- supplier
SYSDATE
FROM Products_Cost_Header
WHERE Product_Name = '{0}'
AND SEQ = '{1}'", PN,
seq_p_c_record,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name
);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
}
#endregion
#region 开始保存 第6至第9列的数据。
for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
string temp = uEHelper.getSpecificCellValue("G" + rowIndex);
string man_hours = uEHelper.getSpecificCellValue("H" + rowIndex);
string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(temp))
{
//右边到头
break;
}
if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours))
{
//为该要工序.
//开始保存
//则temp为具体工序。
specific_process = temp;
sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost(
SEQ,
SEQ_P_C_Record,
Product_Name,
Summary_Process,
Specific_Process,
Man_Hours,
Labour_Cost,
Supplier,
UPDATE_Time
)
SELECT SEQ_PRODUCTS_COST.Nextval,
'{1}', --- SEQ_P_C_Record
'{0}',
Summary_Process,
'{2}', --- specific_process
'{3}', --- man_hours
'{4}', --- labour_cost
'{5}', --- supplier
SYSDATE
FROM Products_Cost_Header
WHERE Product_Name = '{0}'
AND SEQ = '{1}'", PN,
seq_p_c_record,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
OracleDaoHelper.executeSQL(sqlStr);
pb.Value++;
}
}
#endregion
myExcel.close();
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
pb.Visible = false;
lblResult.Visible = true;
}
/// <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_Template()
{
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();
int notProductsCostFile_Count = 0;
//非成本文件，则忽略。
if (!isProductsCostRecordOfGroup1(firstWS))
{
notProductsCostFile_Count++;
lblPromptForPB.Visible = false;
ShowResult.show(lblResult, " 第1个表格非成衣成本文件！依据：第4行3列和第4行4列均非数字！", 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);
return;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
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 rowMaxIndexOfGroup1 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(1);
int rowMaxIndexOfGroup2 = 0;
//判断是否存在=
bool ifExistsGroup2 = isProductsCostRecordOfGroup2(firstWS);
if (ifExistsGroup2)
{
rowMaxIndexOfGroup2 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(6);
}
//获取第3行
//int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count;
pb.Maximum = rowMaxIndexOfGroup1 - 2 + rowMaxIndexOfGroup2 - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
string prepared_Summary_process = string.Empty;
//先读取第4列的内容。
for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup1; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
//序号为空跳出循环。
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
string content_B_Col = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
//1.读取C列内容，如果为空，说明是主工序。
string content_C_Col = uEHelper.getSpecificCellValue("C" + rowIndex);
if (string.IsNullOrEmpty(content_C_Col))
{
//执行下一次循环。
prepared_Summary_process = content_B_Col;
continue;
}
string specific_process = content_B_Col;
//2. C列不为空。
string man_hours = content_C_Col;
string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//插入该大类中的第一行数据。
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,
prepared_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;
}
//先读取第9列的内容。
for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup2; rowIndex++)
{
string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex);
//序号为空跳出循环。
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
string content_G_Col = uEHelper.getSpecificCellValue("G" + rowIndex).Trim();
//1.读取C列内容，如果为空，说明是主工序。
string content_H_Col = uEHelper.getSpecificCellValue("H" + rowIndex);
if (string.IsNullOrEmpty(content_H_Col))
{
//执行下一次循环。
prepared_Summary_process = content_G_Col;
continue;
}
string specific_process = content_G_Col;
//2. C列不为空。
string man_hours = content_H_Col;
string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex);
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//插入该大类中的第一行数据。
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,
prepared_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, " 第1个表格非成衣成本文件！依据：第3行4列和第3行5列均非数字！", false);
ShowResult.show(lblResult, " 导入完成！", true);
timerRestoreLblResult.Enabled = true;
}
/// <summary>
///
/// </summary>
private void separateOutputAR()
{
string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\考勤汇总";
killHwndOfXls();
Queue <int> prefix_Of_Staffs_Queue = V_AR_RESULT.get_Prefix_Staffs(YearAndMonthStr);
if (prefix_Of_Staffs_Queue.Count == 0)
{
ShowResult.show(lblResult, "尚未导入本月的考勤记录！", false);
timerRestoreTheLblResult.Enabled = true;
return;
}
string prefix_Of_Staffs = string.Empty;
//分几个工作表储存。
while (prefix_Of_Staffs_Queue.Count > 0)
{
prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString();
string _fileName = YearAndMonthStr + "_考勤汇总" + prefix_Of_Staffs.Substring(0, 1) + ".xls";
if (!xlsFilePath.Contains(":"))
{
//导出到Excel中
xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("考勤汇总：", "*.xls|*.xls", _defaultDir, _fileName);
if (!xlsFilePath.Contains(@"\"))
{
return;
}
}
else
{
int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr));
xlsFilePath = xlsFilePath.Remove(index) + _fileName;
}
//int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr));
//创建文件
//DirectoryHelper.createFile(xlsFilePath);
//xlsFilePath = xlsFilePath.Remove(index) + _fileName;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.create();
myExcel.openWithoutAlerts();
//追加Hwnd到队列中.
hwndOfXls_Queue.Enqueue(myExcel.HwndOfApp);
MSG msg = new MSG();
//prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString();
//依据前缀和月份获取列表。
//获取第一张表
Worksheet _firstWS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(_firstWS);
uEHelper.clearAllContents(_firstWS.UsedRange);
//清空该文档中的内容。
Worksheet _secondWS = myExcel.getSecondWorksheetAfterOpen();
uEHelper = new Usual_Excel_Helper(_secondWS);
uEHelper.clearAllContents(_secondWS.UsedRange);
Worksheet _thirdWS = myExcel.getThirdWorksheetAfterOpen();
uEHelper = new Usual_Excel_Helper(_thirdWS);
uEHelper.clearAllContents(_thirdWS.UsedRange);
int machine_no = int.Parse(prefix_Of_Staffs.Substring(0, 1));
//获取一个月内，某考勤机的考勤天数
int nums_of_ar_days = GetARSummary.GetARSummary.get_nums_of_ar_days(machine_no, YearAndMonthStr);
//考勤机的考勤天数
int nums_of_staffs = GetARSummary.GetARSummary.get_nums_of_staffs(machine_no, YearAndMonthStr);
System.Data.DataTable dt_Staff_Info = GetARSummary.GetARSummary.get_Staff_info(YearAndMonthStr, machine_no);
System.Data.DataTable dt_AR_Of_Each_Staff = GetARSummary.GetARSummary.get_AR_Of_Each_Staff(YearAndMonthStr, machine_no);
System.Data.DataTable dt_AR_Summary = GetARSummary.GetARSummary.Get_AR_Summary(YearAndMonthStr, machine_no);
//隐藏 结果 label;
lblResult.Visible = false;
lblPrompt.Visible = true;
lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 员工信息汇总中...";
ExcelHelper.saveDtToExcelWithProgressBar(dt_Staff_Info, _firstWS, pb);
lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 考勤记录汇总中...";
ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Of_Each_Staff, _secondWS, pb);
lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 汇总中...";
ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Summary, _thirdWS, pb);
Microsoft.Office.Interop.Excel.Range range_Src_AR;
//目标 区域
Microsoft.Office.Interop.Excel.Range range_desc_AR;
//AR_Time 在D列存放
Usual_Excel_Helper uEHelper_firstWS = new Usual_Excel_Helper(_firstWS);
for (int i = 1; i <= nums_of_staffs; i++)
{
range_Src_AR = ((Microsoft.Office.Interop.Excel.Range)_secondWS.Range[_secondWS.Cells[(i - 1) * (nums_of_ar_days) + 2, 4], _secondWS.Cells[i * nums_of_ar_days + 1]]);
range_Src_AR.Copy();
//第一张sheet，第4列
range_desc_AR = ((Microsoft.Office.Interop.Excel.Range)_firstWS.Cells[i + 1, 4]);
uEHelper_firstWS.pasteByTranspose(range_desc_AR);
}
//关闭excel
myExcel.save();
myExcel.close();
}
lblResult.Visible = true;
lblPrompt.Visible = false;
}
/// <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();
}
/// <summary>
/// 后台处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void readDataToDB_bgWorker_DoWork_Handler(object sender, DoWorkEventArgs e)
{
this.myExcel = new MyExcel(xlsFilePath);
myExcel.open(false);
this.percentProgress = 0;
//开始读取Excel中的内容。
//1. 产量记录从第三行，第六列开始。
//2. 产量记录到第几列结束了？
this._firstSheet = myExcel.getFirstWorkSheetAfterOpen();
this.uEHelper = new Usual_Excel_Helper(_firstSheet);
//获取最大列
this.validMaxColIndex = uEHelper.getColIndexOfSpecificContentInSpecificRow(2, 1, uEHelper.getMaxColIndex(), "终止标识") - 1;
//获取最大行从第三行起为序号行,当第1列，第一次出现为空的行时，即最大行。
this.validMaxRowIndex = uEHelper.getMaxRowIndexBeforeBlankCell(1);
//********定义局部变量******
string pn, report_year_month_str, lineName, reportTeamName;
//判断是否已经存在该线体的月报表
string contentOfA1 = uEHelper.getCellContentByRowAndColIndex(1, 1);
//1.判断产品名_组_月份格式是否正确。
bool flag = StringHelper.checkPN_Team_Month(contentOfA1, out pn, out report_year_month_str, out lineName, out reportTeamName);
if (!flag)
{
msg.Flag = false;
msg.Msg = "报表中A1单元格内容格式应为：产品名_衬衣1组_3月";
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
//2.判断线体，组名是否存在
msg = isValid_TheLineName_Or_TheTeamName(reportTeamName, lineName);
if (!msg.Flag)
{
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
//2.判断该组,在某月，某线体，所作的某产品 在数据库中是否已经有记录？
System.Data.DataTable dt = Line_Each_One_Quantities.getAllQuantitiesOfTheLine_team_pn_report(lineName, reportTeamName, pn, report_year_month_str);
if (dt.Rows.Count > 0)
{
msg.Flag = false;
msg.Msg = string.Format(@"{0}: 线体(地点):{1},组名：{2},月份: {3} 已经存在！", pn, lineName, reportTeamName, report_year_month_str);
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
//3.先检查工序列有没有工序为空的
msg = checkValidityOfAllProcesses();
if (!msg.Flag)
{
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
//4.检查金额列有无空或者为0.00的数值
msg = checkValidityOfAmountOfMoneyOfAllProcesses();
if (!msg.Flag)
{
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
List <NameAndRealTeam> nameAndRealTeamList = null;
//5. 检查所有的姓名是否符合格式
msg = checkValidityOfAllNameOfLine_Yields_Report(out nameAndRealTeamList);
if (!msg.Flag)
{
this.readDataToDB_bgWorker.ReportProgress(0, msg);
myExcel.close();
return;
}
List <Line_Each_One_Quantities> line_each_one_quantities_list = new List <Line_Each_One_Quantities>();
int maxmium = (validMaxRowIndex - 3 + 1) * (validMaxColIndex - 6 + 1);
int count = 0;
msg.Flag = true;
msg.Msg = "准备读取数据...";
readDataToDB_bgWorker.ReportProgress(0, msg);
//自第(3,6)开始
for (int currColIndex = 6; currColIndex <= validMaxColIndex; currColIndex++)
{
for (int currRowIndex = 3; currRowIndex <= validMaxRowIndex; currRowIndex++)
{
count++;
string contentStr = uEHelper.getCellContentByRowAndColIndex(currRowIndex, currColIndex);
//为空，定位到下一个单元格。
if (string.IsNullOrEmpty(contentStr))
{
readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
continue;
}
//判断是否为数字
int quantities = 0;
flag = int.TryParse(contentStr, out quantities);
//若不为数字
if (!flag)
{
msg.Flag = false;
msg.Msg = string.Format(@"第{0}行，第{1}列,应为整数!", currRowIndex, currColIndex);
readDataToDB_bgWorker.ReportProgress((count / maxmium) * 100, msg);
myExcel.close();
return;
}
//判断是否为整数
Line_Each_One_Quantities each_One_Quantities = new Line_Each_One_Quantities();
each_One_Quantities.Line_Name = lineName;
each_One_Quantities.Report_team_name = reportTeamName;
each_One_Quantities.Products_name = pn;
each_One_Quantities.Report_year_month_str = report_year_month_str;
each_One_Quantities.Quantities = quantities;
//第二列为部位
each_One_Quantities.Summary_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 2);
//第三列为工序
each_One_Quantities.Specific_process = uEHelper.getCellContentByRowAndColIndex(currRowIndex, 3);
each_One_Quantities.Man_hour = int.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 4));
each_One_Quantities.Amount_of_money = decimal.Parse(uEHelper.getCellContentByRowAndColIndex(currRowIndex, 5));
//记录姓名,姓名,自第6列开始。
string realTeamName = nameAndRealTeamList[currColIndex - 6].Real_team_name;
if (!string.IsNullOrEmpty(realTeamName))
{
each_One_Quantities.Real_team_name = realTeamName;
}
else
{
each_One_Quantities.Real_team_name = reportTeamName;
}
each_One_Quantities.Emp_name = nameAndRealTeamList[currColIndex - 6].Emp_name;
line_each_one_quantities_list.Add(each_One_Quantities);
readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
}
}
msg.Flag = true;
msg.Msg = "提交数据中...";
readDataToDB_bgWorker.ReportProgress(0, msg);
count = 0;
//开始提交数据.
maxmium = line_each_one_quantities_list.Count;
for (int i = 0; i < maxmium; i++)
{
count++;
readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium));
line_each_one_quantities_list[i].save();
}
msg.Msg = "提交完成。";
readDataToDB_bgWorker.ReportProgress((count * 100 / maxmium), msg);
myExcel.close();
}
/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Of_First_Sheet()
{
//确认关闭已经打开的Excel
if (CmdHelper.ifExistsTheProcessByName("EXCEL"))
{
FrmCloseExcel frmCloseExcel = new FrmCloseExcel();
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");
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportEmpsARInfo_Click(object sender, EventArgs e)
{
//判断是否存在Excel进程.
if (CmdHelper.ifExistsTheProcessByName("EXCEL.EXE"))
{
ShowResult.show(lblResult, "存在未关闭的Office Excel进程,请先关闭!", false);
return;
}
string _uncertainWSPath = _defaultDir + "\\uncertainRecord_" + TimeHelper.getCurrentTimeStr() + ".xls";
this.dgv.Visible = false;
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_name.Visible = false;
dgv_same_name.DataSource = null;
dgv_same_pinyin_of_name.DataSource = null;
dgv.DataSource = null;
lblResult.Text = "";
lblResult.BackColor = this.BackColor;
tb.Clear();
randomStr = TimeHelper.getCurrentTimeStr();
xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录：", "*.xls|*.xls", defaultDir);
string dir = DirectoryHelper.getDirOfFile(xlsFilePath);
if (string.IsNullOrEmpty(dir))
{
return;
}
List <string> xlsFileList = DirectoryHelper.getXlsFileUnderThePrescribedDir(dir);
List <string> resultList = new List <string>();
foreach (string xlsFile in xlsFileList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFile);
if (!CheckString.CheckARName(fileName))
{
continue;
}
//格式符合: 3月考勤记录1。
resultList.Add(xlsFile);
}
#region 先判断第四行，是否全为数字。
int maxColIndex = 0;
if (!check4thRow(resultList, out maxColIndex))
{
return;
}
#endregion
if (cbCheckSameNamesButDifferentMachineNo.Checked)
{
#region 保存关键信息到后台.
saveCriticalARInfo(resultList);
#endregion
#region 打开4个考勤文件
for (int i = 1; i <= resultList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(resultList[0]);
_1th_my_excel.open();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(resultList[1]);
_2nd_my_excel.open();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(resultList[2]);
_3rd_my_excel.open();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(resultList[3]);
_4th_my_excel.open();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.open();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndex);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
this.dgv_same_pinyin_of_name.Visible = true;
this.dgv_same_pinyin_of_name.DataSource = dt;
DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name);
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
btnSwitch.Text = "查看拼音相同的记录";
btnSwitch_Click(sender, e);
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//显示该文档
CmdHelper.runCmd(_uncertainWSPath);
#endregion
string promptStr = string.Format(@"存在姓名拼音相同的记录。{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//closeThe4ARExcels();
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
btnSwitch.Text = "查看同名记录";
btnSwitch_Click(sender, e);
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@"存在同名记录，将合并;{0}
继续(OK), 取消导入(Cancel)。", "\r\n");
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
return;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
}
resultList.Sort();
//判断该考勤机中是否已经存在，某月的记录
foreach (string xlsFilePath in resultList)
{
string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
int attendanceMachineFlag = int.Parse(fileName.Substring(fileName.Length - 1, 1));
//打开文档获取考勤机，所记录的日期。
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelper.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
System.Data.DataTable dtSubmitInfo = getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
if (!"0".Equals(dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString()))
{
string prompt = string.Format(@"您已经于: {1},{0}提交了{2} 考勤机{3},{0}共计{4}个用户的纪录{0} 覆盖(OK),退出(Cancel)?", "\r\n", dtSubmitInfo.Rows[0]["latest_record_time"].ToString(), year_and_month_str, attendanceMachineFlag, dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString());
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)))
{
return;
}
delTheInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str);
}
}
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
foreach (string xlsFilePath in resultList)
{
tb.Text = xlsFilePath;
lblResult.Visible = false;
MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(xlsFilePath, randomStr, lblPrompt, pb, lblResult);
//导入完成后进行保存，保存该文件至prepared目录中
pb.Visible = false;
lblPrompt.Visible = false;
ShowResult.show(lblResult, msg.Msg, msg.Flag);
timerRestoreTheLblResult.Enabled = true;
if (!msg.Flag)
{
return;
}
//saveTheExcel(xlsFilePath);
}
dgv_same_name.Visible = false;
dgv_same_pinyin_of_name.Visible = false;
//加载导入的数据。
this.dgv.DataSource = null;
this.dgv.DataSource = aR.getAR(randomStr);
this.dgv.Visible = true;
DGVHelper.AutoSizeForDGV(dgv);
tb.Clear();
}
/// <summary>
/// 将考勤记录导入预备表中.
/// </summary>
/// <param name="xlsFilePath"></param>
/// <param name="randomStr"></param>
/// <param name="pb"></param>
/// <returns></returns>
public static MSG Import_Record_To_Preparative_Table(string xlsFilePath, string randomStr, System.Windows.Forms.Label lblPrompt, ProgressBar pb, System.Windows.Forms.Label lblResult)
{
int pbLength = 0;
MSG msg = new MSG();
//用于确定本月最后一天.
Stack <int> sDate = new Stack <int>();
Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>();
AttendanceR.Random_Str = randomStr;
//按指纹日期
string fingerPrintDate = String.Empty;
//导入数据的行数.
int affectedCount = 0;
//行最大值.
int rowsMaxCount = 0;
int colsMaxCount = 0;
Usual_Excel_Helper uEHelper = null;
MyExcel myExcel = new MyExcel(xlsFilePath);
//打开该文档。
myExcel.open();
//只获取第一个表格。
Worksheet ws = myExcel.getFirstWorkSheetAfterOpen();
AttendanceR.File_path = xlsFilePath;
//行;列最大值 赋值.
rowsMaxCount = ws.UsedRange.Rows.Count;
colsMaxCount = ws.UsedRange.Columns.Count;
pb.Value = 0;
pb.Visible = true;
lblPrompt.Visible = true;
AttendanceR.Sheet_name = ws.Name;
//判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");
if (String.IsNullOrEmpty(A1Str))
{
msg.Msg = "工作表的A1单元格不能为空！";
msg.Flag = false;
myExcel.close();
return(msg);
}
//如果A1Str的内容不包含"考勤记录表"5个字。
if (!A1Str.Contains("考勤记录表"))
{
msg.Msg = "A1内容未包含'考勤记录表'";
msg.Flag = false;
myExcel.close();
return(msg);
}
#region 判断名称中是否区分了考勤记录。
string Seq_Attendance_Record = string.Empty;
int indexOfFullStop = xlsFilePath.LastIndexOf(".");
Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
{
msg.Msg = "考勤记录表名称请以数字结尾！";
msg.Flag = false;
myExcel.close();
return(msg);
}
#endregion
string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
// \0: 表空字符.
if (String.IsNullOrEmpty(C3Str))
{
msg.Msg = "异常: 考勤时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//
string[] ArrayC3 = C3Str.Split('~');
if (ArrayC3.Length == 0)
{
msg.Msg = "异常: 考勤时间格式变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
AttendanceR.Start_date = ArrayC3[0].ToString().Trim();
AttendanceR.End_date = ArrayC3[1].ToString().Trim();
//制表时间: L3 3行12列.
string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim();
if (String.IsNullOrEmpty(L3Str))
{
msg.Msg = "异常: 制表时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//制表时间.
AttendanceR.Tabulation_time = L3Str;
//检查第4行是否为;考勤时间:
string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
{
msg.Msg = "异常: 第四行已变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
uEHelper = new Usual_Excel_Helper(ws);
//此刻不能删除，只是获取行号。
Queue <Range> rangeToDelQueue = new Queue <Range>();
//判断是否有空行。
for (int i = 5; i <= rowsMaxCount; i++)
{
if (uEHelper.isBlankRow(i))
{
//只要上一列不是
//删除掉此行。
//判断上一行中的A列是否为工号。
string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
if ("工号:".Equals(temp))
{
//本行为空，上一行为工号行，则也统计。
continue;
}
//本行，为空，上一行非工号行。则删除本行。
Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
//不为工号
rangeToDelQueue.Enqueue(rangeToDel);
}
;
}
Range rangeToDelete;
//开始删除空行。
while (rangeToDelQueue.Count > 0)
{
rangeToDelete = rangeToDelQueue.Dequeue();
rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
}
;
rowsMaxCount = ws.UsedRange.Rows.Count;
//进度条长度增加。
pbLength += colsMaxCount;
pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1));
pb.Maximum = pbLength;
//入队列值0
sDate.Push(0);
//显示进度条。
//考勤表中第4行，某月的最大考勤天数。
lblPrompt.Text = excelName + "，正在读取：";
int actualMaxDay = 0;
//开始循环
for (int i = 1; i <= colsMaxCount; i++)
{
A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
//碰到第4行某列为空，退出循环。
if (String.IsNullOrEmpty(A4Str))
{
break;
}
int aDate = 0;
//对A4Str进行分析.
if (!Int32.TryParse(A4Str, out aDate))
{
msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
msg.Flag = false;
myExcel.close();
return(msg);
}
pb.Value++;
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
//跳出循环.
break;
}
actualMaxDay++;
sDate.Push(aDate);
}
//取其中的最小值。
colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
//考勤日期
fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-');
string tempStr = string.Empty;
//开始循环
for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
{
//从第5行开始.
//奇数;偶数行共用一个对象.
AttendanceR AR = null;
for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
{
//如果行数为奇数则为工号行.
if (rowIndex % 2 == 1)
{
//工号行.
//取工号
AR = new AttendanceR();
AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
//自行拼凑AR.
AR.combine_Job_Number();
//取姓名: K5
AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
//取部门: U5
AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
//部门为空，则填充为NULL;
AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL";
//取日期.填充0;
AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
}
else
{
//偶数行取考勤结果.
//上班时间. 如B10;
tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
string tempFirstTime = String.Empty;
string tempLastTime = String.Empty;
if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime))
{
msg.Msg = string.Format(@"导入失败：表中第{0}行{1}列的按指纹时间格式不对！", rowIndex, colIndex);
msg.Flag = false;
myExcel.close();
return(msg);
}
;
AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime;
AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime;
qAttendanceR.Enqueue(AR);
}
pb.Value++;
}
}
//释放对象
myExcel.close();
System.Threading.Thread.Sleep(2000);
GC.Collect();
GC.WaitForPendingFinalizers();
lblResult.Text = "";
lblResult.Visible = false;
lblPrompt.Visible = false;
lblPrompt.Text = "";
lblPrompt.Text = "提交数据: ";
lblPrompt.Focus();
lblPrompt.Visible = true;
pb.Maximum = qAttendanceR.Count;
pb.Value = 0;
#region
//保存对象
while (qAttendanceR.Count > 0)
{
try
{
AttendanceR aR = qAttendanceR.Dequeue();
affectedCount += aR.import_AR_To_Preparative_Table();
pb.Value++;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + "：导入失败; " + ex.ToString();
msg.Flag = false;
return(msg);
}
}
#endregion
lblPrompt.Visible = false;
//隐藏进度条。
pb.Visible = false;
msg.Flag = true;
msg.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString());
return(msg);
}
/// <summary>
/// 将考勤记录导入数据库.
/// </summary>
/// <param name="xlsFilePath"></param>
/// <param name="randomStr"></param>
/// <param name="pb"></param>
/// <returns></returns>
public static MSG ImportARToDB(string xlsFilePath)
{
int pbLength = 0;
MSG msg = new MSG();
ProgressInfo pI = new ProgressInfo("ConsoleCollectAR");
if (pI.ifExists())
{
pI.delete();
}
pI.add();
//用于确定本月最后一天.
Stack <int> sDate = new Stack <int>();
Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>();
//按指纹日期
string fingerPrintDate = String.Empty;
//导入数据的行数.
int affectedCount = 0;
//行最大值.
int rowsMaxCount = 0;
int colsMaxCount = 0;
MyExcel myExcel = new MyExcel(xlsFilePath);
//打开该文档。
myExcel.open();
#region 获取该工作簿中可视的工作表列表
List <Worksheet> listVisualWS = myExcel.getVisualWS();
List <AR_Sheet_Info> list_AR_Sheet_Info = new List <AR_Sheet_Info>();
Usual_Excel_Helper uEHelper = null;
foreach (Worksheet wS in listVisualWS)
{
int sheet_index = wS.Index;
int max_rows = wS.UsedRange.Rows.Count;
uEHelper = new Usual_Excel_Helper(wS);
//获取A1内容
string contentOfA3 = uEHelper.getSpecificCellValue("A3");
//考勤时间为 空，忽略
if (!"考勤时间".Equals(contentOfA3))
{
continue;
}
int maxColumns = wS.UsedRange.Columns.Count;
if (maxColumns <= 27 || maxColumns >= 32)
{
continue;
}
//列的宽度28-31.
AR_Sheet_Info ar_sheet_info = new AR_Sheet_Info(sheet_index, max_rows);
list_AR_Sheet_Info.Add(ar_sheet_info);
}
#endregion
//获取list_AR_Sheet_Info 中 rows 最少的那个Sheet
//先对list_AR_Sheet_Info进行排序。
list_AR_Sheet_Info.Sort((a, b) => a.Max_rows.CompareTo(b.Max_rows));
int sheetIndex = list_AR_Sheet_Info[0].SheetIndex;
Worksheet ws = listVisualWS[sheetIndex - 1];
//行;列最大值 赋值.
rowsMaxCount = ws.UsedRange.Rows.Count;
colsMaxCount = ws.UsedRange.Columns.Count;
AttendanceR.Sheet_name = ws.Name;
//判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");
if (String.IsNullOrEmpty(A1Str))
{
msg.Msg = "工作表的A1单元格不能为空！";
msg.Flag = false;
myExcel.close();
return(msg);
}
//如果A1Str的内容不包含"考勤记录表"5个字。
if (!A1Str.Contains("考勤记录表"))
{
msg.Msg = "A1内容未包含'考勤记录表'";
msg.Flag = false;
myExcel.close();
return(msg);
}
#region 判断名称中是否区分了考勤记录。
string Seq_Attendance_Record = string.Empty;
int indexOfFullStop = xlsFilePath.LastIndexOf(".");
Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
{
msg.Msg = "考勤记录表名称请以数字结尾！";
msg.Flag = false;
myExcel.close();
return(msg);
}
#endregion
string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
// \0: 表空字符.
if (String.IsNullOrEmpty(C3Str))
{
msg.Msg = "异常: 考勤时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//
string[] ArrayC3 = C3Str.Split('~');
if (ArrayC3.Length == 0)
{
msg.Msg = "异常: 考勤时间格式变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
AttendanceR.Start_date = ArrayC3[0].ToString().Trim();
AttendanceR.End_date = ArrayC3[1].ToString().Trim();
//制表时间: L3 3行12列.
string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim();
if (String.IsNullOrEmpty(L3Str))
{
msg.Msg = "异常: 制表时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//制表时间.
AttendanceR.Tabulation_time = L3Str;
//检查第4行是否为;考勤时间:
string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
{
msg.Msg = "异常: 第四行已变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
uEHelper = new Usual_Excel_Helper(ws);
//此刻不能删除，只是获取行号。
Queue <Range> rangeToDelQueue = new Queue <Range>();
//判断是否有空行。
for (int i = 5; i <= rowsMaxCount; i++)
{
if (uEHelper.isBlankRow(i))
{
//只要上一列不是
//删除掉此行。
//判断上一行中的A列是否为工号。
string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
if ("工号:".Equals(temp))
{
continue;
}
//获取该行。
Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
//不为工号
rangeToDelQueue.Enqueue(rangeToDel);
}
;
}
Range rangeToDelete;
//开始删除空行。
while (rangeToDelQueue.Count > 0)
{
rangeToDelete = rangeToDelQueue.Dequeue();
rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
}
;
rowsMaxCount = ws.UsedRange.Rows.Count;
//进度条长度增加。
pbLength += colsMaxCount;
pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1));
pI.Max_value = pbLength;
pI.Current_value = 0;
pI.Msg = excelName + "，正在读取：";
pI.update();
Console.WriteLine(excelName + "，正在读取：");
//入队列值0
sDate.Push(0);
int actualMaxDay = 0;
//开始循环
for (int i = 1; i <= colsMaxCount; i++)
{
A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
//碰到第4行某列为空，退出循环。
if (String.IsNullOrEmpty(A4Str))
{
break;
}
int aDate = 0;
//对A4Str进行分析.
if (!Int32.TryParse(A4Str, out aDate))
{
msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
msg.Flag = false;
myExcel.close();
return(msg);
}
pI.Current_value++;
pI.update();
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
//跳出循环.
break;
}
actualMaxDay++;
sDate.Push(aDate);
}
//取其中的最小值。
colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
//考勤日期
fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-');
string tempStr = string.Empty;
//开始循环
for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
{
//从第5行开始.
//奇数;偶数行共用一个对象.
AttendanceR AR = null;
for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
{
//如果行数为奇数则为工号行.
if (rowIndex % 2 == 1)
{
//工号行.
//取工号
AR = new AttendanceR();
AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
//自行拼凑AR.
AR.combine_Job_Number();
//取姓名: K5
AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
//取部门: U5
AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
//部门为空，则填充为NULL;
AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL";
//取日期.填充0;
AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
}
else
{
//偶数行取考勤结果.
//上班时间. 如B10;
tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
string tempFirstTime = String.Empty;
string tempLastTime = String.Empty;
if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime))
{
msg.Msg = string.Format(@"导入失败：表中第{0}行{1}列的按指纹时间格式不对！", rowIndex, colIndex);
msg.Flag = false;
return(msg);
}
;
AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime;
AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime;
qAttendanceR.Enqueue(AR);
}
pI.Current_value++;
pI.update();
}
}
Console.WriteLine("提交数据: ");
pI.Max_value = qAttendanceR.Count;
pI.Current_value = 0;
pI.Msg = "提交数据: ";
pI.update();
System.Threading.Thread.Sleep(2000);
#region
OracleDaoHelper.noLogging("Attendance_Record");
//保存对象
while (qAttendanceR.Count > 0)
{
try
{
affectedCount += qAttendanceR.Dequeue().saveDataToDB();
pI.Current_value++;
pI.update();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
msg.Msg = ex.ToString();
msg.Flag = false;
OracleConnHelper.closeConn();
return(msg);
}
}
#endregion
OracleDaoHelper.logging("Attendance_Record");
//释放对象
myExcel.close();
Console.WriteLine(String.Format(@"导入完成;计{0}条.", affectedCount.ToString()));
//隐藏进度条。
pI.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString());
pI.Finish_flag = true;
pI.update();
return(msg);
}
/// <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();
}
private void doNextAfterCheckName()
{
#region 打开4个考勤文件
for (int i = 1; i <= xlsFilePathList.Count; i++)
{
switch (i)
{
case 1:
_1th_my_excel = new MyExcel(xlsFilePathList[0]);
_1th_my_excel.openWithoutAlerts();
_1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen();
break;
case 2:
_2nd_my_excel = new MyExcel(xlsFilePathList[1]);
_2nd_my_excel.openWithoutAlerts();
_2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 3:
_3rd_my_excel = new MyExcel(xlsFilePathList[2]);
_3rd_my_excel.openWithoutAlerts();
_3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen();
break;
case 4:
_4th_my_excel = new MyExcel(xlsFilePathList[3]);
_4th_my_excel.openWithoutAlerts();
_4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen();
break;
}
}
#endregion
#region 创建 _uncertain_myExcel;
MyExcel uncertainRecordExcel = null;
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.create();
uncertainRecordExcel.openWithoutAlerts();
uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen();
//先写，日期行。
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS);
uEHelper.writeToSpecificRow(1, 1, maxColIndexOfCheckedNameOfExcel);
#endregion
System.Data.DataTable dt = getSamePinYinButName();
int amountOfGroupOfSamePinYinButName = getAmountOfGroupOfSamePinYinButName();
bool have_same_pinyin_flag = false;
if (dt != null && dt.Rows.Count > 0)
{
have_same_pinyin_flag = true;
}
//*************判断是否拼音相同 开始********************8
if (have_same_pinyin_flag)
{
ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false);
this.lblPrompt.Visible = false;
timerRestoreTheLblResult.Enabled = true;
#region 写记录到不确定文档中.
int theRowIndex = 0;
int Attendance_Machine_No = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
//获取源区域
//替换源文件的工号为 工号位于第三列
_1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
_2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
_3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
_4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
//设置列宽
uncertainWS.UsedRange.ColumnWidth = 3.75;
//显示该文档对应的图片
#endregion
string promptStr = string.Format(@" 存在姓名拼音相同但书写不同的记录：{1}组;{0}
确定: 将视为不同员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSamePinYinButName);
if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
closeThe4ARExcels();
uncertainWS.UsedRange.ColumnWidth = 3.75M;
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
//显示该文档。
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.open(true);
btnViewTheUncertaiRecordInExcel.Enabled = true;
return;
}
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
}
//*************判断是否拼音相同 结束*****************88
//1.h
dt = getSameNameInfo();
//获取汉字相同的组的数目。
int amountOfGroupOfSameName = getAmountOfGroupOfSameName();
string prompt = string.Empty;
if (dt.Rows.Count != 0)
{
int theRowIndex = 0;
int Attendance_Machine_No = 0;
#region 记录书写结束.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString());
Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString());
switch (Attendance_Machine_No)
{
case 1:
_1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
//获取源区域
srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
//保存一下。
break;
case 2:
_2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 3:
_3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
case 4:
_4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0');
srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]];
srcRange.Cells.Copy(Type.Missing);
//向目标复制。
//或取目标单元格。
currentRow = uncertainWS.UsedRange.Rows.Count;
destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]];
//destRange.Select();
uncertainWS.Paste(destRange, false);
break;
}
}
#endregion
prompt = string.Format(@" 存在同名的记录：{1}组;{0}
确定: 将视为同一员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSameName);
if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示：", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)))
{
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
//显示该文档。
uncertainRecordExcel = new MyExcel(_uncertainWSPath);
uncertainRecordExcel.open(true);
closeThe4ARExcels();
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
return;
}
if (!btnViewTheUncertaiRecordInExcel.Enabled)
{
btnViewTheUncertaiRecordInExcel.Enabled = true;
}
}
//关闭不确定文档。
uEHelper.setAllColumnsWidth(3.75M);
uncertainRecordExcel.saveWithoutAutoFit();
uncertainRecordExcel.close();
closeThe4ARExcels();
xlsFilePathList.Sort();
//直接删除
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.openWithoutAlerts();
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelperTemp = new Usual_Excel_Helper(firstSheet);
string year_and_month_str = uEHelperTemp.getCellContentByRowAndColIndex(3, 3);
year_and_month_str = year_and_month_str.Substring(0, 7);
myExcel.close();
delARDetailInfoByYearAndMonth(year_and_month_str);
//删除完毕。
this.dgv.DataSource = null;
//this.dgv.Columns.Clear();
lblPrompt.Visible = false;
lblPrompt.Text = "";
pb.Value = 0;
pb.Maximum = 0;
pb.Visible = false;
this.btnImportEmpsInfo.Enabled = false;
readDataFromExcelBGWorker.RunWorkerAsync();
}
