我有一个Windows窗体应用程序来导出工资单的详细信息，它要求用户选择一个月和部门，之后用户可以选择导出所有字段或仅导出选定的字段。当用户单击导出或全部导出按钮时，将打开一个保存文件对话框，文件名为payslip_SelectedDesignation_SelectedMonth。当用户选择保存时，它会创建一个新的excel文件，将其保存在选定的位置，然后邮寄导出的文件。现在，当我第一次单击Export或Export All时，它会重新启动该文件，但当我第二次单击该按钮时，它会显示The process cannot access the file D：\PaySlip Recruitment Aug2023.xlsx，因为它正被另一个进程使用。如果我删除代码以将保存的Excel文件作为附件发送，则代码工作正常。但我一定要把那封信寄出去！！！
这是全部导出按钮
`private void btnExportAll_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
Workbook workbook = null;
Worksheet worksheet = null;
try
{
//Microsoft.Office.Interop.Excel.Application
excelApp = new Microsoft.Office.Interop.Excel.Application();
workbook = excelApp.Workbooks.Add(Type.Missing);
worksheet = workbook.Sheets[1] as Worksheet;
DateTime date = dateTimePicker1.Value;
//string month = date.ToString("MMMyyyy", CultureInfo.InvariantCulture);
string selectedValue = cmbYear.Text;
DateTime parsedDate = DateTime.ParseExact(selectedValue, "MMMM yyyy", CultureInfo.InvariantCulture);
string month = parsedDate.ToString("MMMyyyy");
if (boolyearChanged == true && boolMonthChanged == false)
{
MessageBox.Show("Please Select A Month", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string connectionString = ConfigurationManager.ConnectionStrings["HRWEBAPPConn"].ConnectionString;
string selectedDivision = cmbDivision.Text;
string queryString = "Select TOP 20 [Serial No], Month, Division, EmployeeID, [Employee Name], Designation, DOJ, PAN, [PF No], UAN, [Pay Period], [Pay Days], LOP, SL, CL, EL, Basic, HRA, Conveyance, Medical, [Personal Allowance], [Special Allowance], [OverTime Allowance], [Shift Allowance], Bonus, Basic_YTD, HRA_YTD, Conveyance_YTD, Medical_YTD, [Personal Allowance_YTD], [Special Allowance_YTD], [OverTime Allowance_YTD], [Shift Allowance_YTD], Bonus_YTD, [Income Tax], [Provident Fund], [Professional Tax], [Bonus Deduction], [Misc Deduction], [Income Tax_YTD], [Provident Fund_YTD], [Professional Tax_YTD], [Bonus Deduction_YTD], [Misc Deduction_YTD], [Gross Earnings], [Gross Earnings_YTD], [Gross Deductions], [Gross Deductions_YTD], [Bank Name], [IFSC Code], [Account No], [Income Tax Balance], [Professional Tax Balance], EEPF, EEVPF, EREPS, ERPF, EEPF_YTD, EEVPF_YTD, EREPS_YTD, ERPF_YTD, Status, [Created Date], [Created By] from tblPaySlip Where Division = @SelectedDivision AND Month = @Month";
if (selectedDivision != "IT" && selectedDivision != "Recruitment")
{
MessageBox.Show("Please Select A Division", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, con))
{
adapter.SelectCommand.Parameters.AddWithValue("@SelectedDivision", selectedDivision);
adapter.SelectCommand.Parameters.AddWithValue("@Month", month);
System.Data.DataTable dt = new System.Data.DataTable();
adapter.Fill(dt);
if(dt.Rows.Count > 0)
{
string fileName = month;
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Files (*.xlsx)|*.xlsx";
saveFileDialog.FileName = "PaySlip_" + selectedDivision + "_" + fileName + ".xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string savePath = saveFileDialog.FileName;
if (File.Exists(savePath))
{
DialogResult result = MessageBox.Show("The file already exists. Do you want to overwrite it?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.No)
{
return; // Cancel the operation if the user chooses not to overwrite
}
else
{
// Delete the existing file
File.Delete(savePath);
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
for (int row = 0; row < dt.Rows.Count; row++)
{
decimal decNet_Pay = Convert.ToDecimal(dt.Rows[row]["Gross Earnings"]) - Convert.ToDecimal(dt.Rows[row]["Gross Deductions"]);
for (int col = 0; col < dt.Columns.Count; col++)
{
worksheet.Cells[row + 2, col + 1] = dt.Rows[row][col];
}
worksheet.Cells[row + 2, dt.Columns.Count + 1] = decNet_Pay;
worksheet.Cells[row + 2, dt.Columns.Count + 2] = ConvertRupeesToWords(decNet_Pay);
}
Microsoft.Office.Interop.Excel.Range headerRow = worksheet.Rows[1];
headerRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
headerRow.Font.Size = 11;
headerRow.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbRed;
headerRow.Font.Name = "Arial";
headerRow.Font.Bold = true;
for (int row = 2; row <= worksheet.UsedRange.Rows.Count; row++)
{
Microsoft.Office.Interop.Excel.Range currentRow = worksheet.Rows[row];
currentRow.Font.Size = 10;
currentRow.Font.Name = "Arial";
}
#region Column_Names
worksheet.Cells[1, 1] = "Serial No";
worksheet.Cells[1, 2] = "Month";
worksheet.Cells[1, 3] = "Division";
worksheet.Cells[1, 4] = "Employee ID";
worksheet.Cells[1, 5] = "Employee Name";
worksheet.Cells[1, 6] = "Designation";
worksheet.Cells[1, 7] = "DOJ";
worksheet.Cells[1, 8] = "PAN";
worksheet.Cells[1, 9] = "PF No";
worksheet.Cells[1, 10] = "UAN";
worksheet.Cells[1, 11] = "Pay Period";
worksheet.Cells[1, 12] = "Pay Days";
worksheet.Cells[1, 13] = "LOP";
worksheet.Cells[1, 14] = "SL";
worksheet.Cells[1, 15] = "CL";
worksheet.Cells[1, 16] = "EL";
worksheet.Cells[1, 17] = "Basic";
worksheet.Cells[1, 18] = "HRA";
worksheet.Cells[1, 19] = "Conveyance";
worksheet.Cells[1, 20] = "Medical";
worksheet.Cells[1, 21] = "Personal Allowance";
worksheet.Cells[1, 22] = "Special Allowance";
worksheet.Cells[1, 23] = "OverTime Allowance";
worksheet.Cells[1, 24] = "Shift Allowance";
worksheet.Cells[1, 25] = "Bonus";
worksheet.Cells[1, 26] = "Basic_YTD";
worksheet.Cells[1, 27] = "HRA_YTD";
worksheet.Cells[1, 28] = "Conveyance_YTD";
worksheet.Cells[1, 29] = "Medical_YTD";
worksheet.Cells[1, 30] = "Personal Allowance_YTD";
worksheet.Cells[1, 31] = "Special Allowance_YTD";
worksheet.Cells[1, 32] = "OverTime Allowance_YTD";
worksheet.Cells[1, 33] = "Shift Allowance_YTD";
worksheet.Cells[1, 34] = "Bonus_YTD";
worksheet.Cells[1, 35] = "Income Tax";
worksheet.Cells[1, 36] = "Provident Fund";
worksheet.Cells[1, 37] = "Professional Tax";
worksheet.Cells[1, 38] = "Bonus Deduction";
worksheet.Cells[1, 39] = "Misc Deduction";
worksheet.Cells[1, 40] = "Income Tax_YTD";
worksheet.Cells[1, 41] = "Provident Fund_YTD";
worksheet.Cells[1, 42] = "Professional Tax_YTD";
worksheet.Cells[1, 43] = "Bonus Deduction_YTD";
worksheet.Cells[1, 44] = "Misc Deduction_YTD";
worksheet.Cells[1, 45] = "Gross Earnings";
worksheet.Cells[1, 46] = "Gross Earnings_YTD";
worksheet.Cells[1, 47] = "Gross Deductions";
worksheet.Cells[1, 48] = "Gross Deductions_YTD";
worksheet.Cells[1, 49] = "Bank Name";
worksheet.Cells[1, 50] = "IFSC Code";
worksheet.Cells[1, 51] = "Account No";
worksheet.Cells[1, 52] = "Income Tax Balance";
worksheet.Cells[1, 53] = "Professional Tax Balance";
worksheet.Cells[1, 54] = "EEPF";
worksheet.Cells[1, 55] = "EEVPF";
worksheet.Cells[1, 56] = "EREPS";
worksheet.Cells[1, 57] = "ERPF";
worksheet.Cells[1, 58] = "EEPF_YTD";
worksheet.Cells[1, 59] = "EEVPF_YTD";
worksheet.Cells[1, 60] = "EREPS_YTD";
worksheet.Cells[1, 61] = "ERPF_YTD";
worksheet.Cells[1, 62] = "Status";
worksheet.Cells[1, 63] = "Created Date";
worksheet.Cells[1, 64] = "Created By";
worksheet.Cells[1, 65] = "Net Pay";
worksheet.Cells[1, 66] = "Net Pay in Words";
#endregion
#region Alignment
Microsoft.Office.Interop.Excel.Range SNoCol = worksheet.Columns[1];
SNoCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range PayDaysCol = worksheet.Columns[12];
PayDaysCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range LOPCol = worksheet.Columns[13];
LOPCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range SLCol = worksheet.Columns[14];
SLCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range CLCol = worksheet.Columns[15];
CLCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range ELCol = worksheet.Columns[16];
ELCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Microsoft.Office.Interop.Excel.Range BasicCol = worksheet.Columns[17];
BasicCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range HRACol = worksheet.Columns[18];
HRACol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ConveyanceCol = worksheet.Columns[19];
ConveyanceCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range MedicalCol = worksheet.Columns[20];
MedicalCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PACol = worksheet.Columns[21];
PACol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range SACol = worksheet.Columns[22];
SACol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range OACol = worksheet.Columns[23];
OACol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ShACol = worksheet.Columns[24];
ShACol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range BonusCol = worksheet.Columns[25];
BonusCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range BasicYTDCol = worksheet.Columns[26];
BasicYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range HRAYTDCol = worksheet.Columns[27];
HRAYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ConvYTDCol = worksheet.Columns[28];
ConvYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range MedicalYTDCol = worksheet.Columns[29];
MedicalYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PAYTDCol = worksheet.Columns[30];
PAYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range SAYTDCol = worksheet.Columns[31];
SAYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range OAYTDCol = worksheet.Columns[32];
OAYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ShYTDCol = worksheet.Columns[33];
ShYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range BonusYTDCol = worksheet.Columns[34];
BonusYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ITCol = worksheet.Columns[35];
ITCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PFCol = worksheet.Columns[36];
PFCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PTCol = worksheet.Columns[37];
PTCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range BDCol = worksheet.Columns[38];
BDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range MDCol = worksheet.Columns[39];
MDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ITYTDCol = worksheet.Columns[40];
ITYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PFYTDCol = worksheet.Columns[41];
PFYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PTYTDCol = worksheet.Columns[42];
PTYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range BDYTDCol = worksheet.Columns[43];
BDYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range MDYTDCol = worksheet.Columns[44];
MDYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range GECol = worksheet.Columns[45];
GECol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range GEYTDCol = worksheet.Columns[46];
GEYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range GDCol = worksheet.Columns[47];
GDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range GDYTDCol = worksheet.Columns[48];
GDYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ITBCol = worksheet.Columns[52];
ITBCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range PTBCol = worksheet.Columns[53];
PTBCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EEPFCol = worksheet.Columns[54];
EEPFCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EEVPFCol = worksheet.Columns[55];
EEVPFCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EREPSCol = worksheet.Columns[56];
EREPSCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ERPFCol = worksheet.Columns[57];
ERPFCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EEPFYTDCol = worksheet.Columns[58];
EEPFYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EEVPFYTDCol = worksheet.Columns[59];
EEVPFYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range EREPSYTDCol = worksheet.Columns[60];
EREPSYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range ERPFYTDCol = worksheet.Columns[61];
ERPFYTDCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
Microsoft.Office.Interop.Excel.Range NetPayCol = worksheet.Columns[65];
NetPayCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
#endregion
//UAN Number Formatting
int UANcolumnIndex = 10;
Microsoft.Office.Interop.Excel.Range columnRange = worksheet.Columns[UANcolumnIndex];
columnRange.NumberFormat = "0";
//Columns Total
int[] columnsToSum = { 17, 37 };
double[] columnTotals = new double[columnsToSum.Length];
for (int rowNum = 2; rowNum <= worksheet.UsedRange.Rows.Count; rowNum++)
{
foreach (int columnIndex in columnsToSum)
{
Microsoft.Office.Interop.Excel.Range cell = worksheet.Cells[rowNum, columnIndex];
double cellValue = 0;
if (cell.Value != null && double.TryParse(cell.Value.ToString(), out cellValue))
{
columnTotals[Array.IndexOf(columnsToSum, columnIndex)] += cellValue;
}
}
}
//Total Sum
int newRowPosition = worksheet.UsedRange.Rows.Count + 2;
worksheet.Rows[newRowPosition].Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown);
int startingColumnIndex = 1;
Microsoft.Office.Interop.Excel.Range totalCell = worksheet.Cells[newRowPosition, startingColumnIndex];
totalCell.Value = "Total:";
totalCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
totalCell.Font.Bold = true;
totalCell.Font.Size = 11;
totalCell.Font.Name = "Arial";
for (int i = 0; i < columnsToSum.Length; i++)
{
Microsoft.Office.Interop.Excel.Range currentCell = worksheet.Cells[newRowPosition, columnsToSum[i]];
currentCell.Value = columnTotals[i];
currentCell.Font.Bold = true;
currentCell.Font.Size = 11;
currentCell.Font.Name = "Arial";
}
worksheet.Columns.AutoFit();
workbook.SaveAs(savePath);
workbook.Close(false);
Marshal.FinalReleaseComObject(worksheet);
Marshal.FinalReleaseComObject(workbook);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
con.Close();
// Set the objects to null
worksheet = null;
workbook = null;
excelApp = null;
string recipientEmail = "[email protected]";
string senderEmail = "[email protected]";
string emailSubject = Path.GetFileName(savePath);
string emailBody = "Please find attached the PaySlip file.";
if (!string.IsNullOrEmpty(emailSubject) && !string.IsNullOrWhiteSpace(emailSubject))
{
MessageBox.Show("File saved sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
SmtpClient smtpClient = new SmtpClient("smtp.gmail.com");
smtpClient.Port = 587;
smtpClient.Credentials = new NetworkCredential(senderEmail, "xxxx xxxx xxxx xxxx");
smtpClient.EnableSsl = true;
MailMessage mailMessage = new MailMessage(senderEmail, recipientEmail, emailSubject, emailBody);
Attachment attachment = new Attachment(savePath);
mailMessage.Attachments.Add(attachment);
smtpClient.Send(mailMessage);
}
}
}
else
{
MessageBox.Show($"{month} doesn't have any Fields", "Failure", MessageBoxButtons.OK, MessageBoxIcon.Warning);
workbook.Close();
excelApp.Quit();
con.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("An error occurred: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
workbook.Close();
excelApp.Quit();
con.Close();
}
finally
{
// Make sure to release resources even in case of an exception
if (worksheet != null) Marshal.FinalReleaseComObject(worksheet);
if (workbook != null) Marshal.FinalReleaseComObject(workbook);
if (excelApp != null) Marshal.FinalReleaseComObject(excelApp);
}
}
`
1条答案
按热度按时间3xiyfsfu1#
我建议不要使用Interop.Excel。使用一个知道如何读/写excel的库，而不需要任何excel进程或安装。
这将使文件交互方式更可预测，也可能更快。
这种文库的示例是EPPlus