Member Avatar for dummy C#

I'm trying to export an excel file to my sql database. But not all the column inside the excel file i need. So, i just need some selected column for input to the database. But now i still cant finish with the code. HELP ME PLS.(red color word is where my problem)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Runtime.InteropServices;


namespace testingpurpose
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        

  
         
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFD = new OpenFileDialog();

            openFD.CheckFileExists = true;
            openFD.InitialDirectory = "C:";
            openFD.Title = "Open a File";
            openFD.FileName = "";
            openFD.Multiselect = false;
            openFD.Filter = "All Files (*.*)|*.*|Excel Files (*.xls)|*.xls|CSV Files (*.csv*)|*.csv";

            if (openFD.ShowDialog() == DialogResult.OK)
            {
                this.txtPath.Text = openFD.FileName;
         
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (txtPath.Text == "")
            {
                MessageBox.Show("Please select a file");
            }
            else
            {
                String filename = txtPath.Text;
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workBook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Open(filename,
                                                      0,
                                                      true,
                                                      5,
                                                      "",
                                                      "",
                                                      true,
                                                      Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                                                      "\t",
                                                      false,
                                                      false,
                                                      0,
                                                      true,
                                                      1,
                                                      0);
                Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(1);
                Microsoft.Office.Interop.Excel.Range range;

              
                

                range = workSheet.UsedRange;

                Microsoft.Office.Interop.Excel.Range column = (Microsoft.Office.Interop.Excel.Range)workSheet.get_Range("C1", "I1");
                MessageBox.Show(column.Value2.ToString());


               workBook.Close(true, null, null);
               app.Quit();

                releaseObject(workSheet);
                releaseObject(workBook);
                releaseObject(app);


               
              }
        }

             private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
}
}
  • 2 Contributors
  • 3 Replies
  • 399 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

Short note up front: started with Excel Interop and correctly pointed at a range issue, but a more robust pattern is to avoid relying on Excel Interop for data import (Interop is fragile and unsupported for unattended/server use). Use a driver or a small reader library instead. (support.microsoft.com)

Two practical options that solve "only these columns" cleanly:

  • OleDb (ACE or Jet): treat the workbook as a data source and use SQL to select only the columns you want (HDR=YES uses header names; HDR=NO produces F1,F2,...). Watch IMEX=1 and provider bitness — the ACE provider must match your app platform. (connectionstrings.com)
  • ExcelDataReader (recommended for desktop/server code that just needs to read cells): no external COM, supports xls/xlsx, and lets you filter columns while reading. (github.com)

Example (ExcelDataReader — read only columns C and L, then insert into MySQL). C is zero-based index 2, L is 11:

// using ExcelDataReader, ExcelDataReader.DataSet, MySql.Data.MySqlClient
using(var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
using(var reader = ExcelReaderFactory.CreateReader(stream))
{
    var ds = reader.AsDataSet(new ExcelDataSetConfiguration {
        ConfigureDataTable = _ => new ExcelDataTableConfiguration {
            UseHeaderRow = true,
            FilterColumn = (r, colIndex) => colIndex == 2 || colIndex == 11
        }
    });

    var table = ds.Tables[0]; // first sheet
    using(var conn = new MySqlConnection(mysqlConn))
    using(var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "INSERT INTO mytable (colC, colL) VALUES (@c,@l)";
        cmd.Parameters.Add("@c", MySqlDbType.VarChar);
        cmd.Parameters.Add("@l", MySqlDbType.VarChar);
        foreach(DataRow row in table.Rows)
        {
            cmd.Parameters["@c"].Value = row[0] ?? DBNull.Value;
            cmd.Parameters["@l"].Value = row[1] ?? DBNull.Value;
            cmd.ExecuteNonQuery();
        }
    }
}

A few troubleshooting tips: confirm the sheet name (SheetName$) and HDR setting — with HDR=NO the provider names columns F1..Fn. For large imports use transactions or MySQL bulk-loading. If you must keep Interop for a quick desktop tool, limit Excel instances, release COM objects and run it only interactively. ()

Recommended Answers

All 3 Replies

Member Avatar for Tank50

Hi

I think Error is in Microsoft.Office.Interop.Excel.Range column = (Microsoft.Office.Interop.Excel.Range)workSheet.get_Range("C11", "I1");

If u change it into C11 then it works,just use like this
(Microsoft.Office.Interop.Excel.Range)workSheet.get_Range("C11", "C11");

Member Avatar for dummy C#

Tank50, thx for ur reply
C and L is the column that i need only from the excel file starting from A till L column. so i dont know which code should i use other than get_Range

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.