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];
// Merged cell
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[" The name "].ToString() + " Natural conditions ";
// bold
xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i + 1, 14]).Font.set_Bold(true);
// Cell text is horizontally centered
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 border lines
xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i+5, 8]).Borders.set_LineStyle(OWC10.XlLineStyle.xlContinuous);
// Write data ( Here by DS generate )
xlsheet.ActiveSheet.Cells[i + 2, 1] = " The name ";
xlsheet.ActiveSheet.Cells[i + 2, 2] = dr[" The name "].ToString();
xlsheet.ActiveSheet.Cells[i + 2, 3] = " Former name ";
xlsheet.ActiveSheet.Cells[i + 2, 4] = dr[" Former name "].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] = " Working hours ";
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[" national "].ToString();
xlsheet.ActiveSheet.Cells[i + 3, 5] = " Political landscape ";
xlsheet.ActiveSheet.Cells[i + 3, 6] = dr[" Political landscape "].ToString();
xlsheet.ActiveSheet.Cells[i + 3, 7] = " The title ";
xlsheet.ActiveSheet.Cells[i + 3, 8] = dr[" The title "].ToString();
xlsheet.ActiveSheet.Cells[i + 4, 1] = " Record of formal schooling ";
xlsheet.ActiveSheet.Cells[i + 4, 2] = dr[" Record of formal schooling "].ToString();
xlsheet.ActiveSheet.Cells[i + 4, 3] = " A degree in ";
xlsheet.ActiveSheet.Cells[i + 4, 4] = dr[" A degree in "].ToString();
xlsheet.ActiveSheet.Cells[i + 4, 5] = " position ";
xlsheet.ActiveSheet.Cells[i + 4, 6] = dr[" position "].ToString();
xlsheet.ActiveSheet.Cells[i + 4, 7] = " File number ";
//Excel Does not support 0 The first input, plus the first letter of the surname is exactly the number of the full name
xlsheet.ActiveSheet.Cells[i + 4, 8] = dr[" Initials of last name "].ToString() + dr[" File number "].ToString();
xlsheet.ActiveSheet.Cells[i + 5, 1] = " Major: " + dr[" Currently engaged in professional "].ToString();
xlsheet.ActiveSheet.Cells[i + 5, 4] = " Work Unit: " + dr[" Work units "].ToString();
xlsheet.ActiveSheet.Cells[i + 5, 7] = " Id: " + dr[" Id 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
{
}
}