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>