Import Excel to Sql Server C #

Question

Import Excel to Sql Server C #

Navigation

#1 by (5 votes)

5

I need to import a excel file into a SQL table. However, I need to pick up the lines and go through them, since it will not be all the information I will get from excel. I have exactly what I need but get TXT file, but the information that I need to get in excel are the same. I wanted to adapt this code for Excel.

My code with txt file:

public void importarTXTtoSQL()
        {
            double vair = 0;
            int counter = 0;

            string path = Server.MapPath("importados/" + Label2.Text);
            // System.IO.StreamReader file = new System.IO.StreamReader(path);
            string[] colunas;
            double cont = 2;
            double var2 = 10000;

            vair = 49 / var2;
            string[] lines = System.IO.File.ReadAllLines(path);

            int numero_linha = 0;

            foreach (string line in lines)
            {
                // Use a tab to indent each line of the file.
                // l.Text+=("\t" + line);

                numero_linha++;

                string linha = line;

                linha = line.Replace("'", "");

                if (numero_linha > 1)
                {
                    int tamanho_linha = line.Length;

                    string comeco_linha = linha.Substring(4, 4);
                    if (comeco_linha == "4468")
                    {
                        string numero_tel = "";
                        string data_ligacao = "";
                        string[] uf;
                        string cod_operadora = "";
                        string numero_tel_chamado = "";
                        string duracao = "";
                        string descricao_categoria = "";
                        string horario_ligacao = "";
                        string valor_ligacao = "";

                        colunas = linha.Split(';');

                        if (colunas.Length > 7)
                        {
                            data_ligacao = inverte2(colunas[29]);
                            descricao_categoria = colunas[30];
                            cod_operadora = colunas[7];
                            numero_tel = colunas[8];
                            horario_ligacao = colunas[36];
                            numero_tel_chamado = colunas[35];
                            duracao = colunas[37];
                            valor_ligacao = colunas[39];

                            uf = descricao_categoria.Split('/');

                            string strsql = "";

                            strsql = "INSERT INTO [SISTEMAS].[dbo].[TEMP_TELEFONIA] " +
                    "([ITEM0],[ITEM1],[ITEM2] ,[ITEM3] ,[ITEM4],[ITEM5],[ITEM6],[ITEM7])" +
              "VALUES('" + data_ligacao + "','" + horario_ligacao + "','" + numero_tel + "','" + descricao_categoria + " Cód. Operadora: " + cod_operadora + "','" + uf[1] + "','" + numero_tel_chamado + "','" + duracao + "','" + valor_ligacao + "')";


                            string strconn = ConfigurationManager.ConnectionStrings["ConnIntranet"].ConnectionString;
                            SqlConnection objConn = new SqlConnection(strconn);
                            try
                            {
                                SqlCommand objCMD2 = new SqlCommand(strsql, objConn);
                                SqlDataReader objInserir;

                                objConn.Open();
                                objInserir = objCMD2.ExecuteReader();
                                objConn.Close();

                                if (cont >= 49)
                                {
                                    cont = 49;
                                }
                                else
                                {
                                    cont = cont + vair;
                                    Session["Status"] = cont;
                                }
                            }
                            catch (Exception erro)
                            {
                                if (objConn.State == ConnectionState.Open)
                                {
                                    objConn.Close();
                                }
                                lblaviso.Text = "Erro ao Salvar o arquivo - " + erro;
                            }
                        }
                    }

                    counter++;
                }
            }
        }

How can I do this?

My code so far with excel:

public void ImportarExceltoSQL()
        {
                string path = Server.MapPath("importados/" + Label2.Text);
                System.IO.StreamReader file = new System.IO.StreamReader(path);

                Microsoft.Office.Interop.Excel.Application appExcel;
                Microsoft.Office.Interop.Excel.Workbook workbook;
                Microsoft.Office.Interop.Excel.Range range;
                Microsoft.Office.Interop.Excel._Worksheet worksheet;

                appExcel = new Microsoft.Office.Interop.Excel.Application();
                workbook = appExcel.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1];
                range = worksheet.UsedRange;

                int rowCount = range.Rows.Count;
                int colCount = range.Columns.Count;
}

sql-server c# excel

asked by anonymous 05.04.2016 / 21:26

1 answer

Why is strcpy () unsafe? How do I transpose a search result using SQL Server?

score 5 · Accepted Answer

Using the NuPet EPPlus package :

var arquivo = new FileInfo(Server.MapPath("importados/" + Label2.Text));

using (var package = new ExcelPackage(arquivo))
{
    // Obtendo o Workbook
    var workbook = package.Workbook;
    if (workbook != null)
    {
        if (workBook.Worksheets.Count > 0)
        {
            // Obtendo a primeira página do Workbook
            var primeiraPlanilha = workbook.Worksheets.First();

            // Lendo a primeira célula
            object primeiraCelula = primeiraPlanilha.Cells[1, 1].Value;

            /* Coloque sua lógica aqui */ 
        }
    }
}

In your case, it would look something like this:

    public void importarTXTtoSQL()
    {
        var arquivo = new FileInfo(Server.MapPath("importados/" + Label2.Text));

        using (var package = new ExcelPackage(arquivo))
        {
            // Obtendo o Workbook
            var workbook = package.Workbook;
            if (workbook != null)
            {
                if (workBook.Worksheets.Count > 0)
                {
                    var primeiraPlanilha = workbook.Worksheets.First();

                    for (int i = 1; i < worksheet.Dimension.End.Row; i++)
                    {
                        data_ligacao = inverte2(primeiraPlanilha.Cells[i, 29].Value);
                        descricao_categoria = primeiraPlanilha.Cells[i, 30];
                        cod_operadora = primeiraPlanilha.Cells[i, 7];
                        numero_tel = primeiraPlanilha.Cells[i, 8];
                        horario_ligacao = primeiraPlanilha.Cells[i, 36];
                        numero_tel_chamado = primeiraPlanilha.Cells[i, 35];
                        duracao = primeiraPlanilha.Cells[i, 37];
                        valor_ligacao = primeiraPlanilha.Cells[i, 39];

                        /* E assim por diante */
                    }