This topic describes how to edit a workbook. For example, you can import data to, export data from, and share a workbook, and create a pivot table in a workbook.

Go to the workbook editing page

  1. Log on to the DataWorks console.
  2. Click Icon in the upper-left corner and choose All Products > Data Development > DataAnalysis.
  3. On the DataAnalysis homepage, click Experience Now. The Web Excel page appears.
  4. In the All Spreadsheets section of the Web Excel page, click the name of the target workbook in the File Name column.

    When you create a workbook, the workbook editing page appears after you enter the workbook name. For more information, see Create a workbook.

Query and import data from a data store

On the workbook editing page, you can specify a data store, query data from the data store, and then import the queried data to the workbook.
Note
  • You can only query and import data from MaxCompute, MySQL, or PostgreSQL.
  • Only workspaces deployed in the China (Shanghai) region support this feature.
  1. In the upper-right corner of the workbook editing page, click Query From Datasource.
  2. In the Data source section, click +.Query From Datasource
  3. In the Select Datasource dialog box, set the Type, Workspace, and Data Source parameters.
    Note The Data Source parameter is not required if you set the Type parameter to MaxCompute.
  4. Click OK.
  5. In the Data source section, double-click the target data store.
  6. In the right-side query editing area, enter a query statement.

    You can also double-click the name of a table or field to generate a query statement.

  7. Click Run icon in the toolbar. The queried data is imported to the workbook.Operations
    In the lower part of the right-side pane, you can view the statements that you have saved and run on the Saved and History tabs, respectively. You can load, rename, and delete statements as needed. You can also perform the following operations in the query editing area:
    • Click Save icon in the toolbar. In the Save dialog box, enter a name for the statement in the File Name field and click OK.
    • Click Delete icon in the toolbar to delete the query statement that you have entered.
    • Click Settings icon in the toolbar. In the Settings dialog box, set the Data Placement Location, Data Placement, and Get Data With Header parameters and click OK.Settings

Use the template feature

You can apply a template to the current workbook or save the current workbook as a template.
  • Import Template
    1. In the upper-right corner of the workbook editing page, choose Template > Import Template.
    2. In the Import Template dialog box, select a file to be used as a template for the current workbook.
      Note The data of the selected template will overwrite that of the current workbook.
    3. Click OK. The template is applied to the current workbook.
  • Save as Template
    1. In the upper-right corner of the workbook editing page, choose Template > Save as Template.
    2. In the Template settings dialog box, set the Type, Name, and Description parameters.
    3. Click OK. The current workbook is saved as a template.

Import data to a workbook

In the upper-right corner of the workbook editing page, choose Import > Local File. In the Open dialog box, find and select a local file to be imported and click Open. The data in the local file is imported to the current workbook.
Note You can only import data from Excel files.

You can create an ODPS SQL node on the DataStudio page to query data. The result data appears as a workbook.

Then, you can click DataAnalysis in the lower part to go to the workbook editing page. You can also copy and paste the result data to a local Excel file and import the Excel file to a workbook in DataAnalysis.

Export data from a workbook

You can generate SQL statements for inserting the data in the workbook to an existing MaxCompute table or a new MaxCompute table.

Generate MaxCompute Build Table Statement：
  1. In the upper-right corner of the workbook editing page, choose Export > Generate MaxCompute Build Table Statement.
  2. In the Export as MaxCompute Table dialog box, set relevant parameters. You can specify whether to insert data to an existing MaxCompute table or create a MaxCompute table and insert data to the created table.
    Insert mode Parameter Description
    Insert Data into MaxCompute Table (INSERT OVERWRITE) (insert overwrite) Workspace The workspace to which the MaxCompute table belongs.
    Table The MaxCompute table to which you want to insert data.
    Create MaxCompute Table and Insert Data (INSERT OVERWRITE) Workspace The workspace to which the MaxCompute table belongs.
    Table Name The name of the MaxCompute table. Make sure that the table name has not been used. You can click Check Duplicate Names to check whether the table name exists.
  3. After the parameters are set, click Copy SQL Statement.

Create a pivot table

  1. On the workbook editing page, select the data for which you want to create a pivot table and click Pivot in the upper-right corner.
  2. In the Create Pivot Table dialog box, specify the range of the data to be analyzed. You can set the Choose Data parameter to Select Range or Use External Data Source as needed.
    • Select Range

      Select the cells in the workbook for which you want to create a pivot table. The value of the Range field changes based on the selected cells.

    • Use External Data Source

      You can select a data store of the MaxCompute, MySQL, or DataService Studio type as needed. If you use an external data store, make sure that the connection to the data store has been configured. For more information, see Connection configuration.

  3. Click OK. The pivot table editing page appears. The following figure shows the pivot table for a selected range of data.OK
    • Data Source: the range that you specified in the previous step.
    • Pivot Table Fields: the names of the fields that you selected in the previous step.
    • Rows: Drag and drop fields from the Pivot Table Fields section to the Rows section. Each value of the field added to the Rows section occupies a row in the pivot table.
    • Columns: Drag and drop fields from the Pivot Table Fields section to the Columns section. Each value of the field added to the Columns section occupies a column in the pivot table.
    • Values: Click the property setting icon for a field in the Values section. In the Property settings dialog box, set the Summary method and Data Display mode parameters. By default, the Field Name parameter cannot be modified.Parameters
      Parameter Description
      Source Field The name of the selected source field.
      Field Name The name of the field that appears in the pivot table. The name is in the format of Aggregation method:Source field name.
      Summary method The aggregation method. Valid values: SUM, COUNT, MAX, MIN, and AVG.
      Data Display mode The mode for displaying the data. Valid values: No calculation and Percentage of Total.
    • Filters: Drag and drop fields from the Pivot Table Fields section to the Filters section. In the right-side pivot table display area, you can select the fields to filter data.

Share a workbook

In the upper-right corner of the workbook editing page, click Share. In the dialog box that appears, set the sharing mode.

You can share the workbook in the following ways:
  • Link: Click Copy Link and send the copied URL to other users as needed.
  • Users with Edit Access: Click Add in the Users with Edit Access section. In the dialog box that appears, select the users to whom you want to grant the edit permission and click OK.
  • Visible to All: To allow all users to view the workbook, turn on the Visible to All switch.
  • Users with Read Access: To allow only certain users to view the workbook, turn off the Visible to All switch and click Add in the Users with Read Access section. In the dialog box that appears, select the users to whom you want to grant the read-only permission and click OK.

Menu bar

  • FontFont
    No. Feature Description
    1 Font Select a font type from the drop-down list as needed.
    2 Font Size Select a font size from the drop-down list as needed.
    3 Bold Set text in bold.
    4 Italic Set text in italic.
    5 Underline Underline text.
    6 Strikethrough Add a strikethrough to text.
    7 Borders Add borders to cells.
    8 Background Color Specify the background color of cells.
    9 Text Color Change the text color.
  • Text AlignmentText Alignment
    No. Feature Description
    1 Top Align Align text vertically to the top.
    2 Middle Align Align text vertically to the center.
    3 Bottom Align Align text vertically to the bottom.
    4 Wrap Text Display long text in multiple lines in a cell.
    5 Align Left Align text horizontally to the left.
    6 Center Align text horizontally to the center.
    7 Align Right Align text horizontally to the right.
    8 Merge and Center Merge multiple cells to one cell and center the content in the cell.
  • NumberNumber
    No. Feature Description
    1 Data Type Specify the type of data held in cells. You can select General, Number, Currency, Short Date, Long Date, Time, Percentage, Fraction, Scientific, and Text from the drop-down list.
    2 Percentage Apply the percentage format to numbers.
    3 Two Decimal Places Round numbers to two decimal places.
    4 1000 Separator Display numbers with thousands separators, for example, 1,005.
    5 Currency Add a currency sign to numbers. The following currency signs are supported: yuan sign (￥), dollar sign ($), pound sign (£), euro sign (€), and franc sign (Fr).
  • Rows and ColumnsRows and Columns
    No. Feature Description
    1 Insert Row Insert a row to the workbook.
    2 Insert Column Insert a column to the workbook.
    3 Delete Row Delete rows from the workbook.
    4 Delete Column Delete columns from the workbook.
    5 Lock Row Lock the rows before the selected row in the workbook.
    6 Lock Column Lock the columns before the selected column in the workbook.
    7 Hide Row Hide rows in the workbook.
    8 Hide Column Hide columns in the workbook.
  • Conditional FormattingConditional Formatting
    No. Feature Description
    1 Highlight cell rules Specify the rules for highlighting cells.
    2 Data Bar/Color Scale Format cells by using data bars and color scales.
    3 Icon Set Format cells by using icon sets. The icon sets include directional icons, shapes, indicators, and rating icons.
    4 Clear Rule Clear the formatting. You can select Clear Rules from Selected Cells or Clear Rules from Entire Sheet from the drop-down list.
  • StyleStyle
    No. Feature Description
    1 Apply table style Apply a predefined table style to cells.
    2 Delete Remove the applied table style.
    3 Cell Style Apply a cell style to cells.
    4 Clear Clear the content or style in cells. You can select Clear All, Clear Content, or Clear Style from the drop-down list.
  • EditEdit
    No. Feature Description
    1 AutoSum Select an aggregation method. You can select Sum, Average, Count Numbers, Max, or Min from the drop-down list.
    2 Search Click Search or press Ctrl+F to open the search box.
    3 Sort and Filter Filter data and sort data in ascending or descending order.
    4 Clear Clear the content in cells.
  • ChartsCharts
    • Column ChartColumn Chart
    • Line ChartLine Chart
    • Pie ChartPie Chart
    • More icon

      Click the More icon to view more chart types, including area charts, bar charts, scatter charts, and stock charts.

  • List of Shortcut Keys

    Click List of Shortcut Keys icon to view the shortcut keys for different features.

Editing area

The editing area is below the menu bar. The editing area consists of cells and it is the main operation area for editing a workbook.

At the top of the editing area, the Fx field displays the formula that you enter in a cell.

You can enter content in a cell in the editing area. You can also enter a formula that references values in other cells. The column headings are labeled with letters, which start with A and increase alphabetically from left to right. The row headings are labeled with numbers, which start with 1 and increase from top to bottom. You can right-click selected row or column headings and select Hide, Unhide, or Delete to hide, unhide, or delete the selected rows or columns.

Worksheet bar

The worksheet bar is in the lower part of the workbook editing area. You can create multiple worksheets in a workbook. On each worksheet, you can edit data or create pivot tables.

Click + to create a worksheet and then edit it.