/*
* Page layout for printing
*/
private void PageLayout()
{
xlSheet.PageSetup.CenterVertically = false;
xlSheet.PageSetup.CenterHorizontally = true;
xlSheet.PageSetup.TopMargin = xlApp.CentimetersToPoints(0.50);
xlSheet.PageSetup.LeftMargin = xlApp.CentimetersToPoints(0.50);
xlSheet.PageSetup.RightMargin = xlApp.CentimetersToPoints(0.50);
xlSheet.PageSetup.BottomMargin = xlApp.CentimetersToPoints(0.50);
xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
}
private void ChangeSize(object sender, RibbonControlEventArgs e)
{
Excel.Application application = Globals.ThisAddIn.Application;
Excel.Worksheet worksheet = application.ActiveSheet;
float y = 3f / 4f;
if (!int.TryParse(txtSize.Text, out int size))
{
MessageBox.Show("Укажите верный масштаб. Целое число больше 0", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
worksheet.Cells.RowHeight = y * size;
worksheet.Cells.ColumnWidth = 2f / 19f * size;
double x = application.CentimetersToPoints(size / 3.779f / 10);
while (worksheet.Columns[2].Left - worksheet.Columns[1].Left - 0.1f > x)
{
Application.DoEvents();
worksheet.Cells.ColumnWidth = worksheet.Cells.ColumnWidth - 0.1f;
}
while (worksheet.Columns[2].Left - worksheet.Columns[1].Left - 0.1f < x)
{
Application.DoEvents();
worksheet.Cells.ColumnWidth = worksheet.Cells.ColumnWidth + 0.1f;
}
worksheet.Cells.ColumnWidth = worksheet.Cells.ColumnWidth - 0.1f;
}
/// <summary>
/// シートの印刷設定を整えます。
/// </summary>
/// <param name="worksheet"></param>
private void FixPageSetup(Excel.Worksheet worksheet, PageSetup pageSetup)
{
// 印刷設定
Dictionary <string, PaperSize> paperSizes = PaperSizesCreator.paperSizes;
// 印刷設定
worksheet.PageSetup.TopMargin = ExcelApp.CentimetersToPoints(pageSetup.TopMargin);
worksheet.PageSetup.BottomMargin = ExcelApp.CentimetersToPoints(pageSetup.BottmonMargin);
worksheet.PageSetup.LeftMargin = ExcelApp.CentimetersToPoints(pageSetup.LeftMargin);
worksheet.PageSetup.RightMargin = ExcelApp.CentimetersToPoints(pageSetup.RightMargin);
worksheet.PageSetup.HeaderMargin = ExcelApp.CentimetersToPoints(pageSetup.HeaderMargin);
worksheet.PageSetup.FooterMargin = ExcelApp.CentimetersToPoints(pageSetup.FooterMargin);
worksheet.PageSetup.Zoom = pageSetup.Zoom;
worksheet.PageSetup.PaperSize = (Excel.XlPaperSize)PaperSizesCreator.paperSizes[pageSetup.PaperSize].Value;
worksheet.PageSetup.Orientation = (Excel.XlPageOrientation)PageOrientationsCreator.pageOrientations[pageSetup.Orientation].Value;
}
public static void SetPrintSettings(Excel.Worksheet sheet)
{
xlApp.PrintCommunication = false;
Excel.PageSetup pageSetup = (Excel.PageSetup)sheet.PageSetup;
pageSetup.BottomMargin = xlApp.CentimetersToPoints(0.0);
pageSetup.TopMargin = xlApp.CentimetersToPoints(0.5);
pageSetup.LeftMargin = xlApp.CentimetersToPoints(0.7);
pageSetup.RightMargin = xlApp.CentimetersToPoints(0.5);
pageSetup.HeaderMargin = xlApp.CentimetersToPoints(0.0);
pageSetup.FooterMargin = xlApp.CentimetersToPoints(0.0);
pageSetup.PrintArea = "A:Z";
pageSetup.Zoom = 100;
xlApp.PrintCommunication = true;
SetPageSpecificSettings(sheet, pageSetup);
}
/// <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);
}
}
}
/// <summary>
/// 对齐打印边界， 以适应图纸的打印区域。
/// </summary>
/// <param name="app"></param>
/// <param name="rightBoundary">右边界的位置，以厘米为单位</param>
/// <remarks> 由于 单元格的宽度 中一个列宽单位等于“常规”样式中一个字符的宽度。
/// 对于比例字体，则使用字符 0（零）的宽度，而 形状的宽度和定位 是以 磅为单位，1 point = 1/72 inch；
/// 而不同的字符宽度略有区别，为了精确定位，可以通过迭代以达到一个差异精度。</remarks>
public static void 以磅为单位为定位单元格宽度(Application app, double rightBoundary, double bottomBoundary)
{
rightBoundary = app.CentimetersToPoints(Centimeters: rightBoundary); // 单位转换
bottomBoundary = app.CentimetersToPoints(Centimeters: bottomBoundary); // 单位转换
//
var sht = app.ActiveSheet as Worksheet;
var sele = app.Selection as Range;
if (sele != null)
{
var conner = sele.Ex_CornerCell(CornerIndex.BottomRight);
// 调整行高
if (bottomBoundary > 0)
{
Range row = sht.Rows[conner.Row];
try
{
//
double oldRowHight, newRowHight;
double diff = 100;
double oldDiff = diff - 1;
// 而不同的字符宽度略有区别，为了精确定位，可以通过迭代以达到一个差异精度。
while (diff > 0.5 && Math.Abs(diff - oldDiff) > 0.01) // ' 0.5 ' 单位为磅，即 0.17mm
{
oldDiff = diff;
// 设置列宽 √
oldRowHight = row.RowHeight;
var ratio = row.Height / oldRowHight; // 磅 转换为 列宽单位 的比例
newRowHight = (bottomBoundary - row.Top) / ratio;
if (newRowHight < 0)
{
throw new InvalidOperationException("指定的底边界位于所选单元格上侧！");
}
row.RowHeight = newRowHight;
diff = Math.Abs(newRowHight - oldRowHight);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, @"提示");
var line = sht.Shapes.AddLine(0, (float)bottomBoundary, 100, (float)bottomBoundary);
line.Placement = XlPlacement.xlFreeFloating;
}
}
// 调整列宽
if (rightBoundary > 0)
{
Range col = sht.Columns[conner.Column];
try
{
//
double oldcolwidth, newcolwidth;
double diff = 100;
double oldDiff = diff - 1;
// 由于 ColumnWidth 中一个列宽单位等于“常规”样式中一个字符的宽度。对于比例字体，则使用字符 0（零）的宽度，而Width是以 磅为单位，1 point = 1/72 inch；
// 而不同的字符宽度略有区别，为了精确定位，可以通过迭代以达到一个差异精度。
while (diff > 0.5 && Math.Abs(diff - oldDiff) > 0.01) // ' 0.00005 ' 单位为磅，即 0.17mm
{
oldDiff = diff;
// 设置列宽 √
oldcolwidth = col.ColumnWidth;
var ratio = col.Width / oldcolwidth; // 磅 转换为 列宽单位 的比例
newcolwidth = (rightBoundary - col.Left) / ratio;
if (newcolwidth < 0)
{
throw new InvalidOperationException("指定的右边界位于所选单元格左侧！");
}
col.ColumnWidth = newcolwidth;
diff = Math.Abs(newcolwidth - oldcolwidth);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, @"提示");
var line = sht.Shapes.AddLine((float)rightBoundary, 0, (float)rightBoundary, 100);
line.Placement = XlPlacement.xlFreeFloating;
}
}
}
}
private void Export()
{
string fileName = "Excel.xls";
var rep = (ReportPreviewModel.Report as OrderFormReport);
rep.ExportToXls(fileName, new XlsExportOptions());
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Workbook ObjWorkBookGeneral;
ObjWorkBookGeneral = ObjExcel.Workbooks.Open(Environment.CurrentDirectory + "\\" + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
if (File.Exists(Environment.CurrentDirectory + "\\" + fileName))
File.Copy(Environment.CurrentDirectory + "\\" + fileName, Environment.CurrentDirectory + "\\Result_" + fileName, true);
Workbook ObjWorkBookResult = ObjExcel.Workbooks.Open(Environment.CurrentDirectory + "\\Result_" + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
(ObjWorkBookResult.ActiveSheet as Worksheet).UsedRange.Clear();
try
{
Worksheet sheet = ObjWorkBookGeneral.ActiveSheet;
if (sheet != null)
{
Range range = sheet.UsedRange;
if (range != null)
{
int nRows = range.Rows.Count;
int nCols = range.Columns.Count;
Range tempRange;
Range startCell = null;
Range endCell = null;
string animationName = string.Empty;
int currentSheetIndex = 0;
int k = 0;
for (int i = 1; i <= nRows + 1; i++)
{
string d = range[i, 1].value;
if (d == "xrStart" || i == nRows + 1)
{
k = 1;
if (startCell != null || i == nRows + 1)
{
endCell = range.Cells[i - 1, nCols];
tempRange = range.Range[startCell, endCell];
currentSheetIndex = currentSheetIndex + 1;
((Worksheet)ObjWorkBookResult.Sheets[currentSheetIndex]).Select(Type.Missing);
ObjWorkBookResult.ActiveSheet.Name = animationName;
tempRange.Copy(Type.Missing);
ObjWorkBookResult.ActiveSheet.PasteSpecial(XlPasteType.xlPasteColumnWidths);
ObjWorkBookResult.ActiveSheet.PasteSpecial(XlPasteType.xlPasteAllUsingSourceTheme);
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.Orientation = XlPageOrientation.xlLandscape;
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.TopMargin = ObjExcel.CentimetersToPoints(1);
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.BottomMargin = ObjExcel.CentimetersToPoints(1);
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.HeaderMargin = 0;
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.FooterMargin = 0;
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.LeftMargin = ObjExcel.CentimetersToPoints(1);
(ObjWorkBookResult.ActiveSheet as Worksheet).PageSetup.RightMargin = ObjExcel.CentimetersToPoints(1);
startCell = range.Cells[i, 1];
if (range.Cells[i + 1, 1].value != null)
{
animationName = range.Cells[i + 1, 1].value;
animationName = RemoveSpecialCharacters(animationName);
if (animationName.Length > 30)
animationName = animationName.Substring(0, 30);
ObjWorkBookResult.Sheets.Add(Type.Missing, ObjWorkBookResult.ActiveSheet,
Type.Missing, Type.Missing);
}
}
else
{
startCell = range.Cells[i, 1];
animationName = range.Cells[i + 1, 1].value;
animationName = RemoveSpecialCharacters(animationName);
if (animationName.Length > 30)
animationName = animationName.Substring(0, 30);
}
}
Worksheet sh = (ObjWorkBookResult.ActiveSheet as Worksheet);
(sh.Rows[k] as Range).RowHeight = (range.Rows[i] as Range).RowHeight;
k++;
}
}
}
}
finally
{
ObjWorkBookResult.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
ObjWorkBookGeneral.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
ObjExcel.Quit();
File.Delete(fileName);
File.Copy("Result_" + fileName, fileName);
File.Delete("Result_" + fileName);
}
if (File.Exists(fileName))
Process.Start(fileName);
}