Excel数字列转换并导出

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;
public partial class Default : System.Web.UI.Page
{
    string currFilePath = string.Empty; //待读取文件的全路径 
    string currFileExtension = string.Empty;  //文件的扩展名 
    public int count = 0;//计数器
    public static DataTable dt_date = new DataTable();
    public string str_ordernumner = "";
    public string str_Wrongordernumner = "";
    public string ordernumner_a = "";
    public string EmployeeID = "";
    static System.Data.DataTable dt = new System.Data.DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }
    }
    ///<summary>
    ///上传文件到临时目录中 
    ///</ummary>
    private void Upload()
    {
        HttpPostedFile file = this.fileSelect.PostedFile;
        string fileName = file.FileName;
        if (fileName.Length <= 0)
        {
            this.Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('请选择上传文件！')</script>");
            return;
        }
        string time = DateTime.Now.ToString("yyyyMMddHHmmss");
        string filepath = Server.MapPath("upfile/" + time + Path.GetFileName(fileSelect.PostedFile.FileName));
        string tempPath = System.IO.Path.GetTempPath();   //获取系统临时文件路径      
        fileName = System.IO.Path.GetFileName(fileName); //获取文件名（不带路径）     
        this.currFileExtension = System.IO.Path.GetExtension(fileName);   //获取文件的扩展名          
        this.currFilePath = filepath;
        file.SaveAs(filepath);
    }
    /// <summary>
    /// 读取数据源
    /// </summary>
    /// <param name="sExcelFile"></param>
    /// <param name="dgBom"></param>
    public DataTable ReadExcelToTable(string sExcelFile)
    {
        DataTable dt = new DataTable();
        try
        {

            System.Data.DataTable ExcelTable;
            DataSet ds = new DataSet();
            //Excel的连接
            string strconn = "provider=microsoft.jet.oledb.4.0;" + "data source=" + sExcelFile + ";" + "extended properties=excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(strconn);
            objConn.Open();
            System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            string strSql = "select * from [" + tableName + "]";
            OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
            myData.Fill(ds, tableName);//填充数据
            ExcelTable = ds.Tables[tableName];
            dt = ds.Tables[tableName];

            objConn.Close();
        }
        catch
        {
            try
            {


                System.Data.DataTable ExcelTable;
                DataSet ds = new DataSet();
                //Excel的连接
                string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                OleDbConnection objConn = new OleDbConnection(strconn);
                objConn.Open();
                System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
                string strSql = "select * from [" + tableName + "]";
                OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                myData.Fill(ds, tableName);//填充数据
                ExcelTable = ds.Tables[tableName];
                dt = ds.Tables[tableName];

                objConn.Close();
            }
            catch (Exception ex)
            {
                this.Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('出错啦！" + ex.Message + "')</script>");
            }
        }

        return dt;
    }
   
    protected void Button1_Click(object sender, EventArgs e)
    {
        Upload();

        if (this.currFileExtension == ".xlsx" || this.currFileExtension == ".xls" || this.currFileExtension == ".csv")
        {
            DataTable dt = ReadExcelToTable(currFilePath);  //读取Excel文件（.xls和.xlsx格式）

            DataTable dtData = new DataTable();
            dtData.Columns.Add("itemnumber");
            dtData.Columns.Add("ebaytransactionid");
            dtData.Columns.Add("buyerid");
            dtData.Columns.Add("price");
            dtData.Columns.Add("TK");
            try
            {
                  
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                          DataRow dr = dtData.NewRow();
                           dr["itemnumber"]= "\"="+dt.Rows[i].ItemArray[0].ToString().Trim().Replace("\n", "").Trim()+"\"";
                           dr["ebaytransactionid"] = "\"=" + dt.Rows[i].ItemArray[1].ToString().Trim().Replace("\n", "").Trim() + "\"";
                           dr["buyerid"] = dt.Rows[i].ItemArray[2].ToString().Trim().Replace("\n", "").Trim();
                           dr["price"] = dt.Rows[i].ItemArray[3].ToString().Trim().Replace("\n", "").Trim();
                           dr["TK"] = dt.Rows[i].ItemArray[4].ToString().Trim().Replace("\n", "").Trim();
                       
                        dtData.Rows.Add(dr);
                    }
                }

                if (dtData.Rows.Count > 0)
                {
                    ExcelHelper eh = new ExcelHelper();
                    eh.DataTable3Excel(dtData, "EUB");
                }
              
            }
            catch (Exception ex)
            {
                this.Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('抱歉出错了！')</script>");
                return;

            }

 

        }

 


        else
        {

            this.Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('请上传Excel文件格式文件！')</script>");
            return;
        }
       
       
    }
}