Use Excel for advanced reports in C #

The following is an advanced report design. The principle of this method is: first open the Excel file, arrange the version as required, and save it as a file as a template, then copy the file to a new file in C # and fill in the data at the specified position. To add a table line, we recorded a macro, execute this macro in C.

Using system;

Using system. drawing;

Using system. collections;

Using system. componentmodel;

Using system. Windows. forms;

Using system. Data;

 

Using system. IO;

Using system. reflection;

 

Namespace myexcel

{

/// <Summary>

/// Summary of form1.

/// </Summary>

Public class form1: system. Windows. Forms. Form

{

Private system. Windows. Forms. Button btnnormal;

Private system. Windows. Forms. Button btnadvace;

/// <Summary>

/// Required designer variables.

/// </Summary>

Private system. componentmodel. Container components = NULL;

 

Public form1 ()

{

//

// Required for Windows Form Designer support

//

Initializecomponent ();

 

//

// Todo: add Any constructor after initializecomponent callsCode

//

}

 

/// <Summary>

/// Clear all resources in use.

/// </Summary>

Protected override void dispose (bool disposing)

{

If (disposing)

{

If (components! = NULL)

{

Components. Dispose ();

}

}

Base. Dispose (disposing );

}

 

# Region windows Form Designer generated code

/// <Summary>

/// The designer supports the required methods-do not use the code editor to modify

/// Content of this method.

/// </Summary>

Private void initializecomponent ()

{

This. btnnormal = new system. Windows. Forms. Button ();

This. btnadvace = new system. Windows. Forms. Button ();

This. suspendlayout ();

//

// Btnnormal

//

This. btnnormal. Location = new system. Drawing. Point (49, 55 );

This. btnnormal. Name = "btnnormal ";

This. btnnormal. tabindex = 0;

This. btnnormal. Text = "normal Report ";

This. btnnormal. Click + = new system. eventhandler (this. btnnormal_click );

//

// Btnadvace

//

This. btnadvace. Location = new system. Drawing. Point (169, 55 );

This. btnadvace. Name = "btnadvace ";

This. btnadvace. tabindex = 1;

This. btnadvace. Text = "Advanced Report ";

This. btnadvace. Click + = new system. eventhandler (this. btnadvace_click );

//

// Form1

//

This. autoscalebasesize = new system. Drawing. Size (6, 14 );

This. clientsize = new system. Drawing. Size (292,133 );

This. Controls. addrange (new system. Windows. Forms. Control [] {

This. btnadvace,

This. btnnormal });

This. Name = "form1 ";

This. startposition = system. Windows. Forms. formstartposition. centerscreen;

This. Text = "form1 ";

This. resumelayout (false );

 

}

# Endregion

 

/// <Summary>

/// ApplicationProgram.

/// </Summary>

[Stathread]

Static void main ()

{

Application. Run (New form1 ());

}

 

Private string [,] mydata =

{

{"License plate number", "type", "product brand", "Type Number", "color", "Additional license number", "vehicle identification number "},

{"Jajajs", "dangerous goods", "truck", "Tie Feng szg9220yy", "White", "323232", "323232 "},

};

 

// Common report, that is, simple File Export Function

Private void btnnormal_click (Object sender, system. eventargs E)

{

// Create an Excel file

Excel. Application myexcel = new excel. Application ();

Myexcel. application. workbooks. Add (true );

// Make the Excel file visible

Myexcel. Visible = true;

// Name of the first behavior report

Myexcel. cells [] = "normal Report ";

// Write data row by row,

For (INT I = 0; I <11; I ++)

{

For (Int J = 0; j <7; j ++)

{

// Start with a single quotation mark, indicating that the cell is plain text

Myexcel. cells [2 + I, 1 + J] = "'" + mydata [I, j];

}

}

 

}

// Advanced Report, which is generated based on the template

Private void btnadvace_click (Object sender, system. eventargs E)

{

String filename = "";

// Copy the template file to a new file

Savefiledialog mysave = new savefiledialog ();

Mysave. Filter = "Excel file (*. xls) | *. xls | all files (*. *) | *.*";

If (mysave. showdialog ()! = Dialogresult. OK)

{

Return;

}

Else

{

Filename = mysave. filename;

// Copy the template file to the new location. We recommend that you use the relative path during actual development, such as application. startuppath. Trim () + "\ report \ normal.xls"

String filenameold = mysave. filename;

Fileinfo mode = new fileinfo ("D: \ normal.xls ");

Try

{

Mode. copyto (filename, true );

}

Catch (exception ee)

{

MessageBox. Show (EE. Message );

Return;

}

 

}

 

// Open the copied File

Object missing = missing. value;

Excel. Application myexcel = new excel. Application ();

// Open a new file

Myexcel. application. workbooks. Open (filename,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing,

Missing );

// Display the Excel file

Myexcel. Visible = true;

 

// Write data row by row. The first row in the array is the column title. Ignore

For (INT I = 1; I <11; I ++)

{

For (Int J = 0; j <7; j ++)

{

// Start with a single quotation mark, indicating that the cell is plain text

Myexcel. cells [4 + I, 1 + J] = "'" + mydata [I, j];

}

}

 

// Select the column title and actual content

Excel. Workbook mybook = myexcel. workbooks [1];

Excel. worksheet mysheet = (Excel. worksheet) mybook. worksheets [1];

Excel. Range r = mysheet. get_range (mysheet. cells [], mysheet. cells []);

R. Select ();

// ====== Add a border to the table by executing the macro //

Try

{

Myexcel. Run ("macro 1", missing, missing,

 

Missing, missing,

 

Missing, missing,

 

Missing, missing,

 

Missing, missing, and missing );

}

Catch

{

}

// Save the modification

Mybook. Save ();

}

} // End of Form

}

In the above Code, we specify the selected range:

Excel. Range r = mysheet. get_range (mysheet. cells [], mysheet. cells []);

During specific development, we can calculate the range based on the actual data in the database. Our column title is from. cells [3, 1] is fixed in the program. For flexible use, we can fill in some basic information in cells [1, 1] of the template or other cells, for example, the actual starting position of the Data. During the operation, the data is read from the cell, and the content of the cell is replaced with the required content. Another problem is that when we write content to a cell, we assume that the content of a column should be put. For the sake of flexibility, after we get the start position of the column title, read the content of the Cell (that is, the field of the column), and find the corresponding field from the database to fill the column. This ensures that the filled content corresponds to the designed report, you can also ignore useless fields in the database, that is, the same database table can have many types of reports, as long as there is a corresponding template, the code for reading the content of a cell is as follows:

Excel. Range R;

R = mysheet. get_range (mysheet. cells [], mysheet. cells []); // obtain the region where the value is stored.

String strvalue = R. value. tostring ();

Only one cell can be read at a time. Otherwise, no corresponding data is obtained, that is, = mysheet. get_range (mysheet. cells [2, 1], mysheet. in cells [2, 1]), both parameters must be in the same cell. In this example, mysheet is used. cells [2, 1].