private bool convertPDF(string excelFile)
{
string pdfFile = excelFile.Replace(".xlsx", ".pdf"); // PDFファイルフルパス
XlFixedFormatType format = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
Microsoft.Office.Interop.Excel.Application app = null;
Workbook workbook = null;
try
{
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Open(excelFile); //---ブックを開いて
workbook.ExportAsFixedFormat(format, pdfFile, quality); //--- PDF形式で出力
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
if (workbook != null)
{
// Wakamatsu 20170313
workbook.Close(false);
// Wakamatsu 20170313
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
if (excelFile != "" && delFlag == true)
{
if (File.Exists(excelFile))
{
try
{
System.Threading.Thread.Sleep(1000);
// 対象Excelファイル削除
File.Delete(excelFile);
}
catch (IOException)
{
}
}
}
}
}
private void convertExcel(string fileName)
{
string str = Path.GetFileNameWithoutExtension(fileName) + ".pdf";
Application application = (Application)Activator.CreateInstance(Type.GetTypeFromCLSID(new Guid("00024500-0000-0000-C000-000000000046")));
Workbook workbook = null;
object missing = Type.Missing;
string filename = this.workDir + str;
XlFixedFormatType xlTypePDF = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality xlQualityMinimum = XlFixedFormatQuality.xlQualityMinimum;
bool openAfterPublish = false;
bool includeDocProperties = true;
bool ignorePrintAreas = true;
object from = Type.Missing;
object to = Type.Missing;
try
{
workbook = application.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
if (workbook != null)
{
workbook.ExportAsFixedFormat(xlTypePDF, filename, xlQualityMinimum, includeDocProperties, ignorePrintAreas, from, to, openAfterPublish, missing);
}
}
catch (Exception exception)
{
MessageBox.Show("Excel conversion failed for file " + fileName + ". \n Error caught: " + exception.Message);
}
finally
{
if (workbook != null)
{
workbook.Close(false, missing, missing);
workbook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// Deze methode start een nieuwe instantie van een Office Excel applicatie.
/// De print service naar
/// van het Office pakket wordt gebruikt om wordt documenten om te zetten naar een PDF-bestand.
/// </summary>
/// <param name="vehicle_id">voor welke auto?</param>
/// <param name="pdfNamen">Voor welke documenten?</param>
public void ConverteerExcelNaarPDF(string vehicle_id, List <string> pdfNamen)
{
if (excelFiles.Count() == 0)
{
HaalBestandsNamenOp(vehicle_id, pdfNamen);
}
if (pdfNamen.Count != 0 && officeAanwezig)
{
paramExportFormat = XlFixedFormatType.xlTypePDF;
paramExportQuality = XlFixedFormatQuality.xlQualityStandard;
foreach (FileInfo excelFile in excelFiles)
{
ConverteerBestand(excelFile);
}
}
}
// Main conversion routine
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Object activeSheet = null;
Window activeWindow = null;
Windows wbWin = null;
Hashtable templatePageSetup = new Hashtable();
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
// Excel can be very slow to start up, so try to get the COM
// object a few times
int tries = 10;
app = new Microsoft.Office.Interop.Excel.Application();
while (tries > 0)
{
try
{
// Try to set a property on the object
app.ScreenUpdating = false;
}
catch (COMException)
{
// Decrement the number of tries and have a bit of a snooze
tries--;
Thread.Sleep(500);
continue;
}
// Looks ok, so bail out of the loop
break;
}
if (tries == 0)
{
ReleaseCOMObject(app);
return((int)ExitCode.ApplicationError);
}
app.Visible = true;
app.DisplayAlerts = false;
app.AskToUpdateLinks = false;
app.AlertBeforeOverwriting = false;
app.EnableLargeOperationAlert = false;
app.Interactive = false;
app.FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone;
var onlyActiveSheet = (Boolean)options["excel_active_sheet"];
Boolean includeProps = !(Boolean)options["excludeprops"];
Boolean skipRecalculation = (Boolean)options["excel_no_recalculate"];
Boolean showHeadings = (Boolean)options["excel_show_headings"];
Boolean showFormulas = (Boolean)options["excel_show_formulas"];
Boolean isHidden = (Boolean)options["hidden"];
Boolean screenQuality = (Boolean)options["screen"];
Boolean updateLinks = !(Boolean)options["excel_no_link_update"];
int maxRows = (int)options[@"excel_max_rows"];
int worksheetNum = (int)options["excel_worksheet"];
int sheetForConversionIdx = 0;
activeWindow = app.ActiveWindow;
Sheets allSheets = null;
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if (isHidden)
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
// If we have no write password and we're attempting to open for writing, we might be
// caught out by an unexpected write password
if (writePassword == "" && !nowrite)
{
oWritePass = (Object)"FAKEPASSWORD";
try
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
catch (System.Runtime.InteropServices.COMException)
{
// Attempt to open it in read-only mode
workbook = workbooks.Open(inputFile, updateLinks, true, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
}
else
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
// Add in a delay to let Excel sort itself out
AddCOMDelay(options);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Get any template options
SetPageOptionsFromTemplate(app, workbooks, options, ref templatePageSetup);
// Get the sheets
allSheets = workbook.Sheets;
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
// We will save as xlsb (binary format) since this doesn't raise some errors when processing
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xlsb");
fmt = XlFileFormat.xlExcel12;
// Set up the print quality
if (screenQuality)
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// If a worksheet has been specified, try and use just the one
if (worksheetNum > 0)
{
// Force us just to use the active sheet
onlyActiveSheet = true;
try
{
if (worksheetNum > allSheets.Count)
{
// Sheet count is too big
return((int)ExitCode.WorksheetNotFound);
}
if (allSheets[worksheetNum] is _Worksheet)
{
((_Worksheet)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Worksheet)allSheets[worksheetNum]).Index;
}
else if (allSheets[worksheetNum] is _Chart)
{
((_Chart)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Chart)allSheets[worksheetNum]).Index;
}
}
catch (Exception)
{
return((int)ExitCode.WorksheetNotFound);
}
}
if (showFormulas)
{
// Determine whether to show formulas
try
{
activeWindow.DisplayFormulas = true;
}
catch (Exception) { }
}
// Keep the windows hidden
if (isHidden)
{
wbWin = workbook.Windows;
if (null != wbWin)
{
if (wbWin.Count > 0)
{
wbWin[1].Visible = false;
}
}
if (null != activeWindow)
{
activeWindow.Visible = false;
}
}
// Keep track of the active sheet
if (workbook.ActiveSheet != null)
{
activeSheet = workbook.ActiveSheet;
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (maxRows > 0 || showFormulas || showHeadings)
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
// Loop through all the sheets (worksheets and charts)
for (int wsIdx = 1; wsIdx <= allSheets.Count; wsIdx++)
{
var ws = allSheets.Item[wsIdx];
// Skip anything that is not the active sheet
if (onlyActiveSheet)
{
// Have to be careful to treat _Worksheet and _Chart items differently
try
{
int itemIndex = 1;
if (activeSheet is _Worksheet)
{
itemIndex = ((Worksheet)activeSheet).Index;
}
else if (activeSheet is _Chart)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Chart)activeSheet).Index;
}
if (wsIdx != itemIndex)
{
ReleaseCOMObject(ws);
continue;
}
}
catch (Exception)
{
if (ws != null)
{
ReleaseCOMObject(ws);
}
continue;
}
sheetForConversionIdx = wsIdx;
}
if (showHeadings && ws is _Worksheet)
{
PageSetup pageSetup = null;
try
{
pageSetup = ((Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
}
catch (Exception) { }
finally
{
ReleaseCOMObject(pageSetup);
}
}
// If showing formulas, make things auto-fit
if (showFormulas && ws is _Worksheet)
{
Range cols = null;
try
{
((_Worksheet)ws).Activate();
activeWindow.DisplayFormulas = true;
cols = ((Worksheet)ws).Columns;
cols.AutoFit();
}
catch (Exception) { }
finally
{
ReleaseCOMObject(cols);
}
}
// If there is a maximum row count, make sure we check each worksheet
if (maxRows > 0 && ws is _Worksheet)
{
// Check for a print area
var pageSetup = ((Worksheet)ws).PageSetup;
var printArea = pageSetup.PrintArea;
ReleaseCOMObject(pageSetup);
if (string.IsNullOrEmpty(printArea))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > maxRows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Worksheet)ws).Name;
}
ReleaseCOMObject(cellSearch);
}
ReleaseCOMObject(cells);
}
ReleaseCOMObject(rows);
}
ReleaseCOMObject(range);
if (row_count > maxRows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
// Allow for re-calculation to be skipped
if (skipRecalculation)
{
app.Calculation = XlCalculation.xlCalculationManual;
app.CalculateBeforeSave = false;
}
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
if (onlyActiveSheet)
{
// Set up a delegate function for times we want to print
PrintDocument printFunc = delegate(string destination, string printer)
{
((Worksheet)activeSheet).PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (sheetForConversionIdx > 0)
{
activeSheet = allSheets.Item[sheetForConversionIdx];
}
if (activeSheet is _Worksheet)
{
var wps = ((_Worksheet)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
((Worksheet)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
ReleaseCOMObject(wps);
}
else if (activeSheet is _Chart)
{
var wps = ((_Chart)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Chart)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
ReleaseCOMObject(wps);
}
else
{
return((int)ExitCode.UnknownError);
}
AddCOMDelay(options);
}
else
{
PrintDocument printFunc = delegate(string destination, string printer)
{
workbook.PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (HasTemplateOption(options))
{
// Set up the template page setup options on all the worksheets
// in the workbook
var worksheets = workbook.Worksheets;
for (int wsIdx = 1; wsIdx <= worksheets.Count; wsIdx++)
{
var ws = worksheets[wsIdx];
var wps = (ws is _Worksheet) ? ((_Worksheet)ws).PageSetup : ((_Chart)ws).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
ReleaseCOMObject(wps);
ReleaseCOMObject(ws);
}
ReleaseCOMObject(worksheets);
}
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
}
ReleaseCOMObject(allSheets);
ReleaseCOMObject(fmt);
ReleaseCOMObject(quality);
return((int)ExitCode.Success);
}
catch (COMException ce)
{
if ((uint)ce.ErrorCode == 0x800A03EC)
{
return((int)ExitCode.EmptyWorksheet);
}
else
{
Console.WriteLine(ce.Message);
return((int)ExitCode.UnknownError);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
ReleaseCOMObject(activeSheet);
ReleaseCOMObject(activeWindow);
ReleaseCOMObject(wbWin);
GC.Collect();
GC.WaitForPendingFinalizers();
// Excel sometimes needs a bit of a delay before we close in order to
// let things get cleaned up
workbook.Saved = true;
CloseExcelWorkbook(workbook);
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
ReleaseCOMObject(workbook);
ReleaseCOMObject(workbooks);
ReleaseCOMObject(app);
GC.Collect();
GC.WaitForPendingFinalizers();
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
void ExcelToPDF(string sourcePath, string ExportPath)
{
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWorkBook = null;
string paramSourceBookPath = sourcePath;
object paramMissing = Type.Missing;
string paramExportFilePath = ExportPath;// @"C:\Users\Lucky s\Desktop\New folder (2)\Test.pdf";
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = false;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
object oMissing = System.Reflection.Missing.Value;
// XlSaveAsAccessMode Mode = XlSaveAsAccessMode.xl;
// excelWorkBook.Application.DisplayAlerts = false;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
excelWorkBook.Activate();
//Formating
Worksheet sheet = (Worksheet)excelWorkBook.Worksheets[1];
sheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
// Zoom property must be false, otherwise the "FitToPages" properties
// are ignored.
sheet.PageSetup.Zoom = false;
// these set the number of pages tall or wide the worksheet will be
// scaled to when printed.
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = 1;
// sheet.DisplayPageBreaks = true;
sheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
sheet.UsedRange.Font.Size = 14;
//sheet.UsedRange.Borders.Weight = 2;
//sheet.UsedRange.Cells.Borders.Weight = 2;
sheet.UsedRange.Font.Name = "Arial Narrow";
sheet.PageSetup.LeftMargin = 0.5;
sheet.PageSetup.RightMargin = 0.5;
sheet.PageSetup.TopMargin = 1;
sheet.PageSetup.BottomMargin = 0.5;
//End
// Save it in the target format.
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
}
catch (Exception ex)
{
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void convertirexcel()
{
Microsoft.Office.Interop.Excel.Application word = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWorkBook = null;
string paramSourceBookPath = @"C:\Users\raul\Documents\Visual Studio 2012\Projects\WorkFlow Seguros Futuro\WorkFlow Seguros Futuro\files\" + FileUpload.FileName;
object paramMissing = Type.Missing;
string paramExportFilePath = Server.MapPath("~/files/" + Path.GetFileNameWithoutExtension(FileUpload.FileName) + ".pdf");
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
try
{
excelWorkBook = word.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
}
catch (Exception ex)
{
// agregar mensaje de error
}
finally
{
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
if (word != null)
{
word.Quit();
word = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// Deze methode start een nieuwe instantie van een Office Excel applicatie.
/// De print service naar
/// van het Office pakket wordt gebruikt om wordt documenten om te zetten naar een PDF-bestand.
/// </summary>
/// <param name="vehicle_id">voor welke auto?</param>
/// <param name="pdfNamen">Voor welke documenten?</param>
public void ConverteerExcelNaarPDF(string vehicle_id, List<string> pdfNamen)
{
if (excelFiles.Count() == 0)
HaalBestandsNamenOp(vehicle_id, pdfNamen);
if (pdfNamen.Count != 0 && officeAanwezig)
{
paramExportFormat = XlFixedFormatType.xlTypePDF;
paramExportQuality = XlFixedFormatQuality.xlQualityStandard;
foreach (FileInfo excelFile in excelFiles)
{
ConverteerBestand(excelFile);
}
}
}
public static Boolean CreateReportPDFFromExcel(string excelFilePath, String pdfFilePath, Int32 reportIndex)
{
Boolean flag = false;
if (!System.IO.File.Exists(excelFilePath))
{
return(flag);
}
ApplicationClass excelApplication = new ApplicationClass();
Workbook excelWorkBook = null;
string paramSourceBookPath = excelFilePath;
object paramMissing = Type.Missing;
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality = XlFixedFormatQuality.xlQualityMinimum;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = 1;
object paramToPage = 1;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
// Save it in the target format.
if (excelWorkBook != null)
{
if (!Directory.Exists(Path.GetDirectoryName(pdfFilePath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(pdfFilePath));
}
Worksheet reportSheet = excelWorkBook.Sheets[reportIndex] as Worksheet;
if (reportSheet != null)
{
if (File.Exists(pdfFilePath))
{
File.Delete(pdfFilePath);
}
reportSheet.PageSetup.Zoom = 90;
reportSheet.PageSetup.LeftMargin = excelApplication.CentimetersToPoints(3);
reportSheet.PageSetup.RightMargin = excelApplication.CentimetersToPoints(2);
reportSheet.PageSetup.TopMargin = excelApplication.CentimetersToPoints(2.5);
reportSheet.PageSetup.BottomMargin = excelApplication.CentimetersToPoints(2.5);
reportSheet.ExportAsFixedFormat(paramExportFormat, pdfFilePath, paramExportQuality, paramIncludeDocProps,
paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish, paramMissing);
}
}
flag = true;
}
catch (Exception e)
{
throw e;
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Close the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(flag);
}
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
app = new Microsoft.Office.Interop.Excel.Application()
{
Visible = true,
DisplayAlerts = false,
AskToUpdateLinks = false,
AlertBeforeOverwriting = false,
EnableLargeOperationAlert = false,
Interactive = false,
FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone
};
if ((Boolean)options["hidden"])
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
workbook = workbooks.Open(inputFile, true, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xls");
// Set up the file save format
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
if (workbook.HasVBProject)
{
fmt = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
tmpFile += "m";
}
else
{
tmpFile += "x";
}
// Set up the print quality
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if ((Boolean)options["screen"])
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// Remember - Never use 2 dots with COM objects!
// Using more than one dot leaves wrapper objects left over
var wbWin = workbook.Windows;
var appWin = app.Windows;
if ((Boolean)options["excel_show_formulas"])
{
// Determine whether to show formulas
appWin[1].DisplayFormulas = true;
}
if (wbWin.Count > 0)
{
wbWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(wbWin);
}
if (appWin.Count > 0)
{
appWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(appWin);
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
var max_rows = (int)options[@"excel_max_rows"];
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (max_rows > 0 || (Boolean)options["excel_show_formulas"] || (Boolean)options["excel_show_headings"])
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
var worksheets = workbook.Worksheets;
foreach (var ws in worksheets)
{
if ((Boolean)options["excel_show_headings"])
{
var pageSetup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
Converter.ReleaseCOMObject(pageSetup);
}
// If showing formulas, make things auto-fit
if ((Boolean)options["excel_show_formulas"])
{
((Microsoft.Office.Interop.Excel.Worksheet)ws).Activate();
app.ActiveWindow.DisplayFormulas = true;
var cols = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Columns;
cols.AutoFit();
Converter.ReleaseCOMObject(cols);
}
// If there is a maximum row count, make sure we check each worksheet
if (max_rows > 0)
{
// Check for a print area
var page_setup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
var print_area = page_setup.PrintArea;
Converter.ReleaseCOMObject(page_setup);
if (string.IsNullOrEmpty(print_area))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Microsoft.Office.Interop.Excel.Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > max_rows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Name;
Converter.ReleaseCOMObject(cellSearch);
}
Converter.ReleaseCOMObject(cells);
}
Converter.ReleaseCOMObject(rows);
}
}
Converter.ReleaseCOMObject(range);
if (row_count > max_rows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
Converter.ReleaseCOMObject(worksheets);
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
Boolean includeProps = !(Boolean)options["excludeprops"];
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
return((int)ExitCode.Success);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
Converter.ReleaseCOMObject(workbook);
Converter.ReleaseCOMObject(workbooks);
Converter.ReleaseCOMObject(app);
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
public static void ConvertData(string files)
{
ApplicationClass excelApplication = new ApplicationClass();
Workbook excelWorkBook = null;
string paramSourceBookPath = System.Windows.Forms.Application.StartupPath + @"\" + files;// @"D:\test.xls";
object paramMissing = Type.Missing;
string name = Path.GetFileNameWithoutExtension(files);
string paramExportFilePath = System.Windows.Forms.Application.StartupPath + @"\Report\" + name + ".pdf";
//MessageBox.Show(paramExportFilePath);
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
// Save it in the target format.
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
// MessageBox.Show(paramExportFilePath);
}
}
catch //(Exception ex)
{
// MessageBox.Show(ex.Message.ToString());
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
static void ExcelToPDF(string sourcePath, string ExportPath)
{
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Workbook excelWorkBook = null;
string paramSourceBookPath = sourcePath; //@"C:\Users\Lucky s\Desktop\New folder (2)\StocksOnTheMove.xls";
object paramMissing = Type.Missing;
string paramExportFilePath = ExportPath; // @"C:\Users\Lucky s\Desktop\New folder (2)\Test.pdf";
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = true;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = false;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
object oMissing = System.Reflection.Missing.Value;
// object Format=XlFixedFormatType
XlSaveAsAccessMode Mode = XlSaveAsAccessMode.xlNoChange;
//excelWorkBook.Application.DisplayAlerts = false;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
excelWorkBook.Activate();
// Save it in the target format.
if (excelWorkBook != null)
{
//excelWorkBook.SaveAs(paramExportFilePath);
// excelWorkBook.SaveAs(paramExportFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
}
catch (Exception ex)
{
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>导出为指定格式的文件。
/// </summary>
/// <param name="Type">要导出为的文件格式类型。</param>
/// <param name="Filename">要保存的文件的文件名。可以包括完整路径，否则 Excel 2007 会将文件保存在当前文件夹中。</param>
/// <param name="Quality">可选 XlFixedFormatQuality。指定已发布文件的质量。</param>
/// <param name="IncludeDocProperties">若要包括文档属性，则为 True；否则为 False。</param>
/// <param name="IgnorePrintAreas">若要忽略发布时设置的任何打印区域，则为 True；否则为 False。</param>
/// <param name="From">发布的起始页码。如果省略此参数，则从起始位置开始发布。</param>
/// <param name="To">发布的终止页码。如果省略此参数，则发布至最后一页。</param>
/// <param name="OpenAfterPublish">若要在发布文件后在查看器中显示文件，则为 True；否则为 False。</param>
/// <param name="FixedFormatExtClassPtr">Pointer to the FixedFormatExt class</param>
public void ExportAsFixedFormat(XlFixedFormatType Type, string Filename = null, XlFixedFormatQuality? Quality = null, bool? IncludeDocProperties = null, bool? IgnorePrintAreas = null, int? From = null, int? To = null, bool? OpenAfterPublish = null, object FixedFormatExtClassPtr = null)
{
_objaParameters = new object[9] {
Type,
Filename == null ? System.Type.Missing : Filename,
Quality == null ? System.Type.Missing : Quality,
IncludeDocProperties == null ? System.Type.Missing : IncludeDocProperties,
IgnorePrintAreas == null ? System.Type.Missing : IgnorePrintAreas,
From == null ? System.Type.Missing : From,
To == null ? System.Type.Missing : To,
OpenAfterPublish == null ? System.Type.Missing : OpenAfterPublish,
FixedFormatExtClassPtr == null ? System.Type.Missing : FixedFormatExtClassPtr
};
_objRange.GetType().InvokeMember("ExportAsFixedFormat", BindingFlags.InvokeMethod, null, _objRange, _objaParameters);
}
public static string ConvertData(string files)
{
FileInfo newFile = new FileInfo(files);
//if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + @"\Report\"))
//Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + @"\Report\");
ApplicationClass excelApplication = new ApplicationClass();
Workbook excelWorkBook = null;
excelApplication.Caption = System.Guid.NewGuid().ToString().ToUpper();
string paramSourceBookPath = files;//System.Windows.Forms.Application.StartupPath + @"\" + files;// @"D:\test.xls";
object paramMissing = Type.Missing;
string name = Path.GetFileNameWithoutExtension(files);
//string paramExportFilePath = System.Windows.Forms.Application.StartupPath + @"\Report\" + name + ".pdf";
string paramExportFilePath = newFile.DirectoryName + @"\\" + name + ".pdf";
//MessageBox.Show(paramExportFilePath);
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality =
XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
try
{
// Open the source workbook.
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
// Save it in the target format.
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
// MessageBox.Show(paramExportFilePath);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
// Respond to the error.
}
finally
{
// Close the workbook object.
if (excelWorkBook != null)
{
excelApplication.DisplayAlerts = false;
excelWorkBook.Close(false, paramMissing, paramMissing);
Marshal.ReleaseComObject(excelWorkBook);
Marshal.FinalReleaseComObject(excelWorkBook);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
Config.EnsureProcessKilled(IntPtr.Zero, excelApplication.Caption);
Marshal.ReleaseComObject(excelApplication);
Marshal.FinalReleaseComObject(excelApplication);
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(paramExportFilePath);
}
public static bool FileToPDF(FileInterface file, string outputPath)
{
try
{
if (ArquivoAberto(file.file.FullName))
{
DialogResult closed;
do
{
closed = MessageBox.Show(String.Format("O arquivo {0} não pode ser convertido pois está sendo " +
"usado por outro processo neste momento. Por favor, feche o arquivo " +
"e clique em 'Sim' se deseja continuar. Para cancelar a conversão deste arquivo, clique em 'Não'.", file.file.Name), "Erro - Arquivo aberto", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (!ArquivoAberto(file.file.FullName))
{
break;
}
}while (closed.Equals(DialogResult.Yes));
if (closed.Equals(DialogResult.No))
{
Arquivo arquivo = new Arquivo(file.file.Name, file.file.Extension, "Cancelado", "Conversão cancelada pelo usuário");
Arquivos.Add(arquivo);
return(false);
}
}
if (file.file.Extension.Contains("doc"))
{
string wordDoc = file.file.FullName;
string output = RetornarOutputCorreto(outputPath, file.file.Name.Replace(file.file.Extension, ""));
Microsoft.Office.Interop.Word.Application appWord = new Microsoft.Office.Interop.Word.Application();
wordDocument = appWord.Documents.Open(wordDoc);
wordDocument.ExportAsFixedFormat(output, WdExportFormat.wdExportFormatPDF);
object saveOption = Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges;
object originalFormat = Microsoft.Office.Interop.Word.WdOriginalFormat.wdWordDocument;
object routeDocument = false;
appWord.Quit(ref saveOption, ref originalFormat, ref routeDocument);
Arquivo arquivo = new Arquivo(file.file.Name, file.file.Extension, "Convertido", "Arquivo convertido com sucesso!");
Arquivos.Add(arquivo);
return(true);
}
else
{
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkBook = null;
string paramSourceBookPath = file.file.FullName;
object paramMissing = Type.Missing;
//string paramExportFilePath = outputPath + String.Format(@"\{0}.pdf", file.file.Name.Replace(file.file.Extension, ""));
string paramExportFilePath = RetornarOutputCorreto(outputPath, file.file.Name.Replace(file.file.Extension, ""));
XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;
XlFixedFormatQuality paramExportQuality = XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
if (excelWorkBook != null)
{
excelWorkBook.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
paramMissing);
}
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
Arquivo arquivo = new Arquivo(file.file.Name, file.file.Extension, "Convertido", "Arquivo convertido com sucesso!");
Arquivos.Add(arquivo);
return(true);
}
}
catch (Exception erro)
{
Arquivo arquivo = new Arquivo(file.file.Name, file.file.Extension, "Erro", erro.Message);
Arquivos.Add(arquivo);
return(false);
}
}
