.Net 使用 SDK 呼叫 LibreOffice 5x 不像 OpenOffice 一樣安裝後系統就可以讀取得到, 相關設定如下
整合成 Util 方便重複使用
- Visual Studio 2015 的 Visual C++ 可轉散發套件. x86 或 x64 均可.
- 安裝 LibreOffice_5x
- 安裝 LibreOffice_5.x_sdk.msi 安裝後在 cli 目錄下取出 dll 到專案的 bin 下
- windows 系統 ->環境變數, 設定, 於 path後加入 安裝路徑如 ..LibreOffice\program
整合成 Util 方便重複使用
using System; using System.Web; using unoidl.com.sun.star.beans; using unoidl.com.sun.star.container; using unoidl.com.sun.star.frame; using unoidl.com.sun.star.lang; using unoidl.com.sun.star.sheet; using unoidl.com.sun.star.table; using unoidl.com.sun.star.text; using unoidl.com.sun.star.uno; /// <summary> /// Open Office 的摘要描述 /// </summary> public class OOUtils { //excel com.sun.star.sheet.XSheetFilterable public static XComponent openCalcSheet() { XComponentContext oStrap = uno.util.Bootstrap.bootstrap(); XMultiServiceFactory oServMan = (XMultiServiceFactory)oStrap.getServiceManager(); XComponentLoader desktop = (XComponentLoader)oServMan.createInstance("com.sun.star.frame.Desktop"); string url = @"private:factory/scalc"; PropertyValue[] loadProps = new PropertyValue[1]; loadProps[0] = new PropertyValue(); loadProps[0].Name = "Hidden"; loadProps[0].Value = new uno.Any(true); //PropertyValue[] loadProps = new PropertyValue[0]; XComponent document = desktop.loadComponentFromURL(url, "_blank", 0, loadProps); return document; } //取得工作表 public static XSpreadsheet getSheet(XComponent component, int sheetInx) { XSpreadsheets oSheets = ((XSpreadsheetDocument)component).getSheets(); XIndexAccess oSheetsIA = (XIndexAccess)oSheets; return (XSpreadsheet)oSheetsIA.getByIndex(sheetInx).Value; } public static XSpreadsheet getSheet(XComponent component, string sheetName) { XSpreadsheets oSheets = ((XSpreadsheetDocument)component).getSheets(); //XNameAccess oSheetNA = (XNameAccess)oSheets; //return (XSpreadsheet)oSheetNA.getByName(sheetName).Value; return (XSpreadsheet)oSheets.getByName(sheetName).Value; } //寫入儲存格 public static void writeCell(XSpreadsheet sheet, int column, int row, string data) { writeCell(sheet, column, row, data, false, false); } public static void writeCell(XSpreadsheet sheet, int column, int row, string data, bool fontBold, bool textCenter) { XCell cell = sheet.getCellByPosition(column, row); //A1 cell.setFormula(data); //((XText)cell).setString(data); cellStyle(cell, fontBold, textCenter); } public static void writeCell(XSpreadsheet sheet, int column, int row, double data) { writeCell(sheet, column, row, data, false, false); } public static void writeCell(XSpreadsheet sheet, int column, int row, double data, bool fontBold, bool textCenter) { XCell cell = sheet.getCellByPosition(column, row); //A1 cell.setValue(data); cellStyle(cell, fontBold, textCenter); } //粗體, 置中 private static void cellStyle(XCell cell, bool fontBold, bool textCenter) { XPropertySet xPropSet = (unoidl.com.sun.star.beans.XPropertySet)cell; if (fontBold) { xPropSet.setPropertyValue("CharWeight", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); xPropSet.setPropertyValue("CharWeightAsian", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); xPropSet.setPropertyValue("CharWeightComplex", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); } if (textCenter) { xPropSet.setPropertyValue("HoriJustify", new uno.Any((Int32)unoidl.com.sun.star.table.CellHoriJustify.CENTER)); xPropSet.setPropertyValue("VertJustify", new uno.Any((Int32)unoidl.com.sun.star.table.CellVertJustify.CENTER)); } } /// <summary> /// 變更工作表名稱 /// </summary> /// <param name="sheet"></param> /// <param name="sName"></param> public static void setSheetName(XSpreadsheet sheet, String sName) { unoidl.com.sun.star.container.XNamed xNamed = (unoidl.com.sun.star.container.XNamed)sheet; xNamed.setName(sName); } //檔案儲存 public static void saveCalc(XComponent xComponent, String fName) { PropertyValue[] propVals = new PropertyValue[1]; propVals[0] = new PropertyValue(); propVals[0].Name = "FilterName"; propVals[0].Value = new uno.Any("calc8"); string fileName = fName;// @"z:\test.ods"; fileName = "file:///" + fileName.Replace(@"\", "/"); ((XStorable)xComponent).storeToURL(fileName, propVals); } // public static void responseFile(HttpResponse response, string outputFileName, string sourceFile) { response.ClearContent(); response.Clear(); response.ContentType = "application/vnd.ods"; //response.ContentType = "text/plain"; response.AddHeader("Content-Disposition", "attachment; filename=" + outputFileName + ";"); response.TransmitFile(sourceFile);//"z:/test.ods"); response.Flush(); response.End(); } //自動調整欄寬 public static void optimalWidth(XSpreadsheet sheet) { // gets the used range of the sheet XSheetCellCursor XCursor = sheet.createCursor(); XUsedAreaCursor xUsedCursor = (XUsedAreaCursor)XCursor; xUsedCursor.gotoStartOfUsedArea(true); xUsedCursor.gotoEndOfUsedArea(true); XCellRangeAddressable nomCol = (XCellRangeAddressable)xUsedCursor; XColumnRowRange RCol = (XColumnRowRange)nomCol; XTableColumns LCol = RCol.getColumns(); // loops round all of the columns for (int i = 0; i <= nomCol.getRangeAddress().EndColumn; i++) { XPropertySet xPropSet = (XPropertySet)LCol.getByIndex(i).Value; xPropSet.setPropertyValue("OptimalWidth", new uno.Any(true)); } } //合併儲存格 public static void mergeCell(XSpreadsheet sheet, string cellRange) { unoidl.com.sun.star.table.XCellRange xCellRange = sheet.getCellRangeByName(cellRange);//"A1:B1"); unoidl.com.sun.star.util.XMergeable xMerge = (unoidl.com.sun.star.util.XMergeable)xCellRange; xMerge.merge(true); } //儲存格位址 public static String getCellAddressString(int nColumn, int nRow) { String aStr = ""; if (nColumn > 25) aStr += (char)('A' + nColumn / 26 - 1); aStr += (char)('A' + nColumn % 26); aStr += (nRow + 1); return aStr; } // //粗體字 public static void cellTextBold(XSpreadsheet xSheet, int column, int row) { XCell xCell = xSheet.getCellByPosition(column, row); //xCell.setFormula(aHeadline); XPropertySet xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell; xPropSet.setPropertyValue("CharWeight", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); xPropSet.setPropertyValue("CharWeightAsian", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); xPropSet.setPropertyValue("CharWeightComplex", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); } //框線 public static void cellBolder(XSpreadsheet sheet, int column, int row, bool top = true, bool bottom = true, bool left = true, bool right = true, int colorValue = 0x000000) { XCell cell = sheet.getCellByPosition(column, row); //A1 XPropertySet xPropSet = (unoidl.com.sun.star.beans.XPropertySet)cell; BorderLine aLine = new unoidl.com.sun.star.table.BorderLine(); aLine.Color = colorValue; aLine.InnerLineWidth = aLine.LineDistance = 0; aLine.OuterLineWidth = 15; unoidl.com.sun.star.table.TableBorder aBorder = new unoidl.com.sun.star.table.TableBorder(); aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; aBorder.IsTopLineValid = top; aBorder.IsBottomLineValid = true; aBorder.IsLeftLineValid = left; aBorder.IsRightLineValid = right; xPropSet.setPropertyValue("TableBorder", new uno.Any(typeof(unoidl.com.sun.star.table.TableBorder), aBorder)); } }
