how to export Mysql table values to already existing Excel sheet template using ASP.Net

Question

I need to Export Mysql Table values to already existing excel sheet template. And I need to save that Existing file in different name without disturbing the existing template. I have already created an Excel sheet and placed it on my local disk. I have done the following code, but here I have an issue. Every time I need to keep the excel sheet open to Fill the Table values from Mysql. But I don't want to Keep open my already existing excel sheet. And I am not able to do the code for saving that template in different name. can anyone help?

using MySql.Data.MySqlClient;
using System.Data;
using Excel=Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
using System.Data.OleDb;

namespace export_db_to_existing_excel
{
  public partial class db_to_existing_excel : System.Web.UI.Page
  {
      protected void Page_Load(object sender, EventArgs e)
      {

      }

      protected void btn_export_Click(object sender, EventArgs e)
      {
        string connectionstr =  "Server=localhost;userid=root;password=password;database=sample";

        MySqlConnection conn = new MySqlConnection(connectionstr);
        MySqlCommand cmd= new MySqlCommand("select * from employee", conn);
        MySqlDataAdapter mysqda = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        mysqda.Fill(dt);

        Excel.Application xlapp =  (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Applicatio n");
        Excel.Workbook xlworkbook = (Excel.Workbook)xlapp.ActiveWorkbook;
        Excel.Worksheet xlworksheet = (Excel.Worksheet)xlworkbook.ActiveSheet;
        Excel.Range chartRange = xlworksheet.UsedRange;

          for(int i=0;i<dt.Rows.Count;i++)
          {
              for(int j=0;j<dt.Columns.Count;j++)
              {
                 xlworksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
              }
          }


      }
   }
}

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

If you are using Native Office Excel classes you will face these kind of issues.

And one of the issues we faced was, MS Office needs to install on the Production Environment which is indeed a bad choice.

So we ended up with OpenXml libraries. I have played with Documents not with excel, however I am sure you can do it easily.

Some Links to start with Export DataTable to Excel with Open Xml SDK in c#

http://www.codeproject.com/Articles/692092/A-free-Export-to-Excel-Csharp-class-using-OpenXML

how to export Mysql table values to already existing Excel sheet template using ASP.Net

1 Answers1