Read Excel File And Format Records

 OutPutExcel.aspx.cs

 

using  System;
using  System.Data;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Data.OleDb;
using  System.Data.SqlClient;
using  System.Text;
using  System.IO;

public   partial   class  Default3 : System.Web.UI.Page
... {
    private FileStream fs;
    private Byte[] buff;
    protected void Page_Load(object sender, EventArgs e)
    ...{
        this.btnOut.Text = "Create String";
        this.tbStringOutPut.Text = "";
    }
    private string Alert(string alertString, Page page)
    ...{
        string alert = "<script>alert('";
        alert += alertString;
        alert += "');</script>";
        return alert;
    }
    protected void btnOut_Click(object sender, EventArgs e)
    ...{
        if (FileUpload1.PostedFile.FileName == "")
        ...{
            Response.Write(this.Alert("Need Select Excel File!", this.Page));
        }
        else
        ...{

            DataSet ds;
            ds = GetOleData(FileUpload1.PostedFile.FileName);
            try
            ...{
                StringBuilder Strings = new StringBuilder();
                for (int a = 0; a < ds.Tables["Records"].Columns.Count; a++)
                ...{
                    for (int b = 0; b < ds.Tables["Records"].Rows.Count; b++)
                    ...{
                        Strings.Append("<data name="LBL_S_000" + b.ToString() + "" xml:space="preserve"><value>");
                        Strings.Append(Convert.ToString(ds.Tables["Records"].Rows[b][a]));
                        Strings.Append("</value></data>");
                        Strings.Append(Convert.ToChar(10));
                    }
                    Strings.Append(" ");
                }
                this.tbStringOutPut.Text = Strings.ToString();

                string str = FileUpload1.PostedFile.FileName.Substring(0, FileUpload1.PostedFile.FileName.LastIndexOf("/")) + "/strings.txt";
                if (File.Exists(str) == true)
                ...{
                    File.Delete(str);
                    fs = File.Create(str);
                    fs.Close();
                }
                fs = File.Create(str);
                fs.Close();
                fs = File.OpenWrite(str);
                buff = new UTF8Encoding(true).GetBytes(Strings.ToString());
                fs.Write(buff, 0, buff.Length);
                fs.Close();
            }
            catch (OleDbException ew)
            ...{
                if (ew.ErrorCode == -2147467259)
                ...{
                    Response.Write(this.Alert("The Excel File already open !", this.Page));
                }
            }
            finally
            ...{
                ds.Clear();
            }
        }
    }

    private DataSet GetOleData(string FileAddr)
    ...{
        OleDbConnection objConn = null;
        DataSet ds = new DataSet();
        try
        ...{
            string strConn = "Provider=Microsoft.Jet.OleDB.4.0;" + "Data Source=" + FileAddr + ";Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(strConn);
            objConn.Open();
            string strSql = "SELECT * FROM [Sheet1$A1:Z10000]";
            OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
            OleDbDataAdapter sqlada = new OleDbDataAdapter();
            sqlada.SelectCommand = objCmd;
            sqlada.Fill(ds, "Records");
            objConn.Close();
        }
        catch (System.Data.OleDb.OleDbException ee)
        ...{
            if (ee.ErrorCode == -2147467259)
            ...{
                Response.Write(this.Alert("The Excel File already open !", this.Page));
            }
        }
        catch (Exception e)
        ...{
            Response.Write(this.Alert("Not a Excel File !", this.Page));
            throw e;
        }
        return ds;
    }
}

 

OutPutExcel.aspx

 

<% @ Page Language = " C# "  AutoEventWireup = " true "  CodeFile = " OutPutExcel.aspx.cs "  Inherits = " Default3 "   %>

<! DOCTYPE html PUBLIC  " -//W3C//DTD XHTML 1.0 Transitional//EN "   " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >

< html xmlns = " http://www.w3.org/1999/xhtml "   >
< head id = " Head1 "  runat = " server " >
     < title > PutOut Strings </ title >
</ head >
< body >
     < form id = " form1 "  runat = " server " >
     < div >
         & nbsp; </ div >
         < asp:FileUpload ID = " FileUpload1 "  runat = " server "   />
         < asp:Button ID = " btnOut "  runat = " server "  OnClick = " btnOut_Click "  Text = " Button "   />& nbsp; < br  />
         < br  />
         < asp:TextBox ID = " tbStringOutPut "  runat = " server "  Height = " 1666px "  TextMode = " MultiLine "  Width = " 100% " ></ asp:TextBox >
     </ form >
</ body >
</ html >