I have this export function that allows me to export 2 grid views into 2 separated worksheets in one excel.
But my problems are:
1. How can I have a usual popup window like usual download when I click on export button to prompt user to OPEN, SAVE AS, CANCEL of the download instead of saving it to a specific location (currently what I am doing in my codes)?
2. How can I set a code to enable wraptext = true for all my cells and also auto format the column height and width to fixed all the text so that it does not show ###### for date as an example when column width is too small when excel is opened.
protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
String DT1 = "Data table 1";
String DT2 = "Data table 2";
ExportToExcel(app, workbook, Gridview1, DT1, 1);
ExportToExcel(app, workbook, Gridview2, DT2, 2);
}
public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName, int sheetid)
{
app.Visible = true;
worksheet = (Excel.Worksheet)workbook,Worksheets.Add();
worksheet.Name = SheetName;
for (int i = 1; i < gridview.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gridview.Rows.Count - 1; i++)
{
for (int j = 0; j < gridview.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text.ToString();
}
}
workbook.SaveAs(@"C:\Users\test\Desktop\Test\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}