Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#createSheet()

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "objectives-dashboard.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putTables(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "forecast-analysis.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "perspectives-dashboard.xlsx");
	file = null;
	return oid;
}
 

@Test
public void test2() throws IOException {
	 //create a new workbook
	XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    String imgPath = "D:\\Xiaoi\\logo\\logo.png";
    //add picture data to this workbook.
    InputStream is = new FileInputStream(imgPath);
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
    is.close();

    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = wb.createSheet();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(3);
    anchor.setRow1(2);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //auto-size picture relative to its top-left corner
    pict.resize();

    //save workbook
    String file = "E:\\ConvertTester\\excel\\picture.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    try (OutputStream fileOut = new FileOutputStream(file)) {
        wb.write(fileOut);
    }
}
 

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

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
public void process(String fileName) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet("sheet1");
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream("template.xlsx");
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile("sheet", ".xml");
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File("template.xlsx");
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

private void writeMenuSheet(XSSFWorkbook workBook, String menu, String app) {
  XSSFSheet sheet = workBook.getSheet(app + "-menu");
  if (sheet == null) {
    sheet = workBook.createSheet(app + "-menu");
    writeRow(sheet, menuHeaders);
  }
  String usersRights = configMenus.contains(menu) ? "" : "x";
  writeRow(sheet, new String[] {menu, usersRights, "x"});
  appMenus.remove(menu);
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( OutputStream out ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

private String createExcel(Context context) throws Exception {
	String visionOid = (String)context.get("visionOid");
	VisionVO vision = null;
	BscStructTreeObj treeObj = (BscStructTreeObj)this.getResult(context);
	for (VisionVO visionObj : treeObj.getVisions()) {
		if (visionObj.getOid().equals(visionOid)) {
			vision = visionObj;
		}
	}
	BscReportPropertyUtils.loadData();
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	int row = 0;
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	row += this.createHead(wb, sh, row, vision, context);
	row = this.createMainBody(wb, sh, row, vision);
	
	this.putSignature(wb, sh, row+1, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "department-report.xlsx");
	file = null;
	return oid;		
}
 

@Test
public void testExcel2() throws IOException {
	String filepath = "E:\\ConvertTester\\excel\\abcd.xlsx";
	File originFile = new File(filepath);
	InputStream in = new FileInputStream(originFile);
	XSSFWorkbook workbook = new XSSFWorkbook(in);
	XSSFSheet sheet = workbook.createSheet("testSheet");

	XSSFDrawing drawing = sheet.createDrawingPatriarch();
	
	XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);

}
 

/**
 * 写入电子表格的主要流程
 * 
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process(String fileName) throws Exception {
	
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet("sheet1");
	
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream("template.xlsx");
	wb.write(os);
	os.close();

	// 生成xml文件
	File tmp = File.createTempFile("sheet", ".xml");
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();

	// 使用产生的数据替换模板
	File templateFile = new File("template.xlsx");
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	// 删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile() && templateFile.exists()) {
		templateFile.delete();
	}
}
 

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

		}
	}
 

public void writeObjectSheet(XSSFWorkbook workBook, String obj, String menu, String app) {

    XSSFSheet sheet = workBook.getSheet(app);
    if (sheet == null) {
      sheet = workBook.createSheet(app);
      writeRow(sheet, objectHeaders);
    }
    String usersRights = appMenus.contains(menu) ? "rwcde" : "r";
    writeRow(sheet, new String[] {obj, usersRights, "rwcde"});
  }
 

/**
 * 创建Excel表格
 * @throws Exception 
 */
public static boolean createXlsx(File target, String[] columnNames, Object[][] data) {
	try {
		int num = 0;
		// 创建工作簿
		XSSFWorkbook wb = new XSSFWorkbook();
		// 工作表
		XSSFSheet sheet = wb.createSheet("学生信息表");
		// 标头行，代表第一行
		XSSFRow header = sheet.createRow(num++);
		// 创建单元格，0代表第一行第一列
		for (int i = 0; i < columnNames.length; i++) {
			//设置excel表格某一行的值
			header.createCell(i).setCellValue(columnNames[i]);
		}
		
		for (int i = 0; i < data.length; i++) {
			//设置操作行为下一行
			XSSFRow row = sheet.createRow(num++);
			for (int j = 0; j < data[i].length; j++) {
				//设置excel表格某一行的值
				row.createCell(j).setCellValue(data[i][j].toString());
			}
		}
		
		/**
		 * 输出表格
		 */
		FileOutputStream fos = new FileOutputStream(target);
		wb.write(fos);
		fos.close();
		return true;
	} catch (Exception e) {
		return false;
	}
}
 

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

public static void main(String[] args) {
	// Blank workbook
	XSSFWorkbook workbook = new XSSFWorkbook();
	// Create a blank sheet
	XSSFSheet sheet = workbook.createSheet("Employee Data");
	// This data needs to be written (Object[])
	Map<String, Object[]> data = new TreeMap<String, Object[]>();
	data.put("1", new Object[] { "ID", "NAME", "LASTNAME" });
	data.put("2", new Object[] { 1, "Amit", "Shukla" });
	data.put("3", new Object[] { 2, "Lokesh", "Gupta" });
	data.put("4", new Object[] { 3, "John", "Adwards" });
	data.put("5", new Object[] { 4, "Brian", "Schultz" });
	// Iterate over data and write to sheet
	Set<String> keyset = data.keySet();
	int rownum = 0;
	for (String key : keyset) {
		Row row = sheet.createRow(rownum++);
		Object[] objArr = data.get(key);
		int cellnum = 0;
		for (Object obj : objArr) {
			Cell cell = row.createCell(cellnum++);
			if (obj instanceof String)
				cell.setCellValue((String) obj);
			else if (obj instanceof Integer)
				cell.setCellValue((Integer) obj);
		}
	}
	try {
		// Write the workbook in file system
		FileOutputStream out = new FileOutputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		workbook.write(out);
		out.close();
		System.out.println(EXCEL_NAME + Globals.SUFFIX_XLSX
				+ TestUtil.SUCCESS);
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * {@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);
    }

}
 

private void startFile() {
	workbook = new XSSFWorkbook();
	sheet = workbook.createSheet("DSM");
}