Asp. Net use OWC to Operate Excel

 

 

String connstr = System. Configuration. ConfigurationManager. ConnectionStrings ["DqpiHrConnectionString"]. ToString ();

 

SqlConnection conn = new SqlConnection (connstr );

 

SqlDataAdapter sda = new SqlDataAdapter (sql1.Text, conn );

 

DataSet ds = new DataSet ();

 

Conn. Open ();

 

Sda. Fill (ds );

 

Conn. Close ();

 

OWC10.SpreadsheetClass xlsheet;

 

Xlsheet = new OWC10.SpreadsheetClass ();

 

DataRow dr;

 

Int I = 0;

 

For (int ii = 0; ii <ds. Tables [0]. Rows. Count; ii ++)

 

{

 

Dr = ds. Tables [0]. Rows [ii];

 

 

 

 

// Merge Cells

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 1], xlsheet. Cells [I + 1, 8]). set_MergeCells (true );

 

 

Xlsheet. get_Range (xlsheet. Cells [I + 5, 1], xlsheet. Cells [I + 5, 3]). set_MergeCells (true );

 

Xlsheet. get_Range (xlsheet. Cells [I + 5, 4], xlsheet. Cells [I + 5, 6]). set_MergeCells (true );

 

Xlsheet. get_Range (xlsheet. Cells [I + 5, 7], xlsheet. Cells [I + 5, 8]). set_MergeCells (true );

 

Xlsheet. ActiveSheet. Cells [I + 1, 1] = dr ["name"]. ToString () + "natural condition ";

 

// Bold font

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 1], xlsheet. Cells [I + 1, 14]). Font. set_Bold (true );

 

// Align the cell text horizontally.

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 1], xlsheet. Cells [I + 1, 14]). set_HorizontalAlignment (OWC10.XlHAlign. xlHAlignCenter );

 

// Set the font size

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 1], xlsheet. Cells [I + 1, 14]). Font. set_Size (14 );

 

// Set the column width

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 8], xlsheet. Cells [I + 1, 8]). set_ColumnWidth (20 );

 

 

// Draw a border line

 

Xlsheet. get_Range (xlsheet. Cells [I + 1, 1], xlsheet. Cells [I + 5, 8]). Borders. set_LineStyle (OWC10.XlLineStyle. xlContinuous );

 

// Write data (generated by DS)

 

Xlsheet. ActiveSheet. Cells [I + 2, 1] = "name ";

 

Xlsheet. ActiveSheet. Cells [I + 2, 2] = dr ["name"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 2, 3] = "old name ";

 

Xlsheet. ActiveSheet. Cells [I + 2, 4] = dr [""]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 2, 5] = "Date of Birth ";

 

Xlsheet. activeSheet. cells [I + 2, 6] = DateTime. parse (dr ["Date of Birth"]. toString ()). year. toString () + "-" + DateTime. parse (dr ["Date of Birth"]. toString ()). month. toString ();

 

Xlsheet. ActiveSheet. Cells [I + 2, 7] = "Start Time ";

 

Xlsheet. activeSheet. cells [I + 2, 8] = DateTime. parse (dr ["Working Hours"]. toString ()). year. toString () + "-" + DateTime. parse (dr ["Working Hours"]. toString ()). month. toString ();

 

Xlsheet. ActiveSheet. Cells [I + 3, 1] = "gender ";

 

Xlsheet. ActiveSheet. Cells [I + 3, 2] = dr ["gender"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 3, 3] = "national ";

 

Xlsheet. ActiveSheet. Cells [I + 3, 4] = dr [""]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 3, 5] = "political outlook ";

 

Xlsheet. ActiveSheet. Cells [I + 3, 6] = dr ["political outlook"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 3, 7] = "title ";

 

Xlsheet. ActiveSheet. Cells [I + 3, 8] = dr ["title"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 4, 1] = "education ";

 

Xlsheet. ActiveSheet. Cells [I + 4, 2] = dr ["degree"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 4, 3] = "degree ";

 

Xlsheet. ActiveSheet. Cells [I + 4, 4] = dr ["degree"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 4, 5] = "title ";

 

Xlsheet. ActiveSheet. Cells [I + 4, 6] = dr ["title"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 4, 7] = "file number ";

 

// Excel does not support input starting with 0, and the first letter of the last name is exactly the full name of the number.

 

Xlsheet. ActiveSheet. Cells [I + 4, 8] = dr ["First Letter"]. ToString () + dr ["file number"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 5, 1] = "professional:" + dr ["professional"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 5, 4] = "unit of work:" + dr ["unit of work"]. ToString ();

 

Xlsheet. ActiveSheet. Cells [I + 5, 7] = "ID card:" + dr ["ID card number"]. ToString ();

 

I + = 6;

 

}

 

Try

 

{

 

String D = DateTime. Now. Year. ToString () + DateTime. Now. Month. ToString () + DateTime. Now. Day. ToString () +

 

DateTime. Now. Hour. ToString () + DateTime. Now. Minute. ToString () + DateTime. Now. Second. ToString () +

 

DateTime. Now. Millisecond. ToString ();

 

Xlsheet. Export (Server. MapPath ("./") + "\" + D + ". xls", OWC10.SheetExportActionEnum. ssExportActionNone, OWC10.SheetExportFormat. ssExportXMLSpreadsheet );

 

Response. Write ("<script> window. open ('" + D + ". xls') </script> ");

 

}

 

Catch

 

{

 

}

 

}