// ===================================================================================================
public static void createExcelFile(ExcelFileSpreadsheet spreadsheet, String path, String fileName)
{
using (ExcelPackage p = new ExcelPackage())
{
int contWorksheet = 1;
int initalCol = 2;
int initialRow = 2;
// Ingresamos los datos para las propiedades del archivo Excel
p.Workbook.Properties.Author = spreadsheet.propertyAuthor;
p.Workbook.Properties.Title = spreadsheet.propertyTitle;
foreach (ExcelFileWorksheet worksheet in spreadsheet.worksheets)
{
// Creamos un Worksheet
p.Workbook.Worksheets.Add(worksheet.sheetName);
ExcelWorksheet ws = p.Workbook.Worksheets[contWorksheet];
ws.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Font.Size = 10;
ws.Cells.Style.Font.Name = "Arial";
int colIndex = initalCol;
int rowIndex = initialRow;
int contHeader = 1;
int indexHeader = 0;
DataTable dt = worksheet.dtSource;
if (!worksheet.sheetTitle.Equals(""))
{ // Unimos celdas para poder colocar el titulo de la grilla a mostrar
ws.Cells[colIndex, rowIndex].Value = worksheet.sheetTitle;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Merge = true;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.Font.Size = 12;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.Font.Bold = true;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 2;
}
// Creamos las cabeceras de la grilla
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[rowIndex, colIndex];
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#D7D2CB"));
var font = cell.Style.Font;
font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
font.Bold = true;
var border = cell.Style.Border;
border.Top.Style = ExcelBorderStyle.Thin;
border.Top.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
if (contHeader == 1)
{
border.Left.Style = ExcelBorderStyle.Thin;
border.Left.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contHeader < dt.Columns.Count)
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.Color.White);
}
else
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
// Colocamos el nombre de la celda en la cabecera
cell.Value = worksheet.columnHeader[indexHeader];
double cellSize = ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width;
double proposedCellSize = cell.Value.ToString().Trim().Length * 1.3;
if (cellSize <= proposedCellSize)
{
ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width = proposedCellSize;
}
colIndex++;
contHeader++;
indexHeader++;
}
int contRow = 1;
// Agregamos el contenido del DataTable en las celdas de la grilla
foreach (DataRow dr in dt.Rows)
{
colIndex = initalCol;
rowIndex++;
int contCol = 1;
int cellIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[rowIndex, colIndex];
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
if (rowIndex % 2 == 0)
{
fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ECEEE7"));
}
String strValue = "";
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.TEXT))
{
cell.Value = dr[dc.ColumnName].ToString();
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.NUMERIC))
{
cell.Value = Convert.ToInt32(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.FLOAT))
{
cell.Value = Convert.ToDecimal(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DOUBLE))
{
cell.Value = Convert.ToDouble(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DATE))
{
DateTime datetime = DateTime.Parse(dr[dc.ColumnName].ToString());
String day = datetime.ToString("dd");
String month = datetime.ToString("MM");
String year = datetime.ToString("yyyy");
cell.Value = day + "/" + month + "/" + year;
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.TIME))
{
DateTime datetime = DateTime.Parse(dr[dc.ColumnName].ToString());
String hora = datetime.ToString("hh");
String minuto = datetime.ToString("mm");
cell.Value = hora + ":" + minuto;
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DATETIME))
{
cell.Value = dr[dc.ColumnName].ToString();
strValue = cell.Value.ToString();
}
}
}
}
}
}
}
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var border = cell.Style.Border;
if (contCol == 1)
{
border.Left.Style = ExcelBorderStyle.Thin;
border.Left.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contCol < dt.Columns.Count)
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.Color.White);
}
else
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contRow == dt.Rows.Count)
{
border.Bottom.Style = ExcelBorderStyle.Thin;
border.Bottom.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
var font = cell.Style.Font;
font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
font.Bold = false;
double cellSize = ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width;
double proposedCellSize = strValue.Trim().Length * 1.3;
if (cellSize <= proposedCellSize)
{
ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width = proposedCellSize;
}
colIndex++;
contCol++;
cellIndex++;
}
contRow++;
}
//How to Add a Image using EP Plus
/*
* string codigo_color = System.Configuration.ConfigurationManager.AppSettings["codigocolorcss"].ToString();
* string tema = "entel-excel-";
*
* if (codigo_color == "1")
* {
* tema = "nextel-excel-";
* }
* else if (codigo_color == "2")
* {
* tema = "entel-excel-";
* }
*/
//String logoNextelPath = HttpContext.Current.Server.MapPath("~") + "/images/logo/" + tema + "logo.png";
//String logoDescargaPath = HttpContext.Current.Server.MapPath("~") + "/images/logo/" + tema + "descarga.png";
//Bitmap imageNextel = new Bitmap(logoNextelPath);
//Bitmap imageDescarga = new Bitmap(logoDescargaPath);
//ExcelPicture pictureNextel = null;
//ExcelPicture pictureDescarga = null;
//if (imageNextel != null)
//{
// pictureNextel = ws.Drawings.AddPicture("logoNextel", imageNextel);
// pictureNextel.From.Column = initalCol - 1;
// pictureNextel.From.Row = initialRow + contRow + 2;
// pictureNextel.SetSize(imageNextel.Width - 20, imageNextel.Height);
//}
//if (imageDescarga != null)
//{
// int imgRow = initialRow + contRow + 2;
// int imgCol = initalCol + (dt.Columns.Count - 1);
// int cellwidth = ExcelHelper.ColumnWidth2Pixel(ws, ws.Cells[imgRow, imgCol].Worksheet.Column(imgCol).Width);
// int imagewidth = imageDescarga.Width;
// int diff = cellwidth - imagewidth;
// pictureDescarga = ws.Drawings.AddPicture("logoDescarga", imageDescarga);
// pictureDescarga.SetPosition(imgRow, 0, imgCol - 1, diff);
// pictureDescarga.SetSize(imageDescarga.Width, imageDescarga.Height);
//}
contWorksheet++;
}
// Generamos el archivo Excel a ser copiado en el servidor
Byte[] bin = p.GetAsByteArray();
DateTime date = DateTime.Now;
String file = path + "/" + fileName + ".xlsx";
File.WriteAllBytes(file, bin);
}
}
public static void ExportToExcelColor(ExcelFileSpreadsheet spreadsheet, List <String> colores, String filename)
{
using (ExcelPackage p = new ExcelPackage())
{
int contWorksheet = 1;
int initalCol = 2;
int initialRow = 2;
// Ingresamos los datos para las propiedades del archivo Excel
p.Workbook.Properties.Author = spreadsheet.propertyAuthor;
p.Workbook.Properties.Title = spreadsheet.propertyTitle;
foreach (ExcelFileWorksheet worksheet in spreadsheet.worksheets)
{ // Creamos un Worksheet
p.Workbook.Worksheets.Add(worksheet.sheetName);
ExcelWorksheet ws = p.Workbook.Worksheets[contWorksheet];
ws.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
ws.Cells.Style.Font.Size = 10;
ws.Cells.Style.Font.Name = "Arial";
int colIndex = initalCol;
int rowIndex = initialRow;
int contHeader = 1;
int indexHeader = 0;
int contRow = 0;
DataTable dt = worksheet.dtSource;
if (!worksheet.sheetTitle.Equals(""))
{
// Unimos celdas para poder colocar el titulo de la grilla a mostrar
ws.Cells[colIndex, rowIndex].Value = worksheet.sheetTitle;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Merge = true;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.Font.Size = 12;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.Font.Bold = true;
ws.Cells[colIndex, rowIndex, colIndex, rowIndex + (dt.Columns.Count - 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 2;
}
// Creamos las cabeceras de la grilla
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[rowIndex, colIndex];
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#D7D2CB"));
var font = cell.Style.Font;
font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
font.Bold = true;
var border = cell.Style.Border;
border.Top.Style = ExcelBorderStyle.Thin;
border.Top.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
if (contHeader == 1)
{
border.Left.Style = ExcelBorderStyle.Thin;
border.Left.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contHeader < dt.Columns.Count)
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.Color.White);
}
else
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
// Colocamos el nombre de la celda en la cabecera
cell.Value = worksheet.columnHeader[indexHeader];
double cellSize = ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width;
double proposedCellSize = cell.Value.ToString().Trim().Length * 1.3;
if (cellSize <= proposedCellSize)
{
ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width = proposedCellSize;
}
colIndex++;
contHeader++;
indexHeader++;
}
contRow = 1;
// Agregamos el contenido del DataTable en las celdas de la grilla
foreach (DataRow dr in dt.Rows)
{
colIndex = initalCol;
rowIndex++;
int contCol = 1;
int cellIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[rowIndex, colIndex];
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
if (rowIndex % 2 == 0)
{
fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ECEEE7"));
}
if (colores[contRow - 1] != "")
{
fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml(colores[contRow - 1]));
}
String strValue = "";
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.TEXT))
{
cell.Value = dr[dc.ColumnName].ToString();
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.NUMERIC))
{
cell.Value = Convert.ToInt32(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.FLOAT))
{
cell.Value = Convert.ToDecimal(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DOUBLE))
{
cell.Value = Convert.ToDouble(dr[dc.ColumnName]);
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DATE))
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");
DateTime datetime = DateTime.Parse(dr[dc.ColumnName].ToString());
String day = datetime.ToString("dd");
String month = datetime.ToString("MM");
String year = datetime.ToString("yyyy");
cell.Value = day + "/" + month + "/" + year;
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.TIME))
{
DateTime datetime = DateTime.Parse(dr[dc.ColumnName].ToString());
String hora = datetime.ToString("hh");
String minuto = datetime.ToString("mm");
cell.Value = hora + ":" + minuto;
strValue = cell.Value.ToString();
}
else
{
if (worksheet.columnFormat[cellIndex].Equals(ExcelFileCellFormat.DATETIME))
{
cell.Value = dr[dc.ColumnName].ToString();
strValue = cell.Value.ToString();
}
}
}
}
}
}
}
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var border = cell.Style.Border;
if (contCol == 1)
{
border.Left.Style = ExcelBorderStyle.Thin;
border.Left.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contCol < dt.Columns.Count)
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.Color.White);
}
else
{
border.Right.Style = ExcelBorderStyle.Thin;
border.Right.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
if (contRow == dt.Rows.Count)
{
border.Bottom.Style = ExcelBorderStyle.Thin;
border.Bottom.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
}
var font = cell.Style.Font;
font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#766A87"));
font.Bold = false;
double cellSize = ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width;
double proposedCellSize = strValue.Trim().Length * 1.3;
if (cellSize <= proposedCellSize)
{
ws.Cells[rowIndex, colIndex].Worksheet.Column(colIndex).Width = proposedCellSize;
}
colIndex++;
contCol++;
cellIndex++;
}
contRow++;
}
// Agregamos las imagenes que se incluiran en este Worksheet
/*
* string codigo_color = System.Configuration.ConfigurationManager.AppSettings["codigocolorcss"].ToString();
* string tema = "entel-excel-";
*
* if (codigo_color == "1")
* {
* tema = "nextel-excel-";
* }
* else if (codigo_color == "2")
* {
* tema = "entel-excel-";
* }
*/
//Bitmap imageDescarga = new Bitmap(logoDescargaPath);
//String logoDescargaPath = HttpContext.Current.Server.MapPath("~") + "/images/logo/descarga" + Controller.GeneralController.obtenerTemaActual() + ".png";
/*
* String logoNextelPath = HttpContext.Current.Server.MapPath("~") + "/images/logo/excel" + Controller.GeneralController.obtenerTemaActual() + ".png";
*
* Bitmap imageNextel = new Bitmap(logoNextelPath);
*
* ExcelPicture pictureNextel = null;
* //ExcelPicture pictureDescarga = null;
*
* if (imageNextel != null)
* {
* pictureNextel = ws.Drawings.AddPicture("logoNextel", imageNextel);
* pictureNextel.From.Column = initalCol - 1;
* pictureNextel.From.Row = initialRow + contRow + 2;
* pictureNextel.SetSize(imageNextel.Width - 20, imageNextel.Height);
* }
*/
/*
* if (imageDescarga != null)
* {
* int imgRow = initialRow + contRow + 2;
* int imgCol = initalCol + (dt.Columns.Count - 1);
*
* int cellwidth = ExcelHelper.ColumnWidth2Pixel(ws, ws.Cells[imgRow, imgCol].Worksheet.Column(imgCol).Width);
* int imagewidth = imageDescarga.Width;
* int diff = cellwidth - imagewidth;
* pictureDescarga = ws.Drawings.AddPicture("logoDescarga", imageDescarga);
* pictureDescarga.SetPosition(imgRow, 0, imgCol - 1, diff);
* pictureDescarga.SetSize(imageDescarga.Width, imageDescarga.Height);
* }
*/
contWorksheet++;
}
// Generamos el archivo Excel y mostramos el popup de descarga
Byte[] bin = p.GetAsByteArray();
DateTime date = DateTime.Now;
HttpContext context = HttpContext.Current;
context.Response.Clear();
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "_" + date.Date.ToString("yyyyMMdd") + "_" + date.Hour + date.Minute + date.Millisecond + ".xlsx");
context.Response.BinaryWrite(bin);
context.Response.End();
}
}
