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