Seryu Seryu - 4 months ago 12
SQL Question

Export Gridview data fetch by mysql

hi i exported gridview data into excel but unfortunately the data inside the exported file was different,supposed to be a datatable.

enter image description here

Below is my script in export button, can you tell me what is wrong in my script. I am new in ASP.net thanks

try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
int StartCol = 1;
int StartRow = 1;
int j = 0, i = 0;

//Write Headers
for (j = 0; j < GridView1.Columns.Count; j++)
{
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
myRange.Value = GridView1.Columns[j].HeaderText;
}

StartRow++;

//Write datagridview content
for (i = 0; i < GridView1.Rows.Count; i++)
{
for (j = 0; j < GridView1.Columns.Count; j++)
{
try
{
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
myRange.Value2 = GridView1.Rows[i].Cells[j].Text + ";" == null ? "" : GridView1.Rows[i].Cells[j].Text + ";";

}
catch
{
GridView1.DataBind();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());

// ScriptManager.RegisterClientScriptBlock(this, this.GetType(),
// "alertMessage",
// "alert(ex.ToString());", true);

}

Answer

Hi this is were i came up after long journey, i used the Aspose method on Exporting Gridview in Excel, it's simple yet so powerfull! hope this will help,

Code behind Export_button:

  //Instantiate a new workbook
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            //Get the first worksheet in the workbook
            Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
            //Import data from GridView control to fill the worksheet
            worksheet.Cells.ImportGridView(GridView1, 0, 0, new Aspose.Cells.ImportTableOptions() { IsFieldNameShown = true });
            worksheet.AutoFitColumns();
            //Send result to client in XLS format
            workbook.Save(this.Response, "export.xls", ContentDisposition.Attachment, new Aspose.Cells.XlsSaveOptions());