// Original template file String pFromPath = System. AppDomain. CurrentDomain. BaseDirectory + @ "Template \ motor factory test table .xls "; // Target file String pToPath = System. AppDomain. CurrentDomain. BaseDirectory + @ "PrintRecords \" + "product warehouse receiving table" + DateTime. Now. ToString ("yyyyMMddmmss") + ". xls "; # Region Save the warehouse picking list in Excel Try { // Copy the template file and rename it in time format
System. IO. File. Copy (pFromPath, pToPath, true ); Excel. Workbooks objBooks; Excel. Sheets objSheets; Excel. _ Worksheet objSheet; Excel. Range range; If (objApp = null) { ObjApp = new Excel. Application (); } // Open the copied template file to obtain the WorkBook object. ObjBooks = objApp. Workbooks; ObjBook = objBooks. open (pToPath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing ); // Obtain the WorkSheet object ObjSheets = objBook. Worksheets; ObjSheet = (Excel. _ Worksheet) objSheets. get_Item (1 ); // Set the table name ObjSheet. Name = DateTime. Now. ToString ("yyyy-MM-dd") + "motor factory test record table "; # Region write data // Store the data read from the database System. Data. DataTable excelDataTabel; // Read standard parameters DataAcc. selectString = "SELECT motor model, rated torque, rated voltage, rated current, rated speed FROM motor standard parameter table WHERE motor model = '" + cboType. text. trim () + "'"; ExcelDataTabel = dataAcc. GetDataTable (); // Write Model ObjSheet. Cells [2, 2] = "'" + cboType. Text. Trim (); // String strType, strEDDY, strEDZJ, strEDDL, strEDZS; // Write the header information If (excelDataTabel. Rows. Count> 0) { ObjSheet. Cells [2, 6] = "'" + excelDataTabel. Rows [0] ["Rated voltage"]. ToString (); ObjSheet. Cells [2, 9] = "'" + excelDataTabel. Rows [0] ["rated torque"]. ToString (); ObjSheet. Cells [3, 6] = "'" + excelDataTabel. Rows [0] ["Rated current"]. ToString (); ObjSheet. Cells [3, 9] = "'" + excelDataTabel. Rows [0] ["Rated speed"]. ToString (); } // Temporary storage motor number String [] strNoTemp = strNo. Split (',');
// Read test records StrNo = "Motor No. = '" + strNo. Replace (",", "' OR motor No. = '") + "'"; DataAcc. selectString = "SELECT motor No., current of the Forward motor, current of the reverse motor, speed of the Forward motor, speed of the reverse motor, threshold voltage, insulation resistance FROM motor electrical characteristics table WHERE motor model = '"+ cboType. text. trim () + "'AND (" + strNo + ") AND test type = 'accept '"; ExcelDataTabel = dataAcc. GetDataTable (); // Start line Int top = 6; // Start Column Int left = 1; // Set the title line // Print the title line on each page ObjSheet. PageSetup. PrintTitleRows = "$1: $" + Convert. ToString (top-1 ); // Set the footer ObjSheet. PageSetup. LeftFooter = "experimenter:" + frmMain. userInfo [0] ["name"]. ToString (); // left footer ObjSheet. PageSetup. CenterFooter = @ "Page & P/total & N"; // The footer ObjSheet. PageSetup. RightFooter = "Date:" + DateTime. Now. Date. ToString ("yyyy-MM-dd"); // right footer Int rows = 0; // records the number of rows written. If (excelDataTabel. Rows. Count <1) { // If no motor test record exists, write the motor number If (strNoTemp. Length> 0) { For (int I = 0; I <strNoTemp. Length; I ++) { ObjSheet. Cells [top + I, left] = "'" + Convert. ToString (I + 1); // serial number ObjSheet. Cells [top + I, left + 1] = "'" + strNoTemp [I]; // motor number Rows ++;
// Display progress ProgressBarToExcel. Value = Convert. ToInt32 (I/Convert. ToDouble (strNoTemp. Length) * 100 ); } } } Else { // Write test records cyclically For (int I = 0; I <excelDataTabel. Rows. Count; I ++) { ObjSheet. Cells [top + I, left] = "'" + Convert. ToString (I + 1 ); ObjSheet. Cells [top + I, left + 1] = "'" + excelDataTabel. Rows [I] ["Motor No."]. ToString (); ObjSheet. Cells [top + I, left + 2] = "'" + excelDataTabel. Rows [I] ["conversion current"]. ToString (); ObjSheet. Cells [top + I, left + 3] = "'" + excelDataTabel. Rows [I] ["reverse motor current"]. ToString (); ObjSheet. Cells [top + I, left + 4] = "'" + excelDataTabel. Rows [I] ["Rotating motor speed"]. ToString (); ObjSheet. Cells [top + I, left + 5] = "'" + excelDataTabel. Rows [I] ["reverse motor speed"]. ToString (); ObjSheet. Cells [top + I, left + 6] = "'" + excelDataTabel. Rows [I] ["threshold voltage"]. ToString (); ObjSheet. Cells [top + I, left + 7] = "'" + excelDataTabel. Rows [I] ["insulation resistance"]. ToString (); Rows ++; // Display progress ProgressBarToExcel. Value = Convert. ToInt32 (I/Convert. ToDouble (excelDataTabel. Rows. Count) * 100 ); } } //// Set the border Range = objSheet. get_Range (objSheet. Cells [top, left], objSheet. Cells [top + rows-1, 9]); // Linear Range. Cells. Borders. LineStyle = Excel. XlLineStyle. xlContinuous; // Width Range. Cells. Borders. Weight = 2; // Row Height Range. RowHeight = 20; // Font size Range. Font. Size = 12; Range = null; // Save and close the worksheet ObjBook. Save (); ObjBook. Close (Type. Missing, Type. Missing, Type. Missing ); // Clear the object // Close the work thin ObjBooks. Close (); // Exit the application ObjApp. Quit (); ObjBook = null; ObjBooks = null; ObjSheet = null; ObjSheets = null; ObjApp = null;
GC. Collect ();
} Catch (Exception ex) { MessageBox. Show ("An error occurred while generating the Excel file. Make sure that the connection between MS Office Excel and the database is normal! \ N "+ ex. Message," Error ", MessageBoxButtons. OK, MessageBoxIcon. Error ); Return; } Finally { GC. Collect (); This. btnOutput. Enabled = true; This. Cursor = Cursors. Default; ProgressBarToExcel. Visible = false; ProgressBarToExcel. Value = 0; } # Endregion # Endregion |