[Ruby] Reading and Writing Excel files

To use Ruby to Operate Excel files, you must first include the following statements in the script:
Require 'win32ole'

After win32ole is included, you can interact with the Excel API in Windows to read and write Excel files.

Open the Excel file and access the sheet:
Excel = win32ole: New ('excel. application ')
Workbook = excel. workbooks. Open ('C:/examples/spreadsheet.xls ')
Worksheet = Workbook. worksheets (1) # locate the first sheet
Worksheet. Select

Read data:
Worksheet. Range ('a12') ['value'] # Read data in A12
Data = worksheet. Range ('a1: C12 ') ['value'] # Read data into a two-dimensional table.

The value of column A at the first position is null.
Line = 1
While worksheet. Range ("A # {Line}") ['value']
Line = line + 1
End # The line value is the number of rows in the first blank line.

Read the value of the first column into an array.
Line = '1'
Data = []
While worksheet. Range ("A # {Line}") ['value']
Data <worksheet. Range ("A # {Line}: D # {Line}") ['value']
Line. succ!
End

Write data to an Excel worksheet
Worksheet. Range ('E2') ['value'] = time. Now. strftime '% d/% m/% y' # single value
Worksheet. Range ('a5: c5') ['value'] = ['test', '25', 'result'] # Write an array

Call macro definition
Excel. Run ('sortbynumber ')

Set background color
Worksheet. Range ('a3: f5'). Interior ['colorindex'] = 36 # pale yellow
# Restore the background color to colorless
Worksheet. Range ('a3: f5'). Interior ['colorindex'] =-4142 # xlcolorindexnone constant
# Use Excel constant to restore the background color to colorless
Worksheet. Range ('a3: f5'). Interior ['colorindex'] = excelconst: xlcolorindexnone

Save
Workbook. Close (1)
# Or
Workbook. saveas 'myfile.xls'
# The default path is my document defined by the system"

End session
Excel. Quit

Some relatively complete code snippets

Create an Excel file and save it
Require 'win32ole'
Excel = win32ole. New ("Excel. application ")
Excel. Visible = true # in case you want to see what happens
Workbook = excel. workbooks. Add
Workbook. saveas ('C:/examples/spreadsheet1.xls ')
Workbook. Close

Several important elements of an Excel file
Excel => workbook => worksheet => range (cell)
I understand that excel is the class name, and workbook is a specific (Excel file) instance. After creating an instance, worksheet is a worksheet in the instance (workbook, workbook ).

In order to read and write each cell (range (cell) in the worksheet -------------- there is certainly no error in following this operation, but the following statements make me somewhat confused

Excel. workbooks ("mappe1"). worksheets ("tabelle1"). Range ("A1"). Value # Read the values in cells A1 with the worksheet name tabelle1 in an Excel file named mappe1.
Excel. worksheets ("tabelle1"). Range ("A1"). Value # the same as the first statement
Excel. activeworkbook. activesheet. Range ("A1"). Value # Same as the first statement
Excel. activesheet. Range ("A1"). Value # matches the first statement
Excel. Range ("A1"). Value # the same as the first statement

Excel allows you to directly operate on all attributes. The default value is the currently active workbook/worksheet.

Operations on cells:

A cell: sheet. Range ("A1 ")

A1 to C3 value: sheet. Range ("A1", "C3") or sheet. Range ("A1: C3 ")

Column 1: sheet. Range ("A: ")

Row 3: sheet. Range ("3: 3 ")

Obtain the cell value:
Range. Text # Read value. The return value is in string format. If the number is in the cell, the number of digits after the decimal point may be truncated.
Sheet. Range ("A1"). Text

Range. Value # Read value. The number is not truncated.
Sheet. Range ("A1"). Value

Set cell values
Sheet. Range ("A1"). value = 1.2345
Or
Sheet. Range ("A1"). value = '1. 2345'

Iterative access:

Sheet. Range ("A1: A10"). Each {| cell | puts cell. Value}

If the range is a rectangle, the access will be cyclically iterated by row.
Sheet. Range ("A1: B5"). Each {| cell | puts cell. Value}

Block iteration, and print the first value of each line
Sheet. Range ("B3: C7"). Rows. Each {| r | puts R. cells (1, 1). Value}