Read Specified Cells from the Excel and insert into SQL table

Question

0.00/5 (No votes)

See more:

Posted 6-Jan-13 22:39pm

apr1234

Updated 7-Jan-13 0:41am

v4

Add a Solution

3 solutions

Solution 1

It is possible to insert state 2 details into Database...

look while inserting data into table we first create a data table and then in data row we fill data from excel file. In that time give index of only that rows which you want to insert into database.

indexes means Sno. ->0, Details ->1 like this

for eg.

C#

DataTable dt = new DataTable();
       #region datatable
       dt.Columns.Add("SerialNo");//0
       dt.Columns.Add("Details");//1
       dt.Columns.Add("State2");//2
       #endregion

       string strQuery = "truncate table EmployeeMaster1";
       SqlCommand MyCommand3 = new SqlCommand();

       MyConnection1 = new SqlConnection(connectionString);

       if (MyConnection1.State != ConnectionState.Open)
       {
           MyConnection1.Open();

           //   MyConnection1.Open();
       }
       MyCommand3.CommandText = strQuery;
       MyCommand3.Connection = MyConnection1;

       MyCommand3.ExecuteNonQuery();
       if (MyConnection1.State == ConnectionState.Open)
       {
           MyConnection1.Close();
       }
       try
       {
           string line;
           string m_CSV = FileUploadControl1.FileName;       // fileCSV.FileName;

           FileUploadControl1.PostedFile.SaveAs(Server.MapPath("m_CSV.csv"));
           //string m_Path = Server.MapPath("~") + "/" + m_CSV;
           string m_Path = Server.MapPath("m_CSV.csv");
           char[] seps = { ',' };

           if (CheckFileType(FileUploadControl1.FileName))
           {
               // StreamReader sr = new StreamReader(Server.MapPath("~") + "\\" + m_CSV);
               StreamReader sr = new StreamReader(m_Path);

               line = sr.ReadLine();                  //Read the first line of text
               while ((line = sr.ReadLine()) != null)     //Continue to read until you reach end of file
               {
                   String[] fields = line.Split(seps);
                   DataRow dr = dt.NewRow();
                   #region datarow
                   dr[0] = fields[0].ToString().Trim();
                   dr[1] = fields[1].ToString().Trim();
                   dr[2] = fields[3].ToString().Trim();//<big>state2 index</big>

                   #endregion

                   dt.Rows.Add(dr);

               }
               sr.Close();

               DataSet ds = new DataSet();
               ds.Tables.Add(dt);

I hope it is useful to you.............

Posted 7-Jan-13 1:29am

ranjeet.waje

Updated 7-Jan-13 5:12am

v2

Comments

apr1234 7-Jan-13 8:25am

thanks...very usefull.

Solution 2

Hi,
You can use Open Office XML SDK.
http://www.microsoft.com/en-us/download/details.aspx?id=5124[^]

Below you can find a static method wrote in C# which allows you to get the value from a certain cell:

C#

public static string XLGetCellValue(string fileName, string sheetName, string addressName)
        {
            string value = null;

            using (var document = SpreadsheetDocument.Open(fileName, false))
            {
                var wbPart = document.WorkbookPart;

                // Find the sheet with the supplied name, and then use that Sheet
                // object to retrieve a reference to the appropriate worksheet.
                var theSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);

                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }

                // Retrieve a reference to the worksheet part, and then use its
                // Worksheet property to get a reference to the cell whose
                // address matches the address you supplied:
                var wsPart =
                  (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                var theCell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == addressName);

                // If the cell does not exist, return an empty string:
                if (theCell != null)
                {
                    value = theCell.InnerText;

                    // If the cell represents a numeric value, you are done.
                    // For dates, this code returns the serialized value that
                    // represents the date. The code handles strings and Booleans
                    // individually. For shared strings, the code looks up the
                    // corresponding value in the shared string table. For Booleans,
                    // the code converts the value into the words TRUE or FALSE.
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:
                                // For shared strings, look up the value in the shared
                                // strings table.
                                var stringTable = wbPart.
                                  GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                // If the shared string table is missing, something is
                                // wrong. Return the index that you found in the cell.
                                // Otherwise, look up the correct text in the table.
                                if (stringTable != null)
                                {
                                    value = stringTable.SharedStringTable.
                                      ElementAt(int.Parse(value)).InnerText;
                                }
                                break;

                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            return value;
        }

And how to call it:
var valueQuantity = XLGetCellValue(fileName, "Sheet1", "Q" + index);

Code for inserting data in DB already posted.

Enjoy.

Posted 7-Jan-13 1:31am

Vlad-Dragos

Updated 7-Jan-13 1:32am

v2

Comments

apr1234 7-Jan-13 8:26am

thanks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

giri001 · Accepted Answer · 2013-01-07T02:55:00

Hi,i tested its working like charm, just created this DEMO as ur requirement:
and if u have any problem plz let me know.
and on .aspx page :

C#

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
   <div><table style="height: 70px; width: 404px;"><tbody>
<tr>         <td class="style1">Select Date :</td>         <td class="style2"></td>         </tr>
<tr>         <td class="style1">Upload Excel File :</td>         <td class="style2">

<asp:fileupload id="FileUpload1" runat="server" xmlns:asp="#unknown"></asp:fileupload></td>         </tr>
</tbody></table></div><asp:button id="btnUpload" onclick="btnUpload_Click" runat="server" text="Upload Excel File" xmlns:asp="#unknown">
    </asp:button>
<asp:gridview id="ExcelGridView" runat="server" xmlns:asp="#unknown">
    </asp:gridview>

    </form>
</body>
</html>
</html>

on .cs page :

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class ReadDataFromExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.FileName.ToString() != "" && FileUpload1.ToString().Contains("."))
        {
            String filepath = Server.MapPath("UploadedFiles");
            FileUpload1.SaveAs(filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
            string excelPath = (filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
            var dataset = new DataTable();
             dataset = GetExcelData(excelPath); ;
            ExcelGridView.DataSource = GetExcelData(excelPath);
            ExcelGridView.DataBind();
            foreach (DataRow row in dataset.Rows)
            {                                
                                
                System.Data.SqlClient.SqlConnection sqlConnection1 =
    new System.Data.SqlClient.SqlConnection("Data Source=GIRIJESH-PC;Initial Catalog=TestData;Integrated Security=True");

               string temp1 = "'"+Convert.ToString(row[0])+"'";
                string temp2 = "'"+Convert.ToString(row[1])+"'";
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                string text = "INSERT INTO [TestData].[dbo].[UserInfo]([Age],[State])VALUES(" + temp1 + "," + temp2 + ")";
                cmd.CommandText = text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                cmd.ExecuteNonQuery();
                sqlConnection1.Close();
            }

        }  
    }
   public DataTable GetExcelData(string ExcelFilePath)
    {
        string OledbConnectionString = string.Empty;
        OleDbConnection objConn = null;
        OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
        objConn = new OleDbConnection(OledbConnectionString);

        if (objConn.State == ConnectionState.Closed)
        {
            objConn.Open();
        }

        OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1$B1:C4]", objConn);//i have 3 column so just want to take 2 column.
        OleDbDataAdapter objAdapter = new OleDbDataAdapter();
        objAdapter.SelectCommand = objCmdSelect;
        DataSet objDataset = new DataSet();
        objAdapter.Fill(objDataset, "ExcelDataTable");
        objConn.Close();
        return objDataset.Tables[0];

    }

}