Click here to Skip to main content
14,305,109 members
   

Database

 
QuestionAccess SQL Server Remotely Via VPN Pin
Kevin Marois20-Aug-19 5:40
professionalKevin Marois20-Aug-19 5:40 
AnswerRe: Access SQL Server Remotely Via VPN Pin
Richard Deeming20-Aug-19 5:56
mveRichard Deeming20-Aug-19 5:56 
QuestionSQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 7:52
professionalKevin Marois19-Aug-19 7:52 
AnswerRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 8:45
mveRichard Deeming19-Aug-19 8:45 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 9:21
professionalKevin Marois19-Aug-19 9:21 
GeneralRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 9:28
mveRichard Deeming19-Aug-19 9:28 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 9:42
professionalKevin Marois19-Aug-19 9:42 
QuestionDate format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling18-Aug-19 22:15
memberjan Meeling18-Aug-19 22:15 
Hi all,

When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990

Please can some one help me in the right way.

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);
}


Thanks
Regards,
Jan Meeling

modified 19-Aug-19 7:40am.

QuestionRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan18-Aug-19 22:54
protectorRichard MacCutchan18-Aug-19 22:54 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling19-Aug-19 1:39
memberjan Meeling19-Aug-19 1:39 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Victor Nijegorodov19-Aug-19 3:20
memberVictor Nijegorodov19-Aug-19 3:20 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan19-Aug-19 4:23
protectorRichard MacCutchan19-Aug-19 4:23 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Victor Nijegorodov19-Aug-19 0:19
memberVictor Nijegorodov19-Aug-19 0:19 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling19-Aug-19 1:43
memberjan Meeling19-Aug-19 1:43 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Eddy Vluggen19-Aug-19 1:12
mveEddy Vluggen19-Aug-19 1:12 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling19-Aug-19 1:55
memberjan Meeling19-Aug-19 1:55 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Eddy Vluggen19-Aug-19 2:03
mveEddy Vluggen19-Aug-19 2:03 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling20-Aug-19 1:02
memberjan Meeling20-Aug-19 1:02 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan20-Aug-19 4:20
protectorRichard MacCutchan20-Aug-19 4:20 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling20-Aug-19 7:39
memberjan Meeling20-Aug-19 7:39 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan20-Aug-19 21:17
protectorRichard MacCutchan20-Aug-19 21:17 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling20-Aug-19 1:03
memberjan Meeling20-Aug-19 1:03 
QuestionProduct Architecture Pin
Chiranjana13-Aug-19 0:50
memberChiranjana13-Aug-19 0:50 
AnswerRe: Product Architecture Pin
David Mujica13-Aug-19 3:50
memberDavid Mujica13-Aug-19 3:50 
GeneralRe: Product Architecture Pin
Chiranjana13-Aug-19 5:05
memberChiranjana13-Aug-19 5:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.