读写Excel文件最好还是用Office VBA之类的,C++读写原来这么不容易


C++ Read and write Excel files

Submitted by: 
Language: 
Visitors have accessed this post  1822 times.
 

Number Duck is a commercial C++ library that I've written to simplify reading and writing Excel files. You can download Number Duck here, and check out this page for Windows and Linux instructions on installing, building and linking the library.

Below is an overview of the main components and some quick examples that show how to start accessing Excel files with Number Duck.

Components of a spreadsheet

Workbook is the general representation of the Excel spreadsheet, the initial object created before reading or writing. It contains all the Styles, Worksheets and any overall functionality such as saving and loading.

Using Styles you can change the appearance of the cells by applying different fonts and colors. Styles can be created once and then applied to many cells across many worksheets.

Worksheets manage all cells and embedded Pictures.

Cell contain values or formulas, and can have previously created styles applied to them.

Writing to an Excel file

You can refer to individual cells by address just like you would in Excel, eg: "A1", or by its X and Y coordinates, eg: (0,0). Note that the coordinates start at 0 while addresses start at "1".

The cells can then be assigned directly with the SetString, SetFloat, SetBoolean or SetFormula functions.


          
          
  1. #include "Workbook.h"
  2. #include "Worksheet.h"
  3. #include "Cell.h"
  4.  
  5. using namespace FileScribe ;
  6.  
  7. int main ( int argc, char **argv )
  8. {
  9. Workbook workbook ( "" ) ;
  10. Worksheet * pWorksheet = workbook. GetWorksheetByIndex ( 0 ) ;
  11.  
  12. for ( uint16_t y = 0 ; y < 10 ; y ++ )
  13. {
  14. Cell * pCell = pWorksheet - >GetCell ( 0,y ) ;
  15. pCell - >SetFloat (y * 3.1417f ) ;
  16. }
  17.  
  18. pWorksheet - >GetCellByAddress ( "B1" ) - >SetString ( "http://numberduck.com" ) ;
  19. pWorksheet - >GetCellByAddress ( "B2" ) - >SetBoolean ( true ) ;
  20. pWorksheet - >GetCellByAddress ( "B3" ) - >SetFormula ( "=SUM(A1:A10)" ) ;
  21.  
  22. workbook. Save ( "Spreadsheet.xls" ) ;
  23.  
  24. return 0 ;
  25. }

Reading an Excel file

Reading an Excel file is as easy as writing, the file is loaded and the cells referenced in the same way. When reading, you should be careful to use the correct type for the cell. EG: If a cell contains a float, you must use GetFloat. Using an incorrect function will return the default value for that type, EG : GetString returns "" if the cell does not contain a string. GetType function allows two that you check the type.


          
          
  1. Workbook * pWorkbookIn = new Workbook ( "" ) ;
  2. if (pWorkbookIn - >Load ( "Spreadsheet.xls" ) )
  3. {
  4. Worksheet * pWorksheetIn = pWorkbookIn - >GetWorksheetByIndex ( 0 ) ;
  5. Cell * pCellIn = pWorksheetIn - >GetCell ( 0, 0 ) ;
  6. if (pCellIn - >GetType ( ) == Cell :: TYPE_FLOAT )
  7. printf ( "Cell Contents: %f\n", pCellIn - >GetFloat ( ) ) ;
  8. }

Formatting

Existing Styles can be accessed or new styles created through the workbook object. Note that the default style can also be accessed through the Workbook, which is used by all Cells unless overridden.


          
          
  1. Workbook workbook ( "" ) ;
  2. Worksheet * pWorksheet = workbook. GetWorksheetByIndex ( 0 ) ;
  3.  
  4. Style * pDefaultStyle = workbook. GetDefaultStyle ( ) ;
  5. pDefaultStyle - >SetColor (Color ( 128, 128, 128 ) ) ;
  6.  
  7. Style * pStyle = workbook. CreateStyle ( ) ;
  8. pStyle - >SetFont ( "Comic Sans MS" ) ;
  9. pStyle - >SetColor (Color ( 255, 0, 255 ) ) ;
  10. pStyle - >SetBackgroundColor (Color ( 255, 255, 0 ) ) ;
  11. pStyle - >SetItalic ( true ) ;
  12. pStyle - >SetBold ( true ) ;
  13.  
  14. Cell * pCell = pWorksheet - >GetCellByAddress ( "A1" ) ;
  15. pCell - >SetString ( "Default style" ) ;
  16.  
  17. pCell = pWorksheet - >GetCellByAddress ( "A2" ) ;
  18. pCell - >SetStyle (pStyle ) ;
  19. pCell - >SetString ( "This looks hideous!" ) ;
  20.  
  21. workbook. Save ( "Formatting.xls" ) ;

Pictures

The images are created from a JPEG or PNG image through the Worksheet object. SetX and SetY are used to establish the cell coordinates of the picture while SetSubY and SetSubX position of image within the cell in pixel increments.


          
          
  1. Workbook workbook ( "" ) ;
  2. Worksheet * pWorksheet = workbook. GetWorksheetByIndex ( 0 ) ;
  3.  
  4. Picture * pPicture = pWorksheet - >CreatePicture ( "duck.png" ) ;
  5. pPicture - >SetX ( 1 ) ;
  6. pPicture - >SetY ( 1 ) ;
  7. pPicture - >SetSubX ( 10 ) ;
  8. pPicture - >SetSubY ( 10 ) ;
  9.  
  10. workbook. Save ( "Picture.xls" ) ;

Conclusion

If you need more information, there is heaps more Number Duck documentation you can look at.

Thanks for reading!
Adam Clifton,
adam@numberduck.com


点击打开链接

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值