/// <summary>
/// PDFファイルに保存
/// </summary>
/// <param name="FileName"></param>
public void SaveAsPDF(string FileName)
{
try
{
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
m_objBook.ExportAsFixedFormat(targetType, FileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true,
miss, miss, false, miss);
}
catch
{
try
{
m_objBook.Close(false, miss, miss);
}
catch
{
}
try
{
m_objExcel.Quit();
}
catch
{
}
}
}
private void SaveWorkBook(Excel.Workbook wb, string filename)
{
string extension = System.IO.Path.GetExtension(filename);
if (extension.Equals(".xlsx"))
{
wb.SaveAs(filename, missing,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlShared,
Excel.XlSaveConflictResolution.xlLocalSessionChanges, missing, missing, missing, missing);
}
else if (extension.Equals(".pdf"))
{
string paramExportFilePath = filename;
Excel.XlFixedFormatType paramExportFormat = Excel.XlFixedFormatType.xlTypePDF;
Excel.XlFixedFormatQuality paramExportQuality =
Excel.XlFixedFormatQuality.xlQualityStandard;
bool paramOpenAfterPublish = false;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = true;
object paramFromPage = Type.Missing;
object paramToPage = Type.Missing;
// Save it in the target format.
wb.ExportAsFixedFormat(paramExportFormat,
paramExportFilePath, paramExportQuality,
paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,
paramToPage, paramOpenAfterPublish,
missing);
}
}
bool XLSConvertToPDFEach(string sourceFile)
{
string sourcePath = sourceFile;
string targetPath = GetFileName(sourceFile);
bool result = false;
Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;// Excel.XlFixedFormatType.xlTypePDF;
object paramMissing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook excelWorkBook = null;
Excel.Worksheet excelWorksheet = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
excelWorkBook = application.Workbooks.Open(sourcePath);
List <string> lstSheets = new List <string>();
foreach (string item in this.checkedListBox1.CheckedItems)
{
excelWorksheet = (Excel.Worksheet)excelWorkBook.Worksheets[item];
if (excelWorksheet != null)
{
excelWorksheet.ExportAsFixedFormat(targetType, target + "_" + excelWorksheet.Name, Excel.XlFixedFormatQuality.xlQualityStandard, true, paramMissing, paramMissing, paramMissing, false, paramMissing);
result = true;
}
}
}
catch
{
result = false;
}
finally
{
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
//ms solution is like this
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// 把Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
if (workBook != null)
{
workBook.Close();
}
if (application != null)
{
application.Quit();
}
return(result);
}
private static bool ConvertExcelToPdf(string sourcePath, string targetPath, bool land)
{
bool result;
object missing = Type.Missing;
EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF;
EXCEL.Application application = null;
EXCEL.Workbook workBook = null;
try
{
application = new EXCEL.Application();
application.Visible = false;
application.DisplayAlerts = false;
string target = sourcePath.Replace(".xlsx", ".pdf");
object type = targetType;
workBook = application.Workbooks.Open(sourcePath,
AddToMru: false, ReadOnly: true, Password: "******");
foreach (EXCEL.Worksheet sheet in workBook.Sheets)
{
sheet.PageSetup.Orientation = land ?
EXCEL.XlPageOrientation.xlLandscape : EXCEL.XlPageOrientation.xlPortrait;
}
workBook.ExportAsFixedFormat(targetType, targetPath,
EXCEL.XlFixedFormatQuality.xlQualityStandard,
IncludeDocProperties: true,
IgnorePrintAreas: false);
result = true;
}
catch
{
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(SaveChanges: false);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// 将excel文档转换成PDF格式
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns></returns>
private bool ExcelConvertPDF(string sourcePath, string targetPath)
{
bool result;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF; //PDF格式
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Microsoft.Office.Interop.Excel.Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
if (workBook != null)
{
workBook.ExportAsFixedFormat(targetType, target, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
else
{
result = false;
}
}
catch (Exception ex)
{
Response.Write("<p>Excel转PDF出现问题，详情：" + ex.ToString() + "</p>");
result = false;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// 把Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
private Boolean XLSConvertToPDF(String sourcePath, String targetPath)
{
Boolean result = false;
OExcel.XlFixedFormatType targetType = OExcel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
OExcel.ApplicationClass application = null;
OExcel.Workbook workBook = null;
try
{
application = new OExcel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
if (workBook.IsNullOrEmpty())
{
throw new System.ArgumentException("未能实例化");
}
workBook.ExportAsFixedFormat(targetType, target, OExcel.XlFixedFormatQuality.xlQualityMinimum, true, true, missing, missing, false, missing);
result = true;
}
catch (Exception)
{
result = false;
throw;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// 将EXCEL文档转换成PDF格式
/// </summary>
/// <param name="_lstrInputFile"></param>
/// <param name="_lstrOutFile"></param>
/// <returns></returns>
public static bool ConvertExcelToPdf(string _lstrInputFile, string _lstrOutFile)
{
Excel.XlFixedFormatType parameter = Excel.XlFixedFormatType.xlTypePDF;
bool result;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
object target = _lstrOutFile;
object type = parameter;
workBook = application.Workbooks.Open(_lstrInputFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(parameter, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
result = false;
throw new Exception(ex.Message, ex);
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>把Excel文件转换成PDF格式文件</summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public static bool Xls2Pdf(string sourcePath, string targetPath)
{
bool result = false;
Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Excels.ApplicationClass application = null;
Excels.Workbook workBook = null;
try
{
application = new Excels.ApplicationClass();
string source = sourcePath;
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(source, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(targetType, target,
Excels.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
result = false;
throw ex;
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// excel转换PDF
/// </summary>
/// <param name="excelfile">word文件路径<</param>
/// <param name="newPdfFile">pdf文件路径</param>
/// <param name="errorMsg">转换失败后的详细信息</param>
/// <returns></returns>
public static bool ExcelConvertPdf(string excelfile, string newPdfFile, out string errorMsg)
{
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.XlFixedFormatType excelType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
Microsoft.Office.Interop.Excel.ApplicationClass application = null;
Workbook workBook = null;
try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
workBook = application.Workbooks.Open(excelfile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
workBook.ExportAsFixedFormat(excelType, newPdfFile, XlFixedFormatQuality.xlQualityStandard,
true, false, missing, missing, missing, missing);
errorMsg = string.Empty;
return(true);
}
catch (Exception ex)
{
errorMsg = ex.Message;
return(false);
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public static void ConvertExcelToPdf(string excelFileIn, string pdfFileOut)
{
msExcel.Application excel = new msExcel.Application();
try
{
excel.Visible = false;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
FileInfo excelFile = new FileInfo(excelFileIn);
string filename = excelFile.FullName;
msExcel.Workbook wbk = excel.Workbooks.Open(filename, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing);
wbk.Activate();
object outputFileName = wbk.FullName.Replace(".xslx", ".pdf");;
msExcel.XlFixedFormatType fileFormat = msExcel.XlFixedFormatType.xlTypePDF;
// Save document into PDF Format
wbk.ExportAsFixedFormat(fileFormat, outputFileName,
missing, missing, missing,
missing, missing, missing,
missing);
object saveChanges = msExcel.XlSaveAction.xlDoNotSaveChanges;
((msExcel._Workbook)wbk).Close(saveChanges, missing, missing);
wbk = null;
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
((msExcel._Application)excel).Quit();
excel = null;
}
}
/// <summary>
/// 转换为pdf文件，适合（.xls、.xlsx文件类型）
/// </summary>
/// <param name="fileName"></param>
/// <param name="outputFileName"></param>
/// <returns></returns>
private static bool ExcelExportAsPdf(string fileName, string outputFileName)
{
bool isSucceed = false;
Excel.XlFixedFormatType fileFormat = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
Excel.Application excelApp = null;
if (excelApp == null)
{
excelApp = new Excel.Application();
}
Excel.Workbook workBook = null;
try
{
workBook = excelApp.Workbooks.Open(fileName);
workBook.ExportAsFixedFormat(fileFormat, outputFileName);
isSucceed = true;
}
finally
{
if (workBook != null)
{
workBook.Close();
workBook = null;
}
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(isSucceed);
}
/// <summary>
/// 把Excel文件转换成PDF格式文件
/// </summary>
/// <param name="sourcePath">源文件路径</param>
/// <param name="targetPath">目标文件路径</param>
/// <returns>true=转换成功</returns>
public static bool XLSConvertToPDF(string sourcePath, string targetPath)
{
// ConvertExcelPDF(sourcePath, targetPath);
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
#region 设置打印参数
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in workBook.Sheets)
{
sh.PageSetup.Zoom = false;
sh.PageSetup.FitToPagesTall = 1;
sh.PageSetup.FitToPagesWide = 1;
sh.PageSetup.TopMargin = 0;
sh.PageSetup.BottomMargin = 0;
sh.PageSetup.LeftMargin = 0;
sh.PageSetup.RightMargin = 0;
sh.PageSetup.CenterHorizontally = true;
}
#endregion
workBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch (Exception ex)
{
result = false;
//Logger.WriteLog("Service Error:Excel转pdf" + ex.ToString(), "Log\\ServiceInfoError.txt", true);
}
finally
{
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
/// <summary>
/// EXCEL转PDF
/// </summary>
/// <param name="strSourceFile">要转换的EXCEL文件路径</param>
/// <param name="strTargetFile">目标文件</param>
/// <returns>转换结果：TRUE FALSE</returns>
public bool ExcelConvertTOPDF(string strSourceFile, string strTargetFile)
{
bool flag = false;
if (File.Exists(strSourceFile))
{
//转换成的上档格式PDF
EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF;
object targetFile = strTargetFile;
object missing = Type.Missing;
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
EXCEL.Worksheet sheet = null;
try
{
excel = new EXCEL.ApplicationClass();
//excel.DisplayAlerts = true;
workBooks = excel.Workbooks;
workBook = workBooks.Open(strSourceFile, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//设置格式，导出成PDF
sheet = (EXCEL.Worksheet)workBook.Worksheets[1];//下载从1开始
//把sheet设置成横向
//sheet.PageSetup.Orientation = EXCEL.XlPageOrientation.xlLandscape;
//可以设置sheet页的其他相关设置，不列举
sheet.ExportAsFixedFormat(targetType, targetFile, EXCEL.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
flag = true;
}
catch (Exception ex)
{
classLims_NPOI.WriteLog(ex, "");
flag = false;
}
finally
{
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
//Marshal.FinalReleaseComObject(sheet);
sheet = null;
}
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
//flag = KillSpecialExcel(excel);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
bool XLSConvertToPDFMarge(string sourceFile)
{
string sourcePath = sourceFile;
string targetPath = GetFileName(sourceFile);
bool result = false;
Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;// Excel.XlFixedFormatType.xlTypePDF;
object paramMissing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook excelWorkBook = null;
Excel.Worksheet excelWorksheet = null;
Excel.Worksheet sheetDelete = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
excelWorkBook = application.Workbooks.Open(sourcePath);
List <string> lstSheets = new List <string>();
for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
{
if (this.checkedListBox1.GetItemCheckState(i) == CheckState.Unchecked)
{
string deleteSheetName = this.checkedListBox1.Items[i].ToString();
sheetDelete = (Excel.Worksheet)excelWorkBook.Worksheets[deleteSheetName];
if (sheetDelete != null)
{
sheetDelete.Delete();
}
sheetDelete = null;
}
}
excelWorkBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, paramMissing, paramMissing, false, paramMissing);
result = true;
}
catch
{
result = false;
}
finally
{
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
object target = targetPath;
object type = targetType;
//workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing, missing, missing);
// sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx";
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
////ActiveWindow.SmallScroll Down:=6;
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
////ExcelApp.ActiveWindow.SmallScroll = Excel.;
//ExcelApp.ActiveWindow.Zoom = 80;
////ActiveWindow.SmallScroll Down:=-3
//// excelRange.WrapText = true;
////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
//ActiveWindow.SmallScroll Down:=30
//Set ActiveSheet.HPageBreaks(1).Location = Range("A67")
//ActiveWindow.SmallScroll Down:=-75
//Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2];
////上边距
//double top = 0;
////左边距
//double left = 0;
////右边距
//double right = 0;
////下边距
//double footer = 0;
//WS2.DisplayAutomaticPageBreaks = false;//显示分页线
//WS2.PageSetup.CenterFooter = "第 &P 页，共 &N 页";
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右
//WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向
//Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]);
//自动调整列宽
//// excelRange.EntireColumn.AutoFit();
//// excelRange.WrapText = false; //文本自动换行
//excelRange.ShrinkToFit = false;
////设置字体在单元格内的对其方式
//excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//// 文本水平居中方式
//excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//////设置为横向打印
////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
////ExcelApp.ActiveWindow.FreezePanes = true;
////excelRange.EntireColumn.AutoFit();
////WS2.PageSetup.Orientation = 2;
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.CenterHorizontally = true;
//// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
//excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]);
//WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1);
ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
public void ConvertExcelToPdf(string excelFileIn, string pdfFileOut)
{
CheckForExistingExcellProcesses();
var missing = Type.Missing;//System.Reflection.Missing.Value;
Application excel = new Application();
Microsoft.Office.Interop.Excel.Workbook wbk = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
try
{
excel.Visible = false;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
FileInfo excelFile = new FileInfo(excelFileIn);
string filename = excelFile.FullName;
GetTheExcelProcessIdThatUsedByThisInstance();
wbk = excel.Workbooks.Open(filename, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing);
wbk.Activate();
object outputFileName = pdfFileOut;
Microsoft.Office.Interop.Excel.XlFixedFormatType fileFormat = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
Microsoft.Office.Interop.Excel.XlFixedFormatQuality paramExportQuality = Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard;
bool paramIncludeDocProps = true;
bool paramIgnorePrintAreas = false;
if (wbk != null)//save as pdf
{
wbk.ExportAsFixedFormat(fileFormat,
outputFileName,
paramExportQuality,
paramIncludeDocProps,
paramIgnorePrintAreas,
missing,
missing,
missing,
missing);
}
object saveChanges = XlSaveAction.xlDoNotSaveChanges;
((_Workbook)wbk).Close(saveChanges, missing, missing);
excel.Quit();
// liberar
releaseObject(wbk);
releaseObject(excel);
wbk = null;
excel = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
catch (Exception ex)
{
// Cerrar
((_Workbook)wbk).Close(false, missing, missing);
excel.Quit();
// Elimina el archivo creado
File.Delete(pdfFileOut);
KillExcelProcessThatUsedByThisInstance();
throw (ex);
}
finally
{
// Liberar
releaseObject(wbk);
releaseObject(excel);
wbk = null;
excel = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
KillExcelProcessThatUsedByThisInstance();
}
}
private int ProcessProgress(object sender, DoWorkEventArgs e)
{
string filePath = textBox1.Text;
String[] files = Directory.GetFiles(filePath, "*.*", SearchOption.AllDirectories);
int length = files.Length;
if (files != null)
{
for (int i = 0; i < files.Length; i++)
{
try
{
if (files[i].Contains(".doc"))
{
int doc_index = files[i].LastIndexOf(".");
string wpath = files[i];
if (wpath.Contains("$"))
{
continue;
}
string ppath = files[i].Substring(0, doc_index) + ".pdf";
Word.WdExportFormat wd = Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF;
WordConvertPDF(wpath, ppath, wd);
File.Delete(wpath);
}
else if (files[i].Contains(".xls"))
{
int doc_index = files[i].LastIndexOf(".");
string wpath = files[i];
string ppath = files[i].Substring(0, doc_index) + ".pdf";
Excel.XlFixedFormatType excelType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
ExcelConvertPDF(wpath, ppath, excelType);
File.Delete(wpath);
Process[] process = Process.GetProcessesByName("EXCEL.EXE");
foreach (Process p in process)
{
if (!p.HasExited) // 如果程序没有关闭，结束程序
{
p.Kill();
p.WaitForExit();
}
}
}
if (bkWorker.CancellationPending)
{
e.Cancel = true;
return(-1);
}
else
{
int pcount = (i + 1) * 100 / length;
// 状态报告
bkWorker.ReportProgress(pcount);
// 等待，用于UI刷新界面，很重要
System.Threading.Thread.Sleep(1);
}
}
catch (Exception ex) {
MessageBox.Show(files[i]);
}
}
//MessageBox.Show("完成");
}
return(-1);
}
