Массовая вставка из Excel в SQL для выборочных полей на основе расположения ячейки

Вы можете напечатать простым способом.

Используйте ниже для печати 2D-массива

int[][] array = new int[rows][columns];
System.out.println(Arrays.deepToString(array));

Используйте ниже для печати 1D-массива

int[] array = new int[size];
System.out.println(Arrays.toString(array));
3
задан Hadi 20 March 2019 в 00:31
поделиться

2 ответа

Как я уже упоминал в комментариях, я думаю, что написание сценария ac #, который считывает данные из ячеек Excel и группирует их в список или DataTable, а затем выполняет групповую вставку один раз, будет более производительным [1117 ]

Приложение C / задача скрипта

Необходимые сборки

Сначала необходимо импортировать сборку Excel Interop:

using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

Преобразовать букву заголовка столбца в индекс

Теперь вы должны определить следующую функцию, которая преобразует алфавит столбца Excel в индекс:

private int ParseColHeaderToIndex(string colAdress)
{
    int[] digits = new int[colAdress.Length];
    for (int i = 0; i < colAdress.Length; i++)
    {
        digits[i] = Convert.ToInt32(colAdress[i]) - 64;
    }
    int mul = 1;
    int res = 0;
    for (int pos = digits.Length - 1; pos >= 0; pos--)
    {
        res += digits[pos] * mul;
        mul *= 26;
    }
    return res;
}

Функция массовой вставки SQL

Следующая функция предназначена для выполнения операции массовой вставки в SQL [ 1120]

public void InsertToSQLUsingSQLBulk(System.Data.DataTable dt, string connectionstring, string Tablename)
{


    try
    {
        using (var bulkCopy = new SqlBulkCopy(connectionstring, SqlBulkCopyOptions.KeepIdentity))
        {

            foreach (DataColumn col in dt.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            bulkCopy.BulkCopyTimeout = 600;
            bulkCopy.DestinationTableName = Tablename;
            bulkCopy.WriteToServer(dt);
        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Чтение из Excel в целевую таблицу DataTable

Следующая функция принимает путь Excel и диапазоны DataTable в качестве параметра и возвращает DataTable со структурой назначения (Id, AttributeKey, AttributeValue )

public System.Data.DataTable ReadFromExcel(System.Data.DataTable dtRanges,string strPath)
{

    string num = "0123456789";

    //Declare result datatable  
    System.Data.DataTable destination = new System.Data.DataTable();
    destination.Columns.Add("Id");
    destination.Columns.Add("AttributeKey");
    destination.Columns.Add("AttributeValue");

    //Decalre Interop Objects
     Microsoft.Office.Interop.Excel.Application m_XlApp;
     m_XlApp = new Microsoft.Office.Interop.Excel.Application();
     m_XlApp.Visible = false;
     m_XlApp.DisplayAlerts = false;

     Workbook xlWbs = null;
     xlWbs = m_XlApp.Workbooks.Open(strPath, 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);

    xlWbs.DoNotPromptForConvert = true;
    xlWbs.CheckCompatibility = false;
    xlWbs.Application.DisplayAlerts = false;

    //Loop over worksheets
    foreach (Worksheet xlWks in xlWbs.Worksheets) {

        string Name = xlWks.Name;

        //Assing rows relevant to the current sheet

        foreach (DataRow drRow in dtRanges.AsEnumerable().Where(x => x["Sheet_Name"].ToString() == Name))
        {

            string sheet = drRow["Sheet_Name"].ToString();
            string range = drRow["Location_Value"].ToString();
            string field = drRow["Field_Name"].ToString();
            string id = drRow["Id"].ToString();
            string rangeAlpha = range.Split(':')[0];
            int rowidx = 0;
            int colidx = 0;



            foreach (char chr in num) { 
                rangeAlpha = rangeAlpha.Replace(chr, '\0');
            }

            rowidx = Int32.Parse(range.Split(':')[0].Replace(rangeAlpha, ""));
            colidx = ParseColHeaderToIndex(rangeAlpha);


            DataRow dr = destination.NewRow();

            if (xlWks.Cells[rowidx, colidx] != null && (xlWks.Cells[rowidx, colidx] as Range).Value2 != null)
            {

                dr["AttributeValue"] = (string)(xlWks.Cells[rowidx, colidx] as Range).Value2;
            }
            else
            {
                dr["AttributeValue"] = "";
            }



            dr["AttributeKey"] = drRow["Field_Name"].ToString();
            dr["Id"] = drRow["Id"].ToString();

            destination.Rows.Add(dr);
        }

    }

    xlWbs.Close(false, Type.Missing, Type.Missing);
    m_XlApp.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWbs);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_XlApp);


    return destination;

}

Основная программа

public void Main(){

    //Initialize ranges table
    System.Data.DataTable ranges = new System.Data.DataTable();
    ranges.Columns.Add("Id");
    ranges.Columns.Add("Field_Name");
    ranges.Columns.Add("Location_Value");
    ranges.Columns.Add("Sheet_Name");

    //Add rows or read them from database using SQLDataAdapter


    //note that the destination table must exists in the database with identical columns of datatable

    System.Data.DataTable destination = ReadFromExcel(ranges, "C:\\1.xlsx", "dbo.destination");

    InsertToSQLUsingSQLBulk(destination, "Pass SQL Server destination connection string here");



}
<час>

Обновление 1 - повышение производительности

Вы можете улучшить Производительность метода заключается в размещении всего содержимого рабочего листа в двухмерном массиве, а затем в цикле по массиву, а не в цикле внутри рабочего листа Excel.

Excel.Range targetCells = xlWks.UsedRange;
object[,] allValues = (object[,])targetCells.Cells.Value;

...


 if (targetCells.Cells[rowidx, colidx] != null)
 {

     dr["AttributeValue"] = (string)(targetCells.Cells[rowidx, colidx] as Range).Value2;
  }
  else
  {
     dr["AttributeValue"] = "";
  }

Справочник

0
ответ дан Hadi 20 March 2019 в 00:31
поделиться

Как насчет создания одного оператора select для запуска, который получает все записи одновременно.

На основе вашего изображения, что-то вроде этого:

select
 (Select [Field1] from [GenDet$I3:I3]) as Field1
,(Select [Field2] from [GenDet$I4:I4]) as Field2
...

Это был горизонтальный и столбец.

или вы можете пойти вертикально с

 (Select [FieldName],[Field1] as Value from [GenDet$I3:I3]) as Field1
union all
 (Select [Field2],* from [GenDet$I4:I4]) as Field2
...

Я знаю, что их около 3000 или около того, но вы можете построить это с помощью запроса конкатенации строк довольно просто.

Просто мысль.

Это уменьшит время выполнения, так как электронная таблица не открывается и не закрывается на каждой итерации.

0
ответ дан KeithL 20 March 2019 в 00:31
поделиться
Другие вопросы по тегам:

Похожие вопросы: