gbase/cmd/excel.go

package cmd

import (
	"fmt"
	"log"

	"github.com/go-ole/go-ole"
	"github.com/go-ole/go-ole/oleutil"
)

// @title    ExcelSheetExportAsPdf
// @description    把excel文件指定的sheet导出为pdf文件
// @param xlsxPath string excel文件路径,pdfPath string pdf文件路径
// @return

func ExcelSheetExportAsPdf(xlsxPath, pdfPath string, sheetIndex int) error {

	//CoInitialize是Windows提供的API函数，用来告诉 Windows以单线程的方式创建com对象。
	//应用程序调用com库函数（除CoGetMalloc和内存分配函数）之前必须初始化com库。
	ole.CoInitialize(0)
	defer ole.CoUninitialize()

	//创建com对象
	iunk, err := oleutil.CreateObject("Excel.Application")
	if err != nil {
		log.Printf("error creating Wps object: %s", err)
		return fmt.Errorf("error creating Wps object: %s", err)

	}
	defer iunk.Release()

	// 获取能够遍历的对象
	excel := iunk.MustQueryInterface(ole.IID_IDispatch)
	defer excel.Release()

	//获取工作簿对象
	workbooks := oleutil.MustGetProperty(excel, "Workbooks").ToIDispatch()
	defer workbooks.Release()

	//打开指定工作簿
	workbook := oleutil.MustCallMethod(workbooks, "Open", xlsxPath).ToIDispatch()
	defer workbook.Release()

	//获取指定sheet
	worksheet := oleutil.MustGetProperty(workbook, "Worksheets", sheetIndex).ToIDispatch()
	defer worksheet.Release()

	//获取指定sheet的PageSetup
	PageSetup, err := oleutil.GetProperty(worksheet, "PageSetup")
	if err != nil {
		log.Printf("Worksheets(1) get PageSetup error: %s", err)
	} else {
		////设置指定sheet的PageSetup.FitToPagesTall
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "FitToPagesWide", 1)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put FitToPagesTall error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "LeftMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put LeftMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "RightMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put RightMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "TopMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put TopMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "BottomMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put BottomMargin error: %s", err)
		}
	}

	// 导出为指定格式
	oleutil.MustCallMethod(worksheet, "ExportAsFixedFormat", 0, pdfPath)
	//todo 如果不保存wps会弹窗,必须先保存.暂时未找到解决方案
	oleutil.PutProperty(workbook, "Saved", true)
	oleutil.MustCallMethod(workbook, "Close")
	oleutil.MustCallMethod(excel, "Quit")
	return nil
}

// @title    ExcelExportAsPdf
// @description    excel文件导出为pdf文件
// @param xlsxPath string excel文件路径,pdfPath string pdf文件路径
// @return

func ExcelExportAsPdf(xlsxPath, pdfPath string) error {

	//CoInitialize是Windows提供的API函数，用来告诉 Windows以单线程的方式创建com对象。
	//应用程序调用com库函数（除CoGetMalloc和内存分配函数）之前必须初始化com库。
	ole.CoInitialize(0)
	defer ole.CoUninitialize()

	//创建com对象
	iunk, err := oleutil.CreateObject("Excel.Application")
	if err != nil {
		log.Printf("error creating Wps object: %s", err)
		return fmt.Errorf("error creating Wps object: %s", err)

	}
	defer iunk.Release()

	// 获取能够遍历的对象
	excel := iunk.MustQueryInterface(ole.IID_IDispatch)
	defer excel.Release()

	//获取工作簿对象
	workbooks := oleutil.MustGetProperty(excel, "Workbooks").ToIDispatch()
	defer workbooks.Release()

	//打开指定工作簿
	workbook := oleutil.MustCallMethod(workbooks, "Open", xlsxPath).ToIDispatch()
	defer workbook.Release()

	//获取指定sheet
	worksheet := oleutil.MustGetProperty(workbook, "Worksheets", 1).ToIDispatch()
	defer worksheet.Release()

	//获取指定sheet的PageSetup
	PageSetup, err := oleutil.GetProperty(worksheet, "PageSetup")
	if err != nil {
		log.Printf("Worksheets(1) get PageSetup error: %s", err)
	} else {
		////设置指定sheet的PageSetup.FitToPagesTall
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "FitToPagesWide", 1)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put FitToPagesTall error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "LeftMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put LeftMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "RightMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put RightMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "TopMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put TopMargin error: %s", err)
		}
		_, err = oleutil.PutProperty(PageSetup.ToIDispatch(), "BottomMargin", 0)
		if err != nil {
			log.Printf("Worksheets(1).PageSetup put BottomMargin error: %s", err)
		}
	}

	// 导出为指定格式
	oleutil.MustCallMethod(worksheet, "ExportAsFixedFormat", 0, pdfPath)
	//todo 如果不保存wps会弹窗,必须先保存.暂时未找到解决方案
	oleutil.PutProperty(workbook, "Saved", true)
	oleutil.MustCallMethod(workbook, "Close")
	oleutil.MustCallMethod(excel, "Quit")
	return nil
}

// @title    ExcelPrintOut
// @description    调用系统的pdf虚拟打印机生成pdf文件.
// @param xlsxPath string excel文档路径,pdfPath string 生成的pdf文档路径
// @return
// todo 由于打印完需要手动点击确认保存,未找到解决方法

func ExcelPrintOut(xlsxPath, pdfPath string) error {
	defer func() {
		if err := recover(); err != nil {
			log.Printf("ExcelPrintOut error: %v\n", err)
		}
	}()

	ole.CoInitialize(0)
	defer ole.CoUninitialize()

	unknown, err := oleutil.CreateObject("KET.Application")
	if err != nil {
		log.Printf("creating Excel object error: %s", err)
		return fmt.Errorf("please make sure wps installed.\ncreating Excel object error: %s\n", err)
	}
	defer unknown.Release()

	excel := unknown.MustQueryInterface(ole.IID_IDispatch)
	defer excel.Release()

	oleutil.PutProperty(excel, "DisplayAlerts", false)
	oleutil.PutProperty(excel, "Visible", false)

	// opening then saving works due to the call to doc.Settings.SetUpdateFieldsOnOpen(true) above
	workbooks := oleutil.MustGetProperty(excel, "Workbooks").ToIDispatch()
	defer workbooks.Release()

	workbook := oleutil.MustCallMethod(workbooks, "Open", xlsxPath).ToIDispatch()
	defer workbook.Release()

	//oleutil.MustPutProperty(books, "ActiveWorkBook",1)
	// println("==============1")

	worksheet := oleutil.MustGetProperty(workbook, "Worksheets", 1).ToIDispatch()
	defer worksheet.Release()

	// println("==============2")

	// 如果 FitToPagesTall 此屬性為 False，Microsoft Excel 會根據 FitToPagesWide 屬性調整工作表。
	ret, err := oleutil.GetProperty(worksheet, "", "PageSetup", "FitToPagesTall", 1)
	fmt.Printf("FitToPagesTall %v,err==============>%v\n", ret, err)
	_, err = oleutil.PutProperty(worksheet, "", "PageSetup", ".FitToPagesWide", 1)
	fmt.Printf("FitToPagesWide err==============>%v\n", err)

	//
	// https://docs.microsoft.com/zh-cn/office/vba/api/excel.sheets.printout
	/*
		PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)
		From	打印的開始頁號。如果省略此參數，則從起始位置開始打印。
		To	    打印的終止頁號。如果省略此參數，則打印至最後一頁。
		Copies	打印份數。如果省略此參數，則只打印一份。
		Preview	如果爲 True，Microsoft Excel 將在打印對象之前調用打印預覽。如果爲 False（或省略該參數），則立即打印對象。
		ActivePrinter	設置活動打印機的名稱。
		PrintToFile	如果爲 True，則打印到文件。如果沒有指定 PrToFileName，Microsoft Excel 將提示用戶輸入要使用的輸出文件的文件名。
		Collate	如果爲 True，則逐份打印多個副本。
		PrToFileName	如果 PrintToFile 設爲 True，則該參數指定要打印到的文件名。
		注：From 和 To 所描述的“頁”指的是要打印的頁，並非指定工作表或工作簿中的全部頁。
	*/
	oleutil.MustCallMethod(worksheet, "PrintOut", 1, 1, 1, false, "导出为WPS PDF", true, pdfPath, true)

	oleutil.MustCallMethod(workbook, "Close")
	oleutil.MustCallMethod(excel, "Quit")
	return nil

}