public singleExcel createExcel(string fileLocation)
{
var thisExcel = new singleExcel();
thisExcel.xlApp = new Excel.Application();
// var thisExcel.xlApp = ExcelInteropService.GetExcelInterop(thisExcel);
thisExcel.xlWorkbook = thisExcel.xlApp.Workbooks.Open(fileLocation);
thisExcel.xlApp.Visible = false;
thisExcel.xlApp.DisplayAlerts = false;
bool failed = false;
do
{
try
{
thisExcel.xlWorkbook.DoNotPromptForConvert = true;
thisExcel.xlWorkbook.CheckCompatibility = false;
thisExcel.xlWorkbook.Unprotect();
failed = false;
}
catch (System.Runtime.InteropServices.COMException e)
{
failed = true;
}
} while (failed);
return(thisExcel);
}
public static singleExcel ExcelWorkSheetChange(singleExcel thisExcel, int x)
{
thisExcel.xlWorksheet = (Excel._Worksheet)thisExcel.xlApp.Workbooks[1].Worksheets[x];
thisExcel.xlWorksheet.Columns.ClearFormats();
thisExcel.xlWorksheet.Rows.ClearFormats();
thisExcel.excelRange = thisExcel.xlWorksheet.UsedRange;
return(thisExcel);
}
public static Microsoft.Office.Interop.Excel.Application GetExcelInterop(singleExcel thisExcel, int?processId = null)
{
var p = processId.HasValue ? Process.GetProcessById(processId.Value) : Process.Start("excel.exe");
thisExcel.excelProcess = p;
Stopwatch updateStopwatch = Stopwatch.StartNew();
while (updateStopwatch.ElapsedMilliseconds < 200)
{
}
updateStopwatch.Stop();
try
{
return(new ExcelInteropService().SearchExcelInterop(p));
}
catch (Exception)
{
Debug.Assert(p != null, "p != null");
return(GetExcelInterop(thisExcel, p.Id));
}
}
public static void outputListToExcel(List <string> a, string fileName)
{
int x = 0;
object[,] excelDrop = new object[a.Count, 1];
foreach (string b in a)
{
excelDrop[x, 0] = b;
x++;
}
singleExcel outputExcel = new singleExcel().createExcel();
string secondRange = "A" + a.Count;
string thisCell = "A1:" + secondRange;
var cell = outputExcel.xlWorksheet.Range[thisCell, Type.Missing];
cell.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, excelDrop);
string path = "C:\\Users\\" + Environment.UserName + "\\Documents\\Email Attachments\\testdata\\";
string fileName2 = fileName + "-" + a.Count;
outputExcel.xlWorkbook.SaveAs(path + fileName2);
singleExcel.CloseSheet(outputExcel);
}
public static void CloseSheet(singleExcel thisExcel)
{
if (thisExcel.excelProcess != null)
{
try
{
thisExcel.excelProcess.Kill();
thisExcel.excelProcess.Dispose();
}
catch (Exception ex)
{
}
}
else
{
thisExcel.xlWorkbook.Close(true);
thisExcel.xlApp.Quit();
}
releaseObject(thisExcel.xlWorksheet);
releaseObject(thisExcel.xlWorkbook);
releaseObject(thisExcel.xlApp);
releaseObject(thisExcel.excelProcess);
releaseObject(thisExcel);
}
public static void outputObjectToExcel(Library.GroupStats a)
{
int x = 0;
object[,] excelDrop = new object[a.GetType().GetProperties().Length, 2];
foreach (PropertyInfo objPart in a.GetType().GetProperties())
{
excelDrop[x, 0] = objPart.Name;
excelDrop[x, 1] = objPart.GetValue(a, null);
x++;
}
singleExcel outputExcel = new singleExcel().createExcel();
string secondRange = "B" + a.GetType().GetProperties().Length;
string thisCell = "A1:" + secondRange;
var cell = outputExcel.xlWorksheet.Range[thisCell, Type.Missing];
cell.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, excelDrop);
string path = "C:\\Users\\" + Environment.UserName + "\\Documents\\Email Attachments\\testdata\\";
string fileName2 = "output";
outputExcel.xlWorkbook.SaveAs(path + fileName2);
singleExcel.CloseSheet(outputExcel);
}
