Hi hothorasma,
I have modified your code using my sample database record. Refer the below code with screenshot.
C#
SqlConnection conn = new SqlConnection();
string connectionString = "Server=.;DataBase=Test;UID=sa;PWD=password";
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers");
SqlDataAdapter sda = new SqlDataAdapter();
try
{
conn.ConnectionString = connectionString;
cmd.Connection = conn;
conn.Open();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
string path = "D:\\";
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook objBook;
Microsoft.Office.Interop.Excel.Worksheet objSheets;
object objOpt = System.Reflection.Missing.Value;
objBook = objExcel.Workbooks.Add(objOpt);
objSheets = (Microsoft.Office.Interop.Excel.Worksheet)objExcel.Worksheets.Add(objOpt, objOpt, objOpt, objOpt);
objSheets.Name = "dffsf";
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Microsoft.Office.Interop.Excel.Application xlAppToExport = new Microsoft.Office.Interop.Excel.Application();
xlAppToExport.Workbooks.Add();
// ADD A WORKSHEET.
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetToExport = (Microsoft.Office.Interop.Excel.Worksheet)xlAppToExport.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlWorkSheetToExport.Name = "Result";
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 3;
// SHOW THE HEADER.
xlWorkSheetToExport.Cells[1, 1] = "LAPORAN";
Microsoft.Office.Interop.Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range;
range.EntireRow.Font.Name = "Calibri";
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
xlWorkSheetToExport.Range["A1:D1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "ID";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "GROUP";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "EMAIL";
int i;
//for (i = 0; i <= dt.Columns.Count - 1; i++)
//{
// xlWorkSheetToExport.Cells[iRowCnt, i] = dt.Columns[i].ColumnName.ToString();
//}
iRowCnt++;
for (i = 0; i <= dt.Rows.Count - 1; i++)
{
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = dt.Rows[i][0];
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = dt.Rows[i][1];
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = dt.Rows[i][2];
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Microsoft.Office.Interop.Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Microsoft.Office.Interop.Excel.Range;
//range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// SAVE THE FILE IN A FOLDER.
xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");
//dt.WriteXml("D:/Tests.xls");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
}
}
catch (Exception ex)
{
}
finally
{
sda.Dispose();
sda = null;
}
Screenshot
DataBase

After Export In D Drive
