關於DataTable采用StreamWrite導出Excel問題

[bb_chen]

代碼如下：

   public void ExportExcel2(DataTable dt, string fileName)
        {
            string FileName = fileName;
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                strLine = strLine + dt.Columns[i].ColumnName.ToString() + Convert.ToChar(9);

            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    strLine = strLine +  dt.Rows[i][j].ToString() + Convert.ToChar(9);
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";

            }

            objStreamWriter.Close();
            objFileStream.Close();
        }

此部分代碼為寫入值。

strLine = strLine +  dt.Rows[i][j].ToString() + Convert.ToChar(9);

現在遇到的問題是：當dt.Rows[i][j]為string類型，值為像這樣的"00.2200"字符串時，會出現導出後的Excel將它作為數字型,會等於0.22，請問該如何處理？

[BitCoffee]

写入值之前,设置单元格的格式为字符串oRange.NumberFormatLocal = "@";

[bb_chen]

[Quote=引用 1 楼 bitcoffee 的回复:]
写入值之前,设置单元格的格式为字符串oRange.NumberFormatLocal = "@";

[/Quote]
我是用StreamWrite阿

[bb_chen]

[Quote=引用 2 楼 bb_chen 的回复:]
引用 1 楼 bitcoffee 的回复:
写入值之前,设置单元格的格式为字符串oRange.NumberFormatLocal = "@";


我是用StreamWrite阿
[/Quote]
用StreamWrite導出的速度會比較快

[足球中国]

StreamWrite，有办法直接写成EXCEL文件嘛？

[wxhysoftsodc]

我也是这种情况。我把datatable里面的数据前面加了个'。效率地点，但结果总算对了。

[BitCoffee]

这个也可以.
[Quote=引用 5 楼 wxhysoftsodc 的回复:]
我也是这种情况。我把datatable里面的数据前面加了个'。效率地点，但结果总算对了。
[/Quote]

[bb_chen]

[Quote=引用 5 楼 wxhysoftsodc 的回复:]
我也是这种情况。我把datatable里面的数据前面加了个'。效率地点，但结果总算对了。
[/Quote]
這樣的話，導出後的Excel每個單元格前面都有一個　'　,如何處理

[hqbj08]

strLine = strLine + "'" +dt.Rows[i][j].ToString() + Convert.ToChar(9);

[bb_chen]

參考　http://www.codeproject.com/KB/dotnet/ExportToExcel.aspx

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;
     /*
    <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>
    <Worksheet ss:Name="Sheet1">
    </Worksheet>
    </Workbook>
    */
    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();
    }

[bb_chen]

UP