VB.NET Combined with Excel Statistical Production Report - Ernuts Sea Development Tools Discussion on Enterprise Informatization: Microsoft Visual Studio .NET 2003 Operating System: Windows XP
The reason why Excel to do business production reports Excel form generation and formula setting is very powerful and convenient, is a powerful information analysis and processing tool. In particular, Excel's formula, function, VBA language, is extremely powerful. I tried other spreadsheet software, and I have no comparability at all of the EXCEL. I have been convinced for Excel's function, special collection of e-books - "Excel Application Collection", put it online for everyone to download learning. Want to help yourself, first help people, this is also my consistent study philosophy. Visual Studio .NET is also Microsoft's product, Visual Studio .NET calls Excel to do business reports. Certificate I chose Visual Studio .NET as the preferred development tool is correct.
Software concept
Software concept is this: first in Excel is famous as a "Statistics Table", set a variety of formats in the sample table, fill in the fixed item. Put three controls on the form, two DateTimePicker controls to select Start Statistics and End Statistics. A Button is launched. The software wants to implement is: Click on Button1 to automatically find the production plan worksheet that matches the date-in-date range, and then use SortedList to count the number of plans and unfinished quantities, and the number of plans of each product model and unpleained quantity. Write the SortedList's data to "Statistics Table". It should be noted here that each production report format must be unified because the program is read according to the fixed cell position.
SortedList class
In addition to the basic knowledge of VB.NET calls Excel, this example mainly uses the sortedList class. The SortedList class represents the collection of key / value pairs, these keys and value buttons, and can be accessed according to keys and indexes. SORTEDLIST is a mix of Hashtable and Array. When using the Item Indexer Property, the behavior is similar to the HashTable when using the Item Indexer Property. When you use GetByIndex or SetByIndex to access the element according to the index of the element, its behavior is similar to array. SortedList maintains two arrays internally to store the array to the list; that is, an array is used for keys, and another array is used to associate values. Each element is a key / value pair that can be accessed as a DictionaryEntry object. The key cannot be empty (Nothing in Visual Basic), but the value can be. SortedList's capacity is the number of elements that can have. With an element to sortedList, the capacity is automatically increased by redistribution. Capacity attributes can be reduced by calling trimtosize or by explicitly setting the capacity. SortedList's elements will be sorted in accordance with a specific IComparer implementation (specified when creating sortedlist) or by the iComparable implementation provided by the key itself and based on the key. No matter which case, sortedList does not allow repeat keys. The index order is based on the sort order. When an element is added, the element will insert sortedList in the correct order sequence, and the index will adjust accordingly. If the element is moved, the index will also be adjusted accordingly. Therefore, when adding or removing elements in sortedList, the index of a specific key / value pair may change. Since sorting, the operation is slower than the operation on the HashTable on the HashTable. However, sortedList allows access to values to values via the associated keys or via the indexing to provide greater flexibility. The index in this collection is from zero. [Visual Basic, C #] The Foreach statement in the C # language (for veaching in Visual Basic) requires the type of each element in the collection. Since each element of sortedList is a key / value pair, the element type is neither a type of key, nor is the type of value. It is DictionaryEntry type. For example: DIM MYDE AS DICTIONARYENTRY for Each Myde In mysortedList ... Next MyDevb.net combined with Excel statistics production report
The following is a code for reference. In order to facilitate beginners, some are noted.
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 () Call Killexcel () Dim Excelapp As New Excel.Application Dimi 2004 Cooker Production Plan AS EXCEL.WORKBOOK DIM LAN RV line number AS integer = 1 DIM number AS INTEGER = 1 DIM office plan number statistics AS New SortedList DIM Office Completion Statistics AS New Sortedlist Dim Model Count AS New Sortedlist Dim Model Complete Count AS New Sortedlist DIM Statistics Table AS Excel.Worksheet 'Tryi 2004 Cooker Production Plan = Excelapp.workBooks.open (" E: / MY Documents / Production Plan / Jiai 2004 Cooker Production Plan. ×ls ") Statistics Table = CType (Jialini 2004 Cooker Production Plan. Worksheets (" Statistics Table "), Excel.worksheet Statistics Table. Cells (1, 3) .Value = datetimepicker1.value.toshortdateString 'Statistics Start Date Statistics Table. Cells (1, 7) .Value = DateTimePicker2.Value.toshortDateString' Statistical End Date Statistics Table. Range ("C4: Z6"). Value = " "'First empty statistics table in the original copy of the statistics. Range (" C9: Z11 "). Value =" "' DIM Production Plan AS Excel.Worksheet For Each Production Planning IN NewsHeet 2004 Cooker Production Plan. Worksheets' Traversing the production plan, if strings.left (production plan .Name, 1) = "0" or strings.left (production plan .Name, 1) = "1" THEN 'If it is a table name of 0 or 1, Due to the production plan name is 0 or 1 start 'MSGBOX (Production Plan .Name) line number = 4' Production Planning Table Production data from the fourth line start number = 14 'No. 14 is a planned date.
Judging from the scheduled date to determine whether the data DIM Temporary line number as integer = 4 'finds the line number in the final line in the worksheet, starting from the fourth line to plan the While Production Plan. Cells (Timatics, List) .Value <> Nothing 'msgbox (production plan. Cells (line number, list number) .value,, line number, temporary line number = 1 End while temporary line number - = 1' Getting the production plan The last data line is located 'MSGBOX (Temporary line number) FOR line number = 4 TO Temporary line number' Production plan Standard format line number starts from 4, to the last line of IF in the worksheet (CDATETIMEPICKER1.VALUE.TOSHDDATESTRING <= Cdate (production plan. Cells (line number, list number) .value)) and (cdate (datetimepicker2.value.toshortdateString)> = CDATE (production plan. Cells (line number, list number) .value) If the date is in the specified range 'cooker all cities distribution DIM city AS string = production plan. Cells (line number, 3) .Value' No. 4 is the city name DIM plan number as decimal = ctype (production plan Table. Cells (line number, 7) .value, decimal) 'Leads 7 is planned DIM completion number as decimal = ctype (production plan. Cells (line number, 11) .Value, decimal)' 11th column Actual completed DIM unfinished number as decimal = 0 'Use decimal because the number of plus number IF is completed
Planned ELSE Office Planning Statistics. ADD (City, Scheme) End IF IF Office Completion Statistics. THENS (City) The Office completed count statistics .Item (City) = Unplexed number ELSE Office completed Statistics .Add (City, Unplexed number) end if End if else msgbox (Production Plan .Name & "No City Name", MsgboxStyle.critical, "There is a city name in the production plan") ExceLapp.visible = True Production Plan Table .activate () Production Plan .Select () end if 'Calculation Cooker Model Distribution DIM Model AS String = Strings.Left (Production Plan. Cells (Line Number, 2) .Value, 3)' Citting Model Level 3 IF Model Plan Count statistics. THEM (Model) THEN model plan statistics .item (model) = Plan number ELSE model count statistics. ADD (model, plan) END IF IF Model completion count statistics .Contains Model) THEN model completion count statistics .item (model) = unplexed number ELSE model completion number statistics .Add (model, unplexed number) end if end if next end if Next Dim City AS integer = Office plan statistics .Ct Dim City Clone as INTEGER = Office plan statistics. COUNT line number = 4 'Sort by plan size Sort DIM Office Plan count statistics copy AS New Sortedlist Dim Asa As Decimal = 0.001' plus this number to prevent Repeat of key value, optional number of decimal DIM offices in the worksheet Enum as idictionaryenume Rator = Office plan statistics. Getenumerator While Planning Enum.Movenext Office Plan count statistics copy. ADD (office planning Enum.Value ASA, office plan enum.key) ASA = 0.001 End while 'This uses sortedList automatic sorting function DIM I as integer = 1 for color number = 3 to 3 city number - 1' From the third column to fill in data, this is a predefined format statistics table. Cells (line number, column No.) .value = Office plan statistics copy. GetByindex (City Clone - I) 'City Statistics Table. Cells (Line Number 1, List) .Value = Office Planner Statistics Copy .getKey (City Clone " - i) 'Scheme statistics table. Cells (line number 2, list) .Value = Office completion count statistics .Item (office plan statistics copy. Getbyindex (city number clone - i))' Plan number i = 1 Next 'How much model number is sorted by DIM Model Plan count statistics copy AS New SortedList office plan number ENUM =
Model plan statistics. Getenumerator While Plan number ENUM.MOVENEXT model plan count statistics copy. ADD (office plan ENUM.VALUE ASA, office plan enum.key) ASA = 0.001 'msgbox (office plan Number of Enum.Value ASA, Office Program ENUM.KEY) End While DIM Model AS Integer = Model Plan Count Statistics.Count Dim Model CLONE AS INTEGER = Model Plan Count Statistics.Count 'MSGBOX (Model Number) I = 1 line number = 9 FOR number = 3 to 3 model number - 1 statistics table. Cells (line number, list number) .Value = model plan count statistics copy .getbyindex (model number clone - i) 'Cooker model statistics Table. Cells (line number 1, list number) .Value = model plan number statistics copy. Getkey (Model number clone - i) 'Scheme statistics table. Cells (line number 2, list number) .value = model completion Count statistics .item (model plan statistics copy. GetByindex (model number clone - i)) i = 1 Next 'DIM WSF AS Excel.WorksheetFunction = ExceLapp.WorksheetFunction ExceLapp.visible = true' Display Workbook Statistics Table .Select ( ) 'Select Worksheet Statistics Table .activate ()' Activation Worksheet Catch EX AS Exception 'Capture Error Office Planning Country Statistics = Nothing Office Completion Country = Nothing Model Scrap Country = Nothing Statistics Statistics = Nothing Statistics Table = Nothing Jiai 2004 Cooker Production Plan = Nothing ExceLapp = Nothing gc.collect (0) msgbox (ex.totring, msgboxstyle.critical, "Error") Finally office plan statistics = Nothi NG office completion statistics = Nothing model count statistics = Nothing model completion statistics = Nothing Statistics Table = Nothing Jiali 2004 Cooker Production Plan = Nothing Excelapp = Nothing gc.collect (0) End Try Msgbox ("has been counted , Please, "Cooker Production Plan Statistics") 'If the completion is prompted to make us trial, first select the start statistics time and end the statistics, click Button1, and the program will automatically count all the do things. It is not convenient to discharge the number of plans and unfinished numbers of each product.
My QQ: 26624998 My website: http://sunhai.tianyablog.com May 24, 2004
