AsposeExcelHelper

using System;
using System.Linq;
using Aspose.Cells;
using System.IO;
using Neu.Uasd.HrPrinter.Utility.Log4;
using Neu.Uasd.HrPrinter.Utility.entity.certModel;
using System.Collections.Generic;
using Aspose.Cells.Rendering;

namespace Neu.Uasd.HrPrinter.Utility.Tools
{
    public class AsposeExcelHelper
    {

     
        public static void Excel2Tiff(string fileDoc, string outPutFile )
        { 

            Workbook CurrentWorkbook = new Workbook(fileDoc);

            if (File.Exists(outPutFile))
            {
                try
                {
                    File.Delete(outPutFile);
                }
                catch (Exception ex)
                {
                        Error(ex);
                }
            }
            CurrentWorkbook.Save(outPutFile, SaveFormat.TIFF); 
        }
        public static void Excel2Xps(string fileDoc, string outPutFile)
        {

            Workbook CurrentWorkbook = new Workbook(fileDoc);

            if (File.Exists(outPutFile))
            {
                try
                {
                    File.Delete(outPutFile);
                }
                catch (Exception ex)
                {
                        Error(ex);
                }
            }
            CurrentWorkbook.Save(outPutFile, SaveFormat.XPS);
        }

        public static void UpdateSealImage(string fileName,List<SealItem> sealItems, string outFileName )
        {
            //string SealFileName = "";
           // string fileName = "";

            if (File.Exists(fileName))
            {

                Workbook CurrentWorkbook = new Workbook(fileName);

                //  打开第一个sheet
                Worksheet DetailSheet = CurrentWorkbook.Worksheets[0];

                //int iIndex = DetailSheet.Pictures.Add(1, 1, "d:\\1.jpg");

                //Aspose.Cells.Drawing.Picture pic = DetailSheet.Pictures[iIndex];
                //pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
                //pic.BorderLineColor = Color.Red;
                //pic.BorderWeight = 5;

                //Get the first worksheet pictures collection 
                Aspose.Cells.Drawing.PictureCollection pictures = CurrentWorkbook.Worksheets[0].Pictures;


                foreach (var sealItem in sealItems)
                {
                      //Insert the picture from the stream to B2 cell 
                    int iIndex = pictures.Add(sealItem.UpperLeftRow, sealItem.UpperLeftColumn, sealItem.SealFileName);
                    Aspose.Cells.Drawing.Picture pic = DetailSheet.Pictures[iIndex];
                    pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
               
                    pic.HeightCM = sealItem.SealHeithgCM;
                    pic.WidthCM = sealItem.SealWidthCM;

                    pic.Left = sealItem.SealLeftOffset;
                    pic.Top = sealItem.SealTopOffset;
                }
              
                
               // pic.BorderLineColor = Color.Red;
               // pic.BorderWeight = 0;

                //DetailSheet.ActiveCell = "A1";
               // DetailSheet.AutoFitRows();
                //Save the excel file
                RemoveOtherSheet(CurrentWorkbook);
                CurrentWorkbook.Save(outFileName);
            }
             
        }

        public static void SetTopCell(string fileName, string outFileName, List<int> sealSheet)
        {


            if (File.Exists(fileName))
            {

                Workbook CurrentWorkbook = new Workbook(fileName);



                foreach (int iSheet in sealSheet)
                {


                    //  打开第一个sheet
                    Worksheet DetailSheet = CurrentWorkbook.Worksheets[iSheet];

                    DetailSheet.FirstVisibleRow = 0;
                    DetailSheet.FirstVisibleColumn = 0;

                    DetailSheet.ActiveCell = "A1";

                    //   DetailSheet.Cells[5, 1].PutValue("aabb");
                    DetailSheet.AutoFitRows();

                    if (iSheet == 1)
                    {

                        int columnCount = DetailSheet.Cells.MaxColumn;  //获取表页的最大列数
                        //int rowCount = DetailSheet.Cells.MaxRow;        //获取表页的最大行数

                        //for (int col = 0; col < columnCount; col++)
                        //{
                        //    DetailSheet.AutoFitColumn(col, 0, rowCount);
                        //}
                        //for (int col = 0; col < columnCount; col++)
                        //{
                        //    DetailSheet.Cells.SetColumnWidthPixel(col, DetailSheet.Cells.GetColumnWidthPixel(col) + 30);

                        //}
                        //if (columnCount >= 0)
                        //{
                        //    DetailSheet.Cells.SetColumnWidthPixel(columnCount, DetailSheet.Cells.GetColumnWidthPixel(columnCount) - 10);
                        //}
                    }
                }



                CurrentWorkbook.Save(outFileName);
            }
        }

        public static void SetTopCell(string fileName, string outFileName)
        {
            

            if (File.Exists(fileName))
            {

                Workbook CurrentWorkbook = new Workbook(fileName);

                int sheetCount =  CurrentWorkbook.Worksheets.Count;

                for (int iSheet = 0; iSheet < sheetCount; iSheet++)
                {
                    
                 
                    //  打开第一个sheet
                    Worksheet DetailSheet = CurrentWorkbook.Worksheets[iSheet];

                    DetailSheet.FirstVisibleRow = 0;
                     DetailSheet.FirstVisibleColumn = 0;
                
                     DetailSheet.ActiveCell = "A1";

              //   DetailSheet.Cells[5, 1].PutValue("aabb");
                     DetailSheet.AutoFitRows();

                     if (iSheet == 1)
                     {

                         int columnCount = DetailSheet.Cells.MaxColumn;  //获取表页的最大列数
                         //int rowCount = DetailSheet.Cells.MaxRow;        //获取表页的最大行数

                         //for (int col = 0; col < columnCount; col++)
                         //{
                         //    DetailSheet.AutoFitColumn(col, 0, rowCount);
                         //}
                         //for (int col = 0; col < columnCount; col++)
                         //{
                         //    DetailSheet.Cells.SetColumnWidthPixel(col, DetailSheet.Cells.GetColumnWidthPixel(col) + 30);

                         //}
                         //if (columnCount >= 0)
                         //{
                         //    DetailSheet.Cells.SetColumnWidthPixel(columnCount, DetailSheet.Cells.GetColumnWidthPixel(columnCount) - 10);
                         //}
                     }
                }
                

              
                CurrentWorkbook.Save(outFileName);
            }
        }

        public static void UpdateSealImage(string fileName, List<SealItem> sealItems, string outFileName,List<int> sealSheet )
        {
            //string SealFileName = "";
            // string fileName = "";

            if (File.Exists(fileName))
            {

                Workbook CurrentWorkbook = new Workbook(fileName);

                foreach (int iSheet in sealSheet)
                {
                   //  打开第一个sheet
                    Worksheet DetailSheet = CurrentWorkbook.Worksheets[iSheet]; 
                    //Get the first worksheet pictures collection 
                    Aspose.Cells.Drawing.PictureCollection pictures = DetailSheet.Pictures;


                    foreach (var sealItem in sealItems)
                    {
                        //Insert the picture from the stream to B2 cell 
                        int iIndex = pictures.Add(sealItem.UpperLeftRow, sealItem.UpperLeftColumn, sealItem.SealFileName);
                        Aspose.Cells.Drawing.Picture pic = DetailSheet.Pictures[iIndex];
                        pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;

                        pic.HeightCM = sealItem.SealHeithgCM;
                        pic.WidthCM = sealItem.SealWidthCM;

                        pic.Left = sealItem.SealLeftOffset;
                        pic.Top = sealItem.SealTopOffset;
                    }

                   // DetailSheet.AutoFitRows();
                   // DetailSheet.AutoFitColumns();
                     
                }

              
                CurrentWorkbook.Save(outFileName);
            }

        }
        private static void RemoveOtherSheet(Workbook CurrentWorkbook)
        {
            

            //CurrentWorkbook.Worksheets.Clear();
            int sheetCount = CurrentWorkbook.Worksheets.Count();

            for (int i = 1; i < sheetCount; i++)
            { 
                //CurrentWorkbook.Worksheets.RemoveAt(i);

                int delIndex = CurrentWorkbook.Worksheets.Count() - 1;
                CurrentWorkbook.Worksheets.RemoveAt(delIndex);
            }  

        }

        private static void RemoveOtherSheet(Workbook CurrentWorkbook, List<int> removeSheet)
        {
             

        }

        public static void PrintDoc(string fileName)
        {

            if (!System.IO.File.Exists(fileName))
            {
                    Info("打印文件未找到:" + fileName);

            }
            else
            {
                try
                {
                    System.Drawing.Printing.PrinterSettings printSettings = new System.Drawing.Printing.PrinterSettings();
                    string strPrinterName = printSettings.PrinterName;

                    Workbook workbook = new Workbook(fileName);
                    int sheetCounts = workbook.Worksheets.Count();

                    for (int i = 0; i < sheetCounts; i++)
                    {
                             //Get the worksheet to be printed
                        Worksheet worksheet = workbook.Worksheets[i];
                        PageSetup pageSetup = worksheet.PageSetup;

                        
                        //pageSetup.Orientation = PageOrientationType.Landscape;
                        
                        //pageSetup.FitToPagesWide = 1;
                        //pageSetup.FitToPagesTall = 1;


                       pageSetup.Zoom = 100;


                        //pageSetup.LeftMargin = 0;
                        //pageSetup.RightMargin = 0.1;
                        //pageSetup.BottomMargin = 0.3;
                        //pageSetup.PrintArea = "A2:J29";
                        //Apply different Image / Print options.
                        Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
                        options.PrintWithStatusDialog = false;
                        //Set the Printing page property
                        //options.PrintingPage = PrintingPageType.IgnoreStyle;
                        //Render the worksheet
                        SheetRender sr = new SheetRender(worksheet, options);



                        //send to printer
                       // Image map = sr.ToImage(1);

                        sr.ToPrinter(strPrinterName);
                    }

                   
                }
                catch (Exception ex)
                {
                        Error("打印文件异常:" + fileName);
                        Error(ex);
                }
            }

        }


      
    }
}