public TimeSpan SyncApplyingByOffice(string fileName, Model.MotivationEnum.CountryEnum country) { this.contractCache.Clear();//清空合同缓存 watch = new Stopwatch(); watch.Start(); Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Sheets sheets; Microsoft.Office.Interop.Excel.Workbook workbook = null; object oMissiong = System.Reflection.Missing.Value; try { if (app == null) { watch.Stop(); return watch.Elapsed; } workbook = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); // workbook.SaveAs(newFileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); sheets = workbook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表 if (worksheet == null) { watch.Stop(); return watch.Elapsed; } string strContent; int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Range range; for (int iRow = 2; iRow <= iRowCount; iRow++) { Model.ContractData contract = new Model.ContractData(); range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 1]; strContent = (range.Value2 == null) ? "" : range.Text.ToString().Replace("'", "").Trim(); contract.ContractNum = strContent; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 2]; strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.StudentName = strContent; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 3]; strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.BrachCompany = strContent; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 4]; strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.Consultant = strContent; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 6]; strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.ContractStatus = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 11]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 10]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.Senior = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 12]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 11]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.Author = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 16]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 15]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.PSDepartment = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 17]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 16]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.PSPSAuthor = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 29]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 28]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.SignDate = DateTime.Parse(strContent); if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 35]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 36]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.ApplyStatus = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 36]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 37]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.University = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 37]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 38]; } strContent = (range.Value2 == null) ? "" : range.Text.ToString(); contract.Course = strContent; if (country == Model.MotivationEnum.CountryEnum.UK) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 47]; } else { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, 49]; } if (range.Value2 == "-" || range.Value2 == null) { contract.SendDate = DateTime.MinValue; } else { try { contract.SendDate = DateTime.Parse(range.Text); } catch { contract.SendDate = DateTime.MinValue; } } this.contractCache.Add(contract); Controller.SQLiteController.BizContract.Sync(contract, country); } Controller.SQLiteController.BizContract.CheckContract(this.contractCache);//检查删除冗余合同 watch.Stop(); TimeSpan ts = watch.Elapsed; return ts; } catch (Exception ex) { Controller.MessageConsole.WriteConsole(ex); watch.Stop(); return watch.Elapsed; } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; //使用完成后删除原文件 System.IO.File.Delete(fileName); GC.Collect(); GC.WaitForPendingFinalizers(); } }
public TimeSpan SyncApplyingByNPOI(string fileName, Model.MotivationEnum.CountryEnum country) { contractCache.Clear();//清空合同缓存 watch = new Stopwatch(); watch.Start(); Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Sheets sheets; Microsoft.Office.Interop.Excel.Workbook workbook1 = null; object oMissiong = System.Reflection.Missing.Value; try { workbook1 = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); sheets = workbook1.Worksheets; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表 Microsoft.Office.Interop.Excel.Range range; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]; range.Value = "test"; } catch (Exception ex) { Controller.MessageConsole.WriteConsole(ex); watch.Stop(); return watch.Elapsed; } finally { workbook1.Save(); workbook1.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1); workbook1 = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); } FileStream applyingStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); HSSFWorkbook workbook = new HSSFWorkbook(applyingStream); int sheetCount = workbook.NumberOfSheets; for (int j = 0; j < sheetCount; j++) { ISheet sheet = workbook.GetSheetAt(j); if (sheet.LastRowNum<=0) { continue; } int rowCount = sheet.LastRowNum; for (int i = 1; i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null Model.ContractData contract = new Model.ContractData(); contract.ContractNum = row.GetCell(0) == null ? "" : row.GetCell(0).ToString().Replace("'", "").Trim(); contract.StudentName = row.GetCell(1) == null ? "" : row.GetCell(1).ToString(); contract.BrachCompany = row.GetCell(2) == null ? "" : row.GetCell(2).ToString(); contract.Consultant = row.GetCell(3) == null ? "" : row.GetCell(3).ToString(); contract.ContractStatus = row.GetCell(6) != null ? row.GetCell(6).ToString() : ""; if (country == Model.MotivationEnum.CountryEnum.UK) { contract.Senior = row.GetCell(11) != null ? row.GetCell(11).ToString() : ""; } else { contract.Senior = row.GetCell(10) != null ? row.GetCell(10).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.Author = row.GetCell(5) != null ? row.GetCell(5).ToString() : ""; } else { contract.Author = row.GetCell(5) != null ? row.GetCell(5).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.PSDepartment = row.GetCell(15) != null ? row.GetCell(15).ToString() : ""; } else { contract.PSDepartment = row.GetCell(14) != null ? row.GetCell(14).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.PSPSAuthor = row.GetCell(16) != null ? row.GetCell(16).ToString() : ""; } else { contract.PSPSAuthor = row.GetCell(15) != null ? row.GetCell(15).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.SignDate = (row.GetCell(28) != null && row.GetCell(28).ToString() != "-") ? Convert.ToDateTime(row.GetCell(28).ToString()) : DateTime.MinValue; } else { contract.SignDate = (row.GetCell(27) != null && row.GetCell(27).ToString() != "-") ? Convert.ToDateTime(row.GetCell(27).ToString()) : DateTime.MinValue; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.ApplyStatus = row.GetCell(34) != null ? row.GetCell(34).ToString() : ""; } else { contract.ApplyStatus = row.GetCell(35) != null ? row.GetCell(35).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.University = row.GetCell(35) != null ? row.GetCell(35).ToString() : ""; } else { contract.University = row.GetCell(36) != null ? row.GetCell(36).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.Course = row.GetCell(36) != null ? row.GetCell(36).ToString() : ""; } else { contract.Course = row.GetCell(37) != null ? row.GetCell(37).ToString() : ""; } if (country == Model.MotivationEnum.CountryEnum.UK) { contract.SendDate = (row.GetCell(48) != null && row.GetCell(48).ToString() != "-") ? Convert.ToDateTime(row.GetCell(48).ToString()) : DateTime.MinValue; } else { contract.SendDate = (row.GetCell(48) != null && row.GetCell(48).ToString() != "-") ? Convert.ToDateTime(row.GetCell(48).ToString()) : DateTime.MinValue; } this.contractCache.Add(contract); Controller.SQLiteController.BizContract.Sync(contract, country); } } Controller.SQLiteController.BizContract.CheckContract(this.contractCache);//检查删除冗余合同 watch.Stop(); //使用完成后删除原文件 //System.IO.File.Delete(fileName); GC.Collect(); GC.WaitForPendingFinalizers(); return watch.Elapsed; }