Design statistical production reports with VB. NET and Excel

Why Excel is used for enterprise production reports

Excel table generation and formula settings are very powerful and convenient. It is a powerful information analysis and processing tool. Especially Excel formulas, functions, and VBA languages are extremely powerful. I have tried other spreadsheet software and have no comparable functionality with Excel.

Visual Studio. NET is also a Microsoft product. Visual Studio. NET is very convenient to call Excel for enterprise reports. It proved that I chose Visual Studio. NET as the preferred development tool.

Software Conception

First, create a sample table (Template) named "statistical table" in Excel, set various formats in the sample table, and fill in fixed items.

Put three controls on the form and two datetimepicker controls to select the start time and end time. A button to startProgram.

What the software wants to achieve is: Click button1 to automatically find the production plan worksheet that meets the date range, and then use sortedlist to count the planned quantity and unfinished quantity of each office, and the planned quantity and unfinished quantity of each product model. Then, read the sortedlist data and write it to the statistical table.

Note that the format of each production report must be standardized and unified, because the program reads data at a fixed cell location.

Sortedlist class

In addition to the basic knowledge of using VB. NET to call excel, this example mainly uses the sortedlist class.

Sortedlist class indicates the set of key/value pairs. These keys and values are sorted by key and index.

Sortedlist is a mixture of hashtable and array. When the item indexer attribute is used to access an element according to the key of the element, its behavior is similar to hashtable. When getbyindex or setbyindex is used to access an element based on the element index, the behavior is similar to array.

Sortedlist maintains two arrays internally to store the arrays in the list. That is, one array is used for keys, and the other array is used for associated values. Each element is a key/value pair that can be accessed as a dictionaryentry object. The key cannot be a null reference (nothing in Visual Basic), but the value can be.

Sortedlist capacity is the number of elements available in the list. With the addition of elements to sortedlist, capacity increases automatically by reallocation as needed. You can call trimtosize or explicitly set the capacity attribute to reduce the capacity.

Sortedlist elements are implemented according to the specific icomparer implementation (specified when sortedlist is created) or the icomparable implementation provided by the key itself and sorted by the key. In either case, sortedlist does not allow duplicate keys.
 

VB. NET combined with Excel to produce statistical reports

The following is the implementationCodeFor reference. For beginners, some comments are provided.

Private sub form1_load (byval sender as object, byval e as system. eventargs) handles mybase. Load Datetimepicker1.maxdate = date. Now Datetimepicker1.mindate = #1/1/2004 # Datetimepicker2.maxdate = date. Now Datetimepicker2.mindate = #1/1/2004 # End sub Private sub cooker analysis statistics () Call killexcel () Dim excelapp as new excel. Application Dim jiani 2004 cooker Production Plan as Excel. Workbook Dim row number as integer = 1 Dim column number as integer = 1 Dim office plan count statistics as new sortedlist Dim office completion count statistics as new sortedlist Dim model plan count statistics as new sortedlist Dim model completion count statistics as new sortedlist Dim statistical table as Excel. worksheet' Try Jani 2004 cooker Production Plan = excelapp. workbooks. Open ("E: \ my kitchen ents \ Production Plan \ Jani 4cooker Production Plan .xls ") Statistical table = ctype (jiani 2004 cooker production plan. worksheets ("statistical table"), Excel. worksheet) Statistical table. cells (1, 3). value = datetimepicker1.value. tow.datestring 'statistical start date Statistical table. cells (1, 7). value = datetimepicker2.value. to1_datestring 'statistical end date Statistical table. Range ("C4: z6"). value = "" 'First clears the original data in the statistical table. Statistical table. Range ("C9: z11"). value = ""' Dim production schedule as Excel. Worksheet For each production schedule in January 2004 cooker production plan. worksheets 'traverse the production schedule If strings. left (production schedule. name, 1) = "0" or strings. left (production schedule. name, 1) = "1" then' if it is a table name starting with 0 or 1, because the production schedule name starts with 0 or 1 'Msgbox (production schedule. Name) Row number = 4'production data in the production schedule starts from the fourth row The number of columns = 14' 14th indicates the planned date of delivery. Determine whether the data is required from the planned delivery date Dim temporary row number as integer = 4 '. Calculate the row number of the last row in the worksheet from the fourth row. While production schedule. cells (temporary row number, column number). value <> nothing 'Msgbox (production schedule. cells (row number, column number). Value, row number) Temporary row number + = 1 End while Temporary row number-= 1 'to get the row number of the last data row in the production schedule 'Msgbox (temporary row number) For row number = 4 to temporary row number 'production plan standard format row number starting from 4, to the last row in the worksheet If (cdate (datetimepicker1.value. tow.datestring) <= cdate (production schedule. cells (row number, column number ). value) and (cdate (datetimepicker2.value. tow.datestring)> = cdate (production schedule. cells (row number, column number ). value) then' if the date is within the specified range 'Distribution of stoves in various cities Dim City as string = production schedule. cells (row number, 3). value' column 3rd is the city name Dim Number of projects as decimal = ctype (production schedule. cells (row number, 7). Value, decimal) 'column 7th is the number of projects Dim completions as decimal = ctype (production schedule. cells (row number, 11). Value, decimal) 'column 11th is the actual completions Dim unfinished count as decimal = 0' decimal is used because decimal is followed Number of if completions <Number of projects then' if not completed Number of unfinished projects = number of projects-number of completed projects End if If city <> "" then If instr (city, "Shenyang") <> 0 or instr (city, "Anshan") <> 0 or instr (city, "Harbin ") <> 0 or instr (city, "Huludao") <> 0 then If Office Planning Count statistics. Contains ("Shenyang") then Office plan count statistics. Item ("Shenyang") + = plan count Else Office plan count statistics. Add ("Shenyang", plan count) End if If office completion count statistics. Contains ("Shenyang") then Office completion count statistics. Item ("Shenyang") + = incomplete count Else Office completion count statistics. Add ("Shenyang", incomplete count) End if Else If Office Planning Count statistics. Contains (city) then Office plan count statistics. Item (city) + = plan count Else Office Planning Count statistics. Add (city, Planning Count) End if If office completion count statistics. Contains (city) then Office completions count. Item (city) + = incomplete count Else Office completion count statistics. Add (city, incomplete count) End if End if Else Msgbox (production schedule. Name & "no city name", msgboxstyle. Critical, "city name in Production Plan ") Excelapp. Visible = true Production Schedule. Activate () Production Schedule. Select () End if 'The distribution of the stove model is calculated below Dim model as string = strings. Left (production schedule. cells (row number, 2). Value, 3) 'cooker model in column 3rd If Model Planning Count statistics. Contains (model) then Model plan count statistics. Item (model) + = plan count Else Model scheduler count statistics. Add (model, scheduler count) End if If Model completion count statistics. Contains (model) then Model completion count statistics. Item (model) + = incomplete count Else Model completion count statistics. Add (model, incomplete count) End if End if Next End if Next Dim city count as integer = Office Planning Count count. Count Dim city count clone as integer = Office Planning Count count. Count Row number = 4 'Sort by number of projects Dim office plan count statistical copy as new sortedlist Dim Asa as decimal = 0.001 'is added to prevent duplicate key values. optional decimal places are not displayed in the worksheet. Dim office plan count Enum as idictionaryenumerator = Office plan count statistics. getenumerator While office planning enum. movenext Copy of Office Planning Count statistics. Add (office planning quantity enum. Value + ASA, office planning quantity enum. Key) Asa + = 0.001 End while 'Use sortedlist's Automatic Sorting function here Dim I as integer = 1 For column number = 3 to 3 + number of cities-1 'fill in data from column 3, which is a pre-defined format Statistical table. cells (row number, column number). value = Office Planning Count statistical copy. getbyindex (city number clone-I) 'city Statistical table. cells (row number + 1, column number). value = number of office projects statistical copy. getkey (number of cities clone-I) 'Number of projects Statistical table. cells (row number + 2, column number). value = Office completion count statistics. Item (Copy of Office Planning Count statistics. getbyindex (city count clone-I) 'Number of projects I + = 1 Next 'Order by number of model plans Dim model plan count statistical copy as new sortedlist Office Number of projects Enum = model number of projects statistics. getenumerator While office planning enum. movenext Model plan count statistical copy. Add (office plan count enum. Value + ASA, office plan count enum. Key) Asa + = 0.001 'Msgbox (number of office projects enum. Value + ASA, number of office projects enum. Key) End while Dim model count as integer = model plan count. Count Dim model count clone as integer = model plan count. Count 'Msgbox (Model quantity) I = 1 Row number = 9 For column number = 3 to 3 + model number-1 Statistical table. cells (row number, column number). value = model plan count statistical copy. getbyindex (model number clone-I) 'cooker Model Statistical table. cells (row number + 1, column number). value = model plan count statistical copy. getkey (model number clone-I) 'Number of projects Statistical table. cells (row number + 2, column number). value = model completion count statistics. Item (model plan count statistical copy. getbyindex (model count clone-I )) I + = 1 Next 'Dim WSF as Excel. worksheetfunction = excelapp. worksheetfunction Excelapp. Visible = true' show workbook Statistical table. Select () 'select Worksheet Statistical table. Activate () 'activation Worksheet Catch ex as exception 'catch an error Office plan count statistics = nothing Office completion count statistics = nothing Model plan count statistics = nothing Model completion COUNT = nothing Statistical table = nothing Jani 2004 cooker Production Plan = nothing Excelapp = nothing GC. Collect (0) Msgbox (ex. tostring, msgboxstyle. Critical, "error ") Finally Office plan count statistics = nothing Office completion count statistics = nothing Model plan count statistics = nothing Model completion COUNT = nothing Statistical table = nothing Jani 2004 cooker Production Plan = nothing Excelapp = nothing GC. Collect (0) End try Msgbox ("statistics are complete, please refer to", msgboxstyle. defaultbutton1, "cooker Production Plan Statistics") 'prompt if it is complete End sub

Now let's try it out. First, select the statistical start time and End Time, and click button1. The program will automatically count the planned quantity and unfinished quantity of each office and each product, is it very convenient.