TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Ishak Khan
NA
25
5.8k
Excel file export - out of memory exception
Apr 13 2017 10:02 AM
protected void imgBtnExcel_Click(object sender, ImageClickEventArgs e)
{
string strExcelPath = string.Empty;
string strCharTypeID = "";
foreach (ListItem item in lstChartType.Items)
{
if (item.Selected == true)
{
strCharTypeID += item.Value.ToString() + ",";
}
}
strCharTypeID = strCharTypeID.TrimEnd(',');
DataTable dtReports = new DataTable();
dtReports = objReportBL.GetHospitalInvoiceProductionReport(txtBeginDate.Text.Trim(), txtEndDate.Text.Trim());
if (Session["dtReport"] != null)
{
DataTable dtReport = Session["dtReport"] as DataTable;
strExcelPath = GenerateExcelWorkSheet(dtReports, "Production Report");
if (dgvProduction.Rows.Count > 0)
{
dgvProduction.UseAccessibleHeader = true;
dgvProduction.HeaderRow.TableSection = TableRowSection.TableHeader;
imgBtnExcel.Visible = true;
}
else
{
imgBtnExcel.Visible = false;
}
try
{
FileInfo file = new FileInfo(strExcelPath);
if (file.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + "ProductionReport" + DateTime.Now.ToString().Replace("/", "").Replace(" ", "").Replace(":", "").ToString() + ".xlsx");
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
}
catch (Exception ex)
{
}
}
}
private string GenerateExcelWorkSheet(DataTable dtExcelData, string sheetName, string fileName)
{
string _strAppendFileName = string.Empty;
int rowIndex = 1;
int colIndex = 0;
exclSheets = exclBook.Worksheets.Add(sheetName);
exclBook.ShowGridLines = false;
colIndex++;
fileName = fileName + ".xlsx";
exclSheets.Cell(rowIndex, colIndex).Style.Font.Bold = true;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Fill.BackgroundColor = Excel.XLColor.Yellow;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count + 1).Style.Border.LeftBorder = Excel.XLBorderStyleValues.Thin;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Alignment.Horizontal = Excel.XLAlignmentHorizontalValues.Center;
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Font.SetFontColor(Excel.XLColor.Black);
exclSheets.Range(1, 1, 1, dtExcelData.Columns.Count).Style.Font.Bold = true;
string strText = "";
foreach (DataColumn dCol in dtExcelData.Columns)
{
strText = dCol.Caption.ToString();
exclSheets.Cell(rowIndex, colIndex).Value = strText;
colIndex++;
}
colIndex = 1;
rowIndex++;
colIndex = 0;
string strCellvalue = "";
for (int i = 0; i < dtExcelData.Rows.Count; i++)
{
for (int j = 0; j < dtExcelData.Columns.Count; j++)
{
colIndex++;
strCellvalue = dtExcelData.Rows[i][j].ToString().ToUpper();
exclSheets.Cell(rowIndex, colIndex).Value = "'" + strCellvalue;
}
colIndex = 0;
rowIndex++;
}
char c = Convert.ToChar((64 + dtExcelData.Columns.Count - 2));
string strHeaderRange = GetExcelColumnName(dtExcelData.Columns.Count);
exclSheets.Range("A1", "A1").Style.Border.BottomBorder = Excel.XLBorderStyleValues.Thin;
if (File.Exists(Server.MapPath(@"~/Output/") + fileName.Trim()))
{
File.Delete(Server.MapPath(@"~/Output/") + fileName.Trim());
}
exclBook.SaveAs(Server.MapPath(@"~/Output/") + fileName.Trim());
exclBook = null;
exclSheets = null;
string path = Server.MapPath(@"~/Output/") + fileName.Trim();
return path;
}
Reply
Answers (
1
)
c sharpcorner Security | No SSL or Https available
create a winform to backup SQL server