About C # excel, one of the comprehensive applications for generating charts (using Microsoft. Office. InterOP. Excel)

Recently, I am developing a program to use C # to generate an Excel document. The corresponding chart should be generated based on the data. This chart has strict requirements on color and format, I searched Baidu and Google for all the relevant information, but only some of them were introduced. The specific format was not described. After my constant practice and exploration, I finally completed this arduous task.

There are two ways to achieve this: using the owc11 component and using Excel!

With the help of owc11, it is suitable for generating a graphic file, which cannot be edited in the file. Excel is more suitable for adding charts directly to the file using the data in the Excel file for later editing! I tried both of them. Because I am more suitable for using the second one, I have developed more well. The source code is published here for your reference!

Developer: gailzhao

Email: gailzhao@sohu.com

Note: In the window, add a button named BTN. I only write the code section (my data contains two types of data corresponding to the date, as shown in the following table)

Generate the following data (partial) in the Excel document, and generate charts based on the data on the right of the data

Fund net worth index chart

Date	Open Fund	Partial stock fund
2008-1-2	4236.9944	5158.0456
	4246.8861	5172.5288
2008-1-4	4277.9334	5214.2867
	4325.5252	5276.8432
2008-1-8	4306.3272	5252.3962

 

Add the following reference:
Using system. IO;
Using system. runtime. interopservices;
Using Excel = Microsoft. Office. InterOP. Excel;

 

Private void btn_click (Object sender, eventargs E)
{

// Create an Excel document

Createexcel ("title", "document. xls", "worksheet name ");

}

Private void createexcel (String title, string filename, string sheetnames)
{
// Name of the file to be generated
String filename = filename;
String filepath = strcurrentpath + filename;

Fileinfo Fi = new fileinfo (filepath );
If (Fi. exists) // checks whether the object already exists. If so, delete it!
{
Fi. Delete ();
}
If (sheetnames! = NULL & sheetnames! = "")
{
Excel. Application m_excel = new excel. Application (); // create an Excel Object (start the Excel. EXE process at the same time)
M_excel.sheetsinnewworkbook = 1; // Number of worksheets
Excel. _ workbook m_book = (Excel. _ workbook) (m_excel.workbooks.add (missing. Value); // Add a new workbook
Excel. _ worksheet m_sheet;

# Region Processing

Dataset DS = scdata. listdata ("Exec vote_2008.dbo.p_voteresult_update" + Int. parse (fdate ));
If (Ds. Tables. Count <= 0)
{
MessageBox. Show ("no latest data! ");
Return;
}
Datatabletosheet (title, DS. Tables [0], m_sheet, m_book, 0 );
# Endregion

 

# Region Save the Excel file and clear the process
M_book.saveas (filepath, missing. value, missing. value, missing. value, missing. value, missing. value, Excel. xlsaveasaccessmode. xlnochange, missing. value, missing. value, missing. value, missing. value, missing. value );
// M_excel.activeworkbook. _ saveas (filepath, Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );
M_book.close (false, missing. Value, missing. value );
M_excel.quit ();
System. runtime. interopservices. Marshal. releasecomobject (m_book );
System. runtime. interopservices. Marshal. releasecomobject (m_excel );

M_book = NULL;
M_sheet = NULL;
M_excel = NULL;
GC. Collect ();
// This. Close (); // close the form

# Endregion
}
}

# Region writes the data in the datatable to the specified sheet in Excel.
/// <Summary>
/// Write the data in the datatable to the specified sheet in Excel
/// </Summary>
/// <Param name = "DT"> </param>
/// <Param name = "m_sheet"> </param>
Public void datatabletosheet (String title, datatable DT, Excel. _ worksheet m_sheet,
Excel. _ workbook m_book, int startrow)
{

// Fill in the Excel Data
Excel. Range = m_sheet.get_range (m_sheet.cells [1, 1], m_sheet.cells [1, 2]);
Range. mergecells = true; // merge cells.
Range. Font. Bold = true; // characters in the bold Cell
// Write question
M_sheet.cells [startrow, startrow] = title;
Int rownum = DT. Rows. Count; // number of rows
Int columnnum = DT. Columns. Count; // Number of Columns
Int num = rownum + 2; // obtain the maximum number of rows in the data.

// Write the column title
For (Int J = 0; j <columnnum; j ++)
{
Int bt_startrow = startrow + 1;

// Write the field name to the document
M_sheet.cells [bt_startrow, 1 + J] = DT. Columns [J]. columnname;

// Cell background color
M_sheet.get_range (m_sheet.cells [bt_startrow, 1 + J], m_sheet.cells [bt_startrow, 1 + J]). Interior. colorindex = 15 ;}

// Write data row by row
For (INT I = 0; I <rownum; I ++)
{
For (Int J = 0; j <columnnum; j ++)
{
M_sheet.cells [startrow + 2 + I, 1 + J] = DT. Rows [I] [J]. tostring ();
}
}
M_sheet.columns.autofit ();

// Generate a chart based on the data in the current Worksheet

Createchart (m_book, m_sheet, num );
}

Private void createchart (Excel. _ workbook m_book, Excel. _ worksheet m_sheet, int num)
{
Excel. Range oresizerange;
Excel. Series oseries;

M_book.charts.add (missing. Value, missing. Value, 1, missing. value );
M_book.activechart.charttype = excel. xlcharttype. xlline; // set the image

// Set the data value range
M_book.activechart.setsourcedata (m_sheet.get_range ("A2", "C" + num. tostring (), Excel. xlrowcol. xlcolumns );
// M_book.activechart.location (Excel. xlchartlocation. xllocationautomatic, title );
// Place the chart in the specified position
M_book.activechart.location (Excel. xlchartlocation. xllocationasobject, m_sheet.name );
Oresizerange = (Excel. Range) m_sheet.rows.get_item (10, missing. value );
M_sheet.shapes.item ("Chart 1"). Top = (float) (double) oresizerange. Top; // adjust the top margin of the chart position
Oresizerange = (Excel. Range) m_sheet.columns.get_item (6, missing. Value); // adjust the left margin of the chart position
// M_sheet.shapes.item ("Chart 1"). Left = (float) (double) oresizerange. Left;
M_sheet.shapes.item ("Chart 1"). width = 400; // adjust the chart width.
M_sheet.shapes.item ("Chart 1"). Height = 250; // adjust the height of the chart

M_book.activechart.plotarea.interior.colorindex = 19; // set the background color of the drawing area.
M_book.activechart.plotarea.border.linestyle = excel. xllinestyle. xllinestylenone; // set the border line of the drawing area.
M_book.activechart.plotarea.width = 400; // set the width of the drawing area.
// M_book.activechart.chartarea.interior.colorindex = 10; // set the background color of the entire chart
// M_book.activechart.chartarea.border.colorindex = 8; // set the border color of the entire chart.
M_book.activechart.chartarea.border.linestyle = excel. xllinestyle. xllinestylenone; // set the border line
M_book.activechart.hasdatatable = false;

// Set the location and format of the legend
M_book.activechart.legend.top = 20.00; // you can specify the legend top margin.
M_book.activechart.legend.left = 60.00; // you can specify the left margin of the legend.
M_book.activechart.legend.interior.colorindex = excel. xlcolorindex. xlcolorindexnone;
M_book.activechart.legend.width = 150;
M_book.activechart.legend.font.size = 9.5;
// M_book.activechart.legend.font.bold = true;
M_book.activechart.legend.font.name = "";
// M_book.activechart.legend.position = excel. xllegendposition. xllegendpositiontop; // you can specify the legend position.
M_book.activechart.legend.border.linestyle = excel. xllinestyle. xllinestylenone; // set the legend border line.

 

// Set the X axis display
Excel. Axis xaxis = (Excel. axis) m_book.activechart.axes (Excel. xlaxistype. xlvalue, Excel. xlaxisgroup. xlprimary );
Xaxis. majorgridlines. Border. linestyle = excel. xllinestyle. xldot;
Xaxis. majorgridlines. Border. colorindex = 1; // the color of the gridline horizontal line
Xaxis. hastitle = false;
Xaxis. minimumscale = 1500;
Xaxis. maximumscale = 6000;
Xaxis. ticklabels. Font. Name = "";
Xaxis. ticklabels. Font. size = 9;

 

// Set the Y axis display
Excel. Axis yaxis = (Excel. axis) m_book.activechart.axes (Excel. xlaxistype. xlcategory, Excel. xlaxisgroup. xlprimary );
Yaxis. ticklabelspacing = 30;
Yaxis. ticklabels. numberformat = "m month D ";
Yaxis. ticklabels. Orientation = excel. xlticklabelorientation. xlticklabelorientationhorizontal; // the orientation of the Y axis, horizontal or vertical
Yaxis. ticklabels. Font. size = 8;
Yaxis. ticklabels. Font. Name = "";

// M_book.activechart.floor.interior.colorindex = 8;
/***** Set the title *****
M_book.activechart.hastitle = true;
M_book.activechart.charttitle.text = "net worth Index ";
M_book.activechart.charttitle.shadow = true;
M_book.activechart.charttitle.border.linestyle = excel. xllinestyle. xlcontinuous;
*/

Oseries = (Excel. Series) m_book.activechart.seriescollection (1 );
Oseries. Border. colorindex = 45;
Oseries. Border. Weight = excel. xlborderweight. xlthick;
Oseries = (Excel. Series) m_book.activechart.seriescollection (2 );
Oseries. Border. colorindex = 9;
Oseries. Border. Weight = excel. xlborderweight. xlthick;

}