Export data sql server to excel C # window form

Question

Export data sql server to excel C # window form

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I am exporting data from a database in Sql Server to an Excel. I'm doing it in C # of Windows Forms type.

If you export the information to me, but the columns of my table do not generate them and also some fields it puts as true as it is shown in this image:

Exception from HRESULT: 0x800A03EC

pointing to this line of code:

xlWorkSheet.Cells[i, j] = miTabla.Columns[i].ColumnName.ToString();

This is the code:

    private void button1_Click(object sender, EventArgs e)
    {



        SqlConnection cnn;
        string connectionstring = null;
        string sql = null;
      string data = null;

        int i = 1;
        int j = 1;

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;


        xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        connectionstring = "server=192.168.105.150\sql2012;    database=Credito;         user id=sa;                   password=DBServ@14;";
        cnn = new SqlConnection(connectionstring);
        cnn.Open();


        sql = "SELECT top 1 * FROM credito..cuotacredito"; 

        SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
        DataSet ds = new DataSet();
        dscmd.Fill(ds);

        DataTable miTabla = ds.Tables[0];


        for (i = 1; i < miTabla.Rows.Count; i++)
        {
            xlWorkSheet.Cells[i, j] = miTabla.Columns[i].ColumnName.ToString();

            // Recorrer las columnas de la tabla:
            for (j = 1; j < miTabla.Columns.Count; j++)
            {
                //xlWorkSheet.Cells[i, j] = miTabla.Rows[j][i].ToString();
                xlWorkSheet.Cells[i, j].Value = miTabla.Rows[j][i].ToString();
            }

        }







        /*
   for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
   {
       for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
       {

           data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
           data1 = ds.Tables[0].Columns[j].ToString();
           xlWorkSheet.Cells[i + 2, j + 1] = data+data1;

       }

   } */


        xlApp.Visible = true;
    }

c# windows-forms

asked by PieroDev 28.02.2017 в 18:30

source

2 answers

0

After this line:

dscmd.Fill(ds);

Create a variable of type Datatable like this:

// Esta es la tabla que exportarás a Excel.
DataTable miTabla = ds.Tables[0];

And modify your code in a similar way to this pseudo-code:

// Recorrer las filas de la tabla.
for (int filas = 1; filas < miTabla.Rows.Count; filas++) {

    // Recorrer las columnas de la tabla:
    for (int cols = 1; cols < miTabla.Columns.Count; cols++) {

        // En la celda de la hoja de Excel coloco el valor de esa celda del DataTable:
        xlWorkSheet.Cells[filas, cols].Value = miTabla.Rows[cols][filas].ToString();
    }   
}

NOTE: In Excel, the index does not exist in 0, therefore, the first   position is 1.

Source

answered by 28.02.2017 в 21:03

Do not run an ActionResult when I refresh the page get data from a table, transact sql

score 0 · Accepted Answer

What is failing and the rows and codes were not shown is because it was not showing and also adding this code:

for (int i = 1; i < miTabla.Columns.Count + 1; i++)
{    
    xlWorkSheet.Cells[1, i] = miTabla.Columns[i - 1].ColumnName;
}

for (int j = 0; j < miTabla.Rows.Count; j++)
{
    for (int k = 0; k < miTabla.Columns.Count; k++)
    {
        xlWorkSheet.Cells[j + 2, k + 1] = miTabla.Rows[j].ItemArray[k].ToString();       
    }
}

xlApp.Visible = true;


private void button1_Click(object sender, EventArgs e)
{
    SqlConnection cnn;
    string connectionstring = null;
    string sql = null;
    string data = null;

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    connectionstring = "server=192.168.105.150\sql2012;    database=Credito;         user id=sa;                   password=DBServ@14;";
    cnn = new SqlConnection(connectionstring);
    cnn.Open();   

    sql = "SELECT top 1 * FROM credito..cuotacredito"; 

    SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
    DataSet ds = new DataSet();
    dscmd.Fill(ds);

    DataTable miTabla = ds.Tables[0];

    for (int i = 1; i < miTabla.Columns.Count + 1; i++)
    {
        xlWorkSheet.Cells[1, i] = miTabla.Columns[i - 1].ColumnName;
    }

    for (int j = 0; j < miTabla.Rows.Count; j++)
    {
        for (int k = 0; k < miTabla.Columns.Count; k++)
        {
            xlWorkSheet.Cells[j + 2, k + 1] = miTabla.Rows[j].ItemArray[k].ToString();
        }
    }

    xlApp.Visible = true;
}