string sql = @"SELECT Serial AS 序号 , Type AS 类型 , ItemChanage AS 变更项目 , Name AS 单位名称 , TypeName AS 单位性质 , Place AS 经营场所面积 , PlaceUsed AS 使用面积 , LicenceSerialHealth AS 原许可证号 , Licence AS 许可证号 , LicenceDateStart AS 发证日期 , LicenceDataEnd AS 许可有效期 , LicenceItem AS 许可项目 , BusinePosition AS 地址 , BusinePositionOwnership AS 房屋产权 , OwnerShipTypeName AS 经济性质 , Principal AS 业主 , PersonIdNumber AS 身份证号码 , PersonLegal AS 法人及法人代表 , WorkerCount AS 职工人数 , Tel AS 联系电话 , LicenceGov AS 发证机关 FROM sup_shi_company"; if (where != "") sql += string.Format(" where {0}", where); DataSet ds = DBHelperSQL.Query(sql); HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); resp.ContentType = "application/ms-excel"; resp.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls"); string colHeaders = "", ls_item = ""; //定义表对象与行对象，同时用DataSet对其值进行初始化 System.Data.DataTable dt = ds.Tables[0]; DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = dt.Columns.Count; //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加\n { colHeaders += dt.Columns[i].Caption.ToString() + "\n"; } else { colHeaders += dt.Columns[i].Caption.ToString() + "\t"; } } resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加\n { ls_item += row[i].ToString() + "\n"; } else { if (row.Table.Columns[i].ColumnName != "身份证号码") { ls_item += row[i].ToString() + "\t"; } else { if (row.IsNull(i)) ls_item += "\t"; else ls_item += "'" + row[i].ToString() + "\t"; } } } resp.Write(ls_item); ls_item = ""; } resp.End();
