private void updateExcelTuitional(int sheetID) { var xApp = new Microsoft.Office.Interop.Excel.Application(); xApp.Visible = false; var xBook = xApp.Workbooks.Open(getExcel()); Dictionary <int, List <indexColumn> > sheetColumns = ViewState[vs_SheetColumn] as Dictionary <int, List <indexColumn> >; List <indexColumn> AllColumns; bool existSheet = sheetColumns.TryGetValue(sheetID, out AllColumns); if (existSheet) { try { Business.Common.CommonBusiness CB = new Business.Common.CommonBusiness(); DataTable dt; var sheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Worksheets.Item[sheetID]; int lastRow = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row; int lastCol = 1; int tempLast = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column; for (int col = 1; col < tempLast; col++) { if ((sheet.Cells[3, col] as Microsoft.Office.Interop.Excel.Range).Value2 == null) { break; } else { lastCol++; } } (sheet.Cells[firstRow, lastCol + 1] as Microsoft.Office.Interop.Excel.Range).Value2 = "شهریه ثابت"; (sheet.Cells[firstRow, lastCol + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = "شهریه متغیر"; (sheet.Cells[firstRow, lastCol + 3] as Microsoft.Office.Interop.Excel.Range).Value2 = "جمع شهریه"; string term = Convert.ToString((sheet.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range).Value2); int stcodeColumnNumber = Convert.ToInt32(AllColumns.FindLast(c => c.columnName == "stcode").selectedInExcel); for (int i = firstRow + 1; i < lastRow; i++) { var stcode = (sheet.Cells[i, stcodeColumnNumber] as Microsoft.Office.Interop.Excel.Range).Value2; if (stcode != null) { dt = CB.getStudentTuitional(term, Convert.ToString(stcode)); if (dt.Rows.Count == 0) { continue; } (sheet.Cells[i, lastCol + 1] as Microsoft.Office.Interop.Excel.Range).Value2 = dt.Rows[0][0]; (sheet.Cells[i, lastCol + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = dt.Rows[0][1]; (sheet.Cells[i, lastCol + 3] as Microsoft.Office.Interop.Excel.Range).Value2 = dt.Rows[0][2]; } } (sheet.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range).Value2 = null; xBook.Close(true); xApp.Quit(); } catch (Exception ex) { showMessage(ex.Message); xBook.Close(false); xApp.Quit(); //System.IO.File.Delete(MapPath(ViewState[vs_ExcelFile].ToString())); } } }