private static bool GeneratePdf(string xlFilePath, XlPaperSize paperSize, Workbook wkb, out string path)
{
foreach (Worksheet ws in wkb.Worksheets.OfType<Worksheet>())
{
ws.PageSetup.Orientation = XlPageOrientation.xlLandscape;
if (!paperSize.Equals(null))
{
ws.PageSetup.PaperSize = paperSize;
}
ws.PageSetup.Zoom = false;
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = false;
}
string pdfFilePthe = xlFilePath.Contains(".xls")
? xlFilePath.Replace(".xls", ".pdf")
: xlFilePath.Replace(".xlsx", ".pdf");
string physicalPDfPath = System.Web.HttpContext.Current.Server.MapPath(pdfFilePthe);
wkb.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, physicalPDfPath);
path = pdfFilePthe;
return true;
return false;
}
/// <summary>
/// obtiene el tamaño de hoja para el print dialog
/// </summary>
/// <returns>Page media size</returns>
/// <history>
/// [emoguel] created 23/09/2016
/// </history>
private PageMediaSize setPageSize()
{
XlPaperSize paperSize = (XlPaperSize)cmbPageSize.SelectedValue;
switch (paperSize)
{
case XlPaperSize.xlPaperLegal:
{
return(new PageMediaSize(PageMediaSizeName.NorthAmericaLegal));
}
case XlPaperSize.xlPaperA3:
{
return(new PageMediaSize(PageMediaSizeName.ISOA3));
}
case XlPaperSize.xlPaperA4:
{
return(new PageMediaSize(PageMediaSizeName.ISOA4));
}
case XlPaperSize.xlPaperA5:
{
return(new PageMediaSize(PageMediaSizeName.ISOA5));
}
case XlPaperSize.xlPaper11x17:
{
return(new PageMediaSize(PageMediaSizeName.NorthAmerica11x17));
}
case XlPaperSize.xlPaperNote:
{
return(new PageMediaSize(PageMediaSizeName.NorthAmericaNote));
}
default:
{
return(new PageMediaSize(PageMediaSizeName.NorthAmericaLetter));
}
}
}
public string ConvertToPdf(string xlFilePath, XlPaperSize paperSize)
{
string physicalPath = System.Web.HttpContext.Current.Server.MapPath(xlFilePath);
Application app = new Application();
Workbook wkb = app.Workbooks.Open(physicalPath);
try
{
string path;
if (GeneratePdf(xlFilePath, paperSize, wkb, out path)) return path;
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return "";
}
public static string ExcelToPdf(string inputFilePath, XlPaperSize paperSize = XlPaperSize.xlPaperA4, XlPageOrientation orientation = XlPageOrientation.xlLandscape)
{
var ext = Path.GetExtension(inputFilePath);
if (string.IsNullOrEmpty(ext) || (!ext.Equals(".xls") && !ext.Equals(".xlsx")))
{
throw new ArgumentException("副檔名錯誤！應為*.xls, *.xlsx", nameof(inputFilePath));
}
string outputDir = Path.GetTempPath();
string outputFileName = Guid.NewGuid().ToString().ToUpper();
string outputPath = string.Empty;
if (!File.Exists(inputFilePath))
{
throw new ArgumentException(string.Format("找不到檔案：{0}！", inputFilePath), nameof(inputFilePath));
}
var excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook book = excelApp.Workbooks.Open(inputFilePath);
foreach (Worksheet sheet in book.Sheets)
{
sheet.PageSetup.PaperSize = paperSize;
sheet.PageSetup.Orientation = orientation;
// Fit Sheet on One Page
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 1;
}
outputPath = Path.Combine(outputDir, outputFileName + ".pdf");
book.SaveAs(outputPath, (XlFileFormat)57);
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.Quit();
return(outputPath);
}
/// <summary>
/// Crea un documento dependiendo del fileformat que se requiera
/// </summary>
/// <param name="enumFileFormat">enumerdo con el formato para crear el documento</param>
/// <history>
/// [emoguel] 02/09/2016 created
/// </history>
private void CreateFile(EnumFileFormat enumFileFormat)
{
Workbook wb = null;
Microsoft.Office.Interop.Excel.Application excel = null;
string defaultPrinter = "";
bool blnChagePrinter = false;
try
{
if (_excelFile.Exists)
{
#region Printer
defaultPrinter = PrinterHelper.GetDefaultPrinter();
if (!string.IsNullOrWhiteSpace(defaultPrinter) && defaultPrinter.Contains("pdf", StringComparison.OrdinalIgnoreCase)) //Ver si hay impresora predeterminada y que no sea pdf
{
var lstPrinters = PrinterHelper.getAllPrinters().Where(printer => !printer.Contains("pdf", StringComparison.OrdinalIgnoreCase)).ToList(); //Obtener la lista de impresoras que no sean pdf
//Verificar si hay una impresora xps
string xpsPrinter = lstPrinters.FirstOrDefault(printer => printer.Contains("xps", StringComparison.OrdinalIgnoreCase)); //Buscar impresora XPS
if (!string.IsNullOrWhiteSpace(xpsPrinter)) //Verficar si existe una impresora XPS
{
PrinterHelper.SetDefaultPrinter(xpsPrinter); //Predefinir la impresora XPS
blnChagePrinter = true;
}
else
{
PrinterHelper.SetDefaultPrinter(lstPrinters[0]);//Predefinir la primera impresora
blnChagePrinter = true;
}
}
#endregion
//Obtenemos la orientacion seleccionada
XlPageOrientation pageOrientation = (XlPageOrientation)cmbOrientation.SelectedValue;
//Obtenemos el tamaño de papel seleccionado
XlPaperSize paperSize = (XlPaperSize)cmbPageSize.SelectedValue;
//Obtenemos el margen seleccionado
Margin margin = cmbMargin.SelectedValue as Margin;
//Obtenemos la escala seleccionada
EnumScale enumScale = (EnumScale)cmbScale.SelectedValue;
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
wb = excel.Workbooks.Open(_excelFile.FullName, 0, false, Missing.Value, Missing.Value, Missing.Value, true, XlPlatform.xlWindows, Missing.Value, false, false, Missing.Value, false, true, false);//Cargamos el excel
_Worksheet ws = ((_Worksheet)wb.ActiveSheet);
#region Page Configuration
ws.PageSetup.PaperSize = paperSize; //asignamos el tamaño de hoja
ws.PageSetup.Orientation = pageOrientation; //asignamos orientación de la pagina
ws.PageSetup.LeftMargin = excel.CentimetersToPoints(margin.left); //asignamos Margen Izquierdo
ws.PageSetup.RightMargin = excel.CentimetersToPoints(margin.right); //asignamos Margen Derecho
ws.PageSetup.TopMargin = excel.CentimetersToPoints(margin.top); //asignamos Margen de arriba
ws.PageSetup.BottomMargin = excel.CentimetersToPoints(margin.bottom); //asignamos Margen de abajo
ws.PageSetup.Zoom = false;
#endregion
#region Scale
//Asignamos la escala seleccionada
switch (enumScale)
{
case EnumScale.Noscaling:
{
ws.PageSetup.FitToPagesTall = false;
ws.PageSetup.FitToPagesWide = false;
break;
}
case EnumScale.FitSheetOnOnePage:
{
ws.PageSetup.FitToPagesTall = 1;
ws.PageSetup.FitToPagesWide = 1;
break;
}
case EnumScale.FitAllColumnsOnOnePage:
{
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = false;
break;
}
case EnumScale.FitAllRowsOnOnePage:
{
ws.PageSetup.FitToPagesTall = 1;
ws.PageSetup.FitToPagesWide = false;
break;
}
}
#endregion
ws.PageSetup.Order = XlOrder.xlOverThenDown;//Poner el orden de la paginas
#region Export
switch (enumFileFormat)
{
case EnumFileFormat.Pdf:
{
SaveFileDialog dialog = new SaveFileDialog();//Cargamos el saveFileDialog
dialog.FileName = Uid;
dialog.Filter = "PDF files(*.pdf) | *.pdf;";
if (dialog.ShowDialog() == true)
{
wb.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, dialog.FileName, XlFixedFormatQuality.xlQualityStandard, false, true, Missing.Value, Missing.Value, false, Missing.Value);//Guardamos como PDF
if (File.Exists(dialog.FileName))
{
UIHelper.ShowMessage("Document sufesfully saved.");
Process.Start(dialog.FileName);
}
else
{
UIHelper.ShowMessage("Document not saved.");
}
}
break;
}
case EnumFileFormat.Xps:
{
wb.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, $"{_fullPathAndName}.xps", XlFixedFormatQuality.xlQualityStandard, false, true, Missing.Value, Missing.Value, false, Missing.Value);//Guardamos como XPS
break;
}
}
#endregion
}
}
catch (Exception ex)
{
UIHelper.ShowMessage(ex);
}
finally
{
if (wb != null)
{
wb.Close();
}
if (excel != null)
{
excel.Quit();
}
if (blnChagePrinter)
{
PrinterHelper.SetDefaultPrinter(defaultPrinter);
}
}
}
