📊 libxl · Excel 操作库

// 核心类结构 class libxl.book { — filename, activeSheetIndex, errorMessage, colorMode, calcMode — load, save, loadRaw, saveRaw, close, release — addSheet, insertSheet, delSheet, getSheet, sheet — addFormat, getFormat, findFormat, addFont, getFont — addRichString, addPicture, addNumFormat, getNumFormat — properties, addConditionalFormat, getConditionalFormat — removeVBA, removePrinterSettings, removePhonetics } class libxl.sheet { — name, firstRow, lastRow, firstCol, lastCol, defaultFormat — convertDate, convertFormula, convertRichstr — getCellStr, getCellNum, getCellDate, getCellFormula, getCellBool — setCellStr, setCellNum, setCellDate, setCellFormula, setCellBool — setCellRichStr, setCellPicture, setCellHyperlink — getTable, setTable, getValues, setValues — merge, unMerge, insertRow, delRow, insertCol, delCol — setRow, setCol, setRowPx, setColPx — filter, applyFilter, delFilter — addConditionalFormatting, getConditionalFormatting — addDataValidation, delDataValidations — sort, setProtect, freeze, groupRows, groupCols — setHeader, setFooter, setPrintArea, setPrintFit — addSubTable, getSubTable, getSubTableProperties — addr, addrToRowCol, rowColToAddr — getRangeByName, setRangeByName, delRangeByName } class libxl.format { font, numFormat, alignH, alignV, wrap, border, fillPattern, locked ... } class libxl.font { name, size, bold, italic, color, underline, script ... } class libxl.filter { setRange, setSort, addSort, filterColumn, apply, delete } class libxl.richString { addText, getText, count, getFont, getTexts } class libxl.properties { title, subject, creator, tags, categories, comments ... } class libxl.subtable { name, ref, style, showRowStripes, getFilter, getColumnName, setColumnName } class libxl.conditionalFormatting { addRange, addRule, addTopRule, addNumRule, addStrRule, addAverageRule, addTimeRule, add2ColorRule, add3ColorRule }

// 打开工作簿 var book = godking.libxl.open("data.xlsx", "Sheet1"); var sheet = book.sheet(); // 当前激活的表 // 读写单元格 sheet.setCellStr(1, 1, "姓名"); sheet.setCellNum(2, 2, 3.1415); sheet.setCellDate(3, 3, time.now()); sheet.setCellFormula(4, 4, "=SUM(B2:B3)"); // 批量获取表格数据 (自动识别类型) var data = sheet.getTable(1, 1, 0, 0, 1, "", true); // 参数5=1 表示第一行作为字段名，返回字典数组 // 设置格式 var fmt = book.addFormat({ alignH = 2, wrap = true }); sheet.setCellStr(5, 5, "自动换行文本", fmt); // 筛选与排序 var flt = sheet.filter(); flt.setRange(1, 1, 100, 10); flt.filterColumn(1).addFilter({"张三","李四"}); flt.setSort(2, false); flt.apply(); // 条件格式 (2色标尺) var cf = book.addConditionalFormat(); cf.fillPattern = 1; cf.patternForegroundColor = 0xFF0000; var cfm = sheet.addConditionalFormatting(); cfm.addRange(1, 100, 1, 10); cfm.add2ColorRule(0x00FF00, 0xFF0000, 0, 0, 1, 0, false); // 保存并释放 book.save("output.xlsx"); book.close();