How to import / export excel file using open xml


Download "DocumentFormat.OpenXml.dll" from website and write following code


protected void btnimport_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable dt = new DataTable();
            if (fu_File.PostedFile != null)
            {
                string strtimeStamp = string.Empty;
                strtimeStamp = System.DateTime.Now.Ticks.ToString();
                string fileName = Server.MapPath("~/UploadFile/") + strtimeStamp + "_" + fu_File.FileName;
                fu_File.SaveAs(fileName);
                string result = obj.GetXML(fileName);

                // Check pre-PGI Dt
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(result);
                XmlNode root = doc.FirstChild;
                //Display the contents of the child nodes.
                if (root.HasChildNodes)
                {
                    String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
                    SqlConnection con = new SqlConnection(strConnString);
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "SaveOrderList";
                    cmd.Parameters.Add("@xmlData", SqlDbType.NVarChar).Value = result;
                    cmd.Parameters.Add("@UploadedBy", SqlDbType.NVarChar).Value = 1;
                    cmd.Connection = con;
                    try
                    {
                        con.Open();
                        cmd.ExecuteNonQuery();
                        lblMessage.Text = "Record has been successfully inserted";
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    lblMessage.Text = "No Record has been available for insert";
                }


            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = "! Error:" + ex.Message;
        }
    }




    protected void btnexport_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection con = new     SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            cmd = new SqlCommand("GetOrderList", con);
            cmd.CommandType = CommandType.StoredProcedure;           
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            con.Close();
            DataTable table = new DataTable();
            table = ds.Tables[0];
            obj.DataTableToExcel(table);
        }
        catch (Exception ex)
        {
            lblMessage.Text = "Error: " + ex.Message;
        }
    }





Add class 




    public class ConvertExcelToXml
    {
        /// <summary>
        ///  Read Data from selected excel file into DataTable
        /// </summary>
        /// <param name="filename">Excel File Path</param>
        /// <returns></returns>
        public DataTable ReadExcelFile(string filename)
        {
            // Initialize an instance of DataTable
            DataTable dt = new DataTable();

            try
            {
                // Use SpreadSheetDocument class of Open XML SDK to open excel file
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
                {
                    // Get Workbook Part of Spread Sheet Document
                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                    // Get all sheets in spread sheet document
                    IEnumerable<Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();

                    // Get relationship Id
                    string relationshipId = sheetcollection.First().Id.Value;

                    // Get sheet1 Part of Spread Sheet Document
                    WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);

                    // Get Data in Excel file
                    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    IEnumerable<Row> rowcollection = sheetData.Descendants<Row>();

                    if (rowcollection.Count() == 0)
                    {
                        return dt;
                    }

                    // Add columns
                    foreach (Cell cell in rowcollection.ElementAt(0))
                    {
                        string CellText = GetValueOfCell(spreadsheetDocument, cell);
                        CellText = CellText.Replace(" ", "O"); // All space of excel sheet header replaced by 'O'
                        while (true)
                        {
                            if (dt.Columns.IndexOf(CellText) >= 0)
                            {
                                CellText += "_1";
                                continue;
                            }
                            break;
                        }
                        dt.Columns.Add(CellText);
                    }
                    dt.Columns.Add("Last");
                    //foreach (Cell cell in rowcollection.ElementAt(0))
                    //{
                    //    dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
                    //}

                    // Add rows into DataTable
                    foreach (Row row in rowcollection)
                    {
                        DataRow temprow = dt.NewRow();
                        int columnIndex = 0;
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            // Get Cell Column Index
                            int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference));

                            if (columnIndex < cellColumnIndex)
                            {
                                do
                                {
                                    temprow[columnIndex] = string.Empty;
                                    columnIndex++;
                                }

                                while (columnIndex < cellColumnIndex);
                            }

                            temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell);
                            columnIndex++;
                        }

                        // Add the row to DataTable
                        // the rows include header row
                        dt.Rows.Add(temprow);
                    }
                }

                // Here remove header row
                dt.Rows.RemoveAt(0);
                return dt;
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message);
            }
        }

        /// <summary>
        ///  Get Value of Cell
        /// </summary>
        /// <param name="spreadsheetdocument">SpreadSheet Document Object</param>
        /// <param name="cell">Cell Object</param>
        /// <returns>The Value in Cell</returns>
        private static string GetValueOfCell(SpreadsheetDocument spreadsheetdocument, Cell cell)
        {
            // Get value in Cell
            SharedStringTablePart sharedString = spreadsheetdocument.WorkbookPart.SharedStringTablePart;
            if (cell.CellValue == null)
            {
                return string.Empty;
            }

            string cellValue = cell.CellValue.InnerText;

            // The condition that the Cell DataType is SharedString
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return sharedString.SharedStringTable.ChildElements[int.Parse(cellValue)].InnerText;
            }
            else
            {
                return cellValue;
            }
        }

        /// <summary>
        /// Get Column Name From given cell name
        /// </summary>
        /// <param name="cellReference">Cell Name(For example,A1)</param>
        /// <returns>Column Name(For example, A)</returns>
        private string GetColumnName(string cellReference)
        {
            // Create a regular expression to match the column name of cell
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);
            return match.Value;
        }

        /// <summary>
        /// Get Index of Column from given column name
        /// </summary>
        /// <param name="columnName">Column Name(For Example,A or AA)</param>
        /// <returns>Column Index</returns>
        private int GetColumnIndex(string colName)
        {
            string columnName = colName.ToLowerInvariant();
            int columnIndex = 0;
            //int factor = 1;

            //// From right to left
            //for (int position = columnName.Length - 1; position >= 0; position--)
            //{
            //    // For letters
            //    if (Char.IsLetter(columnName[position]))
            //    {
            //        columnIndex += factor * ((columnName[position] - 'A') + 1) - 1;
            //        factor *= 26;
            //    }
            //}
            string s = "abcdefghijklmnopqrstuvwxyz";
            if (columnName.Length == 1)
            {
                columnIndex = s.IndexOf(columnName);
            }
            else
            {
                char[] parts = new char[] { columnName[0], columnName[1] };
                columnIndex = s.IndexOf(parts[0]);
                columnIndex += (26 * (s.IndexOf(parts[0]) + 1) + s.IndexOf(parts[1]));
            }
            return columnIndex;
        }

        /// <summary>
        /// Convert DataTable to Xml format
        /// </summary>
        /// <param name="filename">Excel File Path</param>
        /// <returns>Xml format string</returns>
        public string GetXML(string filename)
        {
            try
            {
                DataTable dt = new DataTable();
                dt = this.ReadExcelFile(filename);
                int ColIndex = dt.Columns.IndexOf("pre-PGIODt");
                dt.Rows.Cast<DataRow>().Where(r => Convert.ToString(r.ItemArray[ColIndex]).Length == 0).ToList().ForEach(r => r.Delete());// Delete Blank Rows (QC is empty);
                DataTable dtMain = dt.DefaultView.ToTable(false, "Plnt", "SalesODoc.", "NameO1", "CustomerOpurchaseOorderOno", "Description", "OOOrderOqty", "Delivery", "Ac.GIOdate", "pre-PGIODt", "OOOOOOWBSOElement");

                using (DataSet ds = new DataSet())
                {
                    ds.Tables.Add(dtMain);
                    return ds.GetXml();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// convert datatable into excel file.
        /// </summary>
        /// <param name="dt"></param>
        public void DataTableToExcel(DataTable dt)
        {
            DataView dataView = ConvertToDataView(dt);
            string theString = string.Empty;
            ASCIIEncoding objEncoding = null;
            HttpResponse objHttpResponse = null;
            int i = 0;
            objHttpResponse = HttpContext.Current.Response;
            objHttpResponse.Clear();
            objHttpResponse.AddHeader("Content-Disposition", ("attachment;filename=" + "SLIRequest.xls"));
            objHttpResponse.ContentType = "application/ms-excel";

            if ((dataView != null) && (dataView.Table.Rows.Count > 0))
            {
                theString = ConvertDataViewToString(dataView, "", "\t");
                theString = theString.Replace('"', ' ');
            }
            if (theString.Length <= 0)
            {
                theString = "Data not found.";
            }
            objEncoding = new ASCIIEncoding();
            i = objEncoding.GetByteCount(theString);
            objHttpResponse.AddHeader("Content-Length", i.ToString());
            objHttpResponse.BinaryWrite(objEncoding.GetBytes(theString));
            objHttpResponse.Charset = "";
            objHttpResponse.End();

        }
        /// <summary>
        /// convert datatable to dataview
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        DataView ConvertToDataView(DataTable dt)
        {
            DataView dv = new DataView(dt);
            return dv;
        }

        /// <summary>
        /// convert dataview to string
        /// </summary>
        /// <param name="srcDataView"></param>
        /// <param name="Delimiter"></param>
        /// <param name="Separator"></param>
        /// <returns></returns>
        public static string ConvertDataViewToString(DataView srcDataView, string Delimiter, string Separator)
        {
            StringBuilder objStringBuilder = new StringBuilder();
            objStringBuilder.Length = 0;

            foreach (DataColumn column in srcDataView.Table.Columns)
            {
                if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
                {
                    objStringBuilder.Append(Delimiter);
                }
                objStringBuilder.Append(column.ColumnName);
                if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
                {
                    objStringBuilder.Append(Delimiter);
                }
                objStringBuilder.Append(Separator);
            }
            if (objStringBuilder.Length > Separator.Trim().Length)
            {
                objStringBuilder.Length -= Separator.Trim().Length;
            }
            objStringBuilder.Append(Environment.NewLine);
            foreach (DataRowView theDataRowView in srcDataView)
            {
                foreach (DataColumn theDataColumn2 in srcDataView.Table.Columns)
                {
                    if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
                    {
                        objStringBuilder.Append(Delimiter);
                    }
                    objStringBuilder.Append(RuntimeHelpers.GetObjectValue(theDataRowView[theDataColumn2.ColumnName]));
                    if ((Delimiter != null) && (Delimiter.Trim().Length > 0))
                    {
                        objStringBuilder.Append(Delimiter);
                    }
                    objStringBuilder.Append(Separator);
                }
                objStringBuilder.Length--;
                objStringBuilder.Append("\r\n");
            }
            if (objStringBuilder != null)
            {
                return objStringBuilder.ToString();
            }
            else
            {
                return string.Empty;
            }
        }
    }

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