C#將數據輸出為Excel問題

Question

读出来应该加格式控制:     private static void DataSetExportToExcel(DataSet source, string fileName)
    {        System.IO.StreamWriter excelDoc;        excelDoc = new System.IO.StreamWriter(fileName);
        const string startExcelXML = @"<xml version> <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\">  <Styles>  " +
              "<Style ss:ID=\"Default\" ss:Name=\"Normal\">  " +
              "<Alignment ss:Vertical=\"Bottom\"/>  <Borders/>" +
              "  <Font/>  <Interior/>  <NumberFormat/>" +
              "  <Protection/>  </Style>  " +
              "<Style ss:ID=\"BoldColumn\">  <Font " +
              "x:Family=\"Swiss\" ss:Bold=\"1\"/>  </Style>  " +
              "<Style     ss:ID=\"StringLiteral\">  <NumberFormat" +
              " ss:Format=\"@\"/>  </Style>  <Style " +
              "ss:ID=\"Decimal\">  <NumberFormat " +
              "ss:Format=\"0.0000\"/>  </Style>  " +
              "<Style ss:ID=\"Integer\">  <NumberFormat " +
              "ss:Format=\"0\"/>  </Style>  <Style " +
              "ss:ID=\"DateLiteral\">  <NumberFormat " +
              "ss:Format=\"mm/dd/yyyy;@\"/>  </Style>  " +
              "</Styles>  ";
        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)
        {
            rowCount++;
            //if the number of rows is > 64000 create a new page to continue output
            if (rowCount == 64000)
            {
                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("&", "&");
                        XMLstring = XMLstring.Replace(">", ">");
                        XMLstring = XMLstring.Replace("<", "<");
                        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 1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
string path = "C:\\ExcelData1.xls";
if(File.Exists(path))
{
File.Delete(path);
}

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES'";

using(OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

string sql = @"CREATE TABLE Sheet1 
(
  F1 char(255)
, F2 char(255)
)";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//
cmd.ExecuteNonQuery();
} sql = "insert into [Sheet1$] (F1,F2) values ('0241234567','24:00' )";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//
cmd.ExecuteNonQuery();
}
}
}
}
}
用 oleDB 比较方便，如果是现成的 excel 就不需要create table 了；

Answer 2

DataSet 拼成 sql 你应该会的吧
用 cmd.Parameters 的话 insert 还能更好点；

Answer 3

觉得一楼的用xml转化的方法挺好，虽然看起来比较麻烦，但效率是很高的．并且一楼写的也比较详细，楼主可以考虑下．关注中．

Answer 4

网上http://www.qqgb.com/NetProgramme/ASPNet/aspnetdatabase/NetProgramme_123171.html 
这篇对Office Schemas生成Excel文件由比較好的引导。
但一般动态生成不可能有xsl文件，所以xsl文件也需要能够动态生成，这在网上可找一个Rama Krishna's Export class，是动态生成xsl，并加以Excel輸出，但現在应用这程序，由于表中数据有乱码，可能存入时有误区，
但就是这乱码，致使Excel文件打開失敗。该类对于xsl文件是动态生成的，但xml数据很简单由DataSet直接生成.
1楼csShooter是不是正好相反,xsl是固定的，而Xml由程序生成。
希望多贴些代码，就当是此类功能的集合贴。分不够可加。

Answer 5

1楼 csSHooter的方法确实可以运行，但对于字符串超长的显示，在Excel會成為####，不知有什方法能完全显示出 来，一般字串超长255长度，就显示####了，且手動拉長也沒用。
將 ss:Type="text"設上後，打开时就报表。还请高人指点。

Answer 6

問題解決了，經Excel中編輯清除格式後可以看到，再导出XML看，原來只要把CELL 中StyleID去掉即可.

Answer 7

这是我程序里的导出EXCEL模块，不知道是不是楼长想要的。
        // 把DataTable内容导出excel并返回客户端 
        public static void DataTable2Excel(System.Data.DataTable dtData)
        {
            System.Web.UI.WebControls.DataGrid dgExport = null;
            // 当前对话 
            System.Web.HttpResponse resp = System.Web.HttpContext.Current.Response;
            // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid 
            dgExport = new System.Web.UI.WebControls.DataGrid();
            dgExport.DataSource = dtData.DefaultView;
            dgExport.AllowPaging = false;
            dgExport.DataBind();            // 返回客户端 
            resp.Clear();
            resp.Buffer = true;
            //page.Response.Write("<meta http-equiv=Content-Type content=text/html>");
            //page.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
            //resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-7");//使用GB2312有时候会出现乱码
            resp.ContentEncoding = System.Text.Encoding.UTF7;//使用GB2312有时候会出现乱码
            resp.AppendHeader("content-disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8) + ".xls\"");
            //page.EnableViewState = false;            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            dgExport.RenderControl(hw);
            resp.Write(tw.ToString());            resp.End();
        }