public static void PlaceReportGraphic(string strPath, Range oR, _Worksheet oSheet)
{
// Place report graphic
if (!String.IsNullOrEmpty(strPath) && strPath != ".\\")
{
try
{
Image img = Image.FromFile(strPath);
System.Windows.Forms.Clipboard.SetDataObject(img, true);
oR = oSheet.get_Range("A1", Missing.Value);
oSheet.Paste(oR, img);
}
catch { }
}
}
public void Paste()
{
sheet.Paste();
}
/// <summary>
/// Creates a generic header to be placed on all sheets
/// </summary>
/// <param name="image">Agency/Client image</param>
/// <param name="oSheet">Sheet to place header on</param>
/// <param name="oStartCell">
/// Starting cell for header placement (probably "A1") defaults to
/// "A1"
/// </param>
/// <param name="strMajorTitle">
/// 16pt title across the top of the sheet
/// </param>
/// <param name="strMinorTitle">14pt title below major title</param>
/// <param name="strComment">
/// Comment line directly below header information, can be empty
/// string.
/// </param>
/// <returns>oEndCell - Lower right cell of header.</returns>
public static object CreateHeader(String strImagePath, _Worksheet oSheet, object oStartCell, String strMajorTitle, String strMinorTitle, String strComment)
{
object oEndCell = GetCellAtOffset(oStartCell, 4, 14);
object oHeaderTextStartCell = GetCellAtOffset(oStartCell, 0, 1);
object oHeaderTextEndCell = GetCellAtOffset(oHeaderTextStartCell, 1, 9);
Range cellRange = oSheet.get_Range(oStartCell, GetCellAtOffset(oStartCell, 2, 3));
try
{
Image img = Image.FromFile(strImagePath);
System.Windows.Forms.Clipboard.SetDataObject(img, true);
oSheet.Paste(cellRange, img);
}
catch
{
}
object[,] oHeaderData = new object[6, 11];
oHeaderData[0, 0] = strMajorTitle;
oHeaderData[2, 0] = strMinorTitle;
oHeaderData[5, 0] = strComment;
oHeaderData[1, 10] = "Prepared By: ";
oHeaderData[2, 10] = DateTime.Now.ToLongDateString();
oHeaderData[3, 10] = "Identifier: ";
object oEndDataCell = GetCellAtOffset(oHeaderTextStartCell, 5, 10);
Range oRngData = oSheet.get_Range(oHeaderTextStartCell, oEndDataCell);
oRngData.Value2 = oHeaderData;
Range oRngTitles = oSheet.get_Range(oHeaderTextStartCell, oHeaderTextEndCell);
oRngTitles.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oRngTitles.VerticalAlignment = XlVAlign.xlVAlignCenter;
oRngTitles.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
oRngTitles.Font.Size = 20;
oRngTitles.Font.Bold = true;
oRngTitles.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.AntiqueWhite);
oRngTitles.MergeCells = true;
oHeaderTextStartCell = GetCellAtOffset(oHeaderTextStartCell, 2, 0);
oHeaderTextEndCell = GetCellAtOffset(oHeaderTextStartCell, 1, 9);
oRngTitles = oSheet.get_Range(oHeaderTextStartCell, oHeaderTextEndCell);
oRngTitles.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oRngTitles.VerticalAlignment = XlVAlign.xlVAlignCenter;
oRngTitles.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
oRngTitles.Font.Size = 14;
oRngTitles.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.AntiqueWhite);
oRngTitles.MergeCells = true;
if (strComment != "") // do not want the strComment and interior color for every report!
{
Range oRngComments = oSheet.get_Range(GetCellAtOffset(oStartCell, 5, 0), GetCellAtOffset(oStartCell, 5, 13));
oRngComments.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oRngComments.VerticalAlignment = XlVAlign.xlVAlignCenter;
oRngComments.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
oRngComments.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.AntiqueWhite);
oRngComments.MergeCells = true;
}
Range oRngIdentifier = oSheet.get_Range(GetCellAtOffset(oStartCell, 1, 13), GetCellAtOffset(oStartCell, 1, 16));
oRngIdentifier.Font.Italic = true;
oRngIdentifier.MergeCells = false;
oRngIdentifier = oSheet.get_Range(GetCellAtOffset(oStartCell, 2, 13), GetCellAtOffset(oStartCell, 2, 16));
oRngIdentifier.Font.Italic = true;
oRngIdentifier.MergeCells = false;
oRngIdentifier = oSheet.get_Range(GetCellAtOffset(oStartCell, 3, 13), GetCellAtOffset(oStartCell, 3, 16));
oRngIdentifier.Font.Italic = true;
oRngIdentifier.MergeCells = false;
return(oEndCell);
}
private async Task ExportExcelAsync(string savename)
{
await Task.Run(async() =>
{
try
{
Stopwatch Tchk = new Stopwatch();
Tchk.Start();
System.Reflection.Missing empty = System.Reflection.Missing.Value;
// all excel classes and interfaces
// are from Microsoft.Office.Interop.Excel namespace
ApplicationClass excel = new ApplicationClass();
Workbooks wBooks = excel.Workbooks;
_Workbook wBook = wBooks.Add(empty);
_Worksheet wSheet = (_Worksheet)wBook.Worksheets.get_Item(1);
Range range = wSheet.get_Range("A1", empty);
wSheet.Paste(range, false);
wSheet.Name = CB_TableName.Text;
wSheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignCenter;
wSheet.Columns.VerticalAlignment = XlVAlign.xlVAlignCenter;
wSheet.Columns.AutoFit();
// Save the workbook and quit Excel.
string excelSavePath = savename;
wBook.SaveAs(excelSavePath, empty, empty,
empty, empty, empty, XlSaveAsAccessMode.xlNoChange, empty, empty,
empty, empty, empty);
wBook.Close(false, empty, empty);
excel.Workbooks.Open(savename);
excel.Visible = true;
excel.DisplayAlerts = true;
//excel.Quit();
// important so you don't have
// excel lurking in memory
//range = null;
//wSheet = null;
//wBook = null;
//wBooks = null;
//excel = null;
releaseObject(range);
releaseObject(wSheet);
releaseObject(wBook);
releaseObject(wBooks);
releaseObject(excel);
Tchk.Stop();
Debug.Print(Tchk.Elapsed.ToString());
}
catch (Exception ex)
{
StackTrace trace = new StackTrace(ex, true);
StackFrame stackFrame = trace.GetFrame(trace.FrameCount - 1);
int lineNumber = stackFrame.GetFileLineNumber();
I_Sub.ErrorLog(($"({lineNumber}) : {ex.ToString()}"));
}
finally
{
await Task.Delay(1);
}
});
}