Summary

The CP_EXCELQUERY command in CADPower is a versatile, multi-purpose tool that enables users to read data from XLSX files, query it using logical conditions, and insert the retrieved information into CAD drawings. The data can be displayed as tables, MTEXT, or attributed blocks, allowing for flexible and dynamic integration of spreadsheet information into design files. This command is compatible with industry-leading CAD platforms like BricsCAD, AutoCAD, ZWCAD, and ARES Commander, making it a crucial tool for CAD professionals who frequently work with Excel data.

CADPower -> CADPower Object Table Data Tools -> CADPower Object Table Data Tools -> Query and Insert data from Excel sheet

Key features include:

Read Data Directly from XLSX Files:
Access spreadsheet data without exporting it to intermediate formats.

• Flexible Query Options:
  Query Excel data based on rows, columns, or cells, using logical conditions such as equal to, greater than, less than, and contains.
• Dynamic Data Matching with CAD Entities:
  Match Excel data with CAD objects based on XDATA, block attributes, or AutoCAD map object tables.
• Insert Data into CAD Drawings:
  Display the queried information as MTEXT, CAD tables, or attributed blocks.
• Data Filtering Options:
  Apply case-sensitive or partial text matching during data queries for more precise results.
• Precision Control:
  Set the decimal precision to control the formatting of numerical data.
• Customizable Text Properties:
  Define text height for better readability when inserting data as text annotations.
• Interactive Object Selection:
  Query data for single objects or multiple objects with ease.
• Cross-Platform Compatibility:
  Fully compatible with BricsCAD, AutoCAD, ZWCAD, and ARES Commander, ensuring smooth operation across CAD environments.

Practical Applications:

• GIS and Surveying: Query and display location-specific data such as coordinates, elevations, and land-use classifications.
• BIM Documentation: Retrieve and insert material properties, component schedules, and cost estimates into construction drawings.
• Facility Management: Import asset data like equipment IDs, locations, and maintenance records.
• Mechanical Design: Display specifications and performance parameters of mechanical components directly from Excel.

Step-by-Step Workflow:

1. Open the Command:
   Run the CP_EXCELQUERY command from the CADPower toolbar or command line.
2. Select the Excel File:
   Click on “XLSX file name” and browse to select the Excel file you want to query.
3. Define the Sheet and Query Parameters:
   • Enter the sheet name.
   • Specify the title row and define the search criteria (e.g., row, column, or cell).
4. Configure Output Settings:
   • Choose to import data as text or attributed blocks.
   • Set the text height and decimal precision.
   • Select the table name and field names if using AutoCAD Map or CADPower object tables.
5. Set Query Logic:
   • Choose a value condition (e.g., =, >, <, contains).
   • Enter the property value to search for.
   • Enable case-sensitive or partial text match if required.
6. Select CAD Objects (Optional):
   Use “Select object” or “Select objects (multiple)” to link the queried data to existing entities.
7. Insert Data:
   Click OK to generate tables, text annotations, or attributed blocks with the queried information inserted into the drawing.

Example Use Case:

The use-case for this: What is the problem it solves?

You have an Excel sheet and you have a CAD drawing. You need to match the content in the Excel sheet based on your CAD data and pull out relevant information. This command has the solution.

There are two sets of information that you need to specify in this main dialog box.

The first set pertains to the XLSX workbook name and the SheetName where you need to look for the data.

Next, you need to specify the rows and columns you want to search.

The first item in this list is the location of the Title row: usually Excel sheets have the title info specified in one of the rows. You need to specify that row number here.

In the Search Column and Search Row sections, you need to specify a row or column name that you want to quick-search. The results are displayed instantly in a popup window opened by the Show buttons and copied to clipboard and/or placed as MTEXT or attributes block on the drawing. There is a Search Cell option also to retrieve the value of a single cell.

The TitleRow Info from the Excel sheet

The results of a queried column

The results of a query from XLSX displayed as table in CAD

The next section is called Data Feeders where you can get CAD data from selected object(s) or enter your own independent search data.

Data from your drawing can either come from an AutoCAD Map Object table, CADPower object data or from inherent CAD properties like Length, Area, block attribute, text value, radius, elevation, color, rotation angle etc. The complete list of CAD properties that you can use are shown in the image below:

CAD properties that can be queried from XLSX

The next set of information you need to provide is about the CAD entity that you want to use for query. Clicking on the Select button allows you to pick an entity that will offer its internal CAD properties or attached object table or attribute data for query. You need to make the right selections from the Data Feeders and get the property value that you want to query.

You can also specify logical operators on the data and perform the query based on whether you want an exact match, less than or greater than or not equal to the value (for integer and real values). For string values, you can search based on their case, or even partial search and so on.

All the search conditions you specify here will be taken into consideration when searching the XLSX data.

Excel data read in and displayed as table

Excel data read in and displayed as MTEXT