Microsoft.Office.Interop.Excel 操作类及方法

风火石 · 发表于 2016-11-23 14:50:57

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

复制代码

		自动登录	找回密码
密码			立即注册