public DocExcel(string nameExcel, string nameSheet, XlPageOrientation orientation) //конструктор
{
excelapp = new ExcelApp.Application();
excelapp.Caption = nameExcel;
excelBook = excelapp.Workbooks.Add(Type.Missing);
_currentSheet = (ExcelApp.Worksheet)excelBook.Sheets.get_Item(1);
_currentSheet.PageSetup.Orientation = orientation;
}
//УСТАНОВКА ОРИЕНТАЦИИ СТРАНИЦЫ
public void SetOrientation(XlPageOrientation Orientation)
{
//Range.Interior.ColorIndex
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty,
null, WorkSheet, null);
PageSetup.GetType().InvokeMember("Orientation", BindingFlags.SetProperty,
null, PageSetup, new object[] { 2 });
}
/// <summary>
/// Print excel worksheet
/// </summary>
/// <param name="worksheet">The worksheet which will be print</param>
/// <param name="printerName">The printer which will be used to print the worksheet</param>
/// <param name="pageOrientation">The page orientation</param>
public static void PrintWorksheet(Worksheet worksheet, string printerName, XlPageOrientation pageOrientation)
{
if (!string.IsNullOrEmpty(printerName))
{
worksheet.PageSetup.Orientation = pageOrientation;
worksheet.PrintOut(Missing.Value,
Missing.Value,
1,
Missing.Value,
printerName,
Missing.Value,
Missing.Value,
Missing.Value);
}
}
// Formats the Folder Map Sheet and Page. Can this be changed to
// call the common worksheet format thing?
private static void FormatFolderMapSheet(string strSheetName, XlPageOrientation enumOrientation = XlPageOrientation.xlPortrait)
{
int i = 0;
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range formatRange;
//var _with4 = Globals.ThisAddIn.Application;
for (i = _INITIAL_COL; i <= _Column; i++)
{
activeSheet.Columns[i].ColumnWidth = _COL_WIDTH;
}
activeSheet.Columns[_Column + 1].ColumnWidth = _NOTE_WIDTH;
//activeSheet.Range("A2:I2").Select();
formatRange = activeSheet.Range["A2:I2"];
formatRange.HorizontalAlignment = Constants.xlGeneral;
formatRange.VerticalAlignment = Constants.xlBottom;
formatRange.WrapText = true;
formatRange.Orientation = 0;
formatRange.AddIndent = false;
formatRange.IndentLevel = 0;
formatRange.ShrinkToFit = false;
//_with5.ReadingOrder = Constants.xlContext;
formatRange.MergeCells = false;
formatRange.Font.Bold = true;
activeSheet.Columns["A:A"].ColumnWidth = 13.57;
activeSheet.Columns["B:B"].ColumnWidth = 13.57;
activeSheet.Columns["C:C"].ColumnWidth = 13.43;
activeSheet.Columns["E:E"].ColumnWidth = 6.43;
activeSheet.Columns["F:F"].ColumnWidth = 7.71;
activeSheet.Columns["G:G"].ColumnWidth = 13.57;
activeSheet.Columns["H:H"].ColumnWidth = 13.57;
activeSheet.Columns["I:I"].ColumnWidth = 13.43;
//_with4.Range["K2"].Select();
formatRange = activeSheet.Range["K2"];
formatRange.Font.Name = "Arial";
formatRange.Font.Size = 12;
formatRange.Font.Strikethrough = false;
formatRange.Font.Superscript = false;
formatRange.Font.Subscript = false;
formatRange.Font.OutlineFont = false;
formatRange.Font.Shadow = false;
formatRange.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
formatRange.Font.ColorIndex = Constants.xlAutomatic;
formatRange.Font.Bold = true;
activeSheet.Columns["A:J"].Group();
activeSheet.Columns["A:D"].Group();
activeSheet.Columns["G:I"].Group();
activeSheet.Range["A1"].Select();
}
// $Description :Open the folder to map and descend. Called from frmExcel_FolderMaps.
public static void PopulateFolderMap(ref string startingFolder)
{
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet workSheet;
try
{
// Set starting point for folder output
_Row = _INITIAL_ROW;
_Column = _INITIAL_COL;
_TotalFolders = 0;
_TotalFiles = 0;
_TotalSize = 0;
// Always add a new sheet so can accumulate results in Workbook.
// Need to handle case when no Workbook exists. Tried a variety of
// ways to determine empty workbook. PERSONAL.XLS may be open if
// macros have been created so cannot rely on Workbooks.count.
// Worksheets.Count throws an exception.
//
//Dim wb as Microsoft.Office.Interop.Excel.Workbook
//
//For Each wb In .Workbooks
// Debug.Print(wb.Name)
//Next
//Dim ws As Microsoft.Office.Interop.Excel.Worksheet
//For Each ws In .Worksheets
// Debug.Print(ws.Name)
//Next
// ActiveWorkbook seems to work reliably. Maybe Interop issue, who knows.
if (workbook == null)
{
workbook = Globals.ThisAddIn.Application.Workbooks.Add();
// Get a new WorkSheet (or more :)) for free.
workSheet = workbook.ActiveSheet;
}
else
{
// TODO: Prompt to use existing sheet if found.
workSheet = workbook.Worksheets.Add();
}
workSheet.Cells[_HEADING_ROW, _FOLDER_INFO_COL].Value = "Cummulative Folder Count";
workSheet.Cells[_HEADING_ROW, _FOLDER_INFO_COL + 1].Value = "Cummulative File Count";
workSheet.Cells[_HEADING_ROW, _FOLDER_INFO_COL + 2].Value = "Cummulative Size";
workSheet.Cells[_HEADING_ROW, _FILE_INFO_COL].Value = "Count";
workSheet.Cells[_HEADING_ROW, _FILE_INFO_COL + 1].Value = "Size";
workSheet.Cells[_HEADING_ROW, _FILE_INFO_COL + 2].Value = "Last Create";
workSheet.Cells[_HEADING_ROW, _FILE_INFO_COL + 3].Value = "Last Write";
workSheet.Cells[_HEADING_ROW, _FILE_INFO_COL + 4].Value = "Last Access";
workSheet.Cells[_HEADING_ROW, _INITIAL_COL].Value = startingFolder;
int startingRow = _Row;
int startingColumn = _Column;
//int numberFoldersLocal = 0;
//int numberFilesLocal = 0;
//long sizeFilesLocal = 0;
FileInfo dirInfo = new FileInfo(startingFolder);
System.DateTime maxLastCreate = System.DateTime.MinValue;
System.DateTime maxLastWrite = System.DateTime.MinValue;
System.DateTime maxLastAccess = System.DateTime.MinValue;
//int column = 1;
int fontSize = 10;
// Note: maxLastDate is passed in, even though we don't use the updated value.
// it is used during the recursion process when ListFolders calls itself. Do
// not be mislead and rework this logic!
ListFolders(startingFolder, _Column, fontSize, ref _TotalFolders, ref _TotalFiles, ref _TotalSize, ref maxLastCreate, ref maxLastWrite, ref maxLastAccess, _ShowFiles, _ShowFolders);
XlPageOrientation orientation = XlPageOrientation.xlPortrait;
FormatFolderMapSheet(startingFolder, orientation);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw ex;
}
}
//сярюмнбйю нпхемрюжхх ярпюмхжш
public void SetOrientation(XlPageOrientation Orientation)
{
//Range.Interior.ColorIndex
object PageSetup = WorkSheet.GetType().InvokeMember("PageSetup", BindingFlags.GetProperty,
null, WorkSheet, null);
PageSetup.GetType().InvokeMember("Orientation", BindingFlags.SetProperty,
null, PageSetup, new object[] { 2 });
}
/// <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);
}
}
}
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);
}
