比较复杂的导出Excel（1）!!!!!!!!!!

这个比较实用，变化也很多的,该实例是动态增加表头，并且将必须的数据导出来作为一个EXCEL模板：

 [DirectMethod]
        public void btnSave_Click(string str)
        {
            //string AdminManagesURL = "ShowExcel.aspx?ClassId=" + Request.QueryString["ClassID"] +  "&ClassCouse=" + str;
            //LoadConfig AdminManagesConfig = new LoadConfig(AdminManagesURL, LoadMode.IFrame);
            //FramePanel.LoadContent(AdminManagesConfig);
            this.windowBook.Hidden = true;
             try
            {
              
              
               //Server.MapPath(fileName)
                GetExcle(str); //str是页面上通过JS获取的参数，在这里得到的是课程：语文，数学，物理
              
            }
            catch (Exception ex)
            {
                Ext.Net.Notification.Show(new NotificationConfig
                {
                    Title = "温馨提示",
                    Icon = Icon.Information,
                    PinEvent = "mouseover",
                    Html =  ex.Message
                });
            }

             this.windowBook.Hidden = true;
        }

 

这个是导出成为Excel

        public void GetExcle(string str)
        {
            DataTable table = GetNewFellowExcelDataTable(str);
            string fileName = string.Format("学生成绩导入模板{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
            HttpContext.Current.Response.ClearContent();

            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel; charset=utf-8";

            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));

            HttpContext.Current.Response.Write(GetImportFailureStudentExcel(table, getTable(str)));

            HttpContext.Current.Response.End();
        }

 

  private DataTable GetNewFellowExcelDataTable(string ClassCourse)
        {
            DataTable table = new DataTable("NewFellow");
            table.Columns.Add("ColumnName", typeof(string)); //列名
            table.Columns.Add("DataType", typeof(Type)); //数据类型
            table.Columns.Add("IsMust", typeof(bool));  //是否是必填项
            table.Columns.Add("IsList", typeof(bool));  //是否是列表
            table.Columns.Add("ListValue", typeof(string));  //列表值
            table.Columns.Add("HintText", typeof(string));  //提示

            GetImportDataTable(ref table, "序号", typeof(string), false, false, string.Empty, string.Empty);
            GetImportDataTable(ref table, "姓名", typeof(string), false, false, string.Empty, string.Empty);
            GetImportDataTable(ref table, "学籍号", typeof(StringBuilder), false, false, string.Empty, string.Empty);
            GetImportDataTable(ref table, "性别", typeof(string), false, false, string.Empty, string.Empty);

            string[] strCourse = ClassCourse.TrimEnd(',').Split(',');
            foreach (string str in strCourse)
            {
                if (str != "")
                {
                    GetImportDataTable(ref table, str, typeof(string), false, false, string.Empty, string.Empty);
                }
            }

            return table;
        }

 

 

  public static string GetImportFailureStudentExcel(DataTable sourceTable, DataTable failureTable)
        {
                //将要生成的Excel文件
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("<?xml version=/"1.0/"?>");
                sb.AppendLine("<?mso-application progid=/"Excel.Sheet/"?>");
                //Excel工作薄开始
                sb.AppendLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
                sb.AppendLine("  <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
                sb.AppendLine("      <Created>" + DateTime.Now + "</Created>");
                sb.AppendLine("      <Version>11.6408</Version>");
                sb.AppendLine("  </DocumentProperties>");
                //Excel工作薄结束
                //工作薄样式
                sb.AppendLine("  <Styles>");
                sb.AppendLine("      <Style ss:ID=/"Default/" ss:Name=/"Normal/">");
                sb.AppendLine("          <Alignment ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
                sb.AppendLine("          <Borders>");
                sb.AppendLine("              <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                sb.AppendLine("              <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                sb.AppendLine("              <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
                sb.AppendLine("              <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/>");
                sb.AppendLine("          </Borders>");
                sb.AppendLine("          <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/"/>");
                sb.AppendLine("          <Interior />");
                sb.AppendLine("          <Protection />");
                sb.AppendLine("      </Style>");
                sb.AppendLine("      <Style ss:ID=/"s21/">");
                sb.AppendLine("          <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
                sb.AppendLine("          <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#FF0000/" />");
                sb.AppendLine("      </Style>");
                sb.AppendLine("      <Style ss:ID=/"s22/">");
                sb.AppendLine("          <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/"/>");
                sb.AppendLine("          <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#000000/" />");
                sb.AppendLine("      </Style>");
                sb.AppendLine("      <Style ss:ID=/"s23/">");
                sb.AppendLine("         <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/" />");
                sb.AppendLine("         <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#FF0000/" />");
                sb.AppendLine("         <NumberFormat ss:Format=/"@/" />");
                sb.AppendLine("      </Style>");
                sb.AppendLine("      <Style ss:ID=/"s24/">");
                sb.AppendLine("         <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/" ss:WrapText=/"1/" />");
                sb.AppendLine("         <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#000000/" />");
                sb.AppendLine("         <NumberFormat ss:Format=/"@/" />");
                sb.AppendLine("      </Style>");
                //文本样式
                sb.AppendLine("      <Style ss:ID=/"s25/">");
                sb.AppendLine("          <NumberFormat ss:Format=/"@/"/>");
                sb.AppendLine("      </Style>");
                sb.AppendLine("  </Styles>");
                //第i个工作表
                sb.AppendLine("  <Worksheet ss:Name=/"" + sourceTable.TableName + "/">");
                sb.AppendLine("      <Table ss:ExpandedColumnCount=/"" + sourceTable.Rows.Count + "/" ss:DefaultColumnWidth=/"100/">");
                //ExpandedColumnCount：代表Excel文档中的列数
                //ExpandedRowCount：代表Excel文档中的行数
                //生成标题
                StringBuilder validationString = new StringBuilder();
                string listFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><Type>List</Type><CellRangeList /><Value>&quot;{1}&quot;</Value></DataValidation>";
                string dateFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><Type>Date</Type><CellRangeList /><Min>1</Min><Max>402132</Max></DataValidation>";
                string msgFormat = "<DataValidation xmlns=/"urn:schemas-microsoft-com:office:excel/"><Range>C{0}</Range><InputMessage>{1}</InputMessage></DataValidation>";
                // 生成表头
                StringBuilder cellString = new StringBuilder();
                for (int i = 0; i < sourceTable.Rows.Count; i++)
                {
                    DataRow row = sourceTable.Rows[i];
                    bool isMust = Boolean.TrueString.Equals(row["IsMust"].ToString()) ? true : false;  //是否是必填项
                    string styleId = isMust ? "s21" : "s22";
                    if (Type.GetType(row["DataType"].ToString()).Equals(typeof(StringBuilder)))
                    {
                        styleId = isMust ? "s23" : "s24";
                        sb.AppendLine(string.Format("<Column ss:Index=/"{0}/" ss:StyleID=/"s25/" ss:AutoFitWidth=/"0/" ss:Width=/"120/"/>", i + 1));
                    }
                    cellString.AppendLine("          <Cell ss:StyleID=/"" + styleId + "/" ss:Index=/"" + (i + 1) + "/"><Data ss:Type=/"String/">" + row["ColumnName"] + "</Data></Cell>");

                    bool isList = Boolean.TrueString.Equals(row["IsList"].ToString()) ? true : false;  //是否是列表
                    if (isList)
                        validationString.AppendFormat(listFormat, i + 1, row["ListValue"]);

                    if (Type.GetType(row["DataType"].ToString()).Equals(typeof(DateTime)))
                        validationString.AppendFormat(dateFormat, i + 1);

                    if (!string.IsNullOrEmpty(row["HintText"].ToString()))
                        validationString.AppendFormat(msgFormat, i + 1, row["HintText"]);
                }
                sb.AppendLine("          <Row ss:AutoFitHeight=/"0/" ss:Height=/"25/">");
                sb.AppendLine("             " + cellString.ToString() + "");
                sb.AppendLine("          </Row>");
                // 生成表内容
                foreach (DataRow row in failureTable.Rows)
                {
                    sb.AppendLine("      <Row ss:AutoFitHeight=/"0/">");
                    foreach (DataRow sourceRow in sourceTable.Rows)
                    {
                        string coloumnName = sourceRow["ColumnName"].ToString();
                        string value = row[coloumnName].ToString();
                        if (Type.GetType(sourceRow["DataType"].ToString()).Equals(typeof(DateTime)))
                        {
                            DateTime sourceDate = DateTime.MinValue;
                            bool isDate = DateTime.TryParse(row[coloumnName].ToString(), out sourceDate);
                            value = isDate ? sourceDate.ToShortDateString() : row[coloumnName].ToString();
                        }
                        sb.AppendLine(string.Format("<Cell><Data ss:Type=/"String/">{0}</Data></Cell>", value));
                    }
                    sb.AppendLine("      </Row>");
                }

                sb.AppendLine("      </Table>");
                sb.AppendLine("      <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
                sb.AppendLine("          <Selected />");
                sb.AppendLine("          <FreezePanes />");
                sb.AppendLine("          <FrozenNoSplit />");
                sb.AppendLine("          <SplitHorizontal>1</SplitHorizontal>");
                sb.AppendLine("          <TopRowBottomPane>1</TopRowBottomPane>");
                sb.AppendLine("          <ActivePane>2</ActivePane>");
                sb.AppendLine("          <Panes>");
                sb.AppendLine("              <Pane>");
                sb.AppendLine("                  <Number>3</Number>");
                sb.AppendLine("                  <ActiveRow>1</ActiveRow>");
                sb.AppendLine("              </Pane>");
                sb.AppendLine("          </Panes>");
                sb.AppendLine("          <ProtectObjects>False</ProtectObjects>");
                sb.AppendLine("          <ProtectScenarios>False</ProtectScenarios>");
                sb.AppendLine("      </WorksheetOptions>");
                sb.AppendLine("      " + validationString.ToString() + "");
                sb.AppendLine("  </Worksheet>");
                sb.AppendLine("</Workbook>");

              return sb.ToString();
        }

        private void GetImportDataTable(ref DataTable table, string ColumnName, Type DataType, bool IsMust, bool IsList, string ListValue, string HintText)
        {
            DataRow row = table.NewRow();
            row["ColumnName"] = ColumnName;
            row["DataType"] = DataType;
            row["IsMust"] = IsMust;
            row["IsList"] = IsList;
            row["ListValue"] = ListValue;
            row["HintText"] = HintText;

            table.Rows.Add(row);
        }

 

 

        public DataTable getTable(string ClassCourse)
        {
            string[] strCourse = ClassCourse.TrimEnd(',').Split(',');
            DataTable dt = new DataTable();
            dt.Columns.Add("序号");
            dt.Columns.Add("姓名");
            dt.Columns.Add("学籍号");
            dt.Columns.Add("性别");
            int count = 0;

            foreach (string str in strCourse)
            {
                if (str != "")
                {

                    dt.Columns.Add(str);
                }
            }
            Student[] studentList = mrg.QueryUnEnroolStudentByClassId(new Guid(Request.QueryString["ClassID"]));
           
          
            foreach (Student su in studentList)
            {
                DataRow dr = dt.NewRow();
                dr[0] = (++count).ToString();
                dr[1] = su.Name.ToString();
                dr[2] = su.RegisterCode.ToString();
                dr[3] = su.Sex == null ? "男" : su.Sex.Name.ToString();
              
                dt.Rows.Add(dr);
            }

            return dt;

        }

 

 

 

导入该模板，并且写入到XML中：

 

 public void btnExport_Click(object sender, DirectEventArgs e)
        {
            string fileExt = Path.GetExtension(bfUploadFile.FileName);
            if (fileExt != ".xls")
                throw new ApplicationException("上传文件必须为.xls文件！");

            DataTable dt = ExportExcel.GetStudentListByExcel(bfUploadFile.FileBytes);

          
            XXT_SMSSpecialContent sc = BllAccess.Create_XXT_SMSSpecialContentBll().GetModel(new Guid(Request.QueryString["ID"].ToString()));
            Student[] studentList = mrg.QueryUnEnroolStudentByClassId(new Guid(Request.QueryString["ClassID"].ToString()));

         

            string strCouser = "";
            for (int i = 4; i < dt.Columns.Count; i++)
            {
                strCouser =strCouser + dt.Columns[i] + ",";
            }
                try
                {

                  
                    //   dt = ExcelToDS_manual(filePath,ref strCouser);

                    string[] strCList = strCouser.TrimEnd(',').Split(',');
                    #region
                    string str = "";

                    XmlDocument xmlDoc = new XmlDocument();
                    //建立Xml的定义声明
                    XmlDeclaration dec = xmlDoc.CreateXmlDeclaration("1.0", "GB2312", null);
                    xmlDoc.AppendChild(dec);
                    //创建根节点
                    XmlElement root = xmlDoc.CreateElement("Students");
                    xmlDoc.AppendChild(root);

 

                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (Student st in studentList)
                        {
                            if (dr[2].ToString().Trim() == st.RegisterCode.Trim())//空格 智v4_last_1 去掉科学计算法
                            {
                                XmlNode student = xmlDoc.CreateElement("Student");
                                XmlElement number = xmlDoc.CreateElement("Number");
                                number.InnerText = st.RegisterCode;
                                student.AppendChild(number);
                                XmlElement name = xmlDoc.CreateElement("Name");
                                name.InnerText = st.Name;
                                student.AppendChild(name);
                                XmlElement content = xmlDoc.CreateElement("Content");
                                int i = 0;
                                string strContentList = "";
                                foreach (string stC in strCList)
                                {
                                    strContentList = strContentList + stC + "：" + dr[4 + (i++)].ToString() + "；";
                                }
                                content.InnerText = strContentList;
                                student.AppendChild(content);

                                XmlElement id = xmlDoc.CreateElement("ID");
                                id.InnerText = st.ID.ToString();
                                student.AppendChild(id);
                                root.AppendChild(student);
                                break;
                            }
                        }

                    }
                    sc.Content = xmlDoc.OuterXml;
                    BllAccess.Create_XXT_SMSSpecialContentBll().Update(sc);
                    #endregion

                    Ext.Net.Notification.Show(new NotificationConfig
                    {
                        Title = "温馨提示",
                        Icon = Icon.Information,
                        PinEvent = "mouseover",
                        Html = "导入学生成绩成功！"
                    });
                    AddStudentResult_Bind();
                    this.winDoRuExcel.Hidden = true;

                }
                catch (Exception ex)
                {

                    Ext.Net.Notification.Show(new NotificationConfig
                    {
                        Title = "温馨提示",
                        Icon = Icon.Information,
                        PinEvent = "mouseover",
                        Html = "导入学生成绩失败！" + ex.Message
                    });
                }

        }

 

 

 

 

  public static DataTable GetStudentListByExcel(byte[] xml_file)
        {
            MemoryStream stream = new MemoryStream(xml_file);
            XmlNodeReader reader = null;
            string rootValue = string.Empty;
            DataTable table = new DataTable();
            try
            {
                XmlDocument xd = new XmlDocument();
                xd.Load(stream);

                reader = new XmlNodeReader(xd); //创建新的XML reader
                string nodeType = null;

                DataRow row = null;

                int rowIndex = 0;
                int colIndex = 0;
                while (reader.Read())
                {
                    switch (reader.NodeType)
                    {
                        case XmlNodeType.Element:
                            nodeType = reader.Name;
                            if (nodeType.Equals("Row"))
                            {
                                rowIndex++;
                                colIndex = 0;
                                if (rowIndex > 1)
                                {
                                    if (row != null)
                                        table.Rows.Add(row);
                                }
                                row = null;
                            }
                            else if (nodeType.Equals("Cell"))
                            {
                                if (rowIndex > 1)
                                {
                                    if (reader.GetAttribute("ss:Index") == null)
                                        colIndex++;
                                    else
                                    {
                                        if (colIndex == 0)
                                        {
                                            row = table.NewRow();
                                        }
                                        colIndex = Convert.ToInt32(reader.GetAttribute("ss:Index"));
                                    }
                                }
                            }
                            break;
                        case XmlNodeType.Text:
                            if (nodeType.Equals("Data") || nodeType.Equals("ss:Data"))
                            {
                                rootValue = reader.Value.Replace("/r/n", "").Trim();
                                if (rowIndex == 1 && !table.Columns.Contains(rootValue))
                                    table.Columns.Add(rootValue);

                                if (rowIndex > 1)
                                {
                                    if (colIndex == 1)
                                        row = table.NewRow();
                                    row[colIndex - 1] = rootValue;
                                }
                            }
                            break;
                        case XmlNodeType.EndElement:
                            if (reader.Name.Equals("Table"))
                            {
                                if (row != null)
                                    table.Rows.Add(row);
                            }
                            break;
                    }
                }
            }
            catch
            {
                throw new ApplicationException("导入文件错误, 请下载最新导入模板");
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return table;
        }

 

某些说明可能会在后续中解释！！！！！！！