What is the best way save the results of a mysql query to a excel file?

Question

I have looked everywhere for the answer to my problem but I cant seem to figure it out. I tried the method described at Create Excel (.XLS and .XLSX) file from C# using the excellibrary and the excel file was created but the data was completely destroyed and was not recoverable. What is the best way export data from mysql to excel in an asp.net application?

DataSet ds = new DataSet("New_Dataset");
        DataTable dt = new DataTable("New_DataTable");

        ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
        dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

        MySqlConnection con = new MySqlConnection(GetConnectionString());
        con.Open();

        string sql = "SELECT * FROM reportdata WHERE TakenDate =?Date";
        MySqlCommand cmd = new MySqlCommand(sql, con);
        MySqlDataAdapter adapt = new MySqlDataAdapter();

        MySqlParameter[] param = new MySqlParameter[1];
        param[0] =new MySqlParameter("?Date", MySqlDbType.VarChar, 10);
        param[0].Value = txtStartDate.Text;

        for (int i = 0; i < param.Length; i++)
        {
            cmd.Parameters.Add(param[i]);
        }

        adapt.SelectCommand = cmd;
        adapt.Fill(dt);
        con.Close();

        ds.Tables.Add(dt);

        string strDay = DateTime.Now.DayOfWeek.ToString();
        string day = DateTime.Now.Day.ToString();
        string month = DateTime.Now.Month.ToString();
        string year = DateTime.Now.Year.ToString();

        ExcelLibrary.DataSetHelper.CreateWorkbook(@"G:\Reports\Report " + strDay + " " + month + "-" + day + "-" + year + ".xls", ds);

Excel can load XML files, so you could convert your dataset to XML and then load that. — Chuck Savage, Jan 12 '12 at 19:53

score 0 · Answer 1 · answered Jan 12 '12 at 16:04

Consider using the NPOI Library. I found it extremely easy to use and leveraged it to make a quick Excel spreadsheet dump from GridView results. I'm sure you could apply technique in a similar manner. The core of my code was as simple as this:

DocInfo = PropertySetFactory.CreateDocumentSummaryInformation();
DocInfo.Company = "Dillie-O Digital";
Workbook.DocumentSummaryInformation = DocInfo;

SummaryInfo = PropertySetFactory.CreateSummaryInformation();
SummaryInfo.Subject = "Soup to Nuts GridView Export Example";
Workbook.SummaryInformation = SummaryInfo;

DataSheet = Workbook.CreateSheet("Employees");

for(int i = 0; i <= GridData.Rows.Count - 1; i++)
{
   CurrRow = DataSheet.CreateRow(i);

   for(int j = 0; j <= GridData.Rows[i].Cells.Count - 1; j++)
   {
      if(!IgnoreColumns.Contains(j))
      {
         CurrCell = CurrRow.CreateCell(i);
         CurrCell.SetCellValue(GridData.Rows[i].Cells[j].Text);
      }
   }
}

ResultStream = new FileStream(EndPath, FileMode.Create);
Workbook.Write(ResultStream);
ResultStream.Close();

score 0 · Answer 2 · answered Jan 13 '12 at 16:23

I found that GemBox.Spreadsheet worked great and It's free! http://www.gemboxsoftware.com/

Heres my code to use a datatable to fill an excel spreadsheet:

        // Creating the Excel file with Gembox
        ExcelFile ef = new ExcelFile();
        foreach (DataTable dt1 in ds.Tables)
        {
            ExcelWorksheet ws = ef.Worksheets.Add(dt1.TableName);
            ws.InsertDataTable(dt1, "A1", true);
        }

What is the best way save the results of a mysql query to a excel file?

2 Answers2