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>"); } }