復制代碼 代碼如下:

    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];
            //合并單元格
            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["姓名"].ToString() + "自然情況";
            //字體加粗
            xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i + 1, 14]).Font.set_Bold(true);
            //單元格文本水平居中對齊
            xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i + 1, 14]).set_HorizontalAlignment(OWC10.XlHAlign.xlHAlignCenter);
            //設置字體大小
            xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i + 1, 14]).Font.set_Size(14);
            //設置列寬
            xlsheet.get_Range(xlsheet.Cells[i + 1, 8], xlsheet.Cells[i + 1, 8]).set_ColumnWidth(20);
            //畫邊框線
            xlsheet.get_Range(xlsheet.Cells[i + 1, 1], xlsheet.Cells[i+5, 8]).Borders.set_LineStyle(OWC10.XlLineStyle.xlContinuous);
            //寫入數據  (這里由DS生成)
            xlsheet.ActiveSheet.Cells[i + 2, 1] = "姓名";
            xlsheet.ActiveSheet.Cells[i + 2, 2] = dr["姓名"].ToString();
            xlsheet.ActiveSheet.Cells[i + 2, 3] = "曾用名";
            xlsheet.ActiveSheet.Cells[i + 2, 4] = dr["曾用名"].ToString();
            xlsheet.ActiveSheet.Cells[i + 2, 5] = "出生年月";
            xlsheet.ActiveSheet.Cells[i + 2, 6] = DateTime.Parse(dr["出生年月"].ToString()).Year.ToString() + "-" + DateTime.Parse(dr["出生年月"].ToString()).Month.ToString();
            xlsheet.ActiveSheet.Cells[i + 2, 7] = " 參加工作時間";
            xlsheet.ActiveSheet.Cells[i + 2, 8] = DateTime.Parse(dr["參加工作時間"].ToString()).Year.ToString() + "-" + DateTime.Parse(dr["參加工作時間"].ToString()).Month.ToString();
            xlsheet.ActiveSheet.Cells[i + 3, 1] = "性別";
            xlsheet.ActiveSheet.Cells[i + 3, 2] = dr["性別"].ToString();
            xlsheet.ActiveSheet.Cells[i + 3, 3] = "民族";
            xlsheet.ActiveSheet.Cells[i + 3, 4] = dr["民族"].ToString();
            xlsheet.ActiveSheet.Cells[i + 3, 5] = "政治面貌";
            xlsheet.ActiveSheet.Cells[i + 3, 6] = dr["政治面貌"].ToString();
            xlsheet.ActiveSheet.Cells[i + 3, 7] = "職稱";
            xlsheet.ActiveSheet.Cells[i + 3, 8] = dr["職稱"].ToString();
            xlsheet.ActiveSheet.Cells[i + 4, 1] = "學歷";
            xlsheet.ActiveSheet.Cells[i + 4, 2] = dr["學歷"].ToString();
            xlsheet.ActiveSheet.Cells[i + 4, 3] = "學位";
            xlsheet.ActiveSheet.Cells[i + 4, 4] = dr["學位"].ToString();
            xlsheet.ActiveSheet.Cells[i + 4, 5] = "職務";
            xlsheet.ActiveSheet.Cells[i + 4, 6] = dr["職務"].ToString();
            xlsheet.ActiveSheet.Cells[i + 4, 7] = "檔案號碼";
            //Excel不支持0開頭輸入，加上姓氏首字母正好是編號全稱
            xlsheet.ActiveSheet.Cells[i + 4, 8] = dr["姓氏首字母"].ToString() + dr["檔案號碼"].ToString();
            xlsheet.ActiveSheet.Cells[i + 5, 1] = "現從事專業：" + dr["現從事專業"].ToString();
            xlsheet.ActiveSheet.Cells[i + 5, 4] = "工作單位：" + dr["工作單位"].ToString();
            xlsheet.ActiveSheet.Cells[i + 5, 7] = "身份證：" + dr["身份證號"].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("./")+"http://"+D+".xls", OWC10.SheetExportActionEnum.ssExportActionNone, OWC10.SheetExportFormat.ssExportXMLSpreadsheet);
            Response.Write("<script>window.open('"+D+".xls')</script>");
        }
        catch
        {
        }
    }