libxls 简单使用

发表于 更新于 分类于
libxls与xlslib c库的简单使用...

libxls 读取表格

步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
struct xlsWorkBook pwb;
struct xlsWorkShee pws;

xlsWorkBook* xls_open_file(const char *file, const char* charset, xls_error_t *outError);

pwb = xls_open_file(path, "UTF-8", &xls_error_t);
pwd = xls_open(path, "UTF-8");

xls_SummaryInfo(pwb);	// 获取表格基本信息
pwb->sheets.count;		// 工作表数量
pwb->sheets.sheet[i];	// 工作表

//获取工作表
pws = xls_getWorkSheet(pwb, i); //i表示第几个，从0开始

//工作表的行列数
pws->rows.lastrow; //行数
pws->rows.lastcol; //列数

//获取第i行数据
data = pws->rows.row[i];  //struct st_row_data

一些结构体

xlsWorkBook

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
typedef struct xlsWorkBook
{
    //FILE*		file;
    OLE2Stream*	olestr;
    int32_t		filepos;		// position in file

    //From Header (BIFF)
    BYTE		is5ver;
    BYTE		is1904;
    WORD		type;
    WORD		activeSheetIdx;	// index of the active sheet

    //Other data
    WORD		codepage;		// Charset codepage
    char*		charset;
    st_sheet	sheets;
    st_sst		sst;			// SST table
    st_xf		xfs;			// XF table
    st_font		fonts;
    st_format	formats;		// FORMAT table

	char		*summary;		// ole file
	char		*docSummary;	// ole file

    void        *converter;
    void        *utf16_converter;
    void        *utf8_locale;
}xlsWorkBook;

xlsWorkSheet

1
2
3
4
5
6
7
8
9
typedef struct xlsWorkSheet
{
    DWORD		filepos;
    WORD		defcolwidth;
    st_row		rows;
    xlsWorkBook *workbook;
    st_colinfo	colinfo;
}
xlsWorkSheet;

st_row

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
typedef	struct st_row
{
    //	DWORD count;
    WORD lastcol;	// numCols - 1
    WORD lastrow;	// numRows - 1
    struct st_row_data
    {
        WORD index;
        WORD fcell;
        WORD lcell;
        WORD height;
        WORD flags;
        WORD xf;
        BYTE xfflags;
        st_cell cells;
    }
    * row;
}st_row;

st_row_data

1
2
3
4
5
6
7
8
9
10
struct st_row_data {
	WORD index;		//索引，表示第几行，从0开始计数
	WORD fcell; 	//应该是表示最左边的单元格在哪
	WORD lcell; 	//应该表示最右边的单元格在哪
	WORD height; 	//行高
	WORD flags; 	//
	WORD xf; 		//
	BYTE xfflags; 	//
	st_cell cells; 	//这一行的所有单元格
};

st_cell

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
typedef	struct st_cell
{
    DWORD count;
    struct st_cell_data
    {
        WORD	id;
        WORD	row;		// 行的索引
        WORD	col;		// 列的索引
        WORD	xf;			// xformat(不知道干嘛)
        char *	str;		// 单元格的值
        double	d;			// 单元格的值（double类型）
        int32_t	l;			// 单元格的值（int32类型）
        WORD	width;		// Width of col
        WORD	colspan;	// 这个单元格占用的列数
        WORD	rowspan;	// 这个单元格占用的行数
        BYTE	isHidden;	// 是否被隐藏 1为是
    }
    * cell;
};

xlslib 写入表格

步骤

1
2
3
4
5
6
class workbook wb;
class worksheet* sh;

sh = wb.sheet(shname);	//创建一个工作表

//调用worksheet与cell_t中相关接口填充表格

一些接口

class workbook

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
const char*	version() const { return XLSLIB_VERSION; }

worksheet*	sheet(const std::string& sheetname);
worksheet*	sheet(const xlslib_strings::ustring& sheetname);
worksheet*	GetSheet(unsigned16_t sheetnum);

expression_node_factory_t& GetFormulaFactory(void);

font_t*		font(unsigned8_t fontnum); // use as a way to get a font to modify
font_t*		font(const std::string& name);
format_t*	format(const std::string& formatstr);
format_t*	format(const xlslib_strings::ustring& formatstr);

bool		setColor(unsigned8_t r, unsigned8_t g, unsigned8_t b, unsigned8_t idx);

xf_t*		xformat(void);
xf_t*		xformat(font_t* font);
xf_t*		xformat(format_t *format);
xf_t*		xformat(font_t* font, format_t *format);

#ifdef HAVE_WORKING_ICONV
	int			iconvInType(const char *inType);
#endif

	bool		property(property_t prop, const std::string& content);

	void		windPosition(unsigned16_t horz, unsigned16_t vert);
	void		windSize(unsigned16_t width, unsigned16_t height);
	void		firstTab(unsigned16_t firstTab);
	void		tabBarWidth(unsigned16_t width);

	int			Dump(const std::string& filename);

class worksheet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
void MakeActive();   // makes this sheet come up first
size_t NumCells() const { return m_Cells.size(); }
unsigned16_t GetIndex() const { return sheetIndex; }

cell_t* FindCell(unsigned32_t row, unsigned32_t col) const;
cell_t* FindCellOrMakeBlank(unsigned32_t row, unsigned32_t col);

void GetFirstLastRowsAndColumns(unsigned32_t* first_row, unsigned32_t* last_row, unsigned32_t* first_col, unsigned32_t* last_col); /* [i_a] */

// Cell operations
void merge(unsigned32_t first_row, unsigned32_t first_col, unsigned32_t last_row, unsigned32_t last_col);
void colwidth(unsigned32_t col, unsigned16_t width, xf_t* pxformat = NULL); // sets column widths to 1/256 x width of "0"
void rowheight(unsigned32_t row, unsigned16_t heightInTwips, xf_t* pxformat = NULL); // twips

void defaultRowHeight(unsigned16_t width, bool hidden = false) { defRowHeight = width; defRowsHidden = hidden; } // sets column widths to 1/256 x width of "0"
void defaultColwidth(unsigned16_t width) { defColWidth = width; } // in points (Excel uses twips, 1/20th of a point, but xlslib didn't)

formula_t* formula_data();

// Ranges
range* rangegroup(unsigned32_t row1, unsigned32_t col1, unsigned32_t row2, unsigned32_t col2);
// Cells
cell_t* blank(unsigned32_t row, unsigned32_t col, xf_t* pxformat = NULL);

cell_t* label(unsigned32_t row, unsigned32_t col, const std::string& strlabel, xf_t* pxformat = NULL);
cell_t* label(unsigned32_t row, unsigned32_t col, const xlslib_strings::ustring& strlabel, xf_t* pxformat = NULL);

cell_t* number(unsigned32_t row, unsigned32_t col, double numval, format_number_t fmtval, xf_t* pxformat);  // Deprecated
cell_t* number(unsigned32_t row, unsigned32_t col, double numval, xf_t* pxformat = NULL);
// 536870911 >= numval >= -536870912
cell_t* number(unsigned32_t row, unsigned32_t col, signed32_t numval, xf_t* pxformat = NULL);
cell_t* number(unsigned32_t row, unsigned32_t col, unsigned32_t numval, xf_t* pxformat = NULL);

cell_t* boolean(unsigned32_t row, unsigned32_t col,bool boolval, xf_t* pxformat = NULL);

cell_t* error(unsigned32_t row, unsigned32_t col, errcode_t errorcode, xf_t* pxformat = NULL);

cell_t* note(unsigned32_t row, unsigned32_t col, const std::string& remark, const std::string& author, xf_t* pxformat = NULL);
cell_t* note(unsigned32_t row, unsigned32_t col, const xlslib_strings::ustring& remark, const xlslib_strings::ustring& author, xf_t* pxformat = NULL);

cell_t* formula(unsigned32_t row, unsigned32_t col, expression_node_t* expression_root, bool auto_destruct_expression_tree = false, xf_t* pxformat = NULL);
cell_t* formula(unsigned32_t row, unsigned32_t col, formula_t *formula, xf_t* pxformat = NULL);

void validate(const range_t *crange, unsigned32_t options, const formula_t *cond1 = NULL, const formula_t *cond2 = NULL,const std::string& promptTitle = std::string(), const std::string& promptText = std::string(), const std::string& errorTitle = std::string(), const std::string& errorText = std::string());
void validate(const range_t *crange, unsigned32_t options,const formula_t *cond1 = NULL, const formula_t *cond2 = NULL,const xlslib_strings::ustring& promptTitle = xlslib_strings::ustring(), const xlslib_strings::ustring& promptText = xlslib_strings::ustring(), const xlslib_strings::ustring& errorTitle = xlslib_strings::ustring(), const xlslib_strings::ustring& errorText = xlslib_strings::ustring());

// define a cell (label, number, etc) - apply proper url (http://blah.blah), possible text mark too (minus the '#')
void hyperLink(const cell_t *cell, const std::string& url, const std::string& mark = std::string());
void hyperLink(const cell_t *cell, const xlslib_strings::ustring& url, const xlslib_strings::ustring& mark = xlslib_strings::ustring());

class cell_t

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public: // xf_i interface
	void font(font_t* font);
	void format(format_number_t formatidx);
	void format(format_t* format);
	void halign(halign_option_t ha_option);
	void valign(valign_option_t va_option);
	void indent(indent_option_t indent_option);
	void orientation(txtori_option_t ori_option);
	void fillfgcolor(color_name_t color);
	void fillfgcolor(unsigned8_t color);
	void fillbgcolor(color_name_t color);
	void fillbgcolor(unsigned8_t color);
	void fillstyle(fill_option_t fill);
	void locked(bool locked_opt);
	void hidden(bool hidden_opt);
	void wrap(bool wrap_opt);
	void borderstyle(border_side_t side, border_style_t style);
	void bordercolor(border_side_t side, color_name_t color);
	void bordercolor(border_side_t side, unsigned8_t color);
public: //font_i interface
	void fontname(const std::string& fntname);
	void fontheight(unsigned16_t fntheight);
	void fontbold(boldness_option_t fntboldness);
	void fontunderline(underline_option_t fntunderline);
	void fontscript(script_option_t fntscript);
	void fontcolor(color_name_t fntcolor);
	void fontcolor(unsigned8_t fntcolor);
	void fontitalic(bool italic);
	void fontstrikeout(bool so);
	void fontoutline(bool ol);
	void fontshadow(bool sh);