C # Excel (title row, footer, Row Height, border ..)

Source: Internet
Author: User

 

C # Operate Excel (title row, footer, Row Height, border...) [convert

// 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.