C # Call an Excel template to generate an Excel file

Recently, I made a project for the company, requiring that the data in the database be automatically added to the relevant cell based on an Excel template (with images) every day, and requiring different products to generate different files, different sheets are generated for different models of the same product... the Excel files generated on the current day will also be automatically emailed to the specified guests...
Well, this is automation...
It took two weeks to complete the process... let's talk about the key processes and give some help to the same friends:
1.To be automated, you need to use the INI File
Join:
[Dllimport ("Kernel32")]
Private Static extern long writeprivateprofilestring (string section, string key, string Val, string filepath );
[Dllimport ("Kernel32")]
Private Static extern int getprivateprofilestring (string section, string key, string def, stringbuilder retval, int size, string filepath );

In addition, you need to add timer1. When the program starts, read the INI file to determine whether automation is required. The latency of timer1 must also be set in the INI file:
Private void form1_load (Object sender, system. eventargs E)
{

Ini_read ("setup", 7 );
Ini_read ("parameter", 55 );
Ini_read ("email", 9 );
If (this. checkbox1.checked = true) // determines whether automatic execution is set in "setup ".
{
Timer1.interval = int. parse (this. textbox1.text. tostring ());
Timer1.start ();
}
}
Ini_read (string strname, int INTSUM) is my own ini read method.

2.Generate dataset according to the conditions...
3.Attached to a datagrid1
4.Create an Excel file
Get the product name and model name, and use dataview for implementation:
Dataview DV = new dataview ();
DV = (Dataset) datagrid1.datasource). Tables [0]. defaultview;

Call to generate an Excel file:
Public void gettemplate (string strfilename, dataview DV, int intdvparameters, int intdvparametere, int intsheettotal)
Gettemplate (strproductid, DV, (intend-intsheettotal), intend, intsheettotal + 1 );
To explain, strfilename is the name of a product and also the file name. intdvparameters is the index of the product in DV, and intdvparametere is the last index of the product in DV, intsheettotal is the total number of products in DV.

To add a template to gettemplate, copy the template to multiple sheet and save the file:
Call the template.xls file placed under the \ templatefolder,
Microsoft. Office. InterOP. Excel. Application objea;
Objea. workbooks. add (system. environment. currentdirectory. replace ("\", "\") + "\" + "template \ template.xls "));

Generate intsheettotal sheet ....

Copy the template to sheet.
Microsoft. Office. InterOP. Excel. worksheet objes;
Objes. Copy (objea. workbooks [1]. worksheets [1], missing. value );

Use an array to add values. Use for as a loop.
Microsoft. Office. InterOP. Excel. Range [] objer = new Microsoft. Office. InterOP. Excel. Range [30];
Objer [I] = objes. get_range (strpara [I], missing. value );
Objer [I]. value2 = DV [intdvparameters + k-1]. Row [strfield [I]. tostring ();
Strfield [I] is a field in the database set in ini
Strpara [I] is the position in the Excel Set in ini, such as strfield [0] = "productid", strpara [0] = "c22"
...
String strname = strfilename;
String strfolder = This. textbox2.text + "\" + strname + ". xls"; // textbox2 is the folder path
This. listbox1.items. Add (strfolder); // Add the Excel file as an email attachment to the Attachment List for processing.
Objea. workbooks [1]. savecopyas (strfolder );

All right, after adding the loop, save it. the Excel file of the product name is complete, and there are multiple sheet of different models, if multiple products are queried, you can call gettemplate () cyclically.

5.Others are simple
Send email
Using system. Web. mail;
Read the listbox1 attachment.
Folder operation
String strpath;
Strpath = This. textbox2. text;
System. Io. Directory. Delete (strpath, true );
Directory. createdirectory (strpath );
Hey, delete all

Grace, done