Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
Hi,
 
I have an Execl Sheet (2007) in the following structure.
In the Excel sheet Sno.,Details, State1, State2,Name,College,Address,Department
are headings
 
--------------------------------------------
Sno. | DETAILS | STATE1 | STATE2
--------------------------------------------
1 | NAME | John | Peter
2 | COLLEGE | AAA | BBB
3 | ADDRESS | YY | ZZ
4 | DEPARTMENT | IT | ECE
 

I want to insert STATE1,STATE2 into my database.
Can you please provide table structure for the following execl and
Code to select only STATE1,STATE2 Column and insert STATE1,STATE2 colunm details into the database.
Posted 6-Jan-13 22:39pm
apr1234688
Edited 7-Jan-13 0:41am
v4
Rate this: bad
good
Please Sign up or sign in to vote.

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.
 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.............
  Permalink  
v2
Comments
apr1234 at 7-Jan-13 8:25am
   
thanks...very usefull.
Rate this: bad
good
Please Sign up or sign in to vote.

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:
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.
  Permalink  
v2
Comments
apr1234 at 7-Jan-13 8:26am
   
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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 :
<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 :
 
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];
 
    }
 
}
  Permalink  
v2

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

  Print Answers RSS
0 George Jonsson 359
1 Sergey Alexandrovich Kryukov 355
2 CPallini 335
3 BillWoodruff 324
4 OriginalGriff 237
0 OriginalGriff 5,050
1 CPallini 4,225
2 Sergey Alexandrovich Kryukov 3,639
3 George Jonsson 2,911
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100