Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#createRow()
The following examples show how to use
org.apache.poi.xssf.usermodel.XSSFSheet#createRow() .
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: ObjectDataExportServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 8 votes |
|
private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
for (String model : data.keySet()) {
XSSFSheet sheet = workBook.createSheet(model);
int count = 0;
for (String[] record : data.get(model)) {
XSSFRow row = sheet.createRow(count);
int cellCount = 0;
for (String val : record) {
XSSFCell cell = row.createCell(cellCount);
cell.setCellValue(val);
cellCount++;
}
count++;
}
}
File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
FileOutputStream out = new FileOutputStream(excelFile);
workBook.write(out);
out.close();
return metaFiles.upload(excelFile);
}
Example 3
| Source File: PoiXSSFExcelUtil.java From JavaWeb with Apache License 2.0 | 6 votes |
|
private static XSSFWorkbook writeSheetData(List<List<String>> data,String sheetName){
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
XSSFRow[] rows = new XSSFRow[data.size()];
for(int i=0;i<data.size();i++){
List<String> columns = data.get(i);
rows[i] = xssfSheet.createRow(i);
//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
XSSFCell[] cells = new XSSFCell[columns.size()];
for(int j=0;j<columns.size();j++){
cells[j] = rows[i].createCell(j);
//cells[j].setCellStyle(XSSFCellStyle);
cells[j].setCellValue(columns.get(j));
}
}
return xssfWorkbook;
}
Example 4
| Source File: ExcelExportUtil.java From poi with Apache License 2.0 | 6 votes |
|
public static void export2007(String filePath) {
try {
// 输出流
OutputStream os = new FileOutputStream(filePath);
// 工作区
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
for (int i = 0; i < 1000; i++) {
// 创建第一个sheet
// 生成第一行
XSSFRow row = sheet.createRow(i);
// 给这一行的第一列赋值
row.createCell(0).setCellValue("column" + i);
// 给这一行的第一列赋值
row.createCell(1).setCellValue("column" + i);
// System.out.println(i);
}
// 写文件
wb.write(os);
// 关闭输出流
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Example 5
| Source File: Util.java From Knowage-Server with GNU Affero General Public License v3.0 | 6 votes |
|
/**
* @param destination
* the sheet to create from the copy.
* @param the
* sheet to copy.
* @param copyStyle
* true copy the style.
*/
private static void copySheet(List<CellStyle> list, HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
int maxColumnNum = 0;
List<CellStyle> styleMap = null;
if (copyStyle) {
styleMap = list;
}
for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
HSSFRow srcRow = source.getRow(i);
XSSFRow destRow = destination.createRow(i);
if (srcRow != null) {
copyRow(source, destination, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
destination.shiftRows(destination.getFirstRowNum(), destination.getLastRowNum(), 3);
for (int i = 0; i <= maxColumnNum; i++) {
destination.autoSizeColumn(i);
}
}
Example 6
| Source File: Poi2Test.java From easyexcel with Apache License 2.0 | 5 votes |
|
@Test
public void lastRowNumXSSF() throws IOException {
String file = "D:\\test\\珠海.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
xssfSheet.createRow(20);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
Example 7
| Source File: TestExternalRelvarXLSX2.java From Rel with Apache License 2.0 | 5 votes |
|
private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
row = sheet.createRow(rowNum);
cell = row.createCell(0);
cell.setCellValue(arg0);
cell = row.createCell(1);
cell.setCellValue(arg1);
cell = row.createCell(2);
cell.setCellValue(arg2);
}
Example 8
| Source File: ExcelFileUtils.java From SWET with MIT License | 5 votes |
|
public static void writeXLSXFile() throws Exception {
XSSFWorkbook xssfwb = new XSSFWorkbook();
XSSFSheet sheet = xssfwb.createSheet(sheetName);
for (int row = 0; row < tableData.size(); row++) {
XSSFRow xddfrow = sheet.createRow(row);
rowData = tableData.get(row);
for (int col = 0; col < rowData.size(); col++) {
XSSFCell cell = xddfrow.createCell(col);
cell.setCellValue(rowData.get(col));
logger.info("Writing " + row + " " + col + " " + rowData.get(col));
}
}
try (OutputStream fileOutputStream = new FileOutputStream(
excelFileName)) {
xssfwb.write(fileOutputStream);
xssfwb.close();
fileOutputStream.flush();
fileOutputStream.close();
} catch (IOException e) {
String message = String.format("Exception saving XLSX file %s\n",
excelFileName) + e.getMessage();
logger.info(message);
// NOTE: throw exceptions with user friendly messages to be rendered
// by the master app
throw new Exception(message);
}
}
Example 9
| Source File: TestExternalRelvarXLSX3.java From Rel with Apache License 2.0 | 5 votes |
|
@Before
public void testXLS1() throws IOException {
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = null;
XSSFCell cell = null;
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("A");
cell = row.createCell(1);
cell.setCellValue("B");
cell = row.createCell(2);
cell.setCellValue("C");
insert(1,sheet,row,cell,1,2,3);
insert(2,sheet,row,cell,4,5,6);
insert(3,sheet,row,cell,4,5,6);
insert(4,sheet,row,cell,1,2,3);
insert(5,sheet,row,cell,7,8,9);
insert(6,sheet,row,cell,7,8,9);
insert(7,sheet,row,cell,4,5,6);
try (FileOutputStream out = new FileOutputStream(file)) {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
String src =
"BEGIN;\n" +
"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
"END;\n" +
"true";
testEquals("true", src);
}
Example 10
| Source File: TestExternalRelvarXLSX1.java From Rel with Apache License 2.0 | 5 votes |
|
@Before
public void testXLS1() throws IOException {
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = null;
XSSFCell cell = null;
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("A");
cell = row.createCell(1);
cell.setCellValue("B");
cell = row.createCell(2);
cell.setCellValue("C");
insert(1,sheet,row,cell,1,2,3);
insert(2,sheet,row,cell,4,5,6);
insert(3,sheet,row,cell,7,8,9);
try (FileOutputStream out = new FileOutputStream(file)) {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
String src =
"BEGIN;\n" +
"var myvar external xls \"" + file.getAbsolutePath() + "\" autokey;" +
"END;\n" +
"true";
testEquals("true", src);
}
Example 11
| Source File: SurveyInstanceExtractor.java From waltz with Apache License 2.0 | 5 votes |
|
private int writeExcelHeader(List<SurveyQuestion> questions, XSSFSheet sheet) {
Row headerRow = sheet.createRow(0);
AtomicInteger colNum = new AtomicInteger();
mkHeaderStrings(questions).forEach(hdr -> writeExcelHeaderCell(headerRow, colNum, hdr));
return colNum.get();
}
Example 12
| Source File: PoiFormatTest.java From easyexcel with Apache License 2.0 | 5 votes |
|
@Test
public void lastRowNumXSSF() throws IOException {
String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
xssfSheet.createRow(20);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
Example 13
| Source File: TestExternalRelvarXLSX1.java From Rel with Apache License 2.0 | 5 votes |
|
private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
row = sheet.createRow(rowNum);
cell = row.createCell(0);
cell.setCellValue(arg0);
cell = row.createCell(1);
cell.setCellValue(arg1);
cell = row.createCell(2);
cell.setCellValue(arg2);
}
Example 14
| Source File: PoiXSSFExcelUtil.java From JavaWeb with Apache License 2.0 | 5 votes |
|
private static XSSFWorkbook writeSheetObject(List<Object> data,String sheetName) throws Exception {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
XSSFRow[] rows = new XSSFRow[data.size()];
for(int i=0;i<data.size();i++){
Object classes = data.get(i);
Object target = classes.getClass().newInstance();
Field[] fields = target.getClass().getDeclaredFields();
rows[i] = xssfSheet.createRow(i);
//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
XSSFCell[] cells = new XSSFCell[fields.length];
for(int j=0;j<fields.length;j++){
String fieldName = fields[j].getName();
fieldName = fieldName.substring(0,1).toUpperCase()+fieldName.substring(1,fieldName.length());
Method method = target.getClass().getDeclaredMethod("get"+fieldName,new Class[]{});
cells[j] = rows[i].createCell(j);
Object value = method.invoke(classes,new Object[]{});
if(value instanceof Double){
cells[j].setCellValue(new Double(value.toString()));
}else if(value instanceof Integer){
cells[j].setCellValue(new Double(value.toString()).intValue());
}else if(value instanceof Float){
cells[j].setCellValue(new Double(value.toString()));
}else{
cells[j].setCellValue(value.toString());
}
//cells[j].setCellStyle(XSSFCellStyle);
}
}
return xssfWorkbook;
}
Example 15
| Source File: XSSFExcelWriterReader.java From mzmine3 with GNU General Public License v2.0 | 5 votes |
|
/**
* Returns the cell of a given sheet at icol;irow. Former values are overwritten.
*
* @param sheet the sheet to write on
* @param icol index of column
* @param irow index of row
* @return the given cell at icol;irow
* @see
*/
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
// try to get row
Row row = sheet.getRow(irow);
// if not exist: create row
if (row == null)
row = sheet.createRow(irow);
// get cell
return row.createCell(icol);
}
Example 16
| 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 17
| 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 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;
}
}
Example 19
| 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 20
| 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;
}
