【求救！！！】把一个table表保存到execl 中，保存的路径可以选择！！

Question

实现功能 ：

Answer 1

后台生成一个临时Excel文件，然后使用Response和Attachment方式把文件直接发到页面上去，此时会出现IE下载提示框，用户可选择保存或直接打开，保存时就可以选择路径了。

Answer 2

通过DataGrid或GridView显示表格内容，再用RenderControl生成Excel.

Answer 3

public static void exportToExcel(DataSet source,string fileName)
{
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName); const string startExcelXML = "<xml version>\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-    microsoft-com:office:excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n <Styles>\r\n <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n <Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n <Protection/>\r\n </Style>\r\n <Style ss:ID=\"BoldColumn\">\r\n <Font x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n <Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat ss:Format=\"@\"/>\r\n </Style>\r\n <Style ss:ID=\"Decimal\">\r\n <NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n <Style ss:ID=\"Integer\">\r\n <NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n <Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n </Styles>\r\n ";
const string endExcelXML = "</Workbook>"; int rowCount = 0;
int sheetCount = 1; excelDoc.Write(startExcelXML);
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>");
for(int x = 0; x < source.Tables[0].Columns.Count; x++)
{
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>"); foreach(DataRow x in source.Tables[0].Rows)
{
if(rowCount==64000) //if the number of rows is > 64000 create a new page to continue output
{
rowCount = 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
}
excelDoc.Write("<Row>"); //ID=" + rowCount + "
for(int y = 0; y < source.Tables[0].Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch(rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&","&amp;");
XMLstring = XMLstring.Replace(">","&gt;");
XMLstring = XMLstring.Replace("<","&lt;");
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\">");
excelDoc.Write(XMLstring);
excelDoc.Write("</Data></Cell>");
    break;
    case "System.DateTime":
//Excel has a specific Date Format of YYYY-MM-DD followed by the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
//The Following Code puts the date stored in XMLDate to the format above
DateTime XMLDate = (DateTime)x[y];
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" + 
(XMLDate.Month < 10 ? "0" + XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" + XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" + XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" + XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" + XMLDate.Second.ToString() : XMLDate.Second.ToString()) + 
".000";
excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\"><Data ss:Type=\"DateTime\">");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("</Data></Cell>");
    break;
case "System.Boolean":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<Cell ss:StyleID=\"Integer\"><Data ss:Type=\"Number\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<Cell ss:StyleID=\"Decimal\"><Data ss:Type=\"Number\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.DBNull":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
break;
default:
throw(new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("</Row>");
} excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write(endExcelXML);
excelDoc.Close(); }

Answer 4

<asp:Button ID="btnServiceExport" runat="server" Text="导出为Excel" OnClick="Export_2" CommandArgument="select * from .............................." />
<asp:DataGrid ID="dg2" runat="server" AutoGenerateColumns="false" DataKeyField="id" Width="100%">
                        <Columns>
                <asp:TemplateColumn HeaderText="aaa" ItemStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <%# getTypeName((DataBinder.Eval(Container.DataItem, "a")).ToString())%>
                    </ItemTemplate>
                </asp:TemplateColumn>
                <asp:TemplateColumn HeaderText="bbb" ItemStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <%# getStatus((DataBinder.Eval(Container.DataItem, "b")).ToString())%>
                    </ItemTemplate>
                </asp:TemplateColumn>
                <asp:BoundColumn HeaderText="ccc" DataField="c" DataFormatString="{0:d}" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="ddd" DataField="d" ItemStyle-HorizontalAlign="Left"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="eee" DataField="e" ItemStyle-HorizontalAlign="Left"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="fff" DataField="f" DataFormatString="{0:c}" ItemStyle-HorizontalAlign="Right"></asp:BoundColumn>
                <asp:BoundColumn HeaderText="ggg" DataField="g" ItemStyle-HorizontalAlign="Left"></asp:BoundColumn>
            </Columns>
        </asp:DataGrid>public void Export_2(object sender, EventArgs e)
    {
        using (SqlConnection conn = DB.getConnection())
        {
            Button btn = (Button)sender;
            string sql = btn.CommandArgument.ToString();
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();            string file = "Invoice" + DateTime.Now.ToShortDateString();            dg2.DataSource = dr;
            dg2.DataBind();
            Response.Clear();
            Response.Buffer = true;
            //使用中文
            Response.Charset = "gb2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + file + ".xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            //设置格式为Excel
            Response.ContentType = "application/ms-excel";
            EnableViewState = false;
            System.IO.StringWriter sw = new System.IO.StringWriter();
            HtmlTextWriter tw = new HtmlTextWriter(sw);
            dg2.RenderControl(tw);
            Response.Write(sw.ToString());
            Response.End();
        }
    }