C # import Excel

/* Note: During the test, the execution time is set to not the end of the month. In practice, the execution time must be changed to the end of the month. The change method is to change the = of 16 rows! = */
Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. oledb;
Using system. Windows. forms;
Using system. text;
Using system. IO;
Using system. reflection;
/* Import the telephone record and Information Publishing Record in SQL Server 2000 into the Excel file */
Public class introduceexcel: system. Windows. Forms. Form
{
Public introduceexcel ()
{
Datetime today = system. datetime. Today; // get the current time
If (today. Day = datetime. daysinmonth (today. Year, today. month ))
{// If it is not the end of the month, do not import
Return;
}
Else // otherwise, import
If (MessageBox. Show ("click" OK "to start importing, click" no "to manually import \ n", "last month phone records, information release records to import to excel Program Automatically enable ", messageboxbuttons. yesno, messageboxicon. Warning) = dialogresult. No)
{
Return;} // If
Else
{

Getconnect ("callrecord"); // open the data link and enable the import function
Getconnect ("pubinfrecord ");
Deleterecord ("callrecord"); // clear the data for this month
Deleterecord ("pubinfrecord ");
} // Else
}

/* Exel File Import function */
Private void getconnect (string origin)
{
Sqlconnection con = crecon (); // create an SQL 2000 database link
Con. open ();
String SQL = "select * from" + origin + "order by netid"; // query the database
Sqldataadapter SA = new sqldataadapter (SQL, con );
Dataset DS = new dataset ();
SA. Fill (DS, origin); // fill in data
Try
{
Excel. Application Excel = new excel. Application (); // enable Excel
Excel. application. workbooks. Add (true );
Excel. Sheets excelsheets = excel. worksheets; // create a new worksheet
Excel. cells [1, 1] = "netid (network number )";
Excel. cells [1, 2] = "memid (member number )";
Excel. cells [1, 3] = "curcallnum (current phone number )";
Excel. cells [1, 4] = "companyName (company name )";
Excel. cells [1, 5] = "upworker (agent )";
Excel. cells [1, 6] = "sumnumber (number of times this month )";
Object missing = missing. value;
Excel. Visible = true; // Excel file visible
Int rolength = Ds. Tables [0]. Rows. Count; // number of rows
Int I;
For (I = 0; I <rolength; I ++)
{
/* Retrieve telephone information from the database */
String netid = Ds. Tables [0]. Rows [I] [0]. tostring ();
String memid = Ds. Tables [0]. Rows [I] [1]. tostring ();
String callnumber = Ds. Tables [0]. Rows [I] [2]. tostring ();
String companyName = Ds. Tables [0]. Rows [I] [3]. tostring ();
String worker = Ds. Tables [0]. Rows [I] [4]. tostring ();
String number = Ds. Tables [0]. Rows [I] [5]. tostring ();
/* Fill in the worksheet in Excel */
Excel. cells [I + 1, 1] = netid;
Excel. cells [I + 1, 2] = memid;
Excel. cells [I + 1, 3] = callnumber;
Excel. cells [I + 1, 4] = companyName;
Excel. cells [I + 1, 5] = worker;
Excel. cells [I + 1, 6] = number;
} //
Con. Close (); // close this data link

/* In the "save file" dialog box, the file is saved in a file-defined class */

Savefiledialog = new savefiledialog ();
Savefiledialog. Filter = "Excel files (*. xls) | *. xls | all files (*. *) | *.*";
If (origin = "callrecord ")
{
Savefiledialog. Title = "Import phone data to excel ";
}
Else savefiledialog. Title = "import data to excel at the end of the month ";

Savefiledialog. filterindex = 1;
Savefiledialog. restoredirectory = true;
If (savefiledialog. showdialog () = dialogresult. OK)
{

String fname = savefiledialog. filename;
File fsaveas = new file (fname );
Fsaveas. writefile (fname );

}// if ()
excel. workbooks. close ();
excel. quit (); // close the EXCEL program
}// try
catch (system. exception e)
{

system. console. writeline ("something wrong happened about Excel excution or dababase operation", e );
}< br>
}// connect
/* clear the content of the current table */
private void deleterecord (string record)
{< br> sqlconnection connection = crecon ();
connection. open ();
string deletestring = "delete from" + record;
sqlcommand deletecommand = new sqlcommand (deletestring, connection);
deletecommand. executenonquery ();
connection. close ();
}

/* database connection function */
Public static sqlconnection crecon ()
{< br> string SQL = "Server = 127.0.0.1; uid = sa; pwd =; database = Zhaoxia ";
sqlconnection con = new sqlconnection (SQL);
return con;
}< br>/* main function */
static void main ()
{< br> New introduceexcel ();
}

/* File operation class definition */
Public class file
{
String filename;
Public file (string filename)
{
This. filename = filename;
}

Public String readfile ()
{
Try
{
Streamreader sr = new streamreader (filename, encoding. Default );
String result = Sr. readtoend ();
Sr. Close ();
Return result;
}
Catch (exception e) {MessageBox. Show (E. Message );}
Return NULL;
}

Public void writefile (string Str)
{
Try
{
Streamwriter Sw = new streamwriter (filename, false, encoding. Default );
Sw. Write (STR );
Sw. Close ();
}
Catch (exception e) {MessageBox. Show (E. message, "An error occurred while saving the file! ");}
}
} // File class

}