public string getExcelReport(ExcelAction req, string ChapterName)
{
System.Data.OleDb.OleDbConnection conn = null;
string concateFile = "";
try
{
string filename = "";
concateFile = "\\datafiles\\Dashboard\\IssueDetails" + HttpContext.User.Identity.Name + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString() + ".xls";
var oldfilename = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\datafiles\\IssueDetails.xlsx";
filename = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + concateFile;
System.IO.File.Copy(oldfilename, filename, true);
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\""
//Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\""
conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties=\"Excel 12.0 Xml;HDR=NO\";");
conn.Open();
int i = 1;
foreach (var item in req.Items)
{
var strsql = "insert into [Sheet1$A" + (i) + ":K" + (i) + "] (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11)values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format(strsql,
item.RequestNo,
// item.IssueDetail1,
"Aaliya",
item.Comments,
item.Status,
item.ReportedByName,
item.Department,
item.Location,
item.PendingWithEmail,
item.ResponsibleWithEmail,
item.PendingSince,
item.LoggedDate
);
var rowcount = cmd.ExecuteNonQuery();
i++;
}
}
catch (Exception ex)
{
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
var filePath = concateFile;
return(filePath);
}
public void ReadExcelCellRow3ColumnColumnCount()
{
//Arrange
ExcelAction x = new ExcelAction();
GingerAction GA = new GingerAction("Excel");
//Act
x.ReadExcelCell(ref GA, EXCEL_FILE_NAME, "Sheet1", "#3", "#B");
//Assert
// Assert.AreEqual(1, GA.Output.Values.Count);
}
public void ReadExcelCellRow3ColumnB()
{
//Arrange
ExcelAction x = new ExcelAction();
GingerAction GA = new GingerAction("Excel");
//Act
x.ReadExcelCell(ref GA, EXCEL_FILE_NAME, "Sheet1", "#3", "#B");
//Assert
// Assert.AreEqual("Moshe", GA.Output.Values[0].ValueString, "Row 3 Col B = Moshe");
}
public static void QueueAsMacro(ExcelAction action)
{
SendOrPostCallback callback = delegate { action(); };
QueueAsMacro(callback, null);
}
public static void QueueAsMacro(ExcelAction action)
{
QueueAsMacro(delegate { action(); }, null);
}
public override void QueueAsMacro(ExcelAction action)
{
ExcelAsyncUtil.QueueAsMacro(action);
}
public static void QueueAsMacro(ExcelAction action)
{
QueueAsMacro(delegate { action(); }, null);
}
public virtual void QueueAsMacro(ExcelAction action)
{
throw new NotImplementedException();
}
public static void QueueAsMacro(ExcelAction action)
{
SendOrPostCallback callback = delegate { action(); };
QueueAsMacro(callback, null);
}
public static void ExecuteOnForegroundThread(ExcelAction action)
{
ExcelAsyncUtil.QueueAsMacro(action);
}