org.apache.poi.xssf.usermodel.XSSFSheet Java Examples

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

public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    int firstRow1 = sheet1.getFirstRowNum();
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;
            break;
        }
    }
    return equalSheets;
}
 

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

private String createExcel(Context context) throws Exception {
	
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	int row = 0;
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh1 = wb.createSheet( "PDCA Report" );
	
	row += this.createPdca(wb, sh1, row, 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, "pdca-report.xlsx");
	file = null;
	return oid;
}
 

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

/**
 * Writes the data to a cell and returns the cell.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @param data data to write in cell (String, Date, Boolean, Double, Float, Integer, Number)
 * @return the given cell at icol;irow
 * @see
 */
public Cell writeToCell(XSSFSheet sheet, int icol, int irow, Object data) {
  Cell cell = getCell(sheet, icol, irow);
  // write data
  if (data instanceof Date)
    cell.setCellValue((Date) data);
  else if (data instanceof Boolean)
    cell.setCellValue((Boolean) data);
  else if (data instanceof String)
    cell.setCellValue((String) data);
  else if ((data instanceof Double))
    cell.setCellValue((Double) data);
  else if ((data instanceof Number))
    cell.setCellValue(((Number) data).doubleValue());
  else if ((data instanceof Integer))
    cell.setCellValue(((Integer) data).doubleValue());
  else if ((data instanceof Float))
    cell.setCellValue(((Float) data).doubleValue());
  return cell;
}
 

private Span getSpan(XSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 

/**
 * exports a data[rows][columns] array
 *
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public XSSFWorkbook exportDataArrayToFile(File file, String sheetname, Object[][] data,
    boolean rowsFirst) {
  // open wb
  XSSFWorkbook wb = new XSSFWorkbook();
  XSSFSheet sheet = getSheet(wb, sheetname);
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != null) {
        if (rowsFirst)
          writeToCell(sheet, c, r, data[r][c]);
        else
          writeToCell(sheet, r, c, data[r][c]);
      }
    }
  }

  // save wb
  saveWbToFile(file, wb);
  return wb;
}
 

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

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, "kpis-period-trends.xlsx");
	file = null;
	return oid;
}
 

/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) {
	Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
	for (POIXMLDocumentPart dr : sheet.getRelations()) {
		if (dr instanceof XSSFDrawing) {
			XSSFDrawing drawing = (XSSFDrawing) dr;
			List<XSSFShape> shapes = drawing.getShapes();
			for (XSSFShape shape : shapes) {
				XSSFPicture pic = (XSSFPicture) shape;
				XSSFClientAnchor anchor = pic.getPreferredSize();
				CTMarker ctMarker = anchor.getFrom();
				String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
				sheetIndexPicMap.put(picIndex, pic.getPictureData());
			}
		}
	}
	return sheetIndexPicMap;
}
 

/**
 * writes regression data to sheets
 * 
 * @param sheet
 * @param reg
 * @param dp
 * @param quantifier
 */
public void writeRegressionToSheet(XSSFSheet sheet, SimpleRegression reg, double[][] dp) {
  //
  writeToCell(sheet, 0, 1, "c = (I-intercept)/slope");
  writeToCell(sheet, 0, 2, "intercept = ");
  writeToCell(sheet, 1, 2, reg.getIntercept());
  writeToCell(sheet, 0, 2, "slope = ");
  writeToCell(sheet, 1, 2, reg.getSlope());
  writeToCell(sheet, 0, 3, "R^2 = ");
  writeToCell(sheet, 1, 3, reg.getRSquare());
  writeToCell(sheet, 0, 3, "R^2 = ");
  // datapoints
  writeToCell(sheet, 4, 0, "regression");
  writeToCell(sheet, 4, 1, "x");
  writeToCell(sheet, 5, 1, "intensity");
  writeDataArrayToSheet(sheet, dp, 4, 2, true);
}
 

private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    int rowIndex = 0;
    try {
        String sheetName = data.getName();
        if (null == sheetName) {
            sheetName = "Sheet1";
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
        rowIndex = writeExcel(wb, sheet, data);
        wb.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //此处需要关闭 wb 变量
        out.close();
    }
    return rowIndex;
}
 

/**
 * writes a data[rows][columns] array to the given wb
 * 
 * @param realFilePath
 * @param data
 * @param rowsFirst true: [rows][cols] false [cols][rows]
 */
public void writeDataArrayToSheet(XSSFSheet sheet, Object[][] data, int ic, int ir,
    boolean rowsFirst) {
  // write to wb
  for (int r = 0; r < data.length; r++) {
    // all columns
    for (int c = 0; c < data[r].length; c++) {
      if (data[r][c] != null) {
        if (rowsFirst)
          writeToCell(sheet, c + ic, r + ir, data[r][c]);
        else
          writeToCell(sheet, r + ic, c + ir, data[r][c]);
      }
    }
  }
}
 

public static void writeExcel(File file, List<GeographyCode> codes) {
    try {
        if (file == null || codes == null || codes.isEmpty()) {
            return;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = writeExcelHeader(wb, sheet);
        for (int i = 0;
                i < codes.size();
                i++) {
            GeographyCode code = codes.get(i);
            writeExcel(sheet, i, code);
        }
        for (int i = 0;
                i < columns.size();
                i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
    } catch (Exception e) {

    }
}
 

public static XSSFWorkbook merge(XSSFWorkbook source, HSSFSheet sheet) {
	XSSFWorkbook destinationWorkbook = source;
	XSSFSheet destinationSheet = destinationWorkbook.getSheetAt(0);

	List<CellStyle> styleMap = new ArrayList<CellStyle>();

	for (short i = 0; i < destinationWorkbook.getNumCellStyles(); i++) {
		styleMap.add(destinationWorkbook.getCellStyleAt(i));
	}

	copySheetSettings(destinationSheet, sheet);
	copySheet(styleMap, sheet, destinationSheet);
	copyPictures(destinationSheet, sheet);

	refreshFormula(destinationWorkbook);

	return destinationWorkbook;
}
 

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

/**
 * XSSFRichTextString.utfDecode()<br/>
 * value.contains("_x")<br/>
 * Pattern.compile("_x([0-9A-Fa-f]{4})_");
 */
private static void export2007WithStyle(String filePath) {
	try {
		OutputStream os = new FileOutputStream(filePath);
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		XSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellValue(TestUtil.RICH_TEXT_STRINGS[0]
				+ escape(TestUtil.REGEX + TestUtil.RICH_TEXT_STRINGS[1]
						+ TestUtil.REGEX) + TestUtil.RICH_TEXT_STRINGS[2]);
		CellStyle style = sheet.getWorkbook().createCellStyle();
		XSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		style.setFont(font);
		cell.setCellStyle(style);
		// richString.applyFont(font);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

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

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

/** 
 * 获取Excel2007图片 
 * @param sheet 当前sheet对象
 * @param workbook 工作簿对象 
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData 
 */  
public static Map<String, PictureData> getSheetPictrues07(  
        XSSFSheet sheet, XSSFWorkbook workbook) {  
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
    for (POIXMLDocumentPart dr : sheet.getRelations()) {  
        if (dr instanceof XSSFDrawing) {  
            XSSFDrawing drawing = (XSSFDrawing) dr;  
            List<XSSFShape> shapes = drawing.getShapes();  
            for (XSSFShape shape : shapes) {  
                XSSFPicture pic = (XSSFPicture) shape;  
                XSSFClientAnchor anchor = pic.getPreferredSize();  
                CTMarker ctMarker = anchor.getFrom();  
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();  
                sheetIndexPicMap.put(picIndex, pic.getPictureData());  
            }  
        }  
    }  
    return sheetIndexPicMap;  
}
 

private static void setDataValidationList(int j, int k, int l, int m, String data, XSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	// 设置数据有效性加载在哪个单元格上。
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textlist);
	// 四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
	// data_validation_list.setSuppressDropDownArrow(false);
	
	sheet.addValidationData(data_validation_list);
}
 

@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 

@SuppressWarnings("deprecation")
public void readXLSX(String path, int num) throws InvalidFormatException, IOException {
	File file = new File(path);
	@SuppressWarnings("resource")
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(num);

	int rowstart = xssfSheet.getFirstRowNum();
	int rowEnd = xssfSheet.getLastRowNum();
	for (int i = rowstart; i <= rowEnd; i++) {
		XSSFRow row = xssfSheet.getRow(i);
		if (null == row) {
			continue;
		}
		int cellStart = row.getFirstCellNum();
		int cellEnd = row.getLastCellNum();

		for (int k = cellStart; k <= cellEnd; k++) {
			XSSFCell cell = row.getCell(k);
			if (null == cell) {
				continue;
			}

			switch (cell.getCellTypeEnum()) {
				case NUMERIC: // 数字
					System.out.print(cell.getNumericCellValue() + "   ");
					break;
				case STRING: // 字符串
					System.out.print(cell.getStringCellValue() + "   ");
					break;
				case BOOLEAN: // Boolean
					System.out.println(cell.getBooleanCellValue() + "   ");
					break;
				case FORMULA: // 公式
					System.out.print(cell.getCellFormula() + "   ");
					break;
				case BLANK: // 空值
					System.out.println(" ");
					break;
				case ERROR: // 故障
					System.out.println(" ");
					break;
				default:
					System.out.print("未知类型   ");
					break;
			}

		}
		System.out.print("\n");
	}

}
 

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

public static void writeExcel(File file, List<EpidemicReport> reports, List<String> extraFields) {
    try {
        if (file == null || reports == null || reports.isEmpty()) {
            return;
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = writeExcelHeader(wb, sheet, extraFields);
        for (int i = 0; i < reports.size(); i++) {
            EpidemicReport report = reports.get(i);
            writeExcel(sheet, i, report, extraFields);
        }
        for (int i = 0; i < columns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
    } catch (Exception e) {

    }
}
 

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

/**
 * create restrictions on the data cells
 * 
 * @param mainsheet sheet with data
 * @param restrictionsheet sheet with restriction values
 * @param column index of column (starting with zero)
 * @param nbofchoices number of choices (starting with zero)
 * @param nbofrows number of rows (starting with zero)
 */
public static  void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
	DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
	String columnletter =  CellReference.convertNumToColString(column);
	String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
	DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
	CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
	
	DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	dataValidation.setSuppressDropDownArrow(true);
	mainsheet.addValidationData(dataValidation);
}
 

/**
 * 写入电子表格的主要流程
 * @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 setCellPicture(XSSFWorkbook wb, XSSFSheet sh, byte[] iconBytes, int row, int col) throws Exception {
       int myPictureId = wb.addPicture(iconBytes, XSSFWorkbook.PICTURE_TYPE_PNG);
       
       XSSFDrawing drawing = sh.createDrawingPatriarch();
       XSSFClientAnchor myAnchor = new XSSFClientAnchor();
      
       myAnchor.setCol1(col);
       myAnchor.setRow1(row);
       
       XSSFPicture myPicture = drawing.createPicture(myAnchor, myPictureId);
       myPicture.resize();
}