导出EXCEL表格之二－导出复杂表头

 

protected void Excle_Onclick(object sender, ImageClickEventArgs e)
    {
        Pronum = txtProNum.Text.Trim();
        ProNam = txtProNam.Text.Trim();
        Staff = txtStaff.Text.Trim();
        Group = this.DDLGroupname.SelectedValue.ToString();
        if (Group == "全部")
        {
            Group = "";
        }
        dt = Bf.GetProjectVotes(Staff_ID, Pronum, ProNam, Staff, Group);
        DataView dv = dt.DefaultView;
        OutputExcel(dv);
       
    }
    public void OutputExcel(DataView dv)    //导出EXCEL表格
    {
        Excel.Application application = new Excel.Application();
        Excel.Workbook book = application.Workbooks.Add(true);
        Excel.Worksheet sheet = book.ActiveSheet as Excel.Worksheet;

        #region 表头

        Excel.Range range1 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[2, 1]) as Excel.Range;//合并一列两行
        range1.Merge(0);
        range1.Value2 = "HName1";
        range1.Columns.ColumnWidth = 7;
        range1.HorizontalAlignment = XlVAlign.xlVAlignCenter;

        Excel.Range range2 = sheet.get_Range(sheet.Cells[1, 2], sheet.Cells[2, 2]) as Excel.Range;
        range2.Merge(0);
        range2.Value2 = "HName2";
        range2.Columns.ColumnWidth = 9;
        range2.HorizontalAlignment = XlVAlign.xlVAlignCenter;

        Excel.Range range3 = sheet.get_Range(sheet.Cells[1, 3], sheet.Cells[2, 3]) as Excel.Range;
        range3.Merge(0);
        range3.Value2 = "HName3";
        range3.Columns.ColumnWidth = 8;
        range3.HorizontalAlignment = XlVAlign.xlVAlignCenter;

        Excel.Range range4 = sheet.get_Range(sheet.Cells[1, 4], sheet.Cells[2, 4]) as Excel.Range;
        range4.Merge(0);
        range4.Value2 = "HName4";
        range4.Columns.ColumnWidth = 30;
        range4.HorizontalAlignment = XlVAlign.xlVAlignCenter;

        Excel.Range range30 = sheet.get_Range(sheet.Cells[1, 5], sheet.Cells[2, 5]) as Excel.Range;
        range30.Merge(0);
        range30.Value2 = "HName5";
        range30.Columns.ColumnWidth = 6;
        range30.HorizontalAlignment = XlVAlign.xlVAlignCenter;

        Excel.Range range5 = sheet.get_Range(sheet.Cells[1, 6], sheet.Cells[1, 8]) as Excel.Range;//合并一行两列
        range5.Merge(true);
        range5.Columns.ColumnWidth = 4;
        range5.HorizontalAlignment = XlVAlign.xlVAlignCenter;    //居中
        range5.Value2 = "HName6";

        Excel.Range range6 = sheet.Cells[2, 6] as Excel.Range;
        range6.Merge(true);
        range6.Value2 = "HOName61";
        range6.RowHeight = 57;

        Excel.Range range7 = sheet.Cells[2, 7] as Excel.Range;
        range7.Merge(true);
        range7.Value2 = "HOName62";

        Excel.Range range8 = sheet.Cells[2, 8] as Excel.Range;
        range8.Merge(true);
        range8.Value2 = "HOName63";

        Excel.Range range9 = sheet.get_Range(sheet.Cells[1, 9], sheet.Cells[1, 11]) as Excel.Range;
        range9.Merge(true);
        range9.Columns.ColumnWidth = 4;
        range9.HorizontalAlignment = XlVAlign.xlVAlignCenter;  
        range9.Value2 = "HName7";

        Excel.Range range10 = sheet.Cells[2, 9] as Excel.Range;
        range10.Merge(true);
        range10.Value2 = "HOName71";

        Excel.Range range11 = sheet.Cells[2, 10] as Excel.Range;
        range11.Merge(true);
        range11.Value2 = "HOName72";

        Excel.Range range12 = sheet.Cells[2, 11] as Excel.Range;
        range12.Merge(true);
        range12.Value2 = "HOName73";

        Excel.Range range13 = sheet.get_Range(sheet.Cells[1, 12], sheet.Cells[1, 14]) as Excel.Range;
        range13.Merge(true);
        range13.Columns.ColumnWidth = 3;
        range13.HorizontalAlignment = XlVAlign.xlVAlignCenter;  
        range13.Value2 = "HName8";

        Excel.Range range14 = sheet.Cells[2, 12] as Excel.Range;
        range14.Merge(true);
        range14.Value2 = "HOName81";

        Excel.Range range15 = sheet.Cells[2, 13] as Excel.Range;
        range15.Merge(true);
        range15.Value2 = "HOName82";

        Excel.Range range16 = sheet.Cells[2, 14] as Excel.Range;
        range16.Merge(true);
        range16.Value2 = "HOName83";

        Excel.Range range17 = sheet.get_Range(sheet.Cells[1, 15], sheet.Cells[1, 17]) as Excel.Range;
        range17.Merge(true);
        range17.Columns.ColumnWidth = 3;
        range17.HorizontalAlignment = XlVAlign.xlVAlignCenter;   
        range17.Value2 = "HName9";

        Excel.Range range18 = sheet.Cells[2, 15] as Excel.Range;
        range18.Merge(true);
        range18.Value2 = "HOName91";

        Excel.Range range19 = sheet.Cells[2, 16] as Excel.Range;
        range19.Merge(true);
        range19.Value2 = "HOName92";

        Excel.Range range20 = sheet.Cells[2, 17] as Excel.Range;
        range20.Merge(true);
        range20.Value2 = "HOName93";

        Excel.Range range21 = sheet.get_Range(sheet.Cells[1, 18], sheet.Cells[1, 20]) as Excel.Range;
        range21.Merge(true);
        range21.Columns.ColumnWidth = 3;
        range21.HorizontalAlignment = XlVAlign.xlVAlignCenter;  
        range21.Value2 = "HName10";

        Excel.Range range22 = sheet.Cells[2, 18] as Excel.Range;
        range22.Merge(true);
        range22.Value2 = "HOName101";

        Excel.Range range23 = sheet.Cells[2, 19] as Excel.Range;
        range23.Merge(true);
        range23.Value2 = "HOName102";

        Excel.Range range24 = sheet.Cells[2, 20] as Excel.Range;
        range24.Merge(true);
        range24.Value2 = "HOName103";

        Excel.Range range25 = sheet.get_Range(sheet.Cells[1, 21], sheet.Cells[1, 23]) as Excel.Range;
        range25.Merge(true);
        range25.Columns.ColumnWidth = 3;
        range25.HorizontalAlignment = XlVAlign.xlVAlignCenter;  
        range25.Value2 = "HName11";

        Excel.Range range26 = sheet.Cells[2, 21] as Excel.Range;
        range26.Merge(true);
        range26.Value2 = "HOName111";

        Excel.Range range27 = sheet.Cells[2, 22] as Excel.Range;
        range27.Merge(true);
        range27.Value2 = "HOName112";

        Excel.Range range28 = sheet.Cells[2, 23] as Excel.Range;
        range28.Merge(true);
        range28.Value2 = "HOName113";

        Excel.Range range29 = sheet.get_Range(sheet.Cells[1, 24], sheet.Cells[2, 24]) as Excel.Range;
        range29.Merge(0);
        range29.Value2 = "HName12";
        range29.Columns.ColumnWidth = 4;
        range29.HorizontalAlignment = XlVAlign.xlVAlignCenter;
        #endregion

        #region 取数据
        Application excel = new ApplicationClass();
        int rowIndex = 2;
        int colIndex;
        foreach (DataRowView row in dv)
        {
            rowIndex++;
            colIndex =0;
            foreach (DataColumn col in dv.Table.Columns)
            {
                colIndex++;
                if (col.ColumnName.ToString().Equals("Technicallevel"))
                {
                    switch(row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date1 = sheet.Cells[rowIndex, 6] as Excel.Range;
                            date1.Value2 =row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 7] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 8] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 6] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date2 = sheet.Cells[rowIndex, 7] as Excel.Range;
                            date2.Value2 =  row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 8] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex, 6] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 7] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date3 = sheet.Cells[rowIndex, 8] as Excel.Range;
                            date3.Value2 =  row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (col.ColumnName.ToString().Equals("Degreeofinnovation"))
                {
                    switch (row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date5 = sheet.Cells[rowIndex, 9] as Excel.Range;
                            date5.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 10] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 11] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 9] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date6 = sheet.Cells[rowIndex, 10] as Excel.Range;
                            date6.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 11] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex, 9] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 10] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date7 = sheet.Cells[rowIndex, 11] as Excel.Range;
                            date7.Value2 = row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (col.ColumnName.ToString().Equals("Complexity"))
                {
                    switch (row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date8 = sheet.Cells[rowIndex, 12] as Excel.Range;
                            date8.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 13] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 14] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 12] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date9 = sheet.Cells[rowIndex, 13] as Excel.Range;
                            date9.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 14] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex, 12] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 13] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date10 = sheet.Cells[rowIndex, 14] as Excel.Range;
                            date10.Value2 = row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (col.ColumnName.ToString().Equals("Practicality"))
                {
                    switch (row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date11 = sheet.Cells[rowIndex, 15] as Excel.Range;
                            date11.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 16] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 17] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 15] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date12 = sheet.Cells[rowIndex, 16] as Excel.Range;
                            date12.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 17] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex,15] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 16] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date13 = sheet.Cells[rowIndex, 17] as Excel.Range;
                            date13.Value2 = row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (col.ColumnName.ToString().Equals("Economic"))
                {
                    switch (row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date1 = sheet.Cells[rowIndex, 18] as Excel.Range;
                            date1.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 19] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 20] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 18] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date2 = sheet.Cells[rowIndex, 19] as Excel.Range;
                            date2.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 20] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex, 18] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 19] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date3 = sheet.Cells[rowIndex, 20] as Excel.Range;
                            date3.Value2 = row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (col.ColumnName.ToString().Equals("Social"))
                {
                    switch (row[col.ColumnName].ToString())
                    {
                        case "5":
                        case "4":
                            Excel.Range date1 = sheet.Cells[rowIndex, 21] as Excel.Range;
                            date1.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate1 = sheet.Cells[rowIndex, 22] as Excel.Range;
                            nodate1.Value2 = "";
                            Excel.Range nodate2 = sheet.Cells[rowIndex, 23] as Excel.Range;
                            nodate2.Value2 = "";
                        case "3":
                        case "2":
                            Excel.Range nodate3 = sheet.Cells[rowIndex, 21] as Excel.Range;
                            nodate3.Value2 = "";
                            Excel.Range date2 = sheet.Cells[rowIndex, 22] as Excel.Range;
                            date2.Value2 = row[col.ColumnName].ToString(); break;
                            Excel.Range nodate4 = sheet.Cells[rowIndex, 23] as Excel.Range;
                            nodate4.Value2 = "";
                        case "1":
                            Excel.Range nodate5 = sheet.Cells[rowIndex, 21] as Excel.Range;
                            nodate5.Value2 = "";
                            Excel.Range nodate6 = sheet.Cells[rowIndex, 22] as Excel.Range;
                            nodate6.Value2 = "";
                            Excel.Range date3 = sheet.Cells[rowIndex, 23] as Excel.Range;
                            date3.Value2 = row[col.ColumnName].ToString(); break;
                        default:
                            Excel.Range date4 = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                            date4.Value2 = "'" + row[col.ColumnName].ToString(); break;
                    }
                }
                if (colIndex <= 5)
                {
                    Excel.Range date = sheet.Cells[rowIndex, colIndex] as Excel.Range;
                    date.Value2 = "'" + row[col.ColumnName].ToString();
                }
                if (colIndex == 12)
                {
                    Excel.Range date = sheet.Cells[rowIndex, 24] as Excel.Range;
                    date.Value2 = row[col.ColumnName].ToString();
                }
               
            }
        }

        #endregion

        #region 保存
        string fileName = Guid.NewGuid().ToString() + ".xls";
        string pathFileName = HttpContext.Current.Server.MapPath(fileName);
        book.SaveAs(pathFileName, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        book.Close(Type.Missing, Type.Missing, Type.Missing);
        application.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
        book = null;
        application = null;
        sheet = null;
        GC.Collect();

        HttpResponse response = HttpContext.Current.Response;
        string fileName1 = HttpContext.Current.Server.UrlEncode("Report.xls");
        response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName1);
        response.Charset = "GB2312";
        response.ContentEncoding = System.Text.Encoding.Default;
        response.ContentType = "application/ms-excel";

        response.Clear();
        response.WriteFile(pathFileName);
        response.End();
        #endregion
    }
    #endregion