Database

Quote:

protected DataTable ExportDataFromSQLServer()
{
DataTable dataTable = new DataTable();

using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX"))
{
connection.Open();

// Define the query to be performed to export desired data
SqlCommand command = new SqlCommand("select * from BHVUsers", connection);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);

var excelApplication = new Excel.Application();
var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing);

DataColumnCollection dataColumnCollection = dataTable.Columns;

for (int i = 1; i <= dataTable.Rows.Count + 1; i++)
{
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
if (i == 1)
excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString();
else
excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString();
}
}

excelApplication.Cells[1, 2] = "Voornaam";
excelApplication.Cells[1, 3] = "Achternaam";
excelApplication.Cells[1, 4] = "Geboorte Datum";
excelApplication.Cells[1, 5] = "Porto nummer";
excelApplication.Cells[1, 6] = "EHBO behaald";
excelApplication.Cells[1, 7] = "EHBO";
excelApplication.Cells[1, 8] = "BHV";
excelApplication.Cells[1, 9] = "Regisseur";
excelApplication.Cells[1, 10] = "Verlopen EHBO";
excelApplication.Cells[1, 11] = "Laatste herhaling EHBO";
excelApplication.Cells[1, 12] = "EHBO nummer";
excelApplication.Cells[1, 13] = "Verdieping";
excelApplication.Cells[1, 14] = "Ruimte";
excelApplication.Cells[1, 15] = "Email";
excelApplication.Cells[1, 16] = "Mobiel";
excelApplication.Cells[1, 17] = "Opmerking";
excelApplication.Cells[1, 18] = "BHV_Behaald";
excelApplication.Cells[1, 19] = "BHV_Verlopen";
excelApplication.Cells[1, 20] = "Laatste_Herhaling_BHV";
excelApplication.Cells[1, 21] = "Ruisnaam";
excelApplication.Cells[1, 22] = "Regisseur_Verlopen";
excelApplication.Cells[1, 23] = "Regisseur_Behaald";
excelApplication.Cells[1, 24] = "Regisseur_Herhaling";
excelApplication.Cells[1, 25] = "Active_no_yes";

excelApplication.Cells[1, 2].Font.Bold = true;
excelApplication.Cells[1, 3].Font.Bold = true;
excelApplication.Cells[1, 4].Font.Bold = true;
excelApplication.Cells[1, 5].Font.Bold = true;
excelApplication.Cells[1, 6].Font.Bold = true;
excelApplication.Cells[1, 7].Font.Bold = true;
excelApplication.Cells[1, 8].Font.Bold = true;
excelApplication.Cells[1, 9].Font.Bold = true;
excelApplication.Cells[1, 10].Font.Bold = true;
excelApplication.Cells[1, 11].Font.Bold = true;
excelApplication.Cells[1, 12].Font.Bold = true;
excelApplication.Cells[1, 13].Font.Bold = true;
excelApplication.Cells[1, 14].Font.Bold = true;
excelApplication.Cells[1, 15].Font.Bold = true;
excelApplication.Cells[1, 16].Font.Bold = true;
excelApplication.Cells[1, 17].Font.Bold = true;
excelApplication.Cells[1, 18].Font.Bold = true;
excelApplication.Cells[1, 19].Font.Bold = true;
excelApplication.Cells[1, 20].Font.Bold = true;
excelApplication.Cells[1, 21].Font.Bold = true;
excelApplication.Cells[1, 22].Font.Bold = true;
excelApplication.Cells[1, 23].Font.Bold = true;
excelApplication.Cells[1, 24].Font.Bold = true;
excelApplication.Cells[1, 25].Font.Bold = true;

int k = 0;

for (k = 1; k <= 23; k++) // this will aply it form col 1 to 10
{
excelApplication.Columns[k].ColumnWidth = 30;
}

Excel.Range formatRange; formatRange = excelApplication.get_Range("a1", "y1");
formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
//excelApplication.Cells[1,1] = "LightBlue";

formatRange = excelApplication.get_Range("a1", "y1");
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

// Save the excel file at specified location
excelApplication.ActiveWorkbook.SaveCopyAs(@"C:\tmp\test1.xlsx");

excelApplication.ActiveWorkbook.Saved = true;

MessageBox.Show("Excel file created , you can find the file " + "c:\\tmp\\test1.xlsx");
System.Diagnostics.Process.Start("c:\\tmp\\test1.xlsx");
// Close the Excel Application
excelApplication.Quit();

connection.Close();

//Release or clear the COM object
releaseObject(excelWorkBook);
releaseObject(excelApplication);
}