关于一下导出Excel文件的小问题。

Question

Response.Clear(); 
Response.Buffer= true; 
Response.Charset="GB2312";    
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls"); 
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
this.EnableViewState = false;    
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); 
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.MyDataGrid.RenderControl(oHtmlTextWriter); 
Response.Write(oStringWriter.ToString());
Response.End();//////////以上是导出Excel的代码，主要是用来将DataGrid里的内容导出到Excel中，现在DataGrid中有5列，我要如何只导出2-5列的内容，第1列不导出。

Answer 1

晕，隐藏那有什么用啊，我也知道隐藏可行，但问题是我要让第1列显示啊，而导出到EXCEL文件中不让其第1行导出。

Answer 2

大哥，这怎么是个变态的想法呢？如果你要是不懂，请你不要骂人，OK？
我这个想法当然是有目的的啊，如果第一列是小图片或是checkbox，难道我想过滤掉这也叫变态？

Answer 3

//获得需要的数据
doData myconn = new doData();
myconn.Conn_Open();
string strSql = "select Order_No as 订单号,Order_Name as 订购者,Order_Area as 区域,Convert(Varchar,Cast(Order_TotalPri As Money),1) as [应付费(元)],Order_Way as 支付方式,Order_Carry as 运送方式,Convert(varchar(10),Order_Date,102) as 下单日期,(case when Order_State=0  then '等待处理' when Order_State=1 then '客户取消' when Order_State=2 then '待协商订单' when Order_State=3 then '还没付费' when Order_State=4 then '已经退款' when Order_State=5 then '已经付费' when Order_State=6 then '已经发货'  end)  as 订单状态 from [Order] "+Qsql+"  order by ID asc";
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSql,myconn.conn);
DataSet ds = new DataSet();
da.Fill(ds,"CustomerOrder");
myconn.Conn_Close();
myconn.Conn_Dispose();//这里定义下载文件的名称
string fileName = "商城订单("+Convert.ToDateTime(start_date_txt.Value).ToString("yyyyMMdd")+"-"+Convert.ToDateTime(end_date_txt.Value).ToString("yyyyMMdd")+").xls";
System.Web.HttpResponse httpResponse = Page.Response;
httpResponse.AppendHeader("Content-Disposition","attachment;filename="+HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8)); 
httpResponse.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
httpResponse.ContentType = "application/ms-excel";
System.IO.StringWriter  tw = new System.IO.StringWriter() ;
tw.WriteLine("<?xml  version=\"1.0\"?>");  
tw.WriteLine("<?mso-application  progid=\"Excel.Sheet\"?>");  
tw.WriteLine("<Workbook  xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");  
tw.WriteLine("  xmlns:o=\"urn:schemas-microsoft-com:office:office\"");  
tw.WriteLine("  xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");  
tw.WriteLine("  xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");  
tw.WriteLine("  xmlns:html=\"http://www.w3.org/TR/REC-html40\">");  
tw.WriteLine("  <DocumentProperties  xmlns=\"urn:schemas-microsoft-com:office:office\">");  
tw.WriteLine("    <Author>aliketen</Author>");  
tw.WriteLine(string.Format("    <Created>{0}T{1}Z</Created>",  DateTime.Now.ToString("yyyy-mm-dd"),  DateTime.Now.ToString("HH:MM:SS")));  
tw.WriteLine("    <Company>广东原创动力传播有限公司</Company>");  
tw.WriteLine("    <Version>11.5606</Version>");  
tw.WriteLine("  </DocumentProperties>");  
tw.WriteLine("  <ExcelWorkbook  xmlns=\"urn:schemas-microsoft-com:office:excel\">");  
tw.WriteLine("    <WindowHeight>8955</WindowHeight>");  
tw.WriteLine("    <WindowWidth>11355</WindowWidth>");  
tw.WriteLine("    <WindowTopX>480</WindowTopX>");  
tw.WriteLine("    <WindowTopY>15</WindowTopY>");  
tw.WriteLine("    <ProtectStructure>False</ProtectStructure>");  
tw.WriteLine("    <ProtectWindows>False</ProtectWindows>");  
tw.WriteLine("  </ExcelWorkbook>");  
tw.WriteLine("  <Styles>");  
tw.WriteLine("    <Style  ss:ID=\"Default\"  ss:Name=\"Normal\">");  
tw.WriteLine("      <Alignment  ss:Vertical=\"Center\"/>");  
tw.WriteLine("      <Borders/>");  
tw.WriteLine("      <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");  
tw.WriteLine("      <Interior/>"); 
tw.WriteLine("      <NumberFormat/>"); 
tw.WriteLine("      <Protection/>");  
tw.WriteLine("    </Style>");  
tw.WriteLine("  <Style ss:ID=\"s21\">");
tw.WriteLine("   <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
tw.WriteLine("   <Borders>");
tw.WriteLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("   </Borders>");
tw.WriteLine("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Bold=\"1\"/>");
tw.WriteLine("   <Interior ss:Color=\"#CCCCFF\" ss:Pattern=\"Solid\"/>");
tw.WriteLine("   <NumberFormat/>");
tw.WriteLine("   <Protection/>");
tw.WriteLine("  </Style>");
tw.WriteLine("  <Style ss:ID=\"s22\">");
tw.WriteLine("   <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
tw.WriteLine("   <Borders>");
tw.WriteLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
tw.WriteLine("   </Borders>");
tw.WriteLine("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\"/>");
tw.WriteLine("   <Interior/>");
tw.WriteLine("   <NumberFormat/>");
tw.WriteLine("   <Protection/>");
tw.WriteLine("  </Style>");
tw.WriteLine("  </Styles>");  
tw.WriteLine("   <Worksheet ss:Name=\"宝贝商城订单\">");
tw.WriteLine("  <Table ss:ExpandedColumnCount=\"256\" ss:ExpandedRowCount=\""+(ds.Tables[0].Rows.Count+1).ToString()+"\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"40\" ss:DefaultRowHeight=\"20\">");
tw.WriteLine("   <Column ss:AutoFitWidth=\"0\" ss:Width=\"108\" ss:Span=\"255\"/>");
tw.WriteLine("         <Row ss:AutoFitHeight=\"0\">");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">订单号</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">订购者</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">区域</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">应付费(元)</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">支付方式</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">运送方式</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">下单日期</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">订单状态</Data></Cell>");
tw.WriteLine("   </Row>");//这里使用DataTable循环读取数据
foreach  (DataRow  row  in  ds.Tables[0].Rows)  
{  
tw.WriteLine("<Row ss:AutoFitHeight=\"0\">");  
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"Number\">"+row[0].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[1].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[2].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"Number\">"+row[3].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[4].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[5].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[6].ToString()+"</Data></Cell>");
tw.WriteLine("    <Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">"+row[7].ToString()+"</Data></Cell>");
tw.WriteLine("</Row>");  
}
tw.WriteLine("    </Table>");  
tw.WriteLine("      <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
tw.WriteLine("       <Unsynced/>");
tw.WriteLine("       <Selected/>");
tw.WriteLine("       <LeftColumnVisible>2</LeftColumnVisible>");
tw.WriteLine("       <ProtectObjects>False</ProtectObjects>");
tw.WriteLine("       <ProtectScenarios>False</ProtectScenarios>");
tw.WriteLine("      </WorksheetOptions>");
tw.WriteLine("  </Worksheet>");  
tw.WriteLine("</Workbook>"); 
da.Dispose();
ds.Dispose();

Answer 4

使用写XML的办法想怎么倒都可以，哈哈

Answer 5

string filePath = Server.MapPath("")+fileName;
System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
sw.Write(tw.ToString());
sw.Close();Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition","attachment;filename=" + 
HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8) + ";charset=GB2312");
System.IO.FileStream fs= System.IO.File.OpenRead(filePath);
long fLen=fs.Length;
int size=1024;//每1K同时下载数据 
byte[] readData = new byte[size];//指定缓冲区的大小 
if(size>fLen)size=Convert.ToInt32(fLen);
long fPos=0;
bool isEnd=false;
while (!isEnd) 
{ 
if((fPos+size)>fLen)
{
size=Convert.ToInt32(fLen-fPos);
readData = new byte[size];
isEnd=true;
}
fs.Read(readData, 0, size);//读入一个压缩块 
Response.BinaryWrite(readData);
fPos+=size;
} 
fs.Close(); 
System.IO.File.Delete(filePath);
httpResponse.End();

Answer 6

可以用隐藏的方法呀，在导出按钮上写this.DataGrid1.Columns[0].Visible=false;不就可以了吗？