public static void DownLoadAsExcel(HttpResponse _Response, ExcelParameters parameters)
{
_Response.Clear();
_Response.ClearContent();
_Response.ClearHeaders();
//_Response.Buffer = false;
_Response.ContentType = "application/ms-excel";
//_Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
_Response.AddHeader("Content-Disposition", "attachment;filename=" + parameters.FileName);
//CreateExcelDocument(parameters, _Response.OutputStream);
using (var sWriter = new StreamWriter(_Response.OutputStream))
{
//sets font
WriteMainHeaders(parameters, sWriter);
foreach (var p in parameters.TablesParameters)
{
DataTable table = p.table;
WriteTopTitles(sWriter, p);
WriteTableColumnsHeaders(sWriter, table);
WriteTableValues(sWriter, p, table);
WriteBottomTitles(sWriter, p);
sWriter.Write("<TR><td></td></TR>");
sWriter.Write("<TR><td></td></TR>");
sWriter.Write("</Table></font>");
}
}
_Response.Flush();
_Response.Close();
_Response.End();
}
//public static void CreateExcelDocument(DataSet dataSet, string excelFilePath)
//{
// try
// {
// FileInfo fileInfo = new FileInfo(excelFilePath);
// var table = dataSet.Tables[0];
// using (var xls = new OfficeOpenXml.ExcelPackage(fileInfo))
// {
// var sheet = xls.Workbook.Worksheets.Add(table.TableName);
// for (int j = 1; j <= table.Columns.Count; j++) // headers
// {
// sheet.Cells[1, j].Value = table.Columns[j - 1].ColumnName;
// }
// for (int rowIndex = 1; rowIndex <= table.Rows.Count; rowIndex++)
// {
// for (int columnIndex = 1; columnIndex <= table.Columns.Count; columnIndex++)
// {
// sheet.Cells[rowIndex, columnIndex].Value = table.Rows[rowIndex - 1].ItemArray[columnIndex - 1].ToString();
// }
// }
// xls.Save();
// }
// }
// catch (Exception ex)
// {
// string msg = ex.Message;
// }
//}
public static void CreateExcelDocument(ExcelParameters parameters, Stream stream)
{
//..Encoding.GetEncoding(862)
// latinEncoding Encoding.GetEncoding(1252)
// hebrew Encoding.GetEncoding(1255)
using (var sWriter = new StreamWriter(stream, Encoding.GetEncoding(1255)))
{
//sets font
WriteFileHeaders(sWriter);
WriteMainHeaders(parameters, sWriter);
foreach (var p in parameters.TablesParameters)
{
DataTable table = p.table;
WriteTopTitles(sWriter, p);
WriteTableColumnsHeaders(sWriter, table);
WriteTableValues(sWriter, p, table);
WriteBottomTitles(sWriter, p);
sWriter.Write("<TR><td></td></TR>");
sWriter.Write("<TR><td></td></TR>");
sWriter.Write("</Table></font>");
}
}
}
private static void WriteMainHeaders(ExcelParameters parameters, StreamWriter sWriter)
{
sWriter.Write("<font style='font-size:10.0pt; font-family:Calibri;'><BR><BR><BR>");
if (parameters.MainHeaders.Count > 0)
{
sWriter.Write("<Table>");
foreach (var pHeader in parameters.MainHeaders)
{
sWriter.Write(string.Format("<TR style=\"font-size:18px;background:#BDD7EE;width:450px;\"><td colspan=\"10\"><center>{0}</center></td></TR>", pHeader));
}
sWriter.Write("<TR><td colspan=\"10\"></td></TR>");
sWriter.Write("<TR><td colspan=\"10\"></td></TR>");
sWriter.Write("</Table>");
}
}
