0
SqlCommand cmd = new SqlCommand("export_excel", cn);
cmd.CommandType = CommandType.StoredProcedure;

da = new SqlDataAdapter(cmd);

System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);

int k = dt.Columns.Count;
int l = dt.Rows.Count;

if (dt.Rows.Count > 0)
{
    Microsoft.Office.Interop.Excel.ApplicationClass XcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    XcelApp.Application.Workbooks.Add(Type.Missing);

    for (int i = 0; i < k; i++)
    {
        XcelApp.Cells[1, i + 1] = dt.Columns[i].ToString();
    }

    for (int i = 2; i <= l; i++)
    {
        for (int j = 1; j <= k; j++)
        {
            XcelApp.Cells[i, j] = dt.Rows[i - 2][j - 1].ToString();
        }
    }

    XcelApp.Visible = true;
}

My data in SQL Server:

enter image description here

And this is the data in the Excel file:

enter image description here

The Excel file returns reversed value with database, I don't know how to handle it, hope the masters answer!

T.S.
  • 18,195
  • 11
  • 58
  • 78
Tien Dang
  • 1
  • The following may be helpful: https://stackoverflow.com/a/72872634/10024425 - it shows how to set the data type. See code under "CreateExcelWorkbook". – Tu deschizi eu inchid Sep 12 '22 at 16:59
  • Look here `dt.Rows[i - 2][j - 1].ToString();` when you apply `ToString()` to `DateTime`, this is no longer a date and time but rather a string thaty represents it. Write the result of this `ToString()` to a text file or the Output window to see what is going on. Or Add format to your `ToString("FORMAT HERE")` – T.S. Sep 12 '22 at 17:01
  • but my datatype of date is string in database, i dont use datatype DateTime for day type. – Tien Dang Sep 13 '22 at 04:13

0 Answers0