How to export datatable to excel in c# windows application

Question

I found some code through googling that allows me to export a data table to excel file. i successfully export the file from database table and save it in my document My coding is:

  using System;
  using System.Data.OleDb;
  using System.Windows.Forms;
  using MySql.Data.MySqlClient;
  using System.Data;

namespace ImportFile

{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
    String connection = "SERVER=******;"DATABASE=dbd;"UID=root;"PASSWORD=pws;"Convert Zero Datetime = True";
 private void BExport_Click(object sender, EventArgs e)
    {
        DateTime dat = DateTime.Now;
        int time = dat.Hour;
        int time1 = dat.Minute;
        int time2 = dat.Second;
        int month = dat.Month;
        int day = dat.Day;
        MySqlConnection connection1 = new MySqlConnection(connection);
        connection1.Open();
        MySqlCommand command = new MySqlCommand ("SELECT * FROM TABLE_Name",connection1);     
        MySqlDataAdapter dataadpter = new MySqlDataAdapter(command);
        DataTable datatable = new DataTable("TABLE_NAME");
        dataadpter.Fill(datatable);
        datatable.WriteXml("C:\\Users\\Downloads\\agent.xlsx" + time + " - " + time1 + " - " + time2 + " - " + day + " - " + month + "");
        MessageBox.Show("export data");

      }
     }
   }

my problem is that when i download file(click import button)its not download an excel file its just an normal file.

how i download it as excel file . if any one know this.... help me

Did you debug your code and see what happens exactly? I mean, you sure your select statement returns data? By the way, you are aware that you try to write an xlsx file something like `\\agent.xlsx12-4-3...` right? Are you try to put these numbers in your file name instead? — Soner Gönül, Dec 18 '15 at 07:52
The easiest way to get such data (basic row/column data from a table) into Excel is to create a simple `csv` file. Unless you have diagrams or formatting requirements, it's the easiest way and also works well with all other kinds ob applications. — Num Lock, Dec 18 '15 at 07:59

score 3 · Accepted Answer · edited May 23 '17 at 12:06

3

This line is one problem:

datatable.WriteXml("C:\\Users\\Downloads\\agent.xlsx" + time + " - " + time1 + " - " + time2 + " - " + day + " - " + month + "");

You wrote agent.xlsx, but then added some other values at the end.

The second problem is like Lewis Hai already described in his answer, is that you're using WriteXml method, which will write your data as XML.

edited May 23 '17 at 12:06

Community

1
1

answered Dec 18 '15 at 08:00

roemel

3,247
4
29
52

The main problem is `DataTable.WriteXml`, not the file name. – Albireo Dec 18 '15 at 08:08
@Albireo Oh, well. Thanks for the hint. I didn't notice he's writing Xml... Was a bit too busy with the filename... Thx – roemel Dec 18 '15 at 08:09

score 0 · Answer 2 · edited May 23 '17 at 12:33

0

First problem as Roman said above and datatable.WriteXml() with first parameter is not string. Read more at https://msdn.microsoft.com/en-us/library/system.data.datatable.writexml(v=vs.110).aspx

Second problem as this code datatable.WriteXml it means that you write data of datatable as XML.

Refer this post to help you more How to export DataTable to Excel

edited May 23 '17 at 12:33

Community

1
1

answered Dec 18 '15 at 08:07

Lewis Hai

1,114
10
22

How to export datatable to excel in c# windows application

2 Answers2