|
ExportQuotationInspectionExcel 方法的作用是 根据strTmpFilePath 模板文件,替换掉其中的某些字符,然后重新生成一个新的文件 strOutputPath
1. 文件路径为 绝对路径, 即 如 C:\PROGRAME\1.XLSX
2. 新生成的excel 能够保留 模板中的 数据验证信息, 字体样式信息等
3.此方法是通过逐个读取 excel 中的每个 cell 来 进行判断, 60 rows* 9 cols 的excel 大概要10秒才能导出完毕, 性能较差
4.excel中range 可以是一个cell 也可以是多个cell 的组合区域,
5.excel 所有数据的编号 都是从1 开始
6. range 中的值不能局部替换,只能全部替换(还没找到局部替换的方法)
- public static bool ExportQuotationInspectionExcel(string strOutputPath, string strTmpFilePath, QuotationToJobEntity quotation)
- {
- GC.Collect(); //垃圾回收 貌似没什么效果
- Application excelApp = new ApplicationClass(); //定义excel application
- try
- {
- //打开excel
- Microsoft.Office.Interop.Excel.Workbook workBook = excelApp.Workbooks.Open(strTmpFilePath, Missing.Value, Missing.Value,
- Missing.Value, Missing.Value, Missing.Value,
- Missing.Value, Missing.Value, Missing.Value,
- Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
- );
- workBook.Activate();
- excelApp.Visible = false; //设置 excel 的操作界面是否显示
- //读取 sheet
- if (workBook.Sheets.Count > 0)
- {
- var activeSheet = (_Worksheet)workBook.Sheets.get_Item(1); //sheet的编号从1 开始,excel 所有编号都是从1 开始
- //int intRows = activeSheet.Rows.Count;
- //int intCols = activeSheet.Columns.Count;
- int intRows = 47;
- int intCols = 9;
- var clientInfo = JobBusiness.GetClientByJobID(quotation.JobId);
- var payer = JobBusiness.GetPayerCustomerEntityByJobID(quotation.JobId);
- var payerContact = new ContactEntity(clientInfo.PayerContactId.HasValue?clientInfo.PayerContactId.Value:Guid.Empty);
- var strPaymentTerms = AdminBusiness.GetDictionaryName(payer.PaymentTerms);
- var fromPerson = new EmployeeEntity(quotation.ApproveEmployeeId.HasValue ? quotation.ApproveEmployeeId.Value : Guid.Empty);
- var serviceToJobList = quotation.Job.ServiceToJob;
- var strCurrencyName = CurrencyBusiness.GetCurrencyName(payer.Currency.HasValue ? payer.Currency.Value : Guid.Empty);
- var strSampleReceiveDate = JobBusiness.GetSampleReceiveDates(quotation.JobId);
- var arrayParameters = new List<ManualItem>
- {
- new ManualItem(){Name = "{QuotationNumber}",Value =quotation.QuotationNumber},
- new ManualItem(){Name = "{CustomerName}",Value = payer.CustomerName},
- new ManualItem(){Name = "{AttentionTo}",Value =payerContact.ContactName},
- new ManualItem(){Name = "{AttentionEmail}",Value = payerContact.Email},
- new ManualItem(){Name = "{FromPerson}",Value =fromPerson.FirstName+" "+fromPerson.LastName},
- new ManualItem(){Name = "{PrintDate}",Value = DateTime.Now.ToString(ApplicationConfiguration.DateFormat)},
- new ManualItem(){Name = "{CurrencyName}",Value = strCurrencyName},
- new ManualItem(){Name = "{SampleReceiveDate}",Value = strSampleReceiveDate},
- };
- // 逐行逐个 读取 cell, 并进行数据判断
- for (int i = 9; i <= 23; i++)
- {
- for (int j = 1; j <= intCols; j++)
- {
- foreach (var parameter in arrayParameters)
- {
- var rang = (Range)activeSheet.Cells[i, j];
- var tragetRange = rang.Find(parameter.Name, Missing.Value, //找到含有对应参数的range
- XlFindLookIn.xlValues, XlLookAt.xlPart,
- XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
- Missing.Value, Missing.Value);
- if (tragetRange != null)
- {
- //替换 range 内容
- tragetRange.Value2 = tragetRange.Value2.ToString().Replace(parameter.Name, parameter.Value);
- }
- }
- }
- }
- arrayParameters = new List<ManualItem>
- {
- new ManualItem(){Name = "{LeadTime}",Value = quotation.LeadTime},
- new ManualItem(){Name = "{SampleReceivedQuantity}",Value =quotation.SampleQuantity},
- new ManualItem(){Name = "{SupplementaryQuantity}",Value = quotation.SupplementQuantity},
- };
- for (int i = 48; i <= 51; i++)
- {
- for (int j = 1; j <= intCols; j++)
- {
- foreach (var parameter in arrayParameters)
- {
- var rang = (Range)activeSheet.Cells[i, j];
- var tragetRange = rang.Find(parameter.Name, Missing.Value,
- XlFindLookIn.xlValues, XlLookAt.xlPart,
- XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
- Missing.Value, Missing.Value);
- if (tragetRange != null)
- {
- tragetRange.Value2 = tragetRange.Value2.ToString().Replace(parameter.Name, parameter.Value);
- }
- }
- }
- }
- int intServiceCount = 0;
- foreach (var serviceToJob in serviceToJobList)
- {
- var intRow = 24 + intServiceCount;
- var rngServiceStandard = (Range)activeSheet.get_Range(activeSheet.Cells[intRow, 5], activeSheet.Cells[intRow, 6]);
- rngServiceStandard.Value2 = serviceToJob.Service.Standard + (serviceToJob.IfSubcontract ? "(*)" : "");
- var rngServicePrice = (Range)activeSheet.Cells[intRow, 7];
- rngServicePrice.Value2 = serviceToJob.Amount;
- var rngServiceQuantity = (Range)activeSheet.Cells[intRow, 8];
- rngServiceQuantity.Value2 = serviceToJob.Quantity;
- intServiceCount++;
- }
- intServiceCount = 0;
- foreach (var sample in quotation.Job.Sample)
- {
- var intRow = 24 + intServiceCount;
- var rngSampleReference = (Range) activeSheet.Cells[intRow, 1];
- rngSampleReference.Value2 = sample.SampleCode;
- var rngSampleDescription =
- (Range) activeSheet.get_Range(activeSheet.Cells[intRow, 2], activeSheet.Cells[intRow, 4]);
- rngSampleDescription.Value2 = sample.SampleName;
- }
- //生成新的文件
- workBook.SaveAs(strOutputPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
- //关闭原来的
- workBook.Close(false, Missing.Value, Missing.Value);
- workBook = null;
- return true;
- }
- }
- catch (Exception err)
- {
- }
- finally
- {
- //清空
- excelApp.Quit();
- excelApp = null;
- GC.Collect();
- }
- return false;
- }
复制代码 |
|