2nd Method for import / export excel file using open xml

Download "Excel.dll" and "ICSharpCode.SharpZipLib.dll" and paste in bin folder


protected void btnImport_Click(object sender, EventArgs e)
    {
        string strtimeStamp = string.Empty;
        strtimeStamp = System.DateTime.Now.Ticks.ToString();
        string fileName = Server.MapPath("~/UploadFile/") + strtimeStamp + "_" + FileUpload1.FileName;
        FileUpload1.SaveAs(fileName);
        ProcessYINVCO(fileName, 1, true, 1);
    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        DataSet dsDown = new DataSet();
        dsDown = obj.GetYINVCO(0);
        if (dsDown != null && dsDown.Tables.Count > 0)
        {
            if (dsDown.Tables[0].Rows.Count > 0)
            {
                DataTable table = new DataTable();
                table = dsDown.Tables[0];
                obj.DataTableToExcel(table, "YINVCOReport");
            }
            else
            {
                ErrorSuccessNotifiers.AddErrorMessage(" No record found for download");
            }
        }
        else
        {
            ErrorSuccessNotifiers.AddErrorMessage(" No record found for download");
        }

    }


public void ProcessYINVCO(string excelFilePath, int UserId, Boolean Save = false, int MasterUploadId = 0)
    {
        try
        {
            DataSet ds = obj.ReadXL(excelFilePath);
            DataTable dtYINVCO = ds.Tables[0];
            dtYINVCO.Rows.Cast<DataRow>().Where(r => Convert.ToString(r.ItemArray[1]).Length == 0).ToList().ForEach(r => r.Delete());// Delete Blank Rows (QC is empty);
            dtYINVCO.AcceptChanges();
            string ValidCols = ",WBS Element,Site Description,Milest.no.,Scheduled,Actl. date,Doc. Date,SO Created By,Customer,Sales Doc.,Line Item,PO number,PO date,HgLvIt,Material,Description,SU,Order Quantity,per,Net price,Net value,GrossValue,Net Price (EUR),Net Value (EUR),Gross Value (EUR),Exchange Rate,Bill Value (EUR),Inv.Net Value (EUR),Req.dlv.dt,Cu.req.dat,OS,DS,ExtMatlGrp,SearchTerm,Mat. Doc.,GR Date,Discount,Discount Currency,SAP Item level,Customer Material Number,SCC Item,Billing on SCC,Billing on SCC (EUR),HUB PGI Doc,DOP GI date,Rj,Bill.plan,Item,Bill. Date,%,Bill value,RevRecg,IntDelivNr,SubsIt,Ac.GI date,Quantity,Reference Value,CusDelivNr,Del.SubsIt,Del.Ac.GI date,Del.Quantity,Del.Reference Value,Cust.InvNr,Inv.SubsIt,Inv.Bill.date,Inv.Net value,De.ABM-PBM,De.ABM-CID,De.Del-CID,".ToLower();
            int RowCount = dtYINVCO.Rows.Count;
            int delRows = 0;
            for (delRows = 0; delRows < RowCount; delRows++)
            {
                DataRow dr = dtYINVCO.Rows[delRows];
                if (Convert.ToString(dr[1]).ToLower().Trim().Equals("wbs element") && Convert.ToString(dr[2]).ToLower().Trim().Equals("site description"))
                {
                    break;
                }
            }
            if (delRows < RowCount)
                for (int i = 0; i < delRows; i++)
                {
                    dtYINVCO.Rows[i].Delete();
                }
            dtYINVCO.AcceptChanges();
            DataTable dtMod = new DataTable();
            DataRow drhead = dtYINVCO.Rows[0];
            int BCol = 1;
            foreach (DataColumn dc in dtYINVCO.Columns)
            {
                dtMod.Columns.Add(Convert.ToString(drhead[dc.ColumnName]).Length == 0 ? ("Blank" + BCol++) : Convert.ToString(drhead[dc.ColumnName]), typeof(string));
            }

            foreach (DataRow dr in dtYINVCO.Rows)
                dtMod.Rows.Add(dr.ItemArray);

            for (int i = 0; i < dtMod.Columns.Count; )
            {
                if (ValidCols.IndexOf(("," + dtMod.Columns[i].ToString().ToLower().Trim() + ",")) < 0)
                {
                    dtMod.Columns.RemoveAt(i);
                    continue;
                }
                i++;
            }
            //string AllowedCols = ",Site Description,SearchTerm,Sales Doc.,Line Item,WBS Element,Rj,Order Quantity,Net price,GrossValue,PO number,Description,SCC Item,Cust.InvNr,Milest.no.,GR Date,HUB PGI Doc,DOP GI date,Ac.GI date,CusDelivNr,Del.Ac.GI date,Del.Quantity,Customer Material Number,";
            dtMod.Rows[0].Delete();

            string strx = dtMod.Columns.Count.ToString();
            if (Save)
            {
                try
                {
                    DataSet dsSave = new DataSet();
                    dsSave = objData.SaveManageYINVCO(dtMod, 1);
                    if (dsSave != null && dsSave.Tables.Count > 0)
                    {
                        if (dsSave.Tables[0].Rows.Count > 0)
                        {
                            if (Convert.ToString(dsSave.Tables[0].Rows[0]["Status"]) == "1")
                            {
                                ErrorSuccessNotifiers.AddSuccessMessage(strx + " Records has been successfully updated");
                            }
                            else
                            {
                                ErrorSuccessNotifiers.AddSuccessMessage(strx + " Records has been successfully updated");
                            }
                        }
                    }
                    //using (SqlConnection con = new SqlConnection(strConnString))
                    //{
                    //    con.Open();
                    //    using (SqlCommand cmd = new SqlCommand("SaveManageYINVCO", con))
                    //    {
                    //        cmd.CommandType = CommandType.StoredProcedure;
                    //        cmd.Parameters.AddWithValue("@MyParam", dtMod);
                    //        cmd.Parameters.AddWithValue("@UploadedBy", UserId);
                    //        cmd.Parameters[0].SqlDbType = SqlDbType.Structured;
                    //        int x = cmd.ExecuteNonQuery();
                    //    }
                    //}
                }
                catch (Exception ex)
                {
                    throw new Exception("YINVCO Not Saved : [" + ex.Message + "]");
                }
            }


        }
        catch (Exception ex)
        {
            throw new Exception("Unable to download YINVCO List [" + ex.Message + "]");
        }
    }

    public DataTable RemoveUnwantedColumns(DataTable sTable, string AllowedColumns)
    {
        try
        {
            AllowedColumns = "," + AllowedColumns.ToLower() + ",";
            for (int i = 0; i < sTable.Columns.Count; )
            {
                DataColumn dc = sTable.Columns[i];
                if (AllowedColumns.IndexOf(("," + dc.ColumnName.ToLower().Trim() + ",")) < 0)
                {
                    sTable.Columns.Remove(dc);
                    continue;
                }
                i++;
            }
            sTable.AcceptChanges();
            return sTable;
        }
        catch (Exception ex)
        {
            throw new Exception("Unable to remove unwanted Columns [" + ex.Message + "]");
        }
    }
public DataSet ReadXL(string excelFilePath)
    {
        try
        {
            FileStream stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = null;
            if (Path.GetExtension(excelFilePath).ToLower().Equals(".xls"))
            {
                try
                {
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                catch (Exception)
                {
                    throw;
                }
            }
            else
            {
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                try
                {
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);// ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                catch (Exception)
                {
                    throw;
                }
            }
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            //DataSet result = excelReader.AsDataSet();

            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            excelReader.Close();
            stream.Close();
            return result;
        }
        catch (Exception)
        {
            return null;
        }
    }

Comments

Post a Comment

Popular posts from this blog

ASP.NET Session States in SQL Server Mode

Image
ASP.NET Session States in SQL Server Mode  1. Run --> Cmd press enter 2. on DOS screen , type "C:\Windows\Microsoft.NET\Framework64\v4.0.30319" press enter 3. type "aspnet_regsql.exe -S  Test_DB -U sa -P bajaj@123 -ssadd -sstype p" press enter Then ASP State is in Database created. Then Go to web application and define the session state mode in web.config <system.web> <sessionState mode="SQLServer" timeout="30" sqlConnectionString="Data Source=192.168.68.10; User ID=sa;Password=Test@123;" cookieless="false" /> </system.web>
Read more

How to find client's MAC Address in Asp.Net and C#.net

For Web Application using System.Runtime.InteropServices; [DllImport("Iphlpapi.dll")]     private static extern int SendARP(Int32 dest, Int32 host, ref Int64 mac, ref Int32 length);     [DllImport("Ws2_32.dll")]     private static extern Int32 inet_addr(string ip); protected void Page_Load(object sender, EventArgs e)     {         try         {             string userip = Request.UserHostAddress;             string strClientIP = Request.UserHostAddress.ToString().Trim();             Int32 ldest = inet_addr(strClientIP);             Int32 lhost = inet_addr("");             Int64 macinfo = new Int64();             Int32 len = 6;             int res = SendARP(ldest, 0, ref macinfo, ref...
Read more

Use XML Data For Save in Database

CREATE PROCEDURE [dbo].[SaveData] @MH INT, @TxTest INT, @ID INT, @CreatedBy INT, @strXML VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @XMLDocHandle INT; DECLARE @InvestorID INT SET @ID=(SELECT Id FROM tbl_Test WHERE  ID=@ID) IF(@strXML IS NOT NULL AND @strXML<>'') BEGIN EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @strXML CREATE TABLE #temp(ID INT,ClientID INT,InvID INT,Name VARCHAR(100),MaxAmount NVARCHAR(20),BankName NVARCHAR(500), AN VARCHAR(30),Status VARCHAR(20),CreatedDate DATETIME,CreatedBy VARCHAR(10),StartDate DATETIME) INSERT INTO #temp SELECT ID,ClientID,@InvestorID,MName,MA,BName,AN,Status,GETDATE(),@CreatedBy,GETDATE() FROM OPENXML (@XMLDocHandle, 'NewDataSet/Table1',2)  WITH( ID VARCHAR(10) 'ID', ClientID VARCHAR(10) 'ClientID', MN VARCHAR(100) 'MN', MA NVARCHAR(20) 'MA', BN NVARCHAR(500) 'BN', ANumber VAR...
Read more