SQL Query to Excel in C# using Epplus

Question

I have been really struggling with this issue. I am trying to write a strings and column names from a sql query to an excel document. But I only have rows written. How do i fix the code to write all lines and columns?

            var Template = new FileInfo(@"C:\Temp\XLS\New.xlsx");
            var xlPackage = new ExcelPackage(Template);
            var wsCards = xlPackage.Workbook.Worksheets.Add(NAME_WORKSHEET);
            using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
            {
                sqlConn.Open();
                using (SqlCommand command = new SqlCommand(SQL_WORKSHEET, sqlConn))
                {
                    var reader = command.ExecuteReader();
                    int row = 1;

                    DataTable schemaTable = reader.GetSchemaTable();
                    foreach (DataRow rw in schemaTable.Rows)
                    {
                        foreach (DataColumn column in schemaTable.Columns)
                        {
                            if (column.ColumnName == "ColumnName")
                            {
                                wsCards.Cells["A1"].Value = rw[column];
                            }
                        }
                    }
                    while (reader.Read())
                    {
                        row++;
                        for (int col = 1; col <= reader.FieldCount; col++)
                        {
                            wsCards.Cells[row, col].Value = reader.GetValue(col - 1);
                        }
                    }
                    xlPackage.SaveAs(Template);
                    xlPackage.Dispose();
                }
            }

user_15252415 · Answer

            var Template = new FileInfo(@"C:\Temp\XLS\New.xlsx");
            var xlPackage = new ExcelPackage(Template);
            var wsCards = xlPackage.Workbook.Worksheets.Add(NAME_WORKSHEET);
            using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
            {
                sqlConn.Open();
                using (SqlCommand command = new SqlCommand(SQL_WORKSHEET, sqlConn))
                {
                    var reader = command.ExecuteReader();
                    int row = 1,col=1;


                    DataTable schemaTable = reader.GetSchemaTable();
                    //foreach (DataRow rw in schemaTable.Rows)
                    //{
                        // Write the headers to the first row
                        foreach (DataColumn column in schemaTable.Columns)
                        {
                             // Condition Will only be required if you want to write 
                            // specific column names
                            //if (column.ColumnName == "ColumnName")
                            //{ 
                            
                                wsCards.Cells[1,col].Value = rw[column].ColumnName;
                                col++;
                            //}
                        }
                    //}
                    while (reader.Read())
                    {
                        row++;
                        for ( col = 1; col <= reader.FieldCount; col++)
                        {
                            wsCards.Cells[row, col].Value = reader.GetValue(col - 1);
                        }
                    }
                    xlPackage.SaveAs(Template);
                    xlPackage.Dispose();
                }
            }

This code will write the headers from the schema on the first row of the excel sheet

SQL Query to Excel in C# using Epplus

1 Answers