Creating Excel from Database

Question

I want to make Excel report when i click certain button. The data in Excel shows Transaction Number(Received ID) and the items(Material Name). One transaction number could contain one or more items. I have already code to make Excel report. But it's always error. Here is the code :

SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "Save Report";
sfd.FileName = ("Transaction History Report From").Replace("/","-"); // ganti slah jadi strip
sfd.Filter = "Excel FIle| *.xlsx";

/*
 * open dialog
 * -misValue pakai System.Reflection.Missing
 * -Excel.Range
 * -Excel : app,workbook,worksheet
 * -tarik data
 * -isi ke excel
 * 
 */
int row;
if (sfd.ShowDialog() == DialogResult.OK)
{
    //show dialog berhasil
    object misValue = System.Reflection.Missing.Value;
    Excel.Range rng;

    Excel.Application app = new Excel.Application();
    Excel.Workbook wb = app.Workbooks.Add(misValue); // bikin workbook
    Excel.Worksheet ws = wb.Worksheets.get_Item(1); // nikin worksheet

    rng = ws.get_Range("A1:I1");
    rng.Merge();
    rng.Value = "Transaction History Report From" ;
    rng.Font.Bold = true;
    rng.Font.Size = 21;
    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

    row = 3;
    ws.Cells[row, 1] = "TransactionID";
    ws.Cells[row, 2] = "Material";

    DataTable dttrID = con.executeSelect("SELECT receivedID FROM TrInventoryReceived WHERE receivedDate BETWEEN '" + dateTimePicker1.Value + "' AND '" + dateTimePicker2.Value + "'");
    row++;
    for (int i = 0; i < dttrID.Rows.Count; i++) //looping sebanyak transaksi yang ada
    {
        String transactionID = dttrID.Rows[i][0].ToString();
        DataTable material = con.executeSelect("SELECT materialID FROM TrStock WHERE receivedID ='" + transactionID + "'");

        ws.Cells[row + 4, 1] = transactionID;
        row++;
        for (int j = 0; j < material.Rows.Count; j++) //mengulang sebanyak data yang ada di detail transaction
        {
            DataTable Materianame = con.executeSelect("SELECT materialName FROM MsMaterial WHERE materialID ='" + material.Rows[j][0].ToString() + "'");

                ws.Cells[row + 4 + j, 2] = Materianame.Rows[0][0].ToString();
        }
    }

    ws.Columns.AutoFit();//buat nyamain uk7ran cellnyq sama kontenya

    wb.SaveAs(sfd.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    wb.Close(true, misValue, misValue);
    app.Quit();

    releaseObject(ws);
    releaseObject(wb);
    releaseObject(app);    
    MessageBox.Show("Flie created");
}

The error message says "There is no row at position 0"

Also, i have Class Connection to connect to my database. Here is the code:

class Connect
{
    SqlConnection con;
    public Connect()
    {
        String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + @"\Database1.mdf;Integrated Security=True;User Instance=True";
        con = new SqlConnection(connectionString);
    }

    public DataTable executeSelect(String query)
    {
        con.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        con.Close();

        return dt;
    }

    public void execute(String query)
    {

        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.ExecuteNonQuery();

        con.Close();
    }
}

My FOrm Top DatagGridVIew is TrInventoryReceived, bottom is TrStock Table

Result sample in Excel enter image description here

Can anyone help where the error is located in my code and how do i fix this?

Answer 1

Maybe this could help you. It's a method which exports a table from a database and saves it as an excel-file. It uses a SqlConnection to connect to the database. When it reads the data it writes them to the excel-file at the same time.

This method exports the data to the excel-file:

public static void DataBaseToExcel(string connectionString, string table, string saveAs)
    {
        Excel.Application app = new Excel.Application();
        Excel.Workbook workbook = app.Workbooks.Add(System.Reflection.Missing.Value);
        app.ActiveWindow.DisplayGridlines = false;
        Excel.Worksheet worksheet = workbook.Worksheets.Item[1];
        List<string> ColumnsNames = GetColumnsNames(connectionString, table);
        int Row = 2;
        for (int i = 0; i != ColumnsNames.Count; i++)
        {
            worksheet.Cells[1, i + 1].Value = ColumnsNames[i];
        }
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string commandString = "SELECT * FROM " + table;
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = commandString;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int Column = 0; Column != ColumnsNames.Count; Column++)
                        {
                            worksheet.Cells[Row, Column + 1] = reader.GetValue(Column);
                        }
                        Row++;
                    }
                }
            }
            connection.Close();
        }
        workbook.SaveAs(saveAs);
        app.Quit();
    }

It additionally contains the headers of the columns, which I retrieve with this method:

public static List<string> GetColumnsNames(string connectionString, string table)
    {
        List<string> columnNames = new List<string>();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name = '" + table + "' and t.type = 'U'";
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columnNames.Add(reader.GetString(0));
                    }
                }
                connection.Close();
            }
        }
        return columnNames;
    }

Maybe you have to modify these methods for your purpose.