//写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);
}
}
}
