如何在C＃windows应用程序中读取特定的Excel数据列并将其存储在SQL数据库中 [英] How to read particular columns of excel data in C# windows application and store it in SQL database

问题描述

这里我只想从excel（.csv）中获取特定列数据，其中我有60列和20行，我需要获取6列并使用c #windows应用程序将其存储在sql数据库中。



我尝试了什么：

Here I want to fetch only particular columns data from excel(.csv) where I have 60 columns and 20 rows from that I need to fetch 6 columns and store it in sql database using c# windows application.

What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using ExcelApp = Microsoft.Office.Interop.Excel;


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

        }

        private void button1_Click(object sender, EventArgs e)
        {

            ExcelApp.Application excelApp = new ExcelApp.Application();

            if (excelApp == null)
            {
                Console.WriteLine("Excel is not installed!!");
                return;
            }

            ExcelApp.Workbook excelBook = excelApp.Workbooks.Open("filepath");
            ExcelApp._Worksheet excelSheet = excelBook.Sheets[1];
            ExcelApp.Range excelRange = excelSheet.Columns[1];
            ExcelApp.Range excelRange1 = excelSheet.Columns[3];
            ExcelApp.Range excelRange2 = excelSheet.Columns[4];
            ExcelApp.Range excelRange3 = excelSheet.Columns[6];
            ExcelApp.Range excelRange4 = excelSheet.Columns[7];
            ExcelApp.Range excelRange5 = excelSheet.Columns[28];
            ExcelApp.Range findRange;
            string strToFind = "DealerID";
            string strToFind1 = "StockNo";
            string strToFind2 = "VIN";
            string strToFind3 = "Make";
            string strToFind4 = "Model";
            string strToFind5 = "DealerName";


            string valueInColumnA1;
            string valueInColumnC1;
            string valueInColumnD1;
            string valueInColumnF1;
            string valueInColumnG1;
            string valueInColumnAB1;

            findRange = excelRange.Find(strToFind);
            findRange = excelRange1.Find(strToFind1);
            findRange = excelRange2.Find(strToFind2);
            findRange = excelRange3.Find(strToFind3);
            findRange = excelRange4.Find(strToFind4);
            findRange = excelRange5.Find(strToFind5);

            if (findRange == null)
            {
                MessageBox.Show("Do not find value " + strToFind + "in Column A1");

            }
            else
            {
                valueInColumnA1 = excelSheet.Cells[findRange.Column,1].VALUE;
                valueInColumnC1 = excelSheet.Cells[findRange.Column,3].VALUE;
                valueInColumnD1 = excelSheet.Cells[findRange.Column,4].VALUE;
                valueInColumnF1 = excelSheet.Cells[findRange.Column,6].VALUE;
                valueInColumnG1 = excelSheet.Cells[findRange.Column,7].VALUE;
                valueInColumnAB1 = excelSheet.Cells[findRange.Column,28].VALUE;
                MessageBox.Show(strToFind + " is in A1" + findRange.Column + "\n"
                     + "Value in Column A1 is :" + valueInColumnA1 + "\n"
                    + "Value in Column C1 is :" + valueInColumnC1 + "\n"
            + "Value in Column D1 is :" + valueInColumnD1 + "\n"
                 + "Value in Column F1 is :" + valueInColumnF1 + "\n"
                  + "Value in Column G1 is :" + valueInColumnG1 + "\n"
                + "Value in Column AB1 is :" + valueInColumnAB1);
            }

        }
    }
}

我到目前为止已经尝试过这个。

先谢谢。

S far I have tried this.
Thanks in Advance.

推荐答案

引用：

忽略如何保存到数据库，但首先给我一些建议，从c＃windows应用程序中的Excel中获取特定列

Ignore how to save it to the database but firstly give me some advice to fetch particular columns from Excel in c# windows application

这里有一些文章将告诉你如何做到这一点

使用C＃使用Excel [ ^ ]

从C＃读取Excel [ ^ ]

在C＃中读取Excel文件（示例） [ ^ ]

Here are some articles that will show you how to do that
Working with Excel Using C#[^]
Reading Excel From C#[^]
Read Excel File in C# (Example)[^]

您的代码在许多地方都不正确。您将findRange设置了六次，因此它将始终包含最后一项。为每一行编写一个循环并从每一行中选择列条目会更容易。类似于：

Your code is not correct in a number of places. You set findRange six times, so it will always contain the very last item. It would be easier to write a loop for each row and select the column entries from each row. Something like:

for (int row = 0; ; ++row)
{
    string field1 = workSheet.Cells[row, 1].Value as string;
    if (string.IsNullOrEmpty(field1))
        break;      // no more entries
    string field3 = workSheet.Cells[row, 3].Value as string;
    string field4 = workSheet.Cells[row, 4].Value as string;
    string field6 = workSheet.Cells[row, 6].Value as string;
    string field7 = workSheet.Cells[row, 7].Value as string;
    string field28 = workSheet.Cells[row, 28].Value as string;
//
// add fields to the database here
//
}

Am answering my own question
It may helpful to someone. 
 
  public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string csv_file_path = "Give your filepath here";
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
DataView dv = new DataView(csvData);
DataTable dt = dv.ToTable(false, "ColumnName");//which particular columns you want to read
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = ".";
csb.InitialCatalog = "Project";
csb.IntegratedSecurity = true;
string connString = csb.ToString();
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "db TableName";
sqlBulkCopy.ColumnMappings.Add("Dealer ID", "DealerID");
sqlBulkCopy.ColumnMappings.Add("Stock", "StockNo");
sqlBulkCopy.ColumnMappings.Add("VIN", "VIN");
sqlBulkCopy.ColumnMappings.Add("Make", "Make");
sqlBulkCopy.ColumnMappings.Add("Model", "Model");
sqlBulkCopy.ColumnMappings.Add("Dealer Name", "DealerName");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn dtcolumn = new DataColumn(column);
dtcolumn.AllowDBNull = true;
csvData.Columns.Add(dtcolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);

}
}

}
catch (Exception)
{
throw;
}
return csvData;

}
}
}

 
Thanks
Srilekha Bolamoni.
Please accept it as an answer if it helps you.

这篇关于如何在C＃windows应用程序中读取特定的Excel数据列并将其存储在SQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！