static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
ISheet sheet2 = wb.CreateSheet("Second Sheet");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]");
ISheet sheet3 = wb.CreateSheet(safeName);
FileStream sw = File.Create("newWorksheet.xls");
wb.Write(sw);
sw.Close();
}
public void TestCreateSafeNames()
{
String p = "Sheet1";
String actual = WorkbookUtil.CreateSafeSheetName(p);
Assert.AreEqual(p, actual);
p = "O'Brien's sales";
actual = WorkbookUtil.CreateSafeSheetName(p);
Assert.AreEqual(p, actual);
p = " data # ";
actual = WorkbookUtil.CreateSafeSheetName(p);
Assert.AreEqual(p, actual);
p = "data $1.00";
actual = WorkbookUtil.CreateSafeSheetName(p);
Assert.AreEqual(p, actual);
// now the replaced versions ...
actual = WorkbookUtil.CreateSafeSheetName("data?");
Assert.AreEqual("data ", actual);
actual = WorkbookUtil.CreateSafeSheetName("abc/def");
Assert.AreEqual("abc def", actual);
actual = WorkbookUtil.CreateSafeSheetName("data[0]");
Assert.AreEqual("data 0 ", actual);
actual = WorkbookUtil.CreateSafeSheetName("data*");
Assert.AreEqual("data ", actual);
actual = WorkbookUtil.CreateSafeSheetName("abc\\def");
Assert.AreEqual("abc def", actual);
actual = WorkbookUtil.CreateSafeSheetName("'data");
Assert.AreEqual(" data", actual);
actual = WorkbookUtil.CreateSafeSheetName("data'");
Assert.AreEqual("data ", actual);
actual = WorkbookUtil.CreateSafeSheetName("d'at'a");
Assert.AreEqual("d'at'a", actual);
actual = WorkbookUtil.CreateSafeSheetName(null);
Assert.AreEqual("null", actual);
actual = WorkbookUtil.CreateSafeSheetName("");
Assert.AreEqual("empty", actual);
actual = WorkbookUtil.CreateSafeSheetName("1234567890123456789012345678901TOOLONG");
Assert.AreEqual("1234567890123456789012345678901", actual);
actual = WorkbookUtil.CreateSafeSheetName("sheet:a4");
Assert.AreEqual("sheet a4", actual);
}
/// <summary>
/// Creates the report.
/// </summary>
/// <param name="reportFacility">the result of a DPoW validation to be transformed into report form.</param>
/// <param name="destinationStream">target stream for the spreadsheet</param>
/// <param name="format">determines the excel format to use</param>
/// <returns>true if successful, errors are cought and passed to Logger</returns>
public bool Create(Facility reportFacility, Stream destinationStream, SpreadSheetFormat format)
{
var workBook = format == SpreadSheetFormat.Xlsx
// ReSharper disable once RedundantCast
? (IWorkbook) new XSSFWorkbook()
// ReSharper disable once RedundantCast
: (IWorkbook) new HSSFWorkbook();
var facReport = new FacilityReport(reportFacility);
var summaryPage = workBook.CreateSheet("Summary");
if (!CreateSummarySheet(summaryPage, reportFacility))
{
return(false);
}
// reports on Documents
//
if (reportFacility.Documents != null)
{
var documentsPage = workBook.CreateSheet("Documents");
if (!CreateDocumentDetailsSheet(documentsPage, reportFacility.Documents))
{
return(false);
}
}
var iRunningWorkBook = 1;
// reports on AssetTypes details
//
// ReSharper disable once LoopCanBeConvertedToQuery // might restore once code is stable
foreach (var assetType in facReport.AssetRequirementGroups)
{
// only report items with any assets submitted (a different report should probably be provided otherwise)
if (assetType.GetSubmittedAssetsCount() < 1)
{
continue;
}
var firstOrDefault = assetType.RequirementCategories.FirstOrDefault(cat => cat.Classification == @"Uniclass2015");
if (firstOrDefault == null)
{
continue;
}
var tName = firstOrDefault.Code;
var validName = WorkbookUtil.CreateSafeSheetName(string.Format(@"{0} {1}", iRunningWorkBook++, tName));
var detailPage = workBook.CreateSheet(validName);
if (!CreateDetailSheet(detailPage, assetType))
{
return(false);
}
}
// reports on Zones details
// ReSharper disable once LoopCanBeConvertedToQuery // might restore once code is stable
foreach (var zoneGroup in facReport.ZoneRequirementGroups)
{
// only report items with any assets submitted (a different report should probably be provided otherwise)
if (zoneGroup.GetSubmittedAssetsCount() < 1)
{
continue;
}
var firstOrDefault = zoneGroup.RequirementCategories.FirstOrDefault(cat => cat.Classification == @"Uniclass2015");
if (firstOrDefault == null)
{
continue;
}
var tName = firstOrDefault.Code;
var validName = WorkbookUtil.CreateSafeSheetName(string.Format(@"{0} {1}", iRunningWorkBook++, tName));
var detailPage = workBook.CreateSheet(validName);
if (!CreateDetailSheet(detailPage, zoneGroup))
{
return(false);
}
}
try
{
workBook.Write(destinationStream);
}
catch (Exception ex)
{
Logger.LogError(0, ex, "Failed to stream excel report");
return(false);
}
return(true);
}
public void GenerarExcel()
{
var NombreExcel = "Venta - Sistemas de Ventas ";
// Recuperamos la data de las consulta DB
var data = (List <Annies.Entities.Ventas>)Session["ReporteVenta"];
// Creación del libro excel xlsx.
var wb = new XSSFWorkbook();
// Creación del la hoja y se especifica un nombre
var fileName = WorkbookUtil.CreateSafeSheetName("Ventas");
ISheet sheet = wb.CreateSheet(fileName);
// Contadores para filas y columnas.
int rownum = 0;
int cellnum = 0;
// Creacion del estilo de la letra para las cabeceras.
var fontCab = wb.CreateFont();
fontCab.FontHeightInPoints = 10;
fontCab.FontName = "Calibri";
fontCab.Boldweight = (short)FontBoldWeight.Bold;
fontCab.Color = HSSFColor.White.Index;
// Creacion del color del estilo.
var colorCab = new XSSFColor(new byte[] { 7, 105, 173 });
// Se crea el estilo y se agrega el font al estilo
var styleCab = (XSSFCellStyle)wb.CreateCellStyle();
styleCab.SetFont(fontCab);
styleCab.FillForegroundXSSFColor = colorCab;
styleCab.FillPattern = FillPattern.SolidForeground;
string[] Cabezeras =
{
"Código Venta", "Código Producto", "Marca", "Cantidad Venta", "Fecha", "Precio Final", "Talla Venta"
};
// Se crea la primera fila para las cabceras.
IRow row = sheet.CreateRow(rownum++);
ICell cell;
foreach (var item in Cabezeras)
{
// Se crea celdas y se agrega las cabeceras
cell = row.CreateCell(cellnum++);
cell.SetCellValue(item);
cell.CellStyle = styleCab;
sheet.AutoSizeColumn(cellnum);
}
// Creacion del estilo de la letra para la data.
var fontBody = wb.CreateFont();
fontBody.FontHeightInPoints = 10;
fontBody.FontName = "Arial";
var styleBody = (XSSFCellStyle)wb.CreateCellStyle();
styleBody.SetFont(fontBody);
// Impresión de la data
foreach (var item in data)
{
cellnum = 0;
row = sheet.CreateRow(rownum++);
sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Cod_Venta.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Producto.Cod_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Producto.Marca_Prod.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Cant_Venta.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Fecha.ToString().Substring(6, 2) + "/" + item.Fecha.ToString().Substring(4, 2) + "/" + item.Fecha.ToString().Substring(0, 4), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Precio_Final.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
AddValue(row, cellnum++, item.Talla_Venta.ToString(), styleBody); sheet.AutoSizeColumn(cellnum);
}
var nameFile = NombreExcel + DateTime.Now.ToString("dd_MM_yyyy HH:mm:ss") + ".xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + nameFile);
Response.ContentType = "application/octet-stream";
Stream outStream = Response.OutputStream;
wb.Write(outStream);
outStream.Close();
Response.End();
}
private static void AddSheet <T>(XSSFWorkbook workbook, bool setColumnWidth, Sheet <T> sheetElement)
{
var sheet = workbook.CreateSheet(WorkbookUtil.CreateSafeSheetName(sheetElement.Name));
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
var boldStyle = workbook.CreateCellStyle();
boldStyle.SetFont(font);
var numericStyle = workbook.CreateCellStyle();
numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
int rowIndex = 0;
int columnIndex = 0;
var row = sheet.CreateRow(rowIndex++);
foreach (var column in sheetElement.Columns)
{
var cell = row.CreateCell(columnIndex++);
cell.SetCellValue(column.Header);
cell.CellStyle = boldStyle;
}
foreach (var item in sheetElement.Data)
{
columnIndex = 0;
row = sheet.CreateRow(rowIndex++);
foreach (var column in sheetElement.Columns)
{
var cell = row.CreateCell(columnIndex++);
object value = column.Value(item);
if (value == null)
{
continue;
}
if (value is decimal)
{
cell.SetCellValue((double)(decimal)value);
cell.CellStyle = numericStyle;
}
else if (value is int)
{
cell.SetCellValue((double)(int)value);
cell.CellStyle = numericStyle;
}
else if (value is double)
{
cell.SetCellValue((double)value);
cell.CellStyle = numericStyle;
}
else if (value is long)
{
cell.SetCellValue((double)(long)value);
cell.CellStyle = numericStyle;
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
if (setColumnWidth)
{
for (int i = 0; i < sheetElement.Columns.Count(); i++)
{
if (sheet.LastRowNum < 1000)
{
sheet.AutoSizeColumn(i); // Use only for sheets with few rows!
sheet.SetColumnWidth(i, Math.Min(MaximumColumnWidth, (int)(1.2 * sheet.GetColumnWidth(i))));
}
else
{
sheet.SetColumnWidth(i, 6000);
}
}
}
}