Method for generating crosspiece chart through click selection of water conservancy and hydropower engineering technology

Abstract

The invention discloses a method for generating a crosspiece chart through click selection of a water conservancy and hydropower engineering technology, and the method comprises the following steps: 1, starting Excel, loading a VSTO plug-in, and opening a crosspiece chart file to be drawn; step 2, positioning an engineering item of a to-be-drawn crosspiece chart, and selecting a cell corresponding to a starting year month on the right side of a construction period column of a row where the engineering item is located; step 3, clicking a click or continuous click button under the crosspiece chart drawing toolbar; 4, obtaining the engineering quantity value and the construction period value of the row where the selected cell is located; 5, matching the operation of generating the crosspiece chart according to the obtained engineering quantity value and the construction period value; and step 6, generating a crosspiece chart meeting requirements. Therefore, the efficiency of drawing the crosspiece chart can be effectively improved, and manual drawing errors are avoided.

Description

Technical Field

[0001] The invention belongs to the technical field of water conservancy and hydropower engineering, and in particular relates to a method for generating a bar chart by point selection of water conservancy and hydropower engineering technology. Background Art

[0002] Cross-cutting method for construction organization design of water conservancy and hydropower projects Figure 1 It is usually presented in the form of an Excel table, which consists of rectangular blocks within the cell range and numbers marked in the cells. The total number of cells covered by the rectangular blocks represents the duration value used for the project, and the numbers marked in the cells are the construction intensity of the month. The efficiency and accuracy of the drawing of the bar chart of the construction organization design of water conservancy and hydropower projects have an important impact on the subsequent construction organization.

[0003] The traditional manual drawing method of the bar chart can be divided into the following stages: calculating the annotation value to be added; selecting the cell range to add the bar chart; inserting a rectangle in the selected range; adding the annotation value in the selected range; and changing the fill color of the rectangle. The existing method can only manually select the drawing range, calculate the intensity value, insert and adjust the color of the rectangle block, which is very time-consuming and labor-intensive, and prone to errors.

[0004] VSTO is a technology used to create Microsoft Office solutions, which enables developers to use the .NET Framework to build powerful customized Office applications. When developing plug-ins in Excel, VSTO provides rich functions and APIs, allowing developers to implement functions such as custom functions, data processing, and report generation. Through the event model provided by VSTO, various events in Excel, such as cell content changes and worksheet activation, can be captured, so as to develop more flexible and intelligent plug-ins. Summary of the invention

[0005] The purpose of the present invention is to provide a method for generating a bar chart by point selection of water conservancy and hydropower engineering technology, which solves the problem that the existing manual bar chart drawing method is cumbersome and error-prone.

[0006] The technical solution adopted by the present invention is: a method for generating a bar chart by selecting water conservancy and hydropower engineering technology, comprising the following steps:

[0007] Step 1. Start Excel and load the VSTO plug-in, then open the document where you want to draw the bar chart.

[0008] Step 2: Locate the project for which you want to draw a bar chart, and select the cell corresponding to the starting year and month on the right side of the duration column of the row where the project is located;

[0009] Step 3. Click the Select or Continuous Select button on the Bar Chart Drawing Toolbar;

[0010] Step 4: Get the engineering quantity value and duration value of the row where the selected cell is located;

[0011] Step 5: Match the operation of generating the bar chart according to the obtained engineering quantity value and construction period value;

[0012] Step 6: Generate a bar chart that meets the requirements.

[0013] The present invention is also characterized in that:

[0014] In step 3, if you click the Select button, the bar chart will be drawn based on the user's selected cells and the drawing state will be exited; if you click the Continuous Select button, the bar chart of the current row will be drawn based on the user's selected cells. The Excel program will wait for the user to select a cell in another row and then complete the drawing of another row of bar charts. The drawing state will be exited until the user clicks the Continuous Select button again.

[0015] In step 4, the quantity value and duration value of the cell whose column name is quantity and duration in the row where the selected cell is located are obtained.

[0016] Step 5 is specifically as follows: when the obtained engineering quantity value and duration value are non-zero numbers, find out whether there is a bar chart in the row where the selected cell is located. If there is a bar chart in the current row, obtain the sum of the cell values ​​in the drawing range of all bar charts in the current row, record it as the existing strength value, and the number of cells occupied by the bar chart, record it as the number of drawn cells, compare the existing strength value with the engineering quantity value, and divide the bar chart to be generated into two situations: duration exceeded and duration not exceeded, that is, if the existing strength value ≥ engineering quantity value, the duration exceeded, and if the existing strength value < engineering quantity value, the duration did not exceed; if there is no bar chart in the current row, take the selected cell as the starting cell, expand to the right by the corresponding number of cells according to the number of duration values, and then insert a rectangle in the selected range through the Excel program, and mark the strength value in the selected cell, strength value = engineering quantity value / duration value.

[0017] Step 6 is as follows:

[0018] If the construction period exceeds the limit, the Excel program will pop up a window to ask the user whether to redraw from the current position. If the user chooses yes, the Excel program will first obtain the number of columns corresponding to the start and end ranges of all the bar charts on the right side of the selected cell, select the bar chart with the smallest starting column number and delete it, and then subtract the existing strength value from the engineering quantity value to obtain the remaining engineering quantity value, and subtract the number of cells that have been drawn from the construction period value to obtain the remaining construction period value. Finally, with the selected cell as the starting cell, the selection area is expanded to the right according to the number of remaining construction period values ​​and the bar chart is drawn, and the strength value is marked in the cell, and the strength value = remaining engineering quantity value / remaining construction period value; if the user chooses no, the Excel program will exit the drawing mode;

[0019] If the construction period is within the limit and the number of cells drawn is zero, the selected cell is used as the starting cell, and the corresponding number of cells are selected to the right according to the number of construction period values. Then, a rectangle is inserted in the selected range through the Excel program, and the strength value is marked in the selected cell; if the construction period is within the limit and the number of cells drawn is not zero, the selected cell is used as the starting cell, and the corresponding number of cells are selected to the right according to the number of remaining construction period values. If the start and end column range of the selection area does not coincide with the start and end column range of the bar chart drawn in the current row, the selection area is expanded to the right according to the number of remaining construction period values ​​and a bar chart is drawn, and the strength value is marked in the selected area cell, and the strength value = remaining engineering quantity value / remaining construction period value; if the start and end column range of the selection area coincides with the start and end column range of the current row, If the start and end column ranges of the drawn bar chart overlap, the Excel program will pop up a window to ask the user whether to delete the bar chart next to the selected cell on the right. If the user chooses yes, the Excel program will first obtain the column numbers corresponding to the start and end ranges of all bar charts on the right side of the selection, select the bar chart with the smallest starting column number and delete it, then subtract the existing strength value from the engineering quantity value to get the remaining engineering quantity value, and subtract the number of drawn cells from the duration value to get the remaining duration value. Finally, with the selected cell as the starting cell, the selection is expanded to the right according to the number of remaining duration values ​​and a bar chart is drawn, and the strength value is marked in the cell, where the strength value = remaining engineering quantity value / remaining duration value. If the user chooses no, the Excel program exits the drawing mode.

[0020] The beneficial effects of the present invention are as follows: the method for generating a bar chart by clicking on the water conservancy and hydropower engineering technology of the present invention realizes the generation of a bar chart by clicking on the bar chart drawing tool in Excel, thereby reducing the manual operation process and improving the efficiency of drawing the bar chart. The Excel program is used to encapsulate the logical judgment and calculation rules of the present invention to calculate the intensity value, thereby improving the accuracy of the calculation result. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] Figure 1 It is a schematic flow chart of a method for generating a bar chart by point selection of water conservancy and hydropower engineering technology of the present invention;

[0022] Figure 2 It is a schematic diagram of the actual display method of the present invention in Excel;

[0023] Figure 3 It is a schematic diagram of the generated results when there is no other bar chart in the current row;

[0024] Figure 4 This is a pop-up window prompt diagram that appears when a value in the quantity column or duration column is 0;

[0025] Figure 5 This is a schematic diagram of the situation where there are other bar charts in the current row and the total number of cells occupied by the bar charts is greater than or equal to the value in the duration column;

[0026] Figure 6 This is a schematic diagram of the situation where there are other bar charts in the current row, but the total number of cells occupied by the bar charts is less than the value in the duration column and they do not intersect when drawn;

[0027] Figure 7 This is a schematic diagram of the situation where there are other bar charts in the current row, but the total number of cells occupied by the bar charts is less than the value in the duration column and they intersect when drawn. DETAILED DESCRIPTION

[0028] The present invention is described in detail below with reference to the accompanying drawings and specific embodiments.

[0029] Example 1

[0030] The present invention provides a method for generating a bar chart by clicking on water conservancy and hydropower engineering technology, develops a VSTO plug-in and embeds a bar chart drawing tool in Excel software, and a user can generate a bar chart by clicking on the bar chart drawing tool in Excel, thereby improving the drawing efficiency of the bar chart. The Excel program is used to encapsulate the logical judgment and calculation rules of the present invention to calculate the intensity value, thereby improving the accuracy of the calculation result.

[0031] Example 2

[0032] The present invention provides a method for generating a bar chart by selecting water conservancy and hydropower engineering technology points, such as Figure 1 As shown, the following steps are included:

[0033] S1: The user starts the Excel software and loads the VSTO plug-in, and a button for the bar chart drawing tool appears in the Excel ribbon.

[0034] S2: The user opens the Excel document for drawing the bar chart, locates the project item for which the bar chart is to be drawn, and selects the cell corresponding to the starting year and month on the right side of the duration column of the row where the project item is located; if the cell to the left of the cell where the duration column is located is selected, the plug-in will fail to recognize the value of the project quantity, resulting in a calculation error.

[0035] S3: The user clicks the "Click" or "Continuous Click" button in the bar chart drawing toolbar; specifically: the user selects one of the two controls, "Click" and "Continuous Click", and the difference between "Click" and "Continuous Click" is that: "Click" is applicable to the situation where the drawing state is immediately exited after a single execution is completed, and "Continuous Click" is applicable to the situation where bar charts are drawn continuously for multiple rows. This function can reduce the workload of users repeatedly clicking the "Click" button. If the user clicks the "Click" control, the drawing state will be exited immediately after the bar chart is drawn; if the user clicks the "Continuous Click" button, when manually selecting other cells after the first drawing is completed, the Excel program will generate a bar chart according to the project quantity and duration at the corresponding position of the selected row, and it will end when the user clicks the "Continuous Click" button again. The tool buttons are as follows Figure 2 shown.

[0036] S4: Figure 3 As shown, the Excel program obtains the contents of the cells whose column names are engineering quantity and construction period in the row where the selected cell is located, and records them as engineering quantity value and construction period value respectively.

[0037] S5: The Excel program matches the operation of generating the bar chart according to the acquired information.

[0038] S6: The Excel program generates a bar chart that meets the requirements.

[0039] Example 3

[0040] The present invention provides a method for generating a bar chart by selecting water conservancy and hydropower engineering technology. In Example 2, S5 and S6 preferably follow the following steps:

[0041] S501: First determine whether the acquired content is a number;

[0042] S502: Determine whether the duration value or the quantity value is 0. If it is 0, the annotation value cannot be generated and the bar chart drawing operation is not performed. Figure 4 Pop-up window prompts are displayed to facilitate users to check errors by themselves according to the prompts;

[0043] S503: The Excel program searches whether there is a bar chart in the row where the selected cell is located. If there is, the sum of the cell values ​​in the drawing range of all bar charts in the current row (recorded as the existing strength value) and the number of cells occupied by the bar chart (recorded as the number of drawn cells) are obtained.

[0044] S504: Compare the "existing strength value" with the "project quantity value" to divide the situation into two situations: the construction period exceeds the limit and the construction period does not exceed the limit. If the "existing strength value" ≥ "project quantity value", the construction period exceeds the limit, and if the "existing strength value" < "project quantity value", the construction period does not exceed the limit. If there is no bar chart in the current row, take the selected cell as the starting cell, expand to the right by the number of "construction period value", and then call the native Excel insert function to insert a rectangle in the selected range, and mark the strength value in the cell, and the strength value = engineering quantity value / construction period value.

[0045] According to the judgment result of S504, if the construction period exceeds the limit, a pop-up will be displayed. Figure 5 The inquiry window asks whether to redraw from the current position. If the user selects "Yes", the program will first obtain the number of columns corresponding to the start and end ranges of all the bar charts on the right side of the selection area, select the bar chart with the smallest number of starting columns and delete it, then use the "project quantity value" to subtract the "existing strength value" to get the "remaining project quantity value", and use the "construction period value" to subtract the "number of cells drawn" to get the "remaining construction period value". Finally, with the selected cell as the starting cell, expand the selection to the right according to the number of "remaining construction period value" and draw the bar chart, and mark the strength in the cell, strength value = remaining project quantity value / remaining construction period value. If you select "No", the program will directly exit the drawing mode without performing subsequent operations.

[0046] If the result is that the construction period is within the limit and the "Number of drawn cells" is zero, then take the selected cell as the starting cell, expand to the right by the corresponding number of cells according to the "Construction period value", and then call the native Excel insert function to insert a rectangle in the selected range, and mark the strength in the cell. The strength value = engineering quantity value / construction period value.

[0047] If the duration is within the limit and the "Number of cells drawn" is not zero, the situation is as follows Figure 6 At this time, take the selected cell as the starting cell, expand to the right by the number of "remaining duration value" to select the corresponding number of cells, if the start and end column range of the selection area does not coincide with the start and end column range of the bar chart drawn in the current row, then expand the selection area to the right by the number of "remaining duration value" and draw a bar chart, and mark the strength in the selected cell, the strength value = remaining engineering quantity value / remaining duration value; if the start and end column range of the selection area coincides with the start and end column range of the bar chart drawn in the current row, a pop-up window will pop up. Figure 7A pop-up window will ask whether to delete the bar chart adjacent to the right side of the selected cell. Select "Yes". The program will first obtain the number of columns corresponding to the start and end ranges of all bar charts on the right side of the selection, select the bar chart with the smallest starting number of columns and delete it, then subtract the "existing strength value" from the "project quantity value" to get the "remaining project quantity value", and subtract the "number of drawn cells" from the "construction period value" to get the "remaining construction period value". Finally, with the selected cell as the starting cell, expand the selection to the right according to the number of "remaining construction period value" and draw a bar chart, and mark the strength in the cell. Strength value = remaining project quantity value / remaining construction period value.

Claims

1. Method of generating bar chart by selecting water conservancy and hydropower engineering technology. It is characterized in that The following steps are involved: Step 1. Start Excel and load the VSTO plug-in, then open the document where you want to draw the bar chart. Step 2: Locate the project for which you want to draw a bar chart, and select the cell corresponding to the starting year and month on the right side of the duration column of the row where the project is located; Step 3. Click the Select or Continuous Select button on the Bar Chart Drawing Toolbar; Step 4: Get the engineering quantity value and duration value of the row where the selected cell is located; Step 5: Match the operation of generating the bar chart according to the obtained engineering quantity value and construction period value; Step 6: Generate a bar chart that meets the requirements.

2. The method for generating a bar chart by selecting water conservancy and hydropower engineering technology as claimed in claim 1, It is characterized in that If the selection button is clicked in step 3, the bar chart will be drawn based on the user's selected cells and the drawing state will be exited; if the continuous selection button is clicked, the bar chart of the current row will be drawn based on the user's selected cells, and the Excel program will wait for the user to select a cell in another row to complete the drawing of another row of bar charts, and will exit the drawing state after the user clicks the continuous selection button again.

3. The method for generating a bar chart by selecting water conservancy and hydropower engineering technology as claimed in claim 1, It is characterized in that In the step 4, the engineering quantity value and the construction period value of the cell whose column name is engineering quantity and construction period in the row where the selected cell is located are obtained.

4. The method for generating a bar chart by selecting water conservancy and hydropower engineering technology as claimed in claim 1, It is characterized in that The step 5 is specifically as follows: when the obtained engineering quantity value and duration value are non-zero numbers, find out whether there is already a bar chart in the row where the selected cell is located; if there is a bar chart in the current row, obtain the sum of the cell values ​​in the drawing range of all bar charts in the current row, record it as the existing strength value, and the number of cells occupied by the bar chart, record it as the number of cells drawn; compare the existing strength value with the engineering quantity value, and divide the bar chart to be generated into two situations: the duration exceeds the limit and the duration does not exceed the limit, that is, if the existing strength value ≥ the engineering quantity value, the duration exceeds the limit, and if the existing strength value < the engineering quantity value, the duration does not exceed the limit; If there is no bar chart in the current row, take the selected cell as the starting cell, expand to the right by the corresponding number of duration values, and then insert a rectangle in the selected range through the Excel program, and mark the strength value in the selected cell. Strength value = engineering quantity value / duration value.

5. The method for generating a bar graph by selecting water conservancy and hydropower engineering technology as claimed in claim 4, It is characterized in that The step 6 is specifically as follows: If the construction period exceeds the limit, the Excel program will pop up a window to ask the user whether to redraw from the current position. If the user chooses yes, the Excel program will first obtain the number of columns corresponding to the start and end ranges of all bar charts on the right side of the selected cell, select the bar chart with the smallest starting column number and delete it, then subtract the existing strength value from the engineering quantity value to obtain the remaining engineering quantity value, and subtract the number of cells drawn from the construction period value to obtain the remaining construction period value. Finally, with the selected cell as the starting cell, the selection area is expanded to the right according to the number of remaining construction period values ​​and a bar chart is drawn, and the strength value is marked in the cell. Strength value = remaining engineering quantity value / remaining construction period value; If the user selects No, the Excel program exits drawing mode; If the duration is within the limit and the number of cells drawn is zero, the selected cell is used as the starting cell, and the corresponding number of cells are selected to the right according to the duration value. Then, a rectangle is inserted in the selected range through the Excel program, and the intensity value is marked in the selected cell; If the duration is within the limit and the number of cells drawn is not zero, the selected cell is used as the starting cell, and the corresponding number of cells are selected to the right according to the number of remaining duration values. If the start and end column range of the selection area does not coincide with the start and end column range of the bar chart drawn in the current row, the selection area is expanded to the right according to the number of remaining duration values ​​and a bar chart is drawn, and the strength value is marked in the selected cell, and the strength value = remaining engineering quantity value / remaining duration value; if the start and end column range of the selection area coincides with the start and end column range of the bar chart drawn in the current row, the Excel program pops up a window to ask the user whether to delete the cells immediately to the right of the selected cell. For adjacent bar charts, if the user selects yes, the Excel program first obtains the number of columns corresponding to the start and end ranges of all bar charts on the right side of the selection, selects the bar chart with the smallest starting number of columns and deletes it, then subtracts the existing strength value from the quantity value to obtain the remaining quantity value, and subtracts the number of drawn cells from the duration value to obtain the remaining duration value. Finally, with the selected cell as the starting cell, the selection is expanded to the right according to the number of remaining duration values ​​and a bar chart is drawn, and the strength value is marked in the cell, where the strength value = remaining quantity value / remaining duration value. If the user selects no, the Excel program exits the drawing mode.

Images