//写EXCEL public void saveExcel(string savepath, DataSet dstemp) { string filepath = Server.MapPath("Template/Template.xls"); Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); //新建文件 // 多数使用缺省值 (除了 read-only我们设置它为 true) Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); // 取得工作簿(workbook)中表单的集合(sheets) Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets; // 取得表单集合中唯一的一个表(worksheet) if (ExcelObj == null) { //MessageBox.Show("无法启动Excel,可能您的电脑未安装Excel"); return; } try { for (int i = 0; i < dstemp.Tables.Count; i++) { string dtempname = dstemp.Tables[i].TableName; int rowIndex = 1; int colIndex = 0; int sizeofsheet = sheets.Count; Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(dtempname); string a = worksheet.Name; int countrow = dstemp.Tables[dtempname].Rows.Count; int countcolumn = 0; if (dtempname == "主管碰头会议决议事项督办清单") { countcolumn = 9; } else { countcolumn = 8; } var RowAll = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[countrow + 1, countcolumn]); RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; System.Data.DataTable table = dstemp.Tables[dtempname]; foreach (DataColumn col in table.Columns) { colIndex++; worksheet.Cells[2, colIndex] = col.ColumnName; } foreach (DataRow row in table.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; worksheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } rowIndex = 1; colIndex = 0; } ExcelObj.DisplayAlerts = false; ExcelObj.ActiveWorkbook.SaveAs(savepath); //MessageBox.Show("数据导出成功!", "消息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); ExcelObj.Visible = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); sheets = null; theWorkbook.Close(true, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(theWorkbook); theWorkbook = null; ExcelObj.Application.Workbooks.Close(); ExcelObj.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj); Kill(ExcelObj); int generation = System.GC.GetGeneration(ExcelObj); ExcelObj = null; System.GC.Collect(generation); } catch (Exception ex) { //MessageBox.Show(ex.ToString()); } finally { GC.Collect();//垃圾回收 GC.WaitForPendingFinalizers(); } }
protected void lbtn_wanggou_Click(object sender, EventArgs e) { string ids = ""; for (int i = 0; i < rpt_taizhang.Items.Count; i++) { CheckBox ckb = rpt_taizhang.Items[i].FindControl("ckb") as CheckBox; if (ckb.Checked == true) { ids += ckb.ToolTip + ","; } } if (ids.Length > 0) { ids = ids.Substring(0, ids.Length - 1); } else { SystemTool.AlertShow(this, "请选择需要操作的数据"); return; } string sql = "select * from taizhang_info where taizhang_id in(" + ids + ")"; DataSet ds = DB.ExecuteSqlDataSet(sql, null); //模板文件 string TempletFileName = Server.MapPath("~/daochu/wanggou.xlsx");//路径 //导出文件 string dts = DateTime.Now.ToString("yyyyMMddHHmmss"); string ReportFileName = Server.MapPath("~/xiazai/wanggou" + dts + ".xlsx"); Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application(); object oMissing = System.Reflection.Missing.Value; myExcel.Application.Workbooks.Open(TempletFileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); Microsoft.Office.Interop.Excel.Workbook myBook = myExcel.Workbooks[1]; Microsoft.Office.Interop.Excel.Worksheet mySheet = (Microsoft.Office.Interop.Excel.Worksheet)myBook.Worksheets[1]; //Microsoft.Office.Interop.Excel.Range r = null; //r = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[3, 3]; //mySheet.Cells[20, 3] = "123456"; Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)mySheet.Rows[9, Type.Missing]; //插入行 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); } int h = ds.Tables[0].Rows.Count; if (ds.Tables[0].Rows.Count > 0) { mySheet.Cells[3, 1] = "订单编号:" + ds.Tables[0].Rows[0]["dingdanbianhao"].ToString(); mySheet.Cells[4, 1] = "电商平台:" + ds.Tables[0].Rows[0]["dianshangpingtai"].ToString(); mySheet.Cells[4, 9] = "订单日期:" + ds.Tables[0].Rows[0]["dingdanshijian"].ToString(); mySheet.Cells[5, 1] = "网店名称:" + ds.Tables[0].Rows[0]["wangdianmingcheng"].ToString(); mySheet.Cells[5, 9] = "订货依据:" + ds.Tables[0].Rows[0]["dinghuoyiju"].ToString(); mySheet.Cells[6, 1] = "供应商名称:" + ds.Tables[0].Rows[0]["gonghuochangshang"].ToString(); mySheet.Cells[6, 9] = "询价方式:" + ds.Tables[0].Rows[0]["xunjiafangshi"].ToString(); } decimal zongjia = 0; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { mySheet.Cells[9 + i, 1] = (i + 1).ToString(); mySheet.Cells[9 + i, 2] = ds.Tables[0].Rows[i]["shiyongdanwei"].ToString(); mySheet.Cells[9 + i, 3] = ds.Tables[0].Rows[i]["dingjiawuzimingcheng"].ToString(); mySheet.Cells[9 + i, 4] = ds.Tables[0].Rows[i]["dingjiaguigexinghao"].ToString(); mySheet.Cells[9 + i, 5] = ds.Tables[0].Rows[i]["dingjiadanwei"].ToString(); mySheet.Cells[9 + i, 6] = ds.Tables[0].Rows[i]["dingjiashuliang"].ToString(); mySheet.Cells[9 + i, 7] = ds.Tables[0].Rows[i]["hanshuidanjia"].ToString(); decimal shuliang = ds.Tables[0].Rows[i]["dingjiashuliang"].ToString() == "" ? 0 : Convert.ToDecimal(ds.Tables[0].Rows[i]["dingjiashuliang"].ToString()); decimal danjia = ds.Tables[0].Rows[i]["hanshuidanjia"].ToString() == "" ? 0 : Convert.ToDecimal(ds.Tables[0].Rows[i]["hanshuidanjia"].ToString()); zongjia += (danjia * shuliang); mySheet.Cells[9 + i, 8] = (shuliang * danjia).ToString();//ds.Tables[0].Rows[i]["dingdanbianhao"].ToString(); mySheet.Cells[9 + i, 9] = ds.Tables[0].Rows[i]["jiaohuoshijian"].ToString(); mySheet.Cells[9 + i, 10] = ds.Tables[0].Rows[i]["jiaohuodidian"].ToString(); mySheet.Cells[9 + i, 11] = ds.Tables[0].Rows[i]["wuzipinpai"].ToString(); //mySheet.Cells[9 + i, 12] = ds.Tables[0].Rows[i]["beizhu"].ToString(); } if (ds.Tables[0].Rows.Count > 0) { mySheet.Cells[10 + h, 1] = "付款方式:" + ds.Tables[0].Rows[0]["fukuanfangshi"].ToString(); mySheet.Cells[10 + h, 8] = zongjia.ToString(); mySheet.Cells[10 + h, 9] = "大写:(含" + ds.Tables[0].Rows[0]["shuilv"].ToString() + "%的增值税)" + MoneyToChinese(zongjia.ToString()); mySheet.Cells[12 + h, 1] = "询价员:" + ds.Tables[0].Rows[0]["xunjiayuan"].ToString() + " 询价科长: 副经理: "; } try { myBook.SaveAs(ReportFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { } //wb.Save(); myBook.Close(Type.Missing, Type.Missing, Type.Missing); //wbs.Close(); myExcel.Quit(); myBook = null; //wbs = null; myExcel = null; GC.Collect(); //filess.Close(); System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("网购DingHuoTongZhiDan.xlsx")); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", filet.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(filet.FullName); // 停止页面的执行 Response.End(); }
// 完全读取 excel /// <summary> /// 解析Excel,返回DataTable /// </summary> /// <param name="fileName"></param> /// <returns></returns> public System.Data.DataTable ImpExcel(string fileName) { System.Data.DataTable dt = new System.Data.DataTable(); dt.TableName = excelsheetname.ToString(); try { //Microsoft.Office.Interop.Excel.Application app; //Microsoft.Office.Interop.Excel.Workbook wbs; // Microsoft.Office.Interop.Excel.Worksheet ws; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //新建文件 // 多数使用缺省值 (除了 read-only我们设置它为 true) Microsoft.Office.Interop.Excel.Workbook wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); // 取得工作簿(workbook)中表单的集合(sheets) Microsoft.Office.Interop.Excel.Sheets wss = wbs.Worksheets; // 取得表单集合中唯一的一个表(worksheet) Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1); //app = new Microsoft.Office.Interop.Excel.Application(); //wbs = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null); //ws = (Microsoft.Office.Interop.Excel.Worksheet)app.Worksheets.get_Item(1); //int a = ws.Rows.Count; int a = ws.UsedRange.Cells.Rows.Count + 2; //int b = ws.Columns.Count; int b = 20; string name = ws.Name; for (int i = 1; i < a; i++) { if (i == 1) { for (int j = 1; j <= b; j++) { Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]); range.Select(); string columnnames = app.ActiveCell.Text.ToString(); dt.Columns.Add(columnnames, typeof(string)); } } else { DataRow dr = dt.NewRow(); for (int j = 1; j <= b; j++) { Microsoft.Office.Interop.Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]); range.Select(); int rowco = j - 1; string cellc = app.ActiveCell.Text.ToString();; dr[rowco] = cellc; } dt.Rows.Add(dr); } } System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(wss); wss = null; wbs.Close(true, Type.Missing, Type.Missing); //(Microsoft.Office.Interop.Excel.Worksheet)wbs.Close(true, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); wbs = null; app.Application.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); int generation = System.GC.GetGeneration(app); Kill(app); app = null; System.GC.Collect(generation); return(dt); } catch (Exception ex) { // MessageBox.Show("数据绑定Excel失败! 失败原因:Excel格式不正确!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return(dt); } finally { GC.Collect(); //垃圾回收 GC.WaitForPendingFinalizers(); } }
public void WriteExcell(DataTable dtReport) { CheckExcellProcesses(); string path = ""; path = Server.MapPath("~/operations/excelfile/NAD_Export.xls"); ExlApp = new Microsoft.Office.Interop.Excel.Application(); try { ExlWrkBook = ExlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet; int i = 3; foreach (DataRow dr in dtReport.Rows) { ExlWrkSheet.Cells[i, 1] = Convert.ToString(dr["VESSEL_NAME"]); ExlWrkSheet.Cells[i, 2] = Convert.ToString(dr["STRTELEGRAM_DATE"]); ExlWrkSheet.Cells[i, 3] = Convert.ToString(dr["TELEGRAM_TYPE"]); ExlWrkSheet.Cells[i, 4] = Convert.ToString(dr["VOYAGE"]); ExlWrkSheet.Cells[i, 5] = Convert.ToString(dr["LOCATION_NAME"]); ExlWrkSheet.Cells[i, 6] = Convert.ToString(dr["PORT_NAME"]); ExlWrkSheet.Cells[i, 7] = Convert.ToString(dr["UTC_TYPE"]); ExlWrkSheet.Cells[i, 8] = Convert.ToString(dr["UTC_HR"]); ExlWrkSheet.Cells[i, 9] = Convert.ToString(dr["Clocks_type"]); ExlWrkSheet.Cells[i, 10] = Convert.ToString(dr["Clocks_Hr"]); ExlWrkSheet.Cells[i, 11] = Convert.ToString(dr["Latitude_Degrees"]); ExlWrkSheet.Cells[i, 12] = Convert.ToString(dr["Latitude_Minutes"]); ExlWrkSheet.Cells[i, 13] = Convert.ToString(dr["Latitude_Seconds"]); ExlWrkSheet.Cells[i, 14] = Convert.ToString(dr["LATITUDE_N_S"]); ExlWrkSheet.Cells[i, 15] = Convert.ToString(dr["Longitude_Degrees"]); ExlWrkSheet.Cells[i, 16] = Convert.ToString(dr["Longitude_Minutes"]); ExlWrkSheet.Cells[i, 17] = Convert.ToString(dr["Longitude_Seconds"]); ExlWrkSheet.Cells[i, 18] = Convert.ToString(dr["Longitude_E_W"]); ExlWrkSheet.Cells[i, 19] = Convert.ToString(dr["Vessel_Course"]); ExlWrkSheet.Cells[i, 20] = Convert.ToString(dr["Wind_Direction"]); ExlWrkSheet.Cells[i, 21] = Convert.ToString(dr["Wind_Force"]); ExlWrkSheet.Cells[i, 22] = Convert.ToString(dr["Sea_Direction"]); ExlWrkSheet.Cells[i, 23] = Convert.ToString(dr["Sea_Force"]); ExlWrkSheet.Cells[i, 24] = Convert.ToString(dr["Current_Direction"]); ExlWrkSheet.Cells[i, 25] = Convert.ToString(dr["Current_Speed"]); ExlWrkSheet.Cells[i, 26] = Convert.ToString(dr["Swell_Direction"]); ExlWrkSheet.Cells[i, 27] = Convert.ToString(dr["Swell_Height"]); ExlWrkSheet.Cells[i, 28] = Convert.ToString(dr["AirTemp"]); ExlWrkSheet.Cells[i, 29] = Convert.ToString(dr["AirPress"]); ExlWrkSheet.Cells[i, 30] = Convert.ToString(dr["AVERAGE_SPEED"]); ExlWrkSheet.Cells[i, 31] = Convert.ToString(dr["PassTime"]); ExlWrkSheet.Cells[i, 32] = Convert.ToString(dr["PassToGo"]); ExlWrkSheet.Cells[i, 33] = Convert.ToString(dr["DistSinceLastRep"]); ExlWrkSheet.Cells[i, 34] = Convert.ToString(dr["PassDist"]); ExlWrkSheet.Cells[i, 35] = Convert.ToString(dr["PassDistToGo"]); ExlWrkSheet.Cells[i, 36] = Convert.ToString(dr["CDFrmStormCenter"]); ExlWrkSheet.Cells[i, 37] = Convert.ToString(dr["TimeOfCPA"]); ExlWrkSheet.Cells[i, 38] = Convert.ToString(dr["NEXTPORT"]); ExlWrkSheet.Cells[i, 39] = Convert.ToString(dr["ETA_Next_Port"]); ExlWrkSheet.Cells[i, 40] = Convert.ToString(dr["FwdDraft"]); ExlWrkSheet.Cells[i, 41] = Convert.ToString(dr["MIDDRAFT"]); ExlWrkSheet.Cells[i, 42] = Convert.ToString(dr["AftDraft"]); ExlWrkSheet.Cells[i, 43] = Convert.ToString(dr["Trim"]); ExlWrkSheet.Cells[i, 44] = Convert.ToString(dr["GM"]); ExlWrkSheet.Cells[i, 45] = Convert.ToString(dr["Displacement"]); ExlWrkSheet.Cells[i, 46] = Convert.ToString(dr["SeaTemp"]); ExlWrkSheet.Cells[i, 47] = Convert.ToString(dr["EngRPM"]); ExlWrkSheet.Cells[i, 48] = Convert.ToString(dr["Slip"]); ExlWrkSheet.Cells[i, 49] = Convert.ToString(dr["ME_HOcons"]); ExlWrkSheet.Cells[i, 50] = Convert.ToString(dr["AE_HOcons"]); ExlWrkSheet.Cells[i, 51] = Convert.ToString(dr["Blr_HOcons"]); ExlWrkSheet.Cells[i, 52] = Convert.ToString(dr["ME_DOcons"]); ExlWrkSheet.Cells[i, 53] = Convert.ToString(dr["AE_DOcons"]); ExlWrkSheet.Cells[i, 54] = Convert.ToString(dr["Blr_DOcons"]); ExlWrkSheet.Cells[i, 55] = Convert.ToString(dr["HO_ROB"]); ExlWrkSheet.Cells[i, 56] = Convert.ToString(dr["DO_ROB"]); ExlWrkSheet.Cells[i, 57] = Convert.ToString(dr["MECC_ROB"]); ExlWrkSheet.Cells[i, 58] = Convert.ToString(dr["MECYL_ROB"]); ExlWrkSheet.Cells[i, 59] = Convert.ToString(dr["AECC_ROB"]); ExlWrkSheet.Cells[i, 60] = Convert.ToString(dr["FW_CONS"]); ExlWrkSheet.Cells[i, 61] = Convert.ToString(dr["FW_PROD"]); ExlWrkSheet.Cells[i, 62] = Convert.ToString(dr["FW_ROB"]); ExlWrkSheet.Cells[i, 63] = Convert.ToString(dr["RHRS_ME"]); ExlWrkSheet.Cells[i, 64] = Convert.ToString(dr["RHRS_AE1"]); ExlWrkSheet.Cells[i, 65] = Convert.ToString(dr["RHRS_AE2"]); ExlWrkSheet.Cells[i, 66] = Convert.ToString(dr["RHRS_AE3"]); ExlWrkSheet.Cells[i, 67] = Convert.ToString(dr["RHRS_AE4"]); ExlWrkSheet.Cells[i, 68] = Convert.ToString(dr["RHRS_SG"]); ExlWrkSheet.Cells[i, 69] = Convert.ToString(dr["RHRS_BLR"]); ExlWrkSheet.Cells[i, 70] = Convert.ToString(dr["ESP"]); ExlWrkSheet.Cells[i, 71] = Convert.ToString(dr["NOR"]); ExlWrkSheet.Cells[i, 72] = Convert.ToString(dr["ETB"]); ExlWrkSheet.Cells[i, 73] = Convert.ToString(dr["ETD"]); ExlWrkSheet.Cells[i, 74] = Convert.ToString(dr["Anchrd"]); ExlWrkSheet.Cells[i, 75] = Convert.ToString(dr["AnchrgNo"]); ExlWrkSheet.Cells[i, 76] = Convert.ToString(dr["ReasonsAnchg"]); ExlWrkSheet.Cells[i, 81] = Convert.ToString(dr["CntainrOnbd"]); ExlWrkSheet.Cells[i, 82] = Convert.ToString(dr["Reefer_Num"]); ExlWrkSheet.Cells[i, 83] = Convert.ToString(dr["CntainrDmgdSinceDep"]); ExlWrkSheet.Cells[i, 84] = Convert.ToString(dr["CntainrDmgdSinceDep_Remarks"]); ExlWrkSheet.Cells[i, 85] = Convert.ToString(dr["CntainrDmgdInPort"]); ExlWrkSheet.Cells[i, 86] = Convert.ToString(dr["CntainrDmgdInPort_Remarks"]); ExlWrkSheet.Cells[i, 87] = Convert.ToString(dr["Cntainr_Loaded"]); ExlWrkSheet.Cells[i, 88] = Convert.ToString(dr["Cntainr_Discharged"]); ExlWrkSheet.Cells[i, 89] = Convert.ToString(dr["remarks"]); i++; } string FileNameToSave = "NADReport_" + DateTime.Now.ToString("yy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + " " + DateTime.Now.ToString("hh") + DateTime.Now.ToString("mm") + DateTime.Now.ToString("ss") + ".xlsx"; ExlWrkBook.SaveAs(Server.MapPath("~/uploads/NADReports/") + FileNameToSave, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true); ResponseHelper.Redirect("~/uploads/NADReports/" + FileNameToSave, "blank", ""); // File.Delete(Server.MapPath("~/uploads/Purchase/") + "NADReport.xlsx"); } catch { } finally { ExlWrkBook.Close(null, null, null); ExlApp.Workbooks.Close(); ExlApp.Quit(); KillExcel(); } }
public void WriteExcell(DataTable dtReport) { CheckExcellProcesses(); string path = ""; path = Server.MapPath("RestHours_Export.xls"); ExlApp = new Microsoft.Office.Interop.Excel.Application(); try { ExlWrkBook = ExlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); ExlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExlWrkBook.ActiveSheet; int i = 2; foreach (DataRow dr in dtReport.Rows) { ExlWrkSheet.Cells[i, 1] = Convert.ToString(dr["Vessel_Name"]); ExlWrkSheet.Cells[i, 2] = Convert.ToString(dr["REST_HOURS_DATE"]); ExlWrkSheet.Cells[i, 3] = Convert.ToString(dr["Staff_Code"]); ExlWrkSheet.Cells[i, 4] = Convert.ToString(dr["Staff_Name"]); ExlWrkSheet.Cells[i, 5] = Convert.ToString(dr["Staff_rank_Code"]); ExlWrkSheet.Cells[i, 6] = Convert.ToString(dr["SHIPS_CLOCKED_HOURS"]); ExlWrkSheet.Cells[i, 7] = Convert.ToString(dr["WORKING_HOURS"]); ExlWrkSheet.Cells[i, 8] = Convert.ToString(dr["REST_HOURS"]); ExlWrkSheet.Cells[i, 9] = Convert.ToString(dr["Seafarer_Remarks"]); ExlWrkSheet.Cells[i, 10] = Convert.ToString(dr["Verifier_Remarks"]); ExlWrkSheet.Cells[i, 61] = Convert.ToString(dr["REST_HOURS_ANY24"]); if (dr["WH_0000_0030"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("M" + i.ToString(), "M" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0030_0100"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("N" + i.ToString(), "N" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0100_0130"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("O" + i.ToString(), "O" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0130_0200"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("P" + i.ToString(), "P" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0200_0230"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("Q" + i.ToString(), "Q" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0230_0300"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("R" + i.ToString(), "R" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0300_0330"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("S" + i.ToString(), "S" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0330_0400"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("T" + i.ToString(), "T" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0400_0430"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("U" + i.ToString(), "U" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0430_0500"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("V" + i.ToString(), "V" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0500_0530"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("W" + i.ToString(), "W" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0530_0600"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("X" + i.ToString(), "X" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0600_0630"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("Y" + i.ToString(), "Y" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0630_0700"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("Z" + i.ToString(), "Z" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0700_0730"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AA" + i.ToString(), "AA" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0730_0800"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AB" + i.ToString(), "AB" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0800_0830"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AC" + i.ToString(), "AC" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0830_0900"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AD" + i.ToString(), "AD" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0900_0930"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AE" + i.ToString(), "AE" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_0930_1000"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AF" + i.ToString(), "AF" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1000_1030"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AG" + i.ToString(), "AG" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1030_1100"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AH" + i.ToString(), "AH" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1100_1130"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AI" + i.ToString(), "AI" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1130_1200"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AJ" + i.ToString(), "AJ" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1200_1230"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AK" + i.ToString(), "AK" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1230_1300"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AL" + i.ToString(), "AL" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1300_1330"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AM" + i.ToString(), "AM" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1330_1400"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AN" + i.ToString(), "AN" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } ////-------------- if (dr["WH_1400_1430"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AO" + i.ToString(), "AO" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1430_1500"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AP" + i.ToString(), "AP" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1500_1530"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AQ" + i.ToString(), "AQ" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1530_1600"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AR" + i.ToString(), "AR" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1600_1630"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AS" + i.ToString(), "AS" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1630_1700"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AT" + i.ToString(), "AT" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1700_1730"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AU" + i.ToString(), "AU" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1730_1800"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AV" + i.ToString(), "AV" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1800_1830"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AW" + i.ToString(), "AW" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1830_1900"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AX" + i.ToString(), "AX" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1900_1930"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AY" + i.ToString(), "AY" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_1930_2000"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("AZ" + i.ToString(), "AZ" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2000_2030"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BA" + i.ToString(), "BA" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2030_2100"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BB" + i.ToString(), "BB" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2100_2130"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BC" + i.ToString(), "BC" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2130_2200"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BD" + i.ToString(), "BD" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2200_2230"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BE" + i.ToString(), "BE" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2230_2300"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BF" + i.ToString(), "BF" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } if (dr["WH_2300_2330"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BG" + i.ToString(), "BG" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; //System.Drawing.Color.Orange; } if (dr["WH_2330_2400"].ToString() == "1") { Microsoft.Office.Interop.Excel.Range range = ExlWrkSheet.get_Range("BH" + i.ToString(), "BH" + i.ToString()); range.Cells.Interior.Color = System.Drawing.Color.Orange; } i++; } string FileNameToSave = "RestHours_" + DateTime.Now.ToString("yy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + " " + DateTime.Now.ToString("hh") + DateTime.Now.ToString("mm") + DateTime.Now.ToString("ss") + ".xls"; ExlWrkBook.SaveAs(Server.MapPath("~/uploads/Purchase/") + FileNameToSave, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true); ResponseHelper.Redirect("~/uploads/Purchase/" + FileNameToSave, "blank", ""); } catch { } finally { ExlWrkBook.Close(null, null, null); ExlApp.Workbooks.Close(); ExlApp.Quit(); KillExcel(); } }
protected void btnUpload_Click(object sender, EventArgs e) { if (fileUpload.HasFile) { System.IO.DirectoryInfo di = new DirectoryInfo(Server.MapPath("~/Importfile/")); foreach (FileInfo file in di.GetFiles()) { file.Delete(); } string path = string.Concat(Server.MapPath("~/Importfile/" + fileUpload.FileName)); fileUpload.SaveAs(path); Microsoft.Office.Interop.Excel.Application appExcel; Microsoft.Office.Interop.Excel.Workbook workbook; Microsoft.Office.Interop.Excel.Range range; Microsoft.Office.Interop.Excel._Worksheet worksheet; appExcel = new Microsoft.Office.Interop.Excel.Application(); workbook = appExcel.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1]; range = worksheet.UsedRange; int rowCount = range.Rows.Count; int colCount = range.Columns.Count; System.Data.DataTable dt = new System.Data.DataTable(); dt.TableName = "patinet"; dt.Columns.Add("PatientIE_ID", typeof(int)); dt.Columns.Add("FirstName", typeof(string)); dt.Columns.Add("LastName", typeof(string)); dt.Columns.Add("Compensation", typeof(string)); dt.Columns.Add("InsCo", typeof(string)); dt.Columns.Add("Adjuster", typeof(string)); dt.Columns.Add("WCBGroup", typeof(string)); dt.Columns.Add("ClaimNumber", typeof(string)); dt.Columns.Add("policy_no", typeof(string)); dt.Columns.Add("DOA", typeof(string)); dt.Columns.Add("DOB", typeof(string)); for (int Rnum = 1; Rnum <= rowCount; Rnum++) { DataRow dr = dt.NewRow(); //Reading Each Column value From sheet to datatable Colunms for (int Cnum = 1; Cnum <= colCount; Cnum++) { //dr[Cnum - 1] = (range.Cells[Rnum, Cnum]).value.ToString(); dr[Cnum - 1] = Convert.ToString((range.Cells[Rnum, Cnum] as Microsoft.Office.Interop.Excel.Range).Value2); } dt.Rows.Add(dr); // adding Row into DataTable dt.AcceptChanges(); } workbook.Close(true); appExcel.Quit(); string constr = @"Data Source=MOULICK-PC\SQLEXPRESS;Initial Catalog=dbPainTraxXUAT;Integrated Security=True"; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("usp_patient_Export")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.Parameters.AddWithValue("@patient", dt); con.Open(); cmd.ExecuteNonQuery(); con.Close(); lblmsg.Text = "Sucessufully Imported"; } } } }
public void ConvertToExcel(DataSet ds, string strFilenamewithpath) { FnWriteLogFile_Log("", "ConvertToExcel1"); //Instance reference for Excel Application Microsoft.Office.Interop.Excel.Application objXL = null; //Workbook refrence Microsoft.Office.Interop.Excel.Workbook objWB = null; objXL = new Microsoft.Office.Interop.Excel.Application(); // Find the Excel Process Id (ath the end, you kill him int id; GetWindowThreadProcessId(objXL.Hwnd, out id); Process excelProcess = Process.GetProcessById(id); FnWriteLogFile_Log("", "ConvertToExcel3"); objWB = objXL.Workbooks.Open(Server.MapPath("~/Uploads/") + ds.Tables[0].Rows[0][0].ToString());// Server.MapPath("\Log\Feet on Street Report.xlsx"); try { //Instancing Excel using COM services //Adding WorkBook //FnWriteLogFile_Log("", "objXL.Workbooks.Add(1)"); //objWB = objXL.Workbooks.Add(1);//ds.Tables.Count // Server.MapPath("~/ExcelFile/Log/myexcel.xlsx") + "" //Variable to keep sheet count int sheetcount = 1; //Do I need to explain this ??? If yes please close my website and learn abc of .net . //oSheet = oWorkBook.Sheets.Item(1) FnWriteLogFile_Log("", "add sheet"); Microsoft.Office.Interop.Excel.Worksheet objSHT = null; //foreach (System.Data.DataTable dt in ds.Tables) objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[4]; DataTable dt = ds.Tables[2]; //Adding sheet to workbook for each datatable //Naming sheet as SheetData1.SheetData2 etc.... FnWriteLogFile_Log("", "Naming sheet"); objSHT.Name = ds.Tables[1].Rows[0][0].ToString(); for (int i = 0; i < dt.Columns.Count; i++) { objSHT.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString(); objSHT.Range[objSHT.Cells[1, i + 1], objSHT.Cells[1, i + 1]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue); //# d9d9d9 //objSHT.Cells[2, i + 1].Interior.Color = System.Drawing.ColorTranslator.FromHtml("#d9d9d9"); //# d9d9d9 //objSHT.Cells[1, i + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver); } Int32 rows; Int32 columns; rows = dt.Rows.Count; columns = dt.Columns.Count; var Data = new object[rows - 1 + 1, columns - 1 + 1]; for (var i = 0; i <= dt.Rows.Count - 1; i += 1) { for (var j = 0; j <= dt.Columns.Count - 1; j += 1) { Data[i, j] = dt.Rows[i][j]; } } var startCell = objSHT.Cells[2, 1]; var endCell = objSHT.Cells[1 + dt.Rows.Count, dt.Columns.Count]; var writeRange = objSHT.Range[startCell, endCell]; writeRange.Value = Data; //Incrementing sheet count //Saving the work book objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[1]; objSHT.Activate(); //objSHT.Select(Type.Missing); objWB.Saved = true; objWB.SaveCopyAs(Server.MapPath("~/Uploads/New/") + ds.Tables[0].Rows[0][0].ToString()); //Closing work book FnWriteLogFile_Log("", "objWB.Close()"); objWB.Close(); //Closing excel application objXL.Quit(); //Marshal.ReleaseComObject(objSHT); FnWriteLogFile_Log("", "set null"); objSHT = null; objWB = null; objXL = null; GC.Collect(); GC.WaitForPendingFinalizers(); if (objSHT != null) { Marshal.ReleaseComObject(objSHT); } if (objWB != null) { Marshal.ReleaseComObject(objWB); } if (objXL != null) { Marshal.ReleaseComObject(objXL); } FnWriteLogFile_Log("", "complete"); //HttpContext.Current.Response.End(); } catch (Exception ex) { FnWriteLogFile_Log("", "Error:" + ex.StackTrace.ToString()); if (objWB != null) { objWB.Close(); //Closing excel application objXL.Quit(); //Marshal.ReleaseComObject(objSHT); // objSHT = null; objWB = null; objXL = null; GC.Collect(); GC.WaitForPendingFinalizers(); //if (objSHT != null) //{ // Marshal.ReleaseComObject(objSHT); //} if (objWB != null) { Marshal.ReleaseComObject(objWB); } if (objXL != null) { Marshal.ReleaseComObject(objXL); } //Response.Write("Illegal permission"); } } finally { FnWriteLogFile_Log("", "Check HasExited"); if (!excelProcess.HasExited) { FnWriteLogFile_Log("", "kill process"); excelProcess.Kill(); FnWriteLogFile_Log("", "end kill process"); } } }
//Used to check the plagarism protected void plagarismButton_Click(object sender, EventArgs e) { System.Data.DataTable dt = null; MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); try { MySqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select a.assignmentName, a.assignmentId, s.studentId, u.userName, s.filePath From studentassignments s join assignments a on a.assignmentId = s.assignmentId join user u on s.studentId = u.id where s.assignmentId = @assignID"; cmd.Parameters.AddWithValue("@assignID", assignID); MySqlDataAdapter adap = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); adap.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { Response.Redirect("~/Error"); } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } //Create excel sheet Microsoft.Office.Interop.Excel.Application application = null; Microsoft.Office.Interop.Excel._Workbook workbook = null; Microsoft.Office.Interop.Excel._Worksheet worksheet = null; Microsoft.Office.Interop.Excel.Range range = null; object misvalue = System.Reflection.Missing.Value; try { //Start Excel and get Application object. application = new Microsoft.Office.Interop.Excel.Application(); application.Visible = true; //Get a new workbook. workbook = (Microsoft.Office.Interop.Excel._Workbook)(application.Workbooks.Add("")); worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet; if (dt.Rows.Count > 1) { for (int i = 0; i < dt.Rows.Count; i++) { worksheet.Cells[1, i + 2] = dt.Rows[i][3].ToString().Split('@')[0]; worksheet.Cells[i + 2, 1] = dt.Rows[i][3].ToString().Split('@')[0]; for (int j = 0; j < dt.Rows.Count; j++) { if (i != j) { string assignmetnId = dt.Rows[i][1].ToString(); string iStudentId = dt.Rows[i][2].ToString(); string iUserName = dt.Rows[i][3].ToString(); string iFilePath = dt.Rows[i][4].ToString(); string jStudentId = dt.Rows[j][2].ToString(); string jUserName = dt.Rows[j][3].ToString(); string jFilePath = dt.Rows[j][4].ToString(); string file1 = GetFileContent(iFilePath, iStudentId, assignmetnId); string file2 = GetFileContent(jFilePath, jStudentId, assignmetnId);; Plagarism plagarism = new Plagarism(); //This is used to calculate plagarism percentage. double percentage = plagarism.CalculateSimilarity(file1, file2); worksheet.Cells[i + 2, j + 2] = percentage; if (percentage == 1) { worksheet.Cells[i + 2, j + 2] = 100; Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 2, j + 2]; rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } if (percentage > 90) { Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 2, j + 2]; rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); } if (percentage == 100) { Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 2, j + 2]; rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } } } } string destination = assignmentName + "_Plagarism.xlsx"; //Saving the excel sheet workbook.SaveAs(destination, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(); application.Quit(); } } catch { Response.Redirect("~/Error"); } finally { if (range != null) { Marshal.ReleaseComObject(range); } if (worksheet != null) { Marshal.ReleaseComObject(worksheet); } if (workbook != null) { Marshal.ReleaseComObject(workbook); } if (application != null) { Marshal.ReleaseComObject(application); } } }