使用BasicExcel操作Excel
来源:互联网 发布:ios 淘宝购物车demo 编辑:程序博客网 时间:2024/05/16 09:56
摘要:这个类是国外人写的一个操作Excel类,应该说比较好用,虽然对中文的支持不够好,但经过转码后使用起来还是非常不错的。下给大家介绍一下:
此类总共包含4个类文件分别是:BasicExcel.hpp,BasicExcel.cpp,ExcelFormat.h,ExcelFormat.cpp前两个是操作Excel文件的,后两个是设置Excel单元格相关格式的等等。类文件下载路径及使用Demo链接如下:(http://shell.franken.de/svn/sky/excel/trunk/ExcelFormat/)
下面部分是引用CodeProject网站的使用说明和例子程序:
class BasicExcel
void New(int sheets=3)
Create a new Excel workbook with a given number of spreadsheets (Minimum 1).bool Load(const char* filename)
Load an Excel workbook from a file.bool Save()
Save current Excel workbook to opened file.bool SaveAs(const char* filename)
Save current Excel workbook to a file.size_t GetTotalWorkSheets()
Total number of Excel worksheets in current Excel workbook.BasicExcelWorksheet* GetWorksheet(size_t sheetIndex)
Get a pointer to an Excel worksheet at the given index. Index starts from 0
. Returns 0
if index is invalid.BasicExcelWorksheet* GetWorksheet(const char* name)
Get a pointer to an Excel worksheet that has given ANSI name. Returns 0
if there is no Excel worksheet with the given name.BasicExcelWorksheet* GetWorksheet(const wchar_t* name)
Get a pointer to an Excel worksheet that has given Unicode name. Returns0
if there is no Excel worksheet with the given name.BasicExcelWorksheet* AddWorksheet(int sheetIndex=-1)
Add a new Excel worksheet to the given index. Name given to worksheet is SheetX, where X is a number which starts from 1. Index starts from 0
. Worksheet is added to the last position if sheetIndex == -1
. Returns a pointer to the worksheet if successful, 0
if otherwise.BasicExcelWorksheet* AddWorksheet(const char* name, int sheetIndex=-1)
Add a new Excel worksheet with given ANSI name to the given index. Index starts from 0
. Worksheet is added to the last position if sheetIndex == -1
. Returns a pointer to the worksheet if successful, 0 if otherwise.BasicExcelWorksheet* AddWorksheet(const wchar_t* name, int sheetIndex=-1)
Add a new Excel worksheet with given Unicode name to the given index. Index starts from 0
. Worksheet is added to the last position if sheetIndex == -1
. Returns a pointer to the worksheet if successful, 0 if otherwise.bool DeleteWorksheet(size_t sheetIndex)
Delete an Excel worksheet at the given index. Index starts from 0
. Returnstrue
if successful, false
if otherwise.bool DeleteWorksheet(const char* name)
Delete an Excel worksheet that has given ANSI name. Returns true
if successful, false
if otherwise.bool DeleteWorksheet(const wchar_t* name)
Delete an Excel worksheet that has given Unicode name. Returns true
if successful, false
if otherwise.char* GetAnsiSheetName(size_t sheetIndex)
Get the worksheet name at the given index. Index starts from 0
. Returns 0
if name is in Unicode format.wchar_t* GetUnicodeSheetName(size_t sheetIndex)
Get the worksheet name at the given index. Index starts from 0
. Returns 0
if name is in ANSI format.bool GetSheetName(size_t sheetIndex, char* name)
Get the worksheet name at the given index. Index starts from 0
. Returnsfalse
if name is in Unicode format.bool GetSheetName(size_t sheetIndex, wchar_t* name)
Get the worksheet name at the given index. Index starts from 0. Returnsfalse
if name is in ANSI format.bool RenameWorksheet(size_t sheetIndex, const char* to)
Rename an Excel worksheet at the given index to the given ANSI name. Index starts from 0
. Returns true
if successful, false
if otherwise.bool RenameWorksheet(size_t sheetIndex, const wchar_t* to)
Rename an Excel worksheet at the given index to the given Unicode name. Index starts from 0
. Returns true
if successful, false
if otherwise.bool RenameWorksheet(const char* from, const char* to)
Rename an Excel worksheet that has given ANSI name to another ANSI name. Returns true
if successful, false
if otherwise.bool RenameWorksheet(const wchar_t* from, const wchar_t* to)
Rename an Excel worksheet that has given Unicode name to another Unicode name. Returns true
if successful, false
if otherwise.class BasicExcelWorksheet
char* GetAnsiSheetName()
Get the current worksheet name. Returns 0
if name is in Unicode format.wchar_t* GetUnicodeSheetName()
Get the current worksheet name. Returns 0
if name is in ANSI format.bool GetSheetName(char* name)
Get the current worksheet name. Returns false
if name is in Unicode format.bool GetSheetName(wchar_t* name)
Get the current worksheet name. Returns false
if name is in ANSI format.bool Rename(const char* to)
Rename current Excel worksheet to another ANSI name. Returns true
if successful, false
if otherwise.bool Rename(const wchar_t* to)
Rename current Excel worksheet to another Unicode name. Returns true
if successful, false
if otherwise.void Print(ostream& os, char delimiter=',', char textQualifier='\0')
Print entire worksheet to an output stream, separating each column with the defined delimiter and enclosing text using the defined textQualifier
. Leave out the textQualifier
argument if do not wish to have any text qualifiers.size_t GetTotalRows()
Total number of rows in current Excel worksheet.size_t GetTotalCols()
Total number of columns in current Excel worksheet.BasicExcelCell* Cell(size_t row, size_t col)
Return a pointer to an Excel cell. row and col starts from 0
. Returns 0
if row exceeds 65535 or col exceeds 255.bool EraseCell(size_t row, size_t col)
Erase content of a cell. row and col starts from 0
. Returns true
if successful,false
if row or col exceeds range.class BasicExcelCell
int Type() const
Get type of value stored in current Excel cell. Returns one of the following enums: UNDEFINED
, INT
, DOUBLE
, STRING
, WSTRING
.bool Get(int& val) const
Get an integer
value. Returns false
if cell does not contain aninteger
.bool Get(double& val) const
Get a double
value. Returns false
if cell does not contain adouble
.bool Get(char* str) const
Get an ANSI string. Returns false
if cell does not contain an ANSIstring
.bool Get(wchar_t* str) const
Get an Unicode string. Returns false
if cell does not contain an Unicode string
.size_t GetStringLength()
Return length of ANSI or Unicode string (excluding null
character).int GetInteger() const
Get an integer
value. Returns 0
if cell does not contain aninteger
.double GetDouble() const
Get a double
value. Returns 0
.<code>0
if cell does not contain adouble
.const char* GetString() const
Get an ANSI string
. Returns 0
if cell does not contain an ANSIstring
.const wchar_t* GetWString() const
Get an Unicode string
. Returns 0
if cell does not contain an Unicode string
.ostream& operator<<(ostream& os, const BasicExcelCell& cell)
Print cell to output stream
. Print a null character if cell is undefined.void Set(int val)
Set content of current Excel cell to an integer
.void Set(double val)
Set content of current Excel cell to a double
.void Set(const char* str)
Set content of current Excel cell to an ANSI string
.void Set(const wchar_t* str)
Set content of current Excel cell to an Unicode strin
g.void SetInteger(int val)
Set content of current Excel cell to an integer
.void SetDouble(double val)
Set content of current Excel cell to a double
.void SetString(const char* str)
Set content of current Excel cell to an ANSI string
.void SetWString(const wchar_t* str)
Set content of current Excel cell to an Unicode string
.void EraseContents()
Erase the content of current Excel cell. Set type to UNDEFINED
.例子程序
#include "BasicExcel.hpp"using namespace YExcel;int main(int argc, char* argv[]){ BasicExcel e; // Load a workbook with one sheet, display its contents and // save into another file. e.Load("example1.xls"); BasicExcelWorksheet* sheet1 = e.GetWorksheet("Sheet1"); if (sheet1) { size_t maxRows = sheet1->GetTotalRows(); size_t maxCols = sheet1->GetTotalCols(); cout << "Dimension of " << sheet1->GetAnsiSheetName() << " (" << maxRows << ", " << maxCols << ")" << endl; printf(" "); for (size_t c=0; c<maxCols; ++c) printf("%10d", c+1); cout << endl; for (size_t r=0; r<maxRows; ++r) { printf("%10d", r+1); for (size_t c=0; c<maxCols; ++c) { BasicExcelCell* cell = sheet1->Cell(r,c); switch (cell->Type()) { case BasicExcelCell::UNDEFINED: printf(" "); break; case BasicExcelCell::INT: printf("%10d", cell->GetInteger()); break; case BasicExcelCell::DOUBLE: printf("%10.6lf", cell->GetDouble()); break; case BasicExcelCell::STRING: printf("%10s", cell->GetString()); break; case BasicExcelCell::WSTRING: wprintf(L"%10s", cell->GetWString()); break; } } cout << endl; } } cout << endl; e.SaveAs("example2.xls"); // Create a new workbook with 2 worksheets and write some contents. e.New(2); e.RenameWorksheet("Sheet1", "Test1"); BasicExcelWorksheet* sheet = e.GetWorksheet("Test1"); BasicExcelCell* cell; if (sheet) { for (size_t c=0; c<4; ++c) { cell = sheet->Cell(0,c); cell->Set((int)c); } cell = sheet->Cell(1,3); cell->SetDouble(3.141592654); sheet->Cell(1,4)->SetString("Test str1"); sheet->Cell(2,0)->SetString("Test str2"); sheet->Cell(2,5)->SetString("Test str1"); sheet->Cell(4,0)->SetDouble(1.1); sheet->Cell(4,1)->SetDouble(2.2); sheet->Cell(4,2)->SetDouble(3.3); sheet->Cell(4,3)->SetDouble(4.4); sheet->Cell(4,4)->SetDouble(5.5); sheet->Cell(4,4)->EraseContents(); } sheet = e.AddWorksheet("Test2", 1); sheet = e.GetWorksheet(1); if (sheet) { sheet->Cell(1,1)->SetDouble(1.1); sheet->Cell(2,2)->SetDouble(2.2); sheet->Cell(3,3)->SetDouble(3.3); sheet->Cell(4,4)->SetDouble(4.4); sheet->Cell(70,2)->SetDouble(5.5); } e.SaveAs("example3.xls"); // Load the newly created sheet and display its contents e.Load("example3.xls"); size_t maxSheets = e.GetTotalWorkSheets(); cout << "Total number of worksheets: " << e.GetTotalWorkSheets() << endl; for (size_t i=0; i<maxSheets; ++i) { BasicExcelWorksheet* sheet = e.GetWorksheet(i); if (sheet) { size_t maxRows = sheet->GetTotalRows(); size_t maxCols = sheet->GetTotalCols(); cout << "Dimension of " << sheet->GetAnsiSheetName() << " (" << maxRows << ", " << maxCols << ")" << endl; if (maxRows>0) { printf(" "); for (size_t c=0; c<maxCols; ++c) printf("%10d", c+1); cout << endl; } for (size_t r=0; r<maxRows; ++r) { printf("%10d", r+1); for (size_t c=0; c<maxCols; ++c) { cout << setw(10) << *(sheet->Cell(r,c)); // Another way of printing a cell content. } cout << endl; } if (i==0) { ofstream f("example4.csv"); sheet->Print(f, ',', '\"'); // Save the first sheet as a CSV file. f.close(); } } cout << endl; } return 0;}
再看一下我使用在项目的中例子程序哈,用着还不错的:
void CQueryTdZsDlg::ExportData(){SetCtrlEnableWindow();m_dataProgress.SetPos(0);m_dataProgress.SetStep(1);m_dataProgress.SetRange(0,this->m_ListData.GetItemCount());m_DataNum.SetWindowText(_T(""));int nCurRow = 0;int nCurCol = 0;BasicExcel objExcel;objExcel.New(1);BasicExcelWorksheet* pSheet = objExcel.GetWorksheet(0);XLSFormatManager ExlFormat(objExcel);CellFormat celFormat(ExlFormat);ExcelFont celFont;celFont._weight = FW_BOLD;celFont._height = FW_BOLD/2;celFont._name = _T(L"宋体");celFormat.set_font(celFont);celFormat.set_alignment(EXCEL_HALIGN_CENTRED);BasicExcelCell* pCell = pSheet->Cell(0,0);pCell->SetWString("土地登记发证统计台账");pCell->SetFormat(celFormat);pCell->SetMergedColumns(this->m_ListData.GetHeaderCtrl()->GetItemCount());pCell->SetMergedRows(2);nCurRow = 2;celFont._weight = FW_BOLD;celFont._height = FW_BOLD/3;celFont._name= _T(L"宋体");celFormat.set_font(celFont);celFormat.set_alignment(EXCEL_HALIGN_CENTRED);for(int nCol = 0 ; nCol != this->m_ListData.GetHeaderCtrl()->GetItemCount() ; ++nCol){HDITEM hdi;TCHAR lpBuffer[256]={0};hdi.mask= HDI_TEXT;hdi.pszText= lpBuffer;hdi.cchTextMax= 255;this->m_ListData.GetHeaderCtrl()->GetItem(nCol,&hdi);switch(nCol){case 0:pSheet->SetColWidth(nCol,7000/3);break;case 1:pSheet->SetColWidth(nCol,25000/4);break;case 2:pSheet->SetColWidth(nCol,20000/4);break;case 3:pSheet->SetColWidth(nCol,47000/4);break;case 4:pSheet->SetColWidth(nCol,32000/5);break;case 5:pSheet->SetColWidth(nCol,39000/4);break;case 6:case 7:case 8:case 9:case 10:pSheet->SetColWidth(nCol,16000/4);break;default:pSheet->SetColWidth(nCol,16000/4);break;}pCell = pSheet->Cell(nCurRow,nCol);pCell->SetWString(hdi.pszText);pCell->SetFormat(celFormat);}++nCurRow;celFont._weight = FW_NORMAL;celFont._name= _T(L"宋体");celFormat.set_font(celFont);celFormat.set_alignment(EXCEL_HALIGN_CENTRED);celFormat.set_format_string(XLS_FORMAT_TEXT);for(int nRow = 0 ; nRow != this->m_ListData.GetItemCount() ; ++nRow){for(int nCol = 0 ; nCol != this->m_ListData.GetHeaderCtrl()->GetItemCount() ; ++nCol){pCell = pSheet->Cell(nCurRow+nRow,nCol);pCell->SetWString(this->m_ListData.GetItemText(nRow,nCol));pCell->SetFormat(celFormat);}m_dataProgress.SetPos(nRow+1);CString sTemp(_T(""));sTemp.Format(_T("%d/%d 正在导出"),nRow+1,this->m_ListData.GetItemCount());m_DataNum.SetWindowText(sTemp);}CString sFileName(_T("土地登记发证统计台账.xls"));size_t length = MultiByteToWideChar(CP_ACP,MB_COMPOSITE,sFileName,-1,NULL,0);if(length > 0){wchar_t* pUnicode = new wchar_t[length+1];memset(pUnicode,0,(length+1)*sizeof(wchar_t)); MultiByteToWideChar(CP_ACP,MB_COMPOSITE,sFileName,-1,(LPWSTR)pUnicode,length);objExcel.SaveAs(pUnicode);delete[] pUnicode;}//ShellExecute(0,"open",sFileName, NULL, NULL, SW_NORMAL);SetCtrlEnableWindow();CString sTemp(_T(""));sTemp.Format(_T("%d/%d 导出完成!"),this->m_ListData.GetItemCount(),this->m_ListData.GetItemCount());m_DataNum.SetWindowText(sTemp);}
0 0
- 使用BasicExcel操作Excel
- BasicExcel 对Excel 的操作使用
- Excel操作类-CSpreadSheet and BasicExcel
- Excel操作类-CSpreadSheet and BasicExcel
- basicexcel 使用
- [用C++链接Excel,使用BasicExcel库文件]
- basicexcel基本使用
- BasicExcel
- basicexcel
- BasicExcel使用方法,VC读取Excel方法
- QT下BasicExcel库的初步使用
- QT下BasicExcel库的初步使用
- MFC中使用BasicExcel外部类
- QT下使用BasicExcel 并支持中文
- MFC excel读取显示,spreadsheet和basicexcel控件
- Excel与VS连接之—BasicExcel类
- BasicExcel - A Class to Read and Write to Microsoft Excel
- 使用python操作excel
- tab bar selected image invalid
- Configure the network of CentOS
- Java反射机制的原理及在Android下的简单应用
- *p=NULL与p=NULL见解
- android 内存溢出问题
- 使用BasicExcel操作Excel
- delete MySQL
- seo
- 小梅哥fpga学习笔记之NIOS II CPU复位异常的原因及解决方案
- SSH端口转发
- 一个自定义Loading控件的实现
- ACM-车牌号
- stl容器排序
- Android推送服务(GCM)