protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
string _QuotationCode = "";//DDLSupplier.SelectedValue.Split(new char[] { '~' })[1];
if (FileUpload1.HasFile)
{
//FileUpload1.
string strLocalPath = FileUpload1.PostedFile.FileName;
string FileName = Path.GetFileName(strLocalPath);
//FileUpload1.PostedFile.SaveAs(Server.MapPath("TempUpload\\" + FileName));
string strPath = strLocalPath;
//string strPath = Server.MapPath("TempUpload\\" + FileName).ToString();
// ViewState["strPath"] = FileUpload1.PostedFile.FileName;
ViewState["strPath"] = strPath;
//string[] arrfn = FileName.Split('\\');
// string strPath = Server.MapPath("SendRFQ") + "\\" + arrfn[arrfn.Length - 1];
ExlApp = new Microsoft.Office.Interop.Excel.Application();
ExlWrkBook = ExlApp.Workbooks.Open(strPath,
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;
//DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable("LIB_VesselGA");
dt.Columns.Add("Path_ID", typeof(string));
dt.Columns.Add("Object_ID", typeof(string));
dt.Columns.Add("Image_Path", typeof(string));
dt.Columns.Add("SVG_Path", typeof(string));
dt.Columns.Add("Is_GA", typeof(string));
dt.Columns.Add("Parent_Path_ID", typeof(string));
dt.Columns.Add("Path_Name", typeof(string));
dt.Columns.Add("Active_Status", typeof(string));
dt.Columns.Add("Vessel_TypeID", typeof(string));
dt.AcceptChanges();
DataRow dr = dt.NewRow();
int i = 2;
while (((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 1]).Value2 != null)
{
DataRow drNew = dt.NewRow();
drNew["Path_ID"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 1]).Value2);
drNew["Object_ID"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 2]).Value2);
drNew["Image_Path"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 3]).Value2);
drNew["SVG_Path"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 4]).Value2);
drNew["Is_GA"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 5]).Value2);
drNew["Parent_Path_ID"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 6]).Value2);
drNew["Path_Name"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 11]).Value2);
drNew["Active_Status"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 10]).Value2);
drNew["Vessel_TypeID"] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExlWrkSheet.Cells[i, 12]).Value2);
dt.Rows.Add(drNew);
i = i + 1;
}
dt.AcceptChanges();
if (dt.Rows.Count > 0)
{
int k = objSURVBLL.InsertImport_VesselGA(dt);
}
}
else
{
String msg = String.Format("alert('Please select file to import.');");
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "msg", msg, true);
//lblErrorMsg.Text = "There is no file to upload.";
}
}
catch (Exception ex)
{
String msg = String.Format("alert('The uploaded file do not belong to the selected supplier');");
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "msg", msg, true);
//lblErrorMsg.Text = ex.ToString();
}
finally
{
}
}
/// <summary>
/// 打开已有EXCEL并写入数据
/// </summary>
/// <param name="dataSet"></param>
/// <param name="savePath"></param>
public void ToExcelTwo(DataSet dataSet, string excelTempPath, string savePath)
{
DataTable ds = dataSet.Tables[0];
int rowNumber = ds.Rows.Count;
int columnNumber = ds.Columns.Count;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wbks = excel.Workbooks.Open(excelTempPath);
Microsoft.Office.Interop.Excel.Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)wbks.Worksheets[1];
excel.Application.Workbooks.Add(true);
excel.Visible = false;//是否打开该Excel文件
List <uint> fieldPlaceList;
List <Dictionary <uint, string[]> > combineRowList = GetCombineList(dataSet, out fieldPlaceList);
//数据合并
List <List <StringBuilder> > combineList = new List <List <StringBuilder> >();
for (int i = 0; i < combineRowList.Count; i++)
{
combineList.Add(DataCombine(dataSet, combineRowList[i]));
}
//填充数据
for (int c = 0; c < rowNumber; c++)
{
for (int j = 0; j < ds.Columns.Count + combineList.Count; j++)//一行中的每列
{
if (j >= ds.Columns.Count)
{
int index = j - ds.Columns.Count;
switch (c)
{
case 0:
_wsh.Cells[c + 1, j + 1] = "合并字段";
break;
case 1:
_wsh.Cells[c + 1, j + 1] = "@" + combineList[index][2].ToString().Replace('&', '#') + " " + combineList[index][3].ToString().Replace("_", " ");
break;
case 2:
_wsh.Cells[c + 1, j + 1] = "uint#" + combineList[index][3].ToString().Replace("#", "");
break;
case 3:
_wsh.Cells[c + 1, j + 1] = combineList[index][3].ToString().Replace("#", "");
break;
default:
_wsh.Cells[c + 1, j + 1] = combineList[index][c].ToString();
break;
}
}
else
{
//每个单元格内容，加到StringBuilder中
if (fieldPlaceList.Contains((uint)j) && c == 0)
{
_wsh.Cells[c + 1, j + 1] = ds.Rows[c].ItemArray[j] + "#合并";
}
}
}
}
ClosePro(savePath, excel, wbks);
}
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 ExcelHelper()
{
this.XlApp = new Microsoft.Office.Interop.Excel.Application();
this.XlApp.Visible = true;
this.XlApp.DisplayAlerts = false;
}
// 完全读取 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();
}
}
//写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
{
int rowIndex = 1;
int colIndex = 0;
int sizeofsheet = sheets.Count;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
string a = worksheet.Name;
int countrow = dstemp.Tables[excelsheetname.ToString()].Rows.Count;
var RowAll = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[countrow + 1, 9]);
RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
System.Data.DataTable table = dstemp.Tables[excelsheetname.ToString()];
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();
}
}
public void StandingsCalc()
{
MySqlCommand comm5 = new MySqlCommand("Truncate fantasy_race.raceresultsall", new MySqlConnection("server=localhost;uid=root;pwd=vvo084;"));
comm5.Connection.Open();
comm5.ExecuteNonQuery();
comm5.Connection.Close();
MySqlCommand comm1 = new MySqlCommand("Truncate fantasy_race.leaderboard", new MySqlConnection("server=localhost;uid=root;pwd=vvo084;"));
comm1.Connection.Open();
comm1.ExecuteNonQuery();
comm1.Connection.Close();
MySqlDataAdapter dar = new MySqlDataAdapter("select * from fantasy_race.raceresultsf1indy", "server=localhost;uid=root;pwd=vvo084;");
MySqlDataAdapter dat = new MySqlDataAdapter("select * from fantasy_race.raceresultsnascara", "server=localhost;uid=root;pwd=vvo084;");
MySqlDataAdapter das = new MySqlDataAdapter("select * from fantasy_race.raceresultsnascarb", "server=localhost;uid=root;pwd=vvo084;");
DataTable tb = new DataTable();
dar.Fill(tb);
das.Fill(tb);
dat.Fill(tb);
foreach (DataRow drs in tb.Rows)
{
string week = drs["Week"].ToString();
string Circut = drs["Circut"].ToString();
string Team = drs["Team"].ToString();
string driver = drs["Driver"].ToString();
string position = drs["Position"].ToString();
string points = drs["Points"].ToString();
string poll = drs["Poll"].ToString();
string Lapslead = drs["LapsLead"].ToString();
MySqlCommand comm = new MySqlCommand("insert into fantasy_race.raceresultsall(Week,Circut,Team,Driver,Position,Points,Poll,LapsLead) values('" + week + "', '" + Circut + "', '" + Team + "', '" + driver + "', '" + position + "', '" + points + "', '" + poll + "', '" + Lapslead + "')", new MySqlConnection("server=localhost;uid=root;pwd=vvo084;"));
comm.Connection.Open();
comm.ExecuteNonQuery();
comm.Connection.Close();
}
MySqlDataAdapter da = new MySqlDataAdapter("select * from fantasy_race.teams", "server=localhost;uid=root;pwd=vvo084;");
DataTable t = new DataTable();
da.Fill(t);
foreach (DataRow dr in t.Rows)
{
string teams = dr["Team"].ToString();
string wins = Wincount(teams);
string poles = pollcount(teams);
string laps = LapsLeadcount(teams);
string five = topfive(teams);
string ten = topten(teams);
string dnfs = DNFfunction(teams);
string pointbehind = pointsbehind(teams).ToString;
string totals = totalpoints(teams).ToString;
MySqlCommand comm = new MySqlCommand("insert into fantasy_race.leaderboard(Team,Behind,Wins,Poles,TopTen,TopFive,LapsLead,DNF,Points) values('" + teams + "', '" + pointbehind + "', '" + wins + "', '" + poles + "', '" + ten + "', '" + five + "', '" + laps + "', '" + dnfs + "', '" + totals + "')", new MySqlConnection("server=localhost;uid=root;pwd=vvo084;"));
comm.Connection.Open();
comm.ExecuteNonQuery();
comm.Connection.Close();
}
MySqlDataAdapter myda = new MySqlDataAdapter("select Team,Points,Behind,Wins,Poles,TopTen,TopFive,LapsLead,DNF from fantasy_race.leaderboard order by Points desc", "server=localhost;uid=root;pwd=vvo084;");
DataTable lbt = new DataTable();
myda.Fill(lbt);
try
{
StreamWriter sw = new StreamWriter("c:\\Standings.csv", false);
//Write line 1 for column names
string columnnames = "";
foreach (DataColumn dc in lbt.Columns)
{
columnnames += "\"" + dc.ColumnName + "\",";
}
columnnames = columnnames.TrimEnd(',');
sw.WriteLine(columnnames);
//Write out the rows
foreach (DataRow drs in lbt.Rows)
{
string row = "";
foreach (DataColumn dc in lbt.Columns)
{
if ((drs[dc.ColumnName]) is byte[])
{
row += "\"" + getstring(drs[dc.ColumnName]) + "\",";
}
else
{
row += "\"" + drs[dc.ColumnName].ToString() + "\",";
}
}
row = row.TrimEnd(',');
sw.WriteLine(row);
}
sw.Close();
string msg = null;
string title = null;
MsgBoxStyle style = default(MsgBoxStyle);
MsgBoxResult response = default(MsgBoxResult);
msg = "Your file is done. Would you like to open?";
// Define message.
style = MsgBoxStyle.YesNo;
title = "MsgBox";
// Define title.
//Display message.
response = Interaction.MsgBox(msg, style, title);
if (response == MsgBoxResult.Yes)
{
Microsoft.Office.Interop.Excel.Application excel = default(Microsoft.Office.Interop.Excel.Application);
Microsoft.Office.Interop.Excel.Workbook wb = default(Microsoft.Office.Interop.Excel.Workbook);
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open("c:\\\\Standings.csv");
excel.Visible = true;
wb.Activate();
}
catch (Exception ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
}
if (response == MsgBoxResult.Yes)
{
return;
}
}
catch
{
string msg = null;
string title = null;
MsgBoxStyle style = default(MsgBoxStyle);
MsgBoxResult response = default(MsgBoxResult);
msg = "file must be colsed";
// Define message.
style = MsgBoxStyle.DefaultButton1;
title = "MsgBox";
// Define title.
//Display message.
response = Interaction.MsgBox(msg, style, title);
if (response == MsgBoxResult.Ok)
{
return;
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
DataTable data;
DataTable dataSheets = new DataTable();
DataTable filterTable;
DataTable ftable = new DataTable();
int count;
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excelObj = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
if (fileName != null && path != null)
{
if (fileExtension.Equals(".xlsx") || fileExtension.Equals(".xls"))
{
try
{
excelObj = new Microsoft.Office.Interop.Excel.Application();
workbook = excelObj.Workbooks.Open(path + fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
ArrayList sheetname = new ArrayList();
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in workbook.Sheets)
{
//sheetname.Add(sheet.Name);
data = ReadExcelFile(sheet.Name, path + fileName);
dataSheets.Merge(data);
}
//data = ReadExcelFile("Sheet1", path + fileName);
filterTable = FilterFlightInfoData(dataSheets);
ftable = filterTable;
//count = ftable.Rows.Count;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
else if (fileExtension.Equals(".csv"))
{
try
{
string SaveLocation = path + fileName;
DataTable csvData = GetDataTableFromCSVFile(SaveLocation);
filterTable = FilterFlightInfoData(csvData);
ftable = filterTable;
//count = filterTable.Rows.Count;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
try
{
count = ftable.Rows.Count;
if (count > 0)
{
BLL.Flight.BulkInsertFlightInfo(ftable, getConstr.ConStrCMS);
if (workbook != null)
{
workbook.Close(false);
excelObj.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelObj);
}
if (Directory.Exists(path))
{
// Directory.Delete(SaveLocation);
File.Delete(path + "\\" + fileName);
}
string script = "<script>alert('File successfully imported!.')</" + "script>";
ClientScript.RegisterStartupScript(GetType(), "Alert", script);
RadGrid2.DataSource = GetFlightDetails();
RadGrid2.Rebind();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
//if (FileUploadFlightInfo.HasFile)
//{
// string extension = System.IO.Path.GetExtension(FileUploadFlightInfo.FileName); // extension file
// if (extension.Equals(".csv"))
// {
// try
// {
// string folderPath = Server.MapPath("~/Upload");
// //Check whether Directory (Folder) exists.
// if (!Directory.Exists(folderPath))
// {
// //If Directory (Folder) does not exists. Create it.
// Directory.CreateDirectory(folderPath);
// }
// string SaveLocation = folderPath + "\\" + FileUploadFlightInfo.FileName;
// //string SaveLocation = folderPath + Path.GetFileName(FileUploadFlightInfo.FileName);
// // FileUploadFlightInfo.SaveAs(SaveLocation);
// //Save the File to the Directory (Folder).
// FileUploadFlightInfo.SaveAs(SaveLocation);
// DataTable csvData = GetDataTableFromCSVFile(SaveLocation);
// DataTable filterTable = FilterFlightInfoData(csvData);
// int count = filterTable.Rows.Count;
// if(count > 0)
// {
// BLL.Flight.BulkInsertFlightInfo(filterTable, getConstr.ConStrCMS);
// //string script = "<script>CloseOnReload()</" + "script>";
// //ClientScript.RegisterStartupScript(this.GetType(), "CloseOnReload", script);
// if (Directory.Exists(folderPath))
// {
// // Directory.Delete(SaveLocation);
// File.Delete(SaveLocation);
// }
// string script = "<script>alert('File successfully imported!.')</" + "script>";
// ClientScript.RegisterStartupScript(GetType(), "Alert", script);
// RadGrid2.DataSource = GetFlightDetails();
// RadGrid2.Rebind();
// }
// }
// catch(Exception ex)
// {
// Console.WriteLine(ex);
// }
// }
// else
// {
// }
//}
}
protected void Button2_Click(object sender, EventArgs e)
{
DropTable();
CREATTable();
string SQLH = "SELECT * FROM tbl_defn WHERE grouptype = '" + "HTC" + "' ";
SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
cn.Open();
SqlCommand cmd = new SqlCommand(SQLH, cn);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string SQLIN = "INSERT INTO tbl_TempReport(grouptype,code,descrip) VALUES(@grouptype,@code,@descrip) ";
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdINS = new SqlCommand(SQLIN, cn);
cmdINS.Parameters.AddWithValue("@grouptype", SqlDbType.NVarChar).Value = r["grouptype"].ToString();
cmdINS.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = r["code"].ToString();
cmdINS.Parameters.AddWithValue("@descrip", SqlDbType.NVarChar).Value = r["description"].ToString();
cmdINS.ExecuteNonQuery();
}
DataTable dt = GetDatafromDatabase(); //===== returns a DataTable
string SQLT = "SELECT * FROM tbl_TempReport";
string colname;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCommand cmdT = new SqlCommand(SQLT, cn);
SqlDataReader rt = cmdT.ExecuteReader();
while (rt.Read())
{
// For each row, print the values of each column.
foreach (DataColumn column in dt.Columns)
{
foreach (DataRow row in dt.Rows)
{
colname = column.ColumnName.ToString();
//if (colname == "ID")
//{
// // break;
// rt.NextResult();
//}
//else
//{
string m_Code = rt["code"].ToString();
if (m_Code == colname)
{
//insert into tbl_TempReport to match the appropriate column
string SQLP = "UPDATE tbl_TempReport SET VALUEP = @VALUEP WHERE code = @code";
SqlCommand cmdp = new SqlCommand(SQLP, cn);
cmdp.Parameters.AddWithValue("@VALUEP", SqlDbType.Int).Value = row[column].ToString();
cmdp.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = rt["code"].ToString();
cmdp.ExecuteNonQuery();
}
//ListBox1.Items.Add(row[column.ColumnName].ToString());
//}
//======
}
}
}
//========== Write to Excel
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWBK;
Microsoft.Office.Interop.Excel.Worksheet xlWS;
object misValue = System.Reflection.Missing.Value;
try
{
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
string SQLL = "SELECT * FROM tbl_TempReport ";
SqlCommand cmd1 = new SqlCommand(SQLL, cn);
SqlDataAdapter DA = new SqlDataAdapter(cmd1);
cmd1.Connection = cn;
DA.SelectCommand = cmd1;
DA.Fill(ds);
//=====================================================
string directoryPath = Server.MapPath(string.Format("~/{0}/", "XLS"));
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWBK = xlApp.Workbooks.Add(misValue);
xlWS = (Microsoft.Office.Interop.Excel.Worksheet)xlWBK.Worksheets.get_Item(1);
xlApp.Visible = true;
xlWS.Columns.Font.Bold = true;
for (int l = 0; l <= ds.Tables[0].Columns.Count - 1; l++)
{
xlWS.Columns.AutoFit();
xlWS.Columns.Font.Bold = true;
xlWS.Cells[1, l + 1] = ds.Tables[0].Columns[l].ColumnName.Trim();
}
xlWS.Columns.Font.Bold = false;
for (int i = 1; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
var data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWS.Columns.AutoFit();
xlWS.Cells[i + 1, j + 1] = data;
}
}
//string path = @"C:\EMR_INTERFACE\XLS\"; // HttpContext.Current.Server.MapPath("/");
xlWBK.SaveAs(directoryPath + "HTC" + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlWBK.Close(true, misValue, misValue);
//xlApp.Quit();
cn.Close();
//releaseObject(xlWS);
//releaseObject(xlWBK);
//releaseObject(xlApp);
}
catch (Exception ex)
{
}//======== Excel
}
public void ExportToExcel(string Type, string Head, DataGrid Grid, DataRowView GridRow, int RowNum)
{
try
{
DataTable dt;
if (Type == "One")
{
dt = DataGridRow_To_DataTable(Grid, GridRow, Head, RowNum);
}
else
{
dt = DataGrid_To_DataTable(Grid, Head);
}
if ((dt.Columns.Count > 0))
{
Microsoft.Office.Interop.Excel.Application oExcel;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
oExcel = new Microsoft.Office.Interop.Excel.Application();
oExcel.Visible = false;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(System.Type.Missing));
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.ActiveSheet;
oSheet.Range["B2"].Value = Head;
oSheet.Range["B2"].Font.Bold = true;
if (Type == "One")
{
for (int s = 0; s <= dt.Rows.Count - 1; s++)
{
oSheet.Range["B" + (4 + s)].Value = Convert.ToString(dt.Rows[s][0]);
oSheet.Range["C" + (4 + s)].Value = Convert.ToString(dt.Rows[s][1]);
}
oSheet.Columns.AutoFit();
}
else if (Type == "Many")
{
object[,] DataArrayHead = new object[1, Grid.Columns.Count];
for (int s = 0; s <= Grid.Columns.Count - 1; s++)
{
DataArrayHead[0, s] = Grid.Columns[s].Header;
}
oSheet.Range["B4"].Resize[1, Grid.Columns.Count].Value = DataArrayHead;
oSheet.Range["B4"].Resize[1, Grid.Columns.Count].Font.Bold = true;
object[,] DataArray = new object[Grid.Items.Count, Grid.Columns.Count];
for (int r = 0; r <= dt.Rows.Count - 1; r++)
{
for (int s = 0; s <= dt.Columns.Count - 1; s++)
{
DataArray[r, s] = dt.Rows[r][s];
}
}
oSheet.Range["B5"].Resize[Grid.Items.Count, Grid.Columns.Count].Value = DataArray;
oSheet.Columns.AutoFit();
}
oExcel.Visible = true;
oExcel = null;
oSheet = null;
}
}
catch
{
}
}
//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);
}
}
}
