Chaitanya

Chaitanya

  • NA
  • 58
  • 57k

Best way to store the required Excel Sheet Data

Nov 12 2011 12:16 AM
Hi all i am having a requirement to get required data from the excel sheet and to store it to DB. I have a written a piece of code to extract the data but i think this is a bit lengthier as per my concern so i am looking for an alternate way to store the data initially before saving it to DB can any one help me..

The sample data from my excel data that i need to extract is as follows



There was also another data which i need to extract from the same file which will start from A53-G63 the data is as below



I have written some thing like below to extract each and every cell values

Microsoft.Office.Interop.Excel.Application ExcelObj = null;

        ExcelObj = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(strFilename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

        Microsoft.Office.Interop.Excel.Range rangeAtRisk = worksheet.get_Range("B12", "G12");
        System.Array myvalues = (System.Array)rangeAtRisk.Cells.Value2;
        lstAtRisk = AnyListToStrList(myvalues);

        Microsoft.Office.Interop.Excel.Range rangeEconomical = worksheet.get_Range("B13", "G13");
        System.Array myvaluesEconomical = (System.Array)rangeEconomical.Cells.Value2;
        lstEconomical = AnyListToStrList(myvaluesEconomical);

        Microsoft.Office.Interop.Excel.Range rangeEnglish = worksheet.get_Range("B14", "G14");
        System.Array myvaluesEnglish = (System.Array)rangeEnglish.Cells.Value2;
        lstEnglish = AnyListToStrList(myvaluesEnglish);

        Microsoft.Office.Interop.Excel.Range rangeImmigrant = worksheet.get_Range("B15", "G15");
        System.Array myvaluesImmigrant = (System.Array)rangeImmigrant.Cells.Value2;
        lstImmigrant = AnyListToStrList(myvaluesImmigrant);

        Microsoft.Office.Interop.Excel.Range rangeMigrant = worksheet.get_Range("B16", "G16");
        System.Array myvaluesMigrant = (System.Array)rangeMigrant.Cells.Value2;
        lstMigrant = AnyListToStrList(myvaluesMigrant);


        Microsoft.Office.Interop.Excel.Range range1 = worksheet.get_Range("C11", "G11");
        System.Array myvalues1 = (System.Array)range1.Cells.Value2;
        lstYear = AnyListToStrList(myvalues1);

        Microsoft.Office.Interop.Excel.Range rangeBilingual = worksheet.get_Range("C11", "G11");
        System.Array myBilingual = (System.Array)rangeBilingual.Cells.Value2;
        lstBilingual = AnyListToStrList(myvalues1);

        Microsoft.Office.Interop.Excel.Range rangeCareer = worksheet.get_Range("C11", "G11");
        System.Array myCareer = (System.Array)rangeCareer.Cells.Value2;
        lstCareer = AnyListToStrList(myCareer);

        Microsoft.Office.Interop.Excel.Range rangeEnglish1 = worksheet.get_Range("C11", "G11");
        System.Array myEnglish1 = (System.Array)rangeEnglish1.Cells.Value2;
        lstEnglishSecond = AnyListToStrList(myEnglish1);

        Microsoft.Office.Interop.Excel.Range rangeGifted = worksheet.get_Range("C11", "G11");
        System.Array myGifted = (System.Array)rangeGifted.Cells.Value2;
        lstGited = AnyListToStrList(myGifted);

        Microsoft.Office.Interop.Excel.Range rangeSpecialEdu = worksheet.get_Range("C11", "G11");
        System.Array mySpecialEdu = (System.Array)rangeSpecialEdu.Cells.Value2;
        lstSpecailEducation = AnyListToStrList(mySpecialEdu);

But as far as i think this is lengthy so i am looking for an alternative for the best method to store

Answers (1)