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;
}
