the code below is for exporting from my webpage into excel file, as i have it in the code below the export file is based on .xls as you all know xls can accept only 65k rows.
What i need is to use .xlsx, I have tried all the possible ways to do it but no luck. Could you please edit the code below so i can use it for .xlsx?
Appreciate your help
- Code: Select all
#region exportexcelsell
public void exportexcelsell(DataTable dt,int gridid)
{
try
{
dt = Generatecol(dt, gridid);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
////sets font
//HttpContext.Current.Response.Write("<font>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
////sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table " +
" cellSpacing='0' cellPadding='0' " +
"> <TR>");
////am getting my grid's column headers
int columnscount = dt.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
HttpContext.Current.Response.Write("<Td nowrap >");
//Get column headers and make it as bold in excel columns
HttpContext.Current.Response.Write(dt.Columns[j].ColumnName.ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in dt.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < dt.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td nowrap>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
catch { }
}
private DataTable Generatecol(DataTable dt,int gridid)
{
GridFiledtblFilter filter = new GridFiledtblFilter();
filter.GridFiledtblVarFilter.Grf_GrdID = gridid;
DB_GridFiledtbl dbgridfield = new DB_GridFiledtbl();
DataTable dt2 = new System.Data.DataTable();
List<String> dcmain = new List<string>();
dt2 = dbgridfield.GetGridFileddt(filter);
DataTable dt3 = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
dcmain.Add(dc.ColumnName);
}
for (int i = 0; i < dcmain.Count; i++)
{
int valid = 0;
for (int j = 0; j < dt2.Rows.Count; j++)
{
if (dt2.Rows[j]["Grf_fieldNameEN"].ToString() == dcmain[i])
{
valid = 1;
dt.Columns[dcmain[i]].ColumnName = dt2.Rows[j]["Grf_FieldName"].ToString();
}
}
if (valid == 0)
{
string text = dcmain[i];
if (dt.Columns[text] != null)
dt.Columns.Remove(text);
}
}
return dt;
}
private DataTable Generatecol(DataTable dt, Dictionary<string, string> Col)
{
List<String> dcmain = new List<string>();
foreach (DataColumn dc in dt.Columns)
{
dcmain.Add(dc.ColumnName);
}
foreach (string dataCols in dcmain)
{
int valid = 0;
foreach (string data in Col.Values)
{
var internaldata = data;
var internalvalue=data;
if (data.Split(',').Length > 1){
internaldata = data.Split(',')[0];
internalvalue = data.Split(',')[1];
}
if (dataCols == internaldata)
{
valid = 1;
dt.Columns[dataCols].ColumnName = internalvalue;
}
}
if (valid == 0)
{
if (dt.Columns[dataCols] != null)
dt.Columns.Remove(dataCols);
}
}
return dt;
}
public void SpireExportExcell(DataTable dt,int gridid,Dictionary<string,string> Cols=null)
{
string strDownloadFile = "";
string strDownloadFilePath = "";
strDownloadFile = Security.UserID() + DateTime.Now.ToString("_MMM-dd_hh-mm-ss");
strDownloadFilePath += HttpContext.Current.Server.MapPath("~/files/ExportExcell/" + strDownloadFile + ".xls");
try
{
if (Cols == null )
dt = Generatecol(dt, gridid);
else
dt = Generatecol(dt, Cols);
if ( dt.Rows.Count < 2000)
{
int rowID = 0;
int columnID = 0;
string FilePath;
File.Create(strDownloadFilePath).Close();
System.Threading.Thread.Sleep(500);
Microsoft.Office.Interop.Excel.Application ExcelAPP = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excel = ExcelAPP.Workbooks.Open(strDownloadFilePath);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
ExcelAPP.Visible = true;
sheet.Activate();
rowID++;
columnID = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
columnID++;
sheet.Cells[rowID, columnID] = dt.Columns[j].ColumnName.ToString();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
rowID++;
columnID = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
columnID++;
sheet.Cells[rowID, columnID] = dt.Rows[i][j].ToString();
}
}
sheet.DisplayRightToLeft = false;
excel.Save();
if (excel.Saved == false)
excel.Save();
excel.Close(true);
ExcelAPP.Quit();
}
else
{
Workbook book = new Workbook();
Worksheet sheet1 = book.Worksheets[0];
sheet1.InsertDataTable(dt, true, 1,1);
book.Worksheets.Add(sheet1);
book.SaveToFile(strDownloadFilePath);
Microsoft.Office.Interop.Excel.Application ExcelAPP = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excel = ExcelAPP.Workbooks.Open(strDownloadFilePath);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
ExcelAPP.Visible = false;
sheet.Activate();
excel.Save();
if (excel.Saved == false)
excel.Save();
excel.Close(true);
ExcelAPP.Quit();
}
HttpCookie c1 = new HttpCookie("ExportExcellFile");
c1.Value = "/files/ExportExcell/" + strDownloadFile + ".xls";
c1.Expires = DateTime.Now.AddMinutes(2);
HttpContext.Current.Response.Cookies.Add(c1);
}
catch (Exception ex)
{
BaseDB.WriteLog(ex.Message, "EXport");
}
}
public string ExportExcellFile(DataTable dt, int gridid)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Version2007;
dt = Generatecol(dt, gridid);
sheet.InsertDataTable(dt, true, 2, 1, -1, -1);
CellStyle styleHeader = sheet.Rows[0].Style;
sheet.Rows[0].RowHeight = 20;
string filename = Security.UserID() + DateTime.Now.ToString("_MMM-dd_hh-mm-ss");
workbook.SaveToFile(HttpContext.Current.Server.MapPath("~/files/ExportExcell/" + filename + ".xls"));
HttpCookie c1 = new HttpCookie("ExportExcellFile");
c1.Value = "/files/ExportExcell/" + filename + ".xls";
c1.Expires = DateTime.Now.AddMinutes(2);
HttpContext.Current.Response.Cookies.Add(c1);
return HttpContext.Current.Server.MapPath(c1.Value);
}