LIBXL C++库的使用
2019/01/21 21:47
注:以下绝大部分图片,代码来自LIBXL官网,http://www.libxl.com libxl版本 : version 3.8.4
目录:
-1.使用中发现的问题
0.使用前的准备
1.错误处理
2.创建Book,保存,释放
3.创建Sheet,装载Sheet
4.写入与读取
5.插入与删除
6.放置一张图片
7.公式
8.时间与日期
9.合并单元格
10.分组
11.排列,颜色与填充
一. Book相关<Book 的成员函数>
二. Sheet 相关
三.字体相关<Font 的成员函数>
附录:
--#1 COLOR
--#2 FORMAT
未完待续,有空再写。。。// 有道云笔记的格式 在CSDN 上水土不服。。。不改了
---------------------------------------------------------正文---------------------------------------------------
-1.使用中发现的问题
1. 有一些公式不支持 如:EOMONTH
2. xlCreateBook() 只能打开 97-2003,xlCreateXMLBook() 只能开 2007+版本
3.对于 AutoFiler 功能 只有 xlCreateXMLBook()创建的 Book 才可以使用
4.无法 实现”粘贴为数值“ ,有公式的单元格 使用 cellType() 读出来 是 empty,只能用 ` ReadFormula()
5...
0.使用前的准备
#include "libxl.h"
#pragma comment(lib,"libxl.lib")
using namespace libxl;
1.错误处理
std::cout<< book->errorMessage();//返回错误信息
原型:const char* errorMessage() const
2.创建Book,保存,释放
*创建:
Book *book = xlCreateBook(); //excel-2003版本
Book *book1 = xlCreateXMLBook(); //excel 2007+版本
*LOAD一个已存在的Excel:
bool load(const wchar_t* filename, const wchar_t* tempFile = 0)
*序列号的使用:
book->setKey(USER_NAME, USER_PASSWD);
原型:setKey(const TCHAR* name, const TCHAR* key) = 0;
*保存:
book->save(L"out.xls");
*释放BOOK
book->release();//使用完Book,必须释放!!!
3.创建Sheet,装载Sheet
创建: book->addSheet("Sheet Name");
原型:Sheet* addSheet(const wchar_t* name, Sheet* initSheet = 0)
装载: book->getSheet(0);
原型: Sheet* getSheet(int index) const
装载(使用sheet表名):
//通过遍历所有sheet表,寻找与所给名字匹配的Sheet* ,作为指针返回。
Sheet* getSheetByName(Book* book, const wchar_t* name)
{
for(unsigned short i = 0; i < book->sheetCount(); ++i)
{
if(wcscmp(book->getSheet(i)->name(), name) == 0)
return book->getSheet(i);
}
return 0;
}
4.写入与读取
读取:double d = sheet->readNum(row, col);//读数值
const wchar_t* s = sheet->readStr(row, col);//读字符串
bool b = sheet->readBool(row, col);//读布尔值
写入:sheet->setRow(2, 47.25);//将第3行(2+1),的行高设为47.25
sheet->writeStr(2, 1, L"Sales Receipt", titleFormat);
//在第3行,第2列 写文本“Sales Receipt”,并使用titleFormat格式
5.插入与删除
插入:
sheet->insertRow(5, 10);//插入6行,6---11
sheet->insertCol(4, 5);//插入2列 ,5---6
sheet->insertCol(8, 8);
删除:
sheet->removeRow(2, 2);//删除第3行 sheet->removeCol(2, 3);//删除第3列 第4列
6.放置一张图片
int id = book->addPicture(L"picture.jpg");//加载一张图片,并给一个图片ID if(id == -1) { std::cout << "picture not found" << std::endl; return -1; } Sheet* sheet = book->addSheet(L"Sheet1"); if(sheet) sheet->setPicture(10, 1, id);//在11行,2列 放置 图片ID为id的图片
7.公式
sheet->writeFormula(6, 1, L"SUM(B3:B5)", alFormat);
sheet->writeFormula(7, 1, L"AVERAGE(B3:B5)", alFormat);
sheet->writeFormula(8, 1, L"MAX(B3:B5)", alFormat);
8.时间与日期
示例代码:
Format* format2 = book->addFormat();
format2->setNumFormat(NUMFORMAT_CUSTOM_MDYYYY_HMM);
//使用“NUMFORMAT_CUSTOM_MDYYYY_HMM”格式
Format* format3 = book->addFormat();
format3->setNumFormat(book->addCustomNumFormat(L"d mmmm yyyy"));
//addCustomNumFormat()自创格式
sheet->writeNum(3, 1, book->datePack(2010, 3, 11, 10, 25, 55), format2);
sheet->writeNum(4, 1, book->datePack(2010, 3, 11), format3);
int hour, min, sec;
book->dateUnpack(sheet->readNum(3, 1), &year, &month, &day, &hour, &min, &sec);
std::cout << year << "-" << month << "-" << day << " "
<< hour << ":" << min << ":" << sec << std::endl;
9.合并单元格
bool setMerge(int rowFirst, int rowLast, int colFirst, int colLast);//原型
e.g. http://www.libxl.com/examples/merge.xls
Format* format = book->addFormat();
format->setAlignH(ALIGNH_CENTER);
format->setAlignV(ALIGNV_CENTER);
// 设定格式
sheet->setMerge(3,5,1,5);
sheet->setMerge(7,20,1,2);
sheet->setMerge(7, 20, 4, 5);
sheet->writeNum(7, 1, 1, format);
sheet->writeNum(7, 4, 2, format);
10.分组
bool groupRows(int rowFirst, int rowLast, bool collapsed = true)//行分组
bool groupCols(int colFirst, int colLast, bool collapsed = true);//列分组
e.g. http://www.libxl.com/examples/group.xls
sheet->groupCols(0, 1);
sheet->groupCols(4, 7, false);
sheet->groupRows(3, 6);
sheet->groupRows(10, 25, false);
sheet->groupRows(14, 16, false);
sheet->groupRows(19, 21, false);
10.排列,颜色与填充
详见 FORMAT部分,给出官方代码:
http://www.libxl.com/aligning-colors-borders.html
一.Book相关<Book 的成员函数>
*创建
Book* xlCreateBook() //excel97-2003版本
Book* xlCreateXMLBook() //excel-2007版本
*注:以上两个函数并非是Book的成员函数。
*装载load
bool load(const wchar_t* filename);//装载文件
bool loadSheet(const wchar_t* filename, int sheetIndex)
//装载文件的某个sheet
bool loadPartially(constwchar_t*filename, int sheetIndex,
int firstRow, int lastRow)
//装载文件某sheet的某几行
bool loadWithoutEmptyCells(const wchar_t* filename);//只加载非空值
bool loadInfo(const wchar_t* filename);//只装载文件信息,调用后可调用
Book::sheetCount() and Book::getSheetName() 函数
bool loadRaw(const char* data,
unsigned size,
int sheetIndex = -1,
int firstRow = -1,
int lastRow = -1)
// 从内存缓冲区中加载文件,data 为缓冲区指针
*保存(save)&释放
bool save(const wchar_t* filename);//保存文件到磁盘
bool saveRaw(const char** data, unsigned* size);//将文件保存到内存中
void release();//释放Book对象
*Sheet相关函数(book->xxxSheet() )
Sheet* addSheet(const wchar_t* name, Sheet* initSheet = 0)//创建Sheet
Sheet* insertSheet(int index, const wchar_t* name, Sheet* initSheet = 0)
//在所给的index处插入一个Sheet,并返回Sheet的指针,如果你想拷贝
已有Sheet ,你需要使用 参数:initSheet
Sheet* getSheet(int index);//返回index序号的Sheet指针
const wchar_t* getSheetName(int index);//返回index序号的Sheet名
SheetType sheetType(int index);//返回 Sheet类型
SheetType value | Description |
SHEETTYPE_SHEET | standard sheet |
SHEETTYPE_CHART | chart sheet |
SHEETTYPE_UNKNOWN | unknown sheet |
bool moveSheet(int srcIndex, int dstIndex);//移动Sheet,从srcIndex
// 到dstIndex
bool delSheet(int index);//删除index序号的sheet
int sheetCount() const; //返回Sheet表数目
int activeSheet() const;//返回活动Sheet的序号
void setActiveSheet(int index);//将index序号对应的表设为活动
*add系列函数
Sheet* addSheet(const wchar_t* name, Sheet* initSheet = 0)//创建Sheet
Format* addFormat(Format* initFormat = 0);//创建一个格式,
//详见“格式相关”
Font* addFont(Font* initFont = 0);//创建一个字体,详见“字体相关”
int addCustomNumFormat(const wchar_t* customNumFormat);
//创建一个自定义数字格式
*pack系列函数
double datePack(int year, int month, int day,
int hour = 0, int min = 0, int sec = 0, int msec = 0) ;
//将所给参数 封装成 double格式
bool dateUnpack(double value, int* year, int* month, int* day,
int* hour = 0, int* min = 0, int* sec = 0, int* msec = 0);
//将double格式 解装成 时间
Color colorPack(int red, int green, int blue)
void colorUnpack(Color color, int* red, int* green, int* blue)
*图片相关函数
int pictureSize() ;//返回图片数量
PictureType getPicture(int index, const char** data, unsigned* size) const
int addPicture(const wchar_t* filename);//加载一张图片,返回图片ID
......
二. Sheet 相关
*创建&删除
详见 “Book相关--Sheet相关”部分
*单元格类型
CellType cellType(int row, int col);//返回单元格类型
CellType value | Description |
CELLTYPE_EMPTY | empty |
CELLTYPE_NUMBER | number value |
CELLTYPE_STRING | string value |
CELLTYPE_BOOLEAN | boolean value |
CELLTYPE_BLANK | blank |
CELLTYPE_ERROR | error |
*读写相关
const wchar_t* readStr(int row, int col, Format** format = 0);//读字符串
bool writeStr(int row, int col, const wchar_t* value,
Format* format = 0, CellType type = CELLTYPE_STRING)
//向单元格写字符串
double readNum(int row, int col, Format** format = 0) ;//读数值
bool writeNum(int row, int col, double value, Format* format = 0)//写数值
bool readBool(int row, int col, Format** format = 0);//读bool值
bool writeBool(int row, int col, bool value, Format* format = 0) //写bool值
bool readBlank(int row, int col, Format** format = 0);//读空白格 格式
bool writeBlank(int row, int col, Format* format);//向空白格写格式
const wchar_t* readFormula(int row, int col, Format** format = 0)
//读公式
bool writeFormula(int row, int col, const wchar_t* value,
Format* format = 0) //写公式
bool writeFormulaNum(int row, int col, const wchar_t* expr,
double value, Format* format = 0)
//写带有预先计算出的数值的公式
bool writeFormulaStr(int row, int col, const wchar_t* expr,
const wchar_t* value, Format* format = 0)
//写带有预先计算出的字符值的公式
bool writeFormulaBool(int row, int col, const wchar_t* expr,
bool value, Format* format = 0)
//写带有预先计算出的bool值的公式
const wchar_t* readComment(int row, int col) ;//读一个comment
void writeComment(int row, int col, const wchar_t* value,
const wchar_t* author = 0,
int width = 129, int height = 75)
void removeComment(int row, int col);//移除一个comment
*数值&错误类型
ErrorType readError(int row, int col);//读错误类型
void writeError(int row, int col, ErrorType error, Format* format = 0)//写
ErrorType value | Description |
ERRORTYPE_NULL | #NULL! |
ERRORTYPE_DIV_0 | #DIV/0! |
ERRORTYPE_VALUE | #VALUE! |
ERRORTYPE_REF | #REF! |
ERRORTYPE_NAME | #NAME? |
ERRORTYPE_NUM | #NUM! |
ERRORTYPE_NA | #N/A |
ERRORTYPE_NOERROR | no error |
*行列相关
double colWidth(int col) ;//返回列宽
double rowHeight(int row) ;//返回行高
bool setCol(int colFirst, int colLast, double width,
Format* format = 0, bool hidden = false)//设定列
bool setRow(int row, double height, Format* format = 0,
bool hidden = false);//设定行
int firstRow() ;
int lastRow() ;
int firstCol() ;
int lastCol() ;
bool insertRow(int rowFirst, int rowLast, bool updateNamedRanges = true)
bool insertCol(int colFirst, int colLast, bool updateNamedRanges = true)
bool removeRow(int rowFirst, int rowLast, bool updateNamedRanges = true)
bool removeCol(int colFirst, int colLast, bool updateNamedRanges = true)
*AutoFilter相关
AutoFilter autoFilter();//返回一个AutoFilter 变量
void applyFilter();//申请一个 filter
void removeFilter();//移除 filter
*Merge相关
三.字体相关<Font 的成员函数>
*e.g.:
Font* textFont = book->addFont(); //创建字体
textFont->setSize(8); // 设定字体大小
textFont->setName(L"Century Gothic");//设定字体名,e.g.("宋体")
*在原有字体上创建&修改:
Font* font12 = book->addFont(textFont);
font12->setSize(12);
*斜体:
bool italic() const; // 返回该字体是否为斜体
void setItalic(bool italic = true) ;//设定该字体为斜体
*删除体:
bool strikeOut() const;//返回字体是否为删除体
void setStrikeOut(bool strikeOut = true) //设定字体为删除体
*颜色:<COLOR 详见 附录#1>
Color color() const ; //返回当前颜色
void setColor(Color color);//设定颜色
*加粗:
bool bold() const; //返回字体是否加粗
void setBold(bool bold = true);//将字体加粗
*字体名:
const wchar_t* name() const;//返回当前使用的字体名
bool setName(const wchar_t* name);//设定字体名
*脚本(上标,下标,正常)
Script script() const;//返回字体是否有脚本
void setScript(Script script);//设定字体脚本
Script 取值如下:
*下划线:
Underline underline() const;//返回字体下划线类型
void setUnderline(Underline underline)//设定字体下划线类型
Underline 取值如下:
Underline value | View |
UNDERLINE_NONE | |
UNDERLINE_SINGLE | |
UNDERLINE_DOUBLE | |
UNDERLINE_SINGLEACC | |
UNDERLINE_DOUBLEACC |
Appendix (附录)
#1 COLOR
Index | Color value | View | Hex |
8 | COLOR_BLACK | #000000 | |
9 | COLOR_WHITE | #FFFFFF | |
10 | COLOR_RED | #FF0000 | |
11 | COLOR_BRIGHTGREEN | #00FF00 | |
12 | COLOR_BLUE | #0000FF | |
13 | COLOR_YELLOW | #FFFF00 | |
14 | COLOR_PINK | #FF00FF | |
15 | COLOR_TURQUOISE | #00FFFF | |
16 | COLOR_DARKRED | #800000 | |
17 | COLOR_GREEN | #008000 | |
18 | COLOR_DARKBLUE | #000080 | |
19 | COLOR_DARKYELLOW | #808000 | |
20 | COLOR_VIOLET | #800080 | |
21 | COLOR_TEAL | #008080 | |
22 | COLOR_GRAY25 | #C0C0C0 | |
23 | COLOR_GRAY50 | #808080 | |
24 | COLOR_PERIWINKLE_CF | #9999FF | |
25 | COLOR_PLUM_CF | #993366 | |
26 | COLOR_IVORY_CF | #FFFFCC | |
27 | COLOR_LIGHTTURQUOISE_CF | #CCFFFF | |
28 | COLOR_DARKPURPLE_CF | #660066 | |
29 | COLOR_CORAL_CF | #FF8080 | |
30 | COLOR_OCEANBLUE_CF | #0066CC | |
31 | COLOR_ICEBLUE_CF | #CCCCFF | |
32 | COLOR_DARKBLUE_CL | #000080 | |
33 | COLOR_PINK_CL | #FF00FF | |
34 | COLOR_YELLOW_CL | #FFFF00 | |
35 | COLOR_TURQUOISE_CL | #00FFFF | |
36 | COLOR_VIOLET_CL | #800080 | |
37 | COLOR_DARKRED_CL | #800000 | |
38 | COLOR_TEAL_CL | #008080 | |
39 | COLOR_BLUE_CL | #0000FF | |
40 | COLOR_SKYBLUE | #00CCFF | |
41 | COLOR_LIGHTTURQUOISE | #CCFFFF | |
42 | COLOR_LIGHTGREEN | #CCFFCC | |
43 | COLOR_LIGHTYELLOW | #FFFF99 | |
44 | COLOR_PALEBLUE | #99CCFF | |
45 | COLOR_ROSE | #FF99CC | |
46 | COLOR_LAVENDER | #CC99FF | |
47 | COLOR_TAN | #FFCC99 | |
48 | COLOR_LIGHTBLUE | #3366FF | |
49 | COLOR_AQUA | #33CCCC | |
50 | COLOR_LIME | #99CC00 | |
51 | COLOR_GOLD | #FFCC00 | |
52 | COLOR_LIGHTORANGE | #FF9900 | |
53 | COLOR_ORANGE | #FF6600 | |
54 | COLOR_BLUEGRAY | #666699 | |
55 | COLOR_GRAY40 | #969696 | |
56 | COLOR_DARKTEAL | #003366 | |
57 | COLOR_SEAGREEN | #339966 | |
58 | COLOR_DARKGREEN | #003300 | |
59 | COLOR_OLIVEGREEN | #333300 | |
60 | COLOR_BROWN | #993300 | |
61 | COLOR_PLUM | #993366 | |
62 | COLOR_INDIGO | #333399 | |
63 | COLOR_GRAY80 | #333333 | |
64 | COLOR_DEFAULT_FOREGROUND | Auto | |
65 | COLOR_DEFAULT_BACKGROUND | Auto |
#2 FORMAT
Constant | Description | Example | |
NUMFORMAT_GENERAL | general format | ||
NUMFORMAT_NUMBER | general number | 1000 | |
NUMFORMAT_NUMBER_D2 | number with decimal point | 1000 | |
NUMFORMAT_NUMBER_SEP | number with thousands separator | 100,000 | |
NUMFORMAT_NUMBER_SEP_D2 | number with decimal point and thousands separator | 100,000.00 | |
NUMFORMAT_CURRENCY_NEGBRA | monetary value, negative in brackets | (1000$) | |
NUMFORMAT_CURRENCY_NEGBRARED | monetary value, negative is red in brackets | (1000$) | |
NUMFORMAT_CURRENCY_D2_NEGBRA | monetary value with decimal point, negative in brackets | ($1,000.00) | |
NUMFORMAT_CURRENCY_D2_NEGBRARED | monetary value with decimal point, negative is red in brackets | ($1,000.00) | |
NUMFORMAT_PERCENT | percent value, multiply the cell value by 100 | 75% | |
NUMFORMAT_PERCENT_D2 | percent value with decimal point, multiply the cell value by 100 | 75.00% | |
NUMFORMAT_SCIENTIFIC_D2 | scientific value with E character and decimal point | 1.00E+02 | |
NUMFORMAT_FRACTION_ONEDIG | fraction value, one digit | 10 1/2 | |
NUMFORMAT_FRACTION_TWODIG | fraction value, two digits | 10 23/95 | |
NUMFORMAT_DATE | date value, depends on OS settings | 3/11/2009 | |
NUMFORMAT_CUSTOM_D_MON_YY | custom date value | 11-Mar-09 | |
NUMFORMAT_CUSTOM_D_MON | custom date value | 11-Mar | |
NUMFORMAT_CUSTOM_MON_YY | custom date value | 9-Mar | |
NUMFORMAT_CUSTOM_HMM_AM | custom date value | 8:30 AM | |
NUMFORMAT_CUSTOM_HMMSS_AM | custom date value | 8:30:00 AM | |
NUMFORMAT_CUSTOM_HMM | custom date value | 8:30 | |
NUMFORMAT_CUSTOM_HMMSS | custom date value | 8:30:00 | |
NUMFORMAT_CUSTOM_MDYYYY_HMM | custom datetime value | 3/11/2009 8:30 | |
NUMFORMAT_NUMBER_SEP_NEGBRA | number with thousands separator, negative in brackets | -4,000 | |
NUMFORMAT_NUMBER_SEP_NEGBRARED | number with thousands separator, negative is red in brackets | -4,000 | |
NUMFORMAT_NUMBER_D2_SEP_NEGBRA | number with thousands separator and decimal point, negative in brackets | -4,000.00 | |
NUMFORMAT_NUMBER_D2_SEP_NEGBRARED | number with thousands separator and decimal point, negative is red in brackets | -4,000.00 | |
NUMFORMAT_ACCOUNT | account value | 5,000 | |
NUMFORMAT_ACCOUNTCUR | account value with currency symbol | $ | 5,000 |
NUMFORMAT_ACCOUNT_D2 | account value with decimal point | 5,000.00 | |
NUMFORMAT_ACCOUNT_D2_CUR | account value with currency symbol and decimal point | $ | 5,000.00 |
NUMFORMAT_CUSTOM_MMSS | custom time value | 30:55:00 | |
NUMFORMAT_CUSTOM_H0MMSS | custom time value | 20:30:55 | |
NUMFORMAT_CUSTOM_MMSS0 | custom time value | 30:55.0 | |
NUMFORMAT_CUSTOM_000P0E_PLUS0 | custom value | 1.52E+04 | |
NUMFORMAT_TEXT | text value | any text |