Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#createCellStyle()
The following examples show how to use
org.apache.poi.xssf.usermodel.XSSFWorkbook#createCellStyle() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: EpidemicReport.java From MyBox with Apache License 2.0 | 21 votes |
|
public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet, List<String> extraFields) {
try {
List<String> columns = externalNames(extraFields);
sheet.setDefaultColumnWidth(20);
XSSFRow titleRow = sheet.createRow(0);
XSSFCellStyle horizontalCenter = wb.createCellStyle();
horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
for (int i = 0; i < columns.size(); i++) {
XSSFCell cell = titleRow.createCell(i);
cell.setCellValue(columns.get(i));
cell.setCellStyle(horizontalCenter);
}
return columns;
} catch (Exception e) {
return null;
}
}
Example 2
| Source File: GeographyCode.java From MyBox with Apache License 2.0 | 6 votes |
|
public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet) {
try {
List<String> columns = externalNames();
sheet.setDefaultColumnWidth(20);
XSSFRow titleRow = sheet.createRow(0);
XSSFCellStyle horizontalCenter = wb.createCellStyle();
horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
for (int i = 0;
i < columns.size();
i++) {
XSSFCell cell = titleRow.createCell(i);
cell.setCellValue(columns.get(i));
cell.setCellStyle(horizontalCenter);
}
return columns;
} catch (Exception e) {
return null;
}
}
Example 3
| Source File: ActionTemplate.java From o2oa with GNU Affero General Public License v3.0 | 6 votes |
|
private void template(XSSFWorkbook workbook) throws Exception {
XSSFSheet sheet = workbook.createSheet("人员");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("姓名");
cell = row.createCell(1);
cell.setCellValue("手机号");
cell = row.createCell(2);
cell.setCellValue("电子邮件");
cell = row.createCell(3);
cell.setCellValue("唯一编码");
cell = row.createCell(4);
cell.setCellValue("员工号");
cell = row.createCell(5);
cell.setCellValue("性别");
cell = row.createCell(6);
cell.setCellValue("(地址)");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
IntStream.rangeClosed(0, 6).forEach(i -> {
sheet.setDefaultColumnStyle(i, cellStyle);
});
}
Example 4
| Source File: ExcelUtils.java From mySpringBoot with Apache License 2.0 | 5 votes |
|
/**
* 设置内容
*
* @param wb
* @param sheet
* @param rows
* @param rowIndex
* @return
*/
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex;
Font dataFont = wb.createFont();
dataFont.setFontName("simsun");
dataFont.setFontHeightInPoints((short) 14);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
dataRow.setHeightInPoints(25);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
Example 5
| Source File: ExcelUtils.java From mySpringBoot with Apache License 2.0 | 5 votes |
|
/**
* 设置表头
*
* @param wb
* @param sheet
* @param titles
* @return
*/
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
//设置字体
titleFont.setFontName("simsun");
//设置粗体
titleFont.setBoldweight(Short.MAX_VALUE);
//设置字号
titleFont.setFontHeightInPoints((short) 14);
//设置颜色
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
//水平居中
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//垂直居中
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置图案颜色
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
//设置图案样式
titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
Example 6
| Source File: SampleController.java From tutorial with MIT License | 5 votes |
|
/**
* 导出Excel的例子
* 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
* @return
* @throws Exception
*/
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
logger.trace("exportExcel");
HttpHeaders responseHeaders = new HttpHeaders();
responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//中文文件名需要用iso8859-1编码
InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
XSSFSheet sheet = workbook.getSheetAt(0);
List<SampleItem> list = getDataList();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));
for (int i=0; i<list.size(); i++) {
SampleItem si = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
Cell cell1 = row.createCell(0);
cell1.setCellValue(si.getDate());
cell1.setCellStyle(cellStyle);
Cell cell2 = row.createCell(1);
cell2.setCellValue(si.getName());
Cell cell3 = row.createCell(2);
cell3.setCellValue(si.getScore());
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
workbook.close();
return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
Example 7
| Source File: FileUtil.java From JavaWeb with Apache License 2.0 | 5 votes |
|
public static XSSFCellStyle setStyle(XSSFWorkbook workbook) {
//设置字体;
XSSFFont font = workbook.createFont();
//设置字体大小;
font.setFontHeightInPoints((short) 20);
//设置字体名字;
font.setFontName("Courier New");
//font.setItalic(true);
//font.setStrikeout(true);
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(new XSSFColor(Color.BLACK));
//设置左边框;
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(new XSSFColor(Color.BLACK));
//设置右边框;
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(new XSSFColor(Color.BLACK));
//设置顶边框;
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(new XSSFColor(Color.BLACK));
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
Example 8
| Source File: FileUtil.java From JavaWeb with Apache License 2.0 | 5 votes |
|
public static XSSFCellStyle setStyle(XSSFWorkbook workbook) {
//设置字体;
XSSFFont font = workbook.createFont();
//设置字体大小;
font.setFontHeightInPoints((short) 20);
//设置字体名字;
font.setFontName("Courier New");
//font.setItalic(true);
//font.setStrikeout(true);
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(new XSSFColor(Color.BLACK));
//设置左边框;
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(new XSSFColor(Color.BLACK));
//设置右边框;
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(new XSSFColor(Color.BLACK));
//设置顶边框;
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(new XSSFColor(Color.BLACK));
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
Example 9
| Source File: ExcelNodeSerializer.java From dhis2-core with BSD 3-Clause "New" or "Revised" License | 5 votes |
|
@Override
protected void startSerialize( RootNode rootNode, OutputStream outputStream ) throws Exception
{
workbook = new XSSFWorkbook();
sheet = workbook.createSheet( "Sheet1" );
XSSFFont boldFont = workbook.createFont();
boldFont.setBold( true );
XSSFCellStyle boldCellStyle = workbook.createCellStyle();
boldCellStyle.setFont( boldFont );
// build schema
for ( Node child : rootNode.getChildren() )
{
if ( child.isCollection() )
{
if ( !child.getChildren().isEmpty() )
{
Node node = child.getChildren().get( 0 );
XSSFRow row = sheet.createRow( 0 );
int cellIdx = 0;
for ( Node property : node.getChildren() )
{
if ( property.isSimple() )
{
XSSFCell cell = row.createCell( cellIdx++ );
cell.setCellValue( property.getName() );
cell.setCellStyle( boldCellStyle );
}
}
}
}
}
}
Example 10
| Source File: AbstractPoiXlsxReportGenerator.java From yes-cart with Apache License 2.0 | 4 votes |
|
/**
* {@inheritDoc}
*/
@Override
public void generateReport(final ReportDescriptor descriptor,
final Map<String, Object> parameters,
final Object data,
final String lang,
final OutputStream outputStream) {
if (data == null || data instanceof Collection && ((Collection) data).isEmpty()) {
LOG.debug("No data, no report will be generated");
return;
}
try {
final XSSFWorkbook workbook = new XSSFWorkbook();
final XSSFSheet sheet = workbook.createSheet(descriptor.getReportId());
final XSSFCellStyle headerStyle = workbook.createCellStyle();
final XSSFFont font = workbook.createFont();
font.setBold(true);
headerStyle.setFont(font);
int rowNum = 0;
for (Object dataLine : (Collection) data) {
final Object[] line = (Object[]) dataLine;
final Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : line) {
final Cell cell = row.createCell(colNum++);
if (rowNum == 1) {
cell.setCellStyle(headerStyle);
}
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Number) {
cell.setCellValue(((Number) field).doubleValue());
} else {
cell.setCellValue("");
}
}
}
workbook.write(outputStream);
workbook.close();
} catch (Exception exp) {
LOG.error("Unable to generate report for " + descriptor + " in " + lang, exp);
}
}
Example 11
| Source File: PersonalReportExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
private void createFoot(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception {
Row footRow=sh.createRow(row);
Row footRowB=sh.createRow(row+1);
XSSFCellStyle cellStyle=wb.createCellStyle();
cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor("#FFFFFF"), null) );
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
XSSFFont cellFont=wb.createFont();
cellFont.setBold(true);
cellStyle.setFont(cellFont);
cellStyle.setWrapText(true);
Cell footCell1 = footRow.createCell(0);
footCell1.setCellValue("assess:");
footCell1.setCellStyle(cellStyle);
Cell footCell1B = footRowB.createCell(0);
footCell1B.setCellValue("assess:");
footCell1B.setCellStyle(cellStyle);
sh.addMergedRegion(new CellRangeAddress(row, row+1, 0, 0));
Cell footCell2 = footRow.createCell(1);
footCell2.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell2.setCellStyle(cellStyle);
Cell footCell3 = footRow.createCell(2);
footCell3.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell3.setCellStyle(cellStyle);
Cell footCell4 = footRow.createCell(3);
footCell4.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell4.setCellStyle(cellStyle);
Cell footCell2B = footRowB.createCell(1);
footCell2B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell2B.setCellStyle(cellStyle);
Cell footCell3B = footRowB.createCell(2);
footCell3B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell3B.setCellStyle(cellStyle);
Cell footCell4B = footRowB.createCell(3);
footCell4B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
footCell4B.setCellStyle(cellStyle);
sh.addMergedRegion(new CellRangeAddress(row, row+1, 1, 3));
Cell footCell5 = footRow.createCell(4);
footCell5.setCellValue("Total");
footCell5.setCellStyle(cellStyle);
float total = 0.0f;
if ( context.get("total")!=null && context.get("total") instanceof Float ) {
total = (Float)context.get("total");
}
Cell footCell6 = footRow.createCell(5);
footCell6.setCellValue( BscReportSupportUtils.parse2(total) );
footCell6.setCellStyle(cellStyle);
Cell footCell5b = footRowB.createCell(4);
footCell5b.setCellValue("Class");
footCell5b.setCellStyle(cellStyle);
Cell footCell6b = footRowB.createCell(5);
footCell6b.setCellValue( "" );
footCell6b.setCellStyle(cellStyle);
}
Example 12
| Source File: KpiPeriodTrendsExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
@SuppressWarnings("unchecked")
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
XSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ), null ) );
cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
cellHeadStyle.setBorderBottom( BorderStyle.THIN );
cellHeadStyle.setBorderTop( BorderStyle.THIN );
cellHeadStyle.setBorderRight( BorderStyle.THIN );
cellHeadStyle.setBorderLeft( BorderStyle.THIN );
XSSFFont cellHeadFont = wb.createFont();
cellHeadFont.setBold(true);
cellHeadStyle.setFont( cellHeadFont );
sh.setColumnWidth(0, 12000);
int row = 0;
Row nowRow = sh.createRow(row);
Cell cell1 = nowRow.createCell(0);
cell1.setCellStyle(cellHeadStyle);
cell1.setCellValue( "KPI" );
Cell cell2 = nowRow.createCell(1);
cell2.setCellStyle(cellHeadStyle);
cell2.setCellValue( "Maximum" );
Cell cell3 = nowRow.createCell(2);
cell3.setCellStyle(cellHeadStyle);
cell3.setCellValue( "Target" );
Cell cell4 = nowRow.createCell(3);
cell4.setCellStyle(cellHeadStyle);
cell4.setCellValue( "Minimum" );
Cell cell5 = nowRow.createCell(4);
cell5.setCellStyle(cellHeadStyle);
cell5.setCellValue( "Current score" );
Cell cell6 = nowRow.createCell(5);
cell6.setCellStyle(cellHeadStyle);
cell6.setCellValue( "Previous score" );
Cell cell7 = nowRow.createCell(6);
cell7.setCellStyle(cellHeadStyle);
cell7.setCellValue( "Change(%)" );
row++;
List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>)context.get("periodDatas");
for (PeriodTrendsData<KpiVO> periodData : periodDatas) {
nowRow = sh.createRow(row);
cell1 = nowRow.createCell(0);
cell1.setCellValue( periodData.getCurrent().getName() );
cell2 = nowRow.createCell(1);
cell2.setCellValue( periodData.getCurrent().getMax() );
cell3 = nowRow.createCell(2);
cell3.setCellValue( periodData.getCurrent().getTarget() );
cell4 = nowRow.createCell(3);
cell4.setCellValue( periodData.getCurrent().getMin() );
cell5 = nowRow.createCell(4);
cell5.setCellValue( BscReportSupportUtils.parse2( periodData.getCurrent().getScore() ) );
cell6 = nowRow.createCell(5);
cell6.setCellValue( BscReportSupportUtils.parse2( periodData.getPrevious().getScore() ) );
cell7 = nowRow.createCell(6);
cell7.setCellValue( BscReportSupportUtils.parse2( periodData.getChange() ) );
row++;
}
nowRow = sh.createRow(row);
cell1 = nowRow.createCell(0);
cell1.setCellValue( "Current period: " + (String)context.get("currentPeriodDateRange") + " , Previous period: " + (String)context.get("previousPeriodDateRange") );
}
Example 13
| Source File: PdcaReportExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
private int createPdcaItem(XSSFWorkbook wb, XSSFSheet sh, int row, XSSFCellStyle cellNormalStyle, List<PdcaItemVO> items, PdcaAuditVO audit) throws Exception {
XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor("#000000"), null );
XSSFColor bgLabelColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor("#F2F2F2"), null );
XSSFCellStyle cellLabelStyle = wb.createCellStyle();
cellLabelStyle.setFillForegroundColor( bgLabelColor );
cellLabelStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellLabelFont = wb.createFont();
cellLabelFont.setBold(true);
cellLabelFont.setColor(fnColor);
cellLabelStyle.setFont(cellLabelFont);
cellLabelStyle.setBorderBottom(BorderStyle.THIN);
cellLabelStyle.setBorderTop(BorderStyle.THIN);
cellLabelStyle.setBorderRight(BorderStyle.THIN);
cellLabelStyle.setBorderLeft(BorderStyle.THIN);
cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellLabelStyle.setAlignment(HorizontalAlignment.CENTER);
cellLabelStyle.setWrapText(true);
Map<String, String> pdcaTypeMap = PdcaType.getDataMap(false);
for (PdcaItemVO item : items) {
Row labelRow = sh.createRow(row);
Cell labelCell_6_1 = labelRow.createCell(0);
labelCell_6_1.setCellValue( pdcaTypeMap.get(item.getType()) );
labelCell_6_1.setCellStyle(cellLabelStyle);
Cell labelCell_6_2 = labelRow.createCell(1);
labelCell_6_2.setCellValue( item.getTitle() + ( !StringUtils.isBlank(item.getDescription()) ? "\n\n" + item.getDescription() : "" ) );
labelCell_6_2.setCellStyle(cellNormalStyle);
Cell labelCell_6_3 = labelRow.createCell(2);
labelCell_6_3.setCellValue( item.getEmployeeAppendNames() );
labelCell_6_3.setCellStyle(cellNormalStyle);
Cell labelCell_6_4 = labelRow.createCell(3);
labelCell_6_4.setCellValue( item.getStartDateDisplayValue() + " ~ " + item.getEndDateDisplayValue() );
labelCell_6_4.setCellStyle(cellNormalStyle);
Cell labelCell_6_5 = labelRow.createCell(4);
labelCell_6_5.setCellValue( (audit != null ? audit.getEmpId() : " ") );
labelCell_6_5.setCellStyle(cellNormalStyle);
Cell labelCell_6_6 = labelRow.createCell(5);
labelCell_6_6.setCellValue( (audit != null ? audit.getConfirmDateDisplayValue() : " ") );
labelCell_6_6.setCellStyle(cellNormalStyle);
row++;
}
return row;
}
Example 14
| Source File: KpisDashboardExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context, int row) throws Exception {
String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barChartsData") );
BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
ByteArrayOutputStream barBos = new ByteArrayOutputStream();
ImageIO.write( barImage, "png", barBos );
barBos.flush();
SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), row, 0);
//int row = 28;
row = row + 32;
List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
String year = (String)context.get("dateRangeLabel");
XSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellHeadFont = wb.createFont();
cellHeadFont.setBold(true);
cellHeadStyle.setFont( cellHeadFont );
int titleCellSize = 9;
Row headRow = sh.createRow( row );
for (int i=0; i<titleCellSize; i++) {
Cell headCell = headRow.createCell( i );
headCell.setCellStyle(cellHeadStyle);
headCell.setCellValue( "KPIs metrics gauge ( " + year + " )" );
}
sh.addMergedRegion( new CellRangeAddress(row, row, 0, titleCellSize-1) );
row = row+1;
int cellLeft = 5;
int rowSpace = 17;
for (Map<String, Object> data : chartDatas) {
Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0);
String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );
BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
ImageIO.write( imageData, "png", imgBos );
imgBos.flush();
SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor( bgColor );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellFont = wb.createFont();
cellFont.setBold(true);
cellFont.setColor(fnColor);
cellStyle.setFont(cellFont);
int perTitleCellSize = 4;
Row nowRow = sh.createRow(row);
for (int i=0; i<perTitleCellSize; i++) {
Cell cell1 = nowRow.createCell(cellLeft);
cell1.setCellStyle(cellStyle);
cell1.setCellValue( (String)nodeData.get("name") );
}
sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
nowRow = sh.createRow(row+1);
Cell cell2 = nowRow.createCell(cellLeft);
cell2.setCellValue( "Maximum: " + String.valueOf( nodeData.get("max") ) );
nowRow = sh.createRow(row+2);
Cell cell3 = nowRow.createCell(cellLeft);
cell3.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );
nowRow = sh.createRow(row+3);
Cell cell4 = nowRow.createCell(cellLeft);
cell4.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );
nowRow = sh.createRow(row+4);
Cell cell5 = nowRow.createCell(cellLeft);
cell5.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );
row += rowSpace;
}
return row;
}
Example 15
| Source File: PerspectivesDashboardExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
String pieBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("pieCanvasToData") );
String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barCanvasToData") );
BufferedImage pieImage = SimpleUtils.decodeToImage( pieBase64Content );
BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
ByteArrayOutputStream pieBos = new ByteArrayOutputStream();
ImageIO.write( pieImage, "png", pieBos );
pieBos.flush();
ByteArrayOutputStream barBos = new ByteArrayOutputStream();
ImageIO.write( barImage, "png", barBos );
barBos.flush();
SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0);
SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9);
int row = 21;
List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
String year = (String)context.get("year");
XSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellHeadFont = wb.createFont();
cellHeadFont.setBold(true);
//cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) );
cellHeadStyle.setFont( cellHeadFont );
int titleRow = row - 1;
int titleCellSize = 14;
Row headRow = sh.createRow( titleRow );
for (int i=0; i<titleCellSize; i++) {
Cell headCell = headRow.createCell( i );
headCell.setCellStyle(cellHeadStyle);
headCell.setCellValue( "Perspectives metrics gauge ( " + year + " )" );
}
sh.addMergedRegion( new CellRangeAddress(titleRow, titleRow, 0, titleCellSize-1) );
int cellLeft = 10;
int rowSpace = 17;
for (Map<String, Object> data : chartDatas) {
Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0);
String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );
BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
ImageIO.write( imageData, "png", imgBos );
imgBos.flush();
SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor( bgColor );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellFont = wb.createFont();
cellFont.setBold(true);
cellFont.setColor(fnColor);
cellStyle.setFont(cellFont);
int perTitleCellSize = 4;
Row nowRow = sh.createRow(row);
for (int i=0; i<perTitleCellSize; i++) {
Cell cell1 = nowRow.createCell(cellLeft);
cell1.setCellStyle(cellStyle);
cell1.setCellValue( (String)nodeData.get("name") );
}
sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
nowRow = sh.createRow(row+1);
Cell cell2 = nowRow.createCell(cellLeft);
cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );
nowRow = sh.createRow(row+2);
Cell cell3 = nowRow.createCell(cellLeft);
cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );
nowRow = sh.createRow(row+3);
Cell cell4 = nowRow.createCell(cellLeft);
cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );
row += rowSpace;
}
return row;
}
Example 16
| Source File: ObjectivesDashboardExcelCommand.java From bamboobsc with Apache License 2.0 | 4 votes |
|
@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barChartsData") );
BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
ByteArrayOutputStream barBos = new ByteArrayOutputStream();
ImageIO.write( barImage, "png", barBos );
barBos.flush();
SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0);
int row = 28;
List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
String year = (String)context.get("year");
XSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellHeadFont = wb.createFont();
cellHeadFont.setBold(true);
cellHeadStyle.setFont( cellHeadFont );
int titleCellSize = 14;
Row headRow = sh.createRow( row );
for (int i=0; i<titleCellSize; i++) {
Cell headCell = headRow.createCell( i );
headCell.setCellStyle(cellHeadStyle);
headCell.setCellValue( "Objectives metrics gauge ( " + year + " )" );
}
sh.addMergedRegion( new CellRangeAddress(row, row, 0, titleCellSize-1) );
row = row+1;
int cellLeft = 10;
int rowSpace = 17;
for (Map<String, Object> data : chartDatas) {
Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0);
String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );
BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
ImageIO.write( imageData, "png", imgBos );
imgBos.flush();
SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor( bgColor );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellFont = wb.createFont();
cellFont.setBold(true);
cellFont.setColor(fnColor);
cellStyle.setFont(cellFont);
int perTitleCellSize = 4;
Row nowRow = sh.createRow(row);
for (int i=0; i<perTitleCellSize; i++) {
Cell cell1 = nowRow.createCell(cellLeft);
cell1.setCellStyle(cellStyle);
cell1.setCellValue( (String)nodeData.get("name") );
}
sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
nowRow = sh.createRow(row+1);
Cell cell2 = nowRow.createCell(cellLeft);
cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );
nowRow = sh.createRow(row+2);
Cell cell3 = nowRow.createCell(cellLeft);
cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );
nowRow = sh.createRow(row+3);
Cell cell4 = nowRow.createCell(cellLeft);
cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );
row += rowSpace;
}
return row;
}
Example 17
| Source File: PanamaHitek_DataBuffer.java From PanamaHitek_Arduino with GNU Lesser General Public License v3.0 | 4 votes |
|
/**
* Construye la hoja de Excel
*
* @return Instancia de la clase XSSFWorkbook con los datos almacenados en
* el buffer de datos
*/
private XSSFWorkbook buildSpreadsheet() {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
System.out.println("Building spreadsheet...");
System.out.println("Buffer size: " + mainBuffer.size());
for (int i = 0; i <= mainBuffer.get(0).size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < variableList.size(); j++) {
Cell cell = row.createCell(j);
if (i == 0) {
cell.setCellValue((String) variableList.get(j));
} else {
Object value = classList.get(j);
if ((value instanceof String) || (value.equals(String.class))) {
cell.setCellValue((String) mainBuffer.get(j).get(i - 1));
} else if ((value instanceof Boolean) || (value.equals(Boolean.class))) {
cell.setCellValue((Boolean) mainBuffer.get(j).get(i - 1));
} else if ((value instanceof Date) || (value.equals(Date.class))) {
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat(dateStringFormat));
cell.setCellValue((Date) mainBuffer.get(j).get(i - 1));
cell.setCellStyle(cellStyle);
} else if ((value instanceof Integer) || (value.equals(Integer.class))) {
cell.setCellValue((Integer) mainBuffer.get(j).get(i - 1));
} else if ((value instanceof Long) || (value.equals(Long.class))) {
cell.setCellValue((Long) mainBuffer.get(j).get(i - 1));
} else if ((value instanceof Float) || (value.equals(Float.class))) {
cell.setCellValue((Float) mainBuffer.get(j).get(i - 1));
} else if ((value instanceof Double) || (value.equals(Double.class))) {
cell.setCellValue((Double) mainBuffer.get(j).get(i - 1));
}
}
}
}
return workbook;
}
Example 18
| Source File: DataExportController.java From MyBox with Apache License 2.0 | 4 votes |
|
protected boolean writeExcel(File file, String sql) {
try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);
FileWriter writer = new FileWriter(file, Charset.forName("utf-8"))) {
String filename = file.getAbsolutePath();
conn.setReadOnly(true);
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("sheet1");
List<String> columns = columnNames();
XSSFRow titleRow = sheet.createRow(0);
XSSFCellStyle horizontalCenter = wb.createCellStyle();
horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
for (int i = 0; i < columns.size(); i++) {
XSSFCell cell = titleRow.createCell(i);
cell.setCellValue(columns.get(i));
cell.setCellStyle(horizontalCenter);
}
int count = 0;
try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
while (results.next()) {
if (cancelled) {
updateLogs(message("Cancelled") + " " + filename);
return false;
}
writeExcel(conn, sheet, results, count);
count++;
if (verboseCheck.isSelected() && (count % 50 == 0)) {
updateLogs(message("Exported") + " " + count + ": " + filename);
}
}
}
for (int i = 0; i < columns.size(); i++) {
sheet.autoSizeColumn(i);
}
try ( OutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
return true;
} catch (Exception e) {
updateLogs(e.toString());
return false;
}
}
