internal static void CreatePdfFromClipboard(string workSheetName, string fullFilePath,
ExcelTableStyle tableStyle, bool toOpen)
{
//Excel Application class
var app = new ApplicationClass();
//Get process id
int excelProcessId = GetApplicationProcessId(app);
app.DisplayAlerts = false;
app.Visible = false;
try
{
//Add workbook
Workbook theWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Add worksheet
var theWorksheet =
(Worksheet)theWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string sheetName = workSheetName;
if (workSheetName.Length > 31)
{
sheetName = workSheetName.Substring(0, 31);
}
theWorksheet.Name = sheetName;
app.SheetsInNewWorkbook = 1;
theWorksheet.Activate();
//Paste to the worksheet from clpboard
theWorksheet.Paste(Type.Missing, Type.Missing);
//Apply Borders
ApplyBorder(theWorksheet.UsedRange);
//Auto Fit All columns
Range xlRange = theWorksheet.UsedRange;
// put all hardcodes in a constant class
xlRange.Font.Name = "Arial";
xlRange.Font.Size = 9;
var firstRowRange = (Range)xlRange.Rows[1, Missing.Value];
firstRowRange.EntireRow.Font.Bold = true;
firstRowRange.EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//Set Wrap Test to false
xlRange.WrapText = false;
object misValue = Missing.Value;
theWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
xlRange, misValue, XlYesNoGuess.xlYes, misValue).Name = "myTableList";
string tableStyleName = "TableStyle" + tableStyle;
theWorksheet.ListObjects["myTableList"].TableStyle = tableStyleName;
xlRange.Columns.AutoFit();
theWorksheet.Activate();
//Save the file
string xcelFile = Path.Combine(Path.GetTempPath(),
Path.GetFileNameWithoutExtension(fullFilePath) + ".xlsx");
theWorkbook.SaveAs(xcelFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
theWorksheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, fullFilePath,
XlFixedFormatQuality.xlQualityStandard, true, false, Missing.Value,
Missing.Value, toOpen, Missing.Value);
//Clean up
app.Quit();
Marshal.ReleaseComObject(app);
File.Delete(xcelFile);
}
catch
{
MessageBox.Show(
"Please add 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS , dowload it from this location http://www.microsoft.com/download/en/details.aspx?id=7",
"Error", MessageBoxButton.OK, MessageBoxImage.Error);
ForceExcelClose(excelProcessId);
throw;
}
}
internal static void CreatePdfFromClipboard(string workSheetName, string fullFilePath,
ExcelTableStyle tableStyle, bool toOpen)
{
//Excel Application class
var app = new ApplicationClass();
//Get process id
int excelProcessId = GetApplicationProcessId(app);
app.DisplayAlerts = false;
app.Visible = false;
try
{
//Add workbook
Workbook theWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Add worksheet
var theWorksheet =
(Worksheet)theWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string sheetName = workSheetName;
if (workSheetName.Length > 31)
sheetName = workSheetName.Substring(0, 31);
theWorksheet.Name = sheetName;
app.SheetsInNewWorkbook = 1;
theWorksheet.Activate();
//Paste to the worksheet from clpboard
theWorksheet.Paste(Type.Missing, Type.Missing);
//Apply Borders
ApplyBorder(theWorksheet.UsedRange);
//Auto Fit All columns
Range xlRange = theWorksheet.UsedRange;
// put all hardcodes in a constant class
xlRange.Font.Name = "Arial";
xlRange.Font.Size = 9;
var firstRowRange = (Range)xlRange.Rows[1, Missing.Value];
firstRowRange.EntireRow.Font.Bold = true;
firstRowRange.EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//Set Wrap Test to false
xlRange.WrapText = false;
object misValue = Missing.Value;
theWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
xlRange, misValue, XlYesNoGuess.xlYes, misValue).Name = "myTableList";
string tableStyleName = "TableStyle" + tableStyle;
theWorksheet.ListObjects["myTableList"].TableStyle = tableStyleName;
xlRange.Columns.AutoFit();
theWorksheet.Activate();
//Save the file
string xcelFile = Path.Combine(Path.GetTempPath(),
Path.GetFileNameWithoutExtension(fullFilePath) + ".xlsx");
theWorkbook.SaveAs(xcelFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
theWorksheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, fullFilePath,
XlFixedFormatQuality.xlQualityStandard, true, false, Missing.Value,
Missing.Value, toOpen, Missing.Value);
//Clean up
app.Quit();
Marshal.ReleaseComObject(app);
File.Delete(xcelFile);
}
catch
{
MessageBox.Show(
"Please add 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS , dowload it from this location http://www.microsoft.com/download/en/details.aspx?id=7",
"Error", MessageBoxButton.OK, MessageBoxImage.Error);
ForceExcelClose(excelProcessId);
throw;
}
}
internal static void CreateExcelFromClipboard(string workSheetName, string fullFilePath,
ExcelTableStyle excelTableStyle, bool toOpen)
{
//Excel Application class
var app = new ApplicationClass();
//Get process id
int excelProcessId = GetApplicationProcessId(app);
app.DisplayAlerts = false;
try
{
//Add workbook
Workbook theWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Add worksheet
var theWorksheet =
(Worksheet)theWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string sheetName = workSheetName;
if (workSheetName.Length > 31)
{
sheetName = workSheetName.Substring(0, 31);
}
theWorksheet.Name = sheetName;
app.SheetsInNewWorkbook = 1;
theWorksheet.Activate();
//Paste to the worksheet from clpboard
theWorksheet.Paste(Type.Missing, Type.Missing);
//Apply Borders
ApplyBorder(theWorksheet.UsedRange);
//Auto Fit All columns
Range xlRange = theWorksheet.UsedRange;
// put all hardcodes in a constant class
xlRange.Font.Name = "Arial";
xlRange.Font.Size = 9;
var firstRowRange = (Range)xlRange.Rows[1, Missing.Value];
firstRowRange.EntireRow.Font.Bold = true;
firstRowRange.EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//Set Wrap Test to false
xlRange.WrapText = false;
object misValue = Missing.Value;
theWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
xlRange, misValue, XlYesNoGuess.xlYes, misValue).Name = "myTableList";
string tableStyleName = "TableStyle" + excelTableStyle;
theWorksheet.ListObjects["myTableList"].TableStyle = tableStyleName;
xlRange.Columns.AutoFit();
theWorksheet.Activate();
//Save the file
theWorkbook.SaveAs(fullFilePath, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
if (!toOpen)
{
//Clean up
app.Quit();
Marshal.ReleaseComObject(app);
}
}
catch
{
ForceExcelClose(excelProcessId);
throw;
}
finally
{
app.Visible = toOpen;
}
}
internal static void CreateExcelFromClipboard(string workSheetName, string fullFilePath,
ExcelTableStyle excelTableStyle, bool toOpen)
{
//Excel Application class
var app = new ApplicationClass();
//Get process id
int excelProcessId = GetApplicationProcessId(app);
app.DisplayAlerts = false;
try
{
//Add workbook
Workbook theWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Add worksheet
var theWorksheet =
(Worksheet)theWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
string sheetName = workSheetName;
if (workSheetName.Length > 31)
sheetName = workSheetName.Substring(0, 31);
theWorksheet.Name = sheetName;
app.SheetsInNewWorkbook = 1;
theWorksheet.Activate();
//Paste to the worksheet from clpboard
theWorksheet.Paste(Type.Missing, Type.Missing);
//Apply Borders
ApplyBorder(theWorksheet.UsedRange);
//Auto Fit All columns
Range xlRange = theWorksheet.UsedRange;
// put all hardcodes in a constant class
xlRange.Font.Name = "Arial";
xlRange.Font.Size = 9;
var firstRowRange = (Range)xlRange.Rows[1, Missing.Value];
firstRowRange.EntireRow.Font.Bold = true;
firstRowRange.EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//Set Wrap Test to false
xlRange.WrapText = false;
object misValue = Missing.Value;
theWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
xlRange, misValue, XlYesNoGuess.xlYes, misValue).Name = "myTableList";
string tableStyleName = "TableStyle" + excelTableStyle;
theWorksheet.ListObjects["myTableList"].TableStyle = tableStyleName;
xlRange.Columns.AutoFit();
theWorksheet.Activate();
//Save the file
theWorkbook.SaveAs(fullFilePath, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
if (!toOpen)
{
//Clean up
app.Quit();
Marshal.ReleaseComObject(app);
}
}
catch
{
ForceExcelClose(excelProcessId);
throw;
}
finally
{
app.Visible = toOpen;
}
}
