/** * ************************************************************************* Public methods * ************************************************************************ */ @Override public void open(String filepath) throws Exception { this.filepath = filepath; String author = ini.getValue("Excel", "Author", "Toël Hartmann"); String keywords = ini.getValue("Excel", "Keywords", ""); wb = new HSSFWorkbook(); // Set some properties wb.createInformationProperties(); wb.getSummaryInformation().setAuthor(author); wb.getSummaryInformation().setKeywords(keywords); wb.getSummaryInformation().setCreateDateTime(new Date()); // Create the cell style for column titles titleStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleStyle.setFont(font); String sheetName = getConfigValue("Excel", "sheet", null); if (sheetName == null || sheetName.isEmpty()) sheetName = FileUtils.getFileNameWithoutExtention(filepath); sheet = wb.createSheet(sheetName); if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) { sheet.createFreezePane(0, 1); } }
/** * Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件] * * @param sheetName 写入excel的sheet名称 * @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件 * @param map 要导出的字段。(格式:字段名 <-> 字段中文名)其中字段名应该是对应javaBean中的某属性。<br> * 若该字段为公式的话,必须符合一定的格式:公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符(,:)&操作符<br> * 如:SUM&A&C&2&,&- 它返回的公司是:SUM(A3,-C3)。具体参照: <code>com.integral.util.office.ExcelFormula * </code> * @param dateFormat 日期格式(可选),默认:yyyy-MM-dd * @author:[代超] * @throws IOException * @throws Exception * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ @SuppressWarnings("deprecation") public void writExcelFile( String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat) throws Exception { if (dataSet == null || dataSet.size() < 1) { return; } if (map == null || map.size() < 1) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } sheetName = sheetName == null ? "" : sheetName; // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 设置表格的样式 CellStyle headerStyle = setRootSheetSysle(workbook); // 产生表格标题行 HSSFRow row = sheet.createRow(0); // 将第一行冻结 sheet.createFreezePane(1, 1); if (map != null) { Object[] obj = map.values().toArray(); for (int i = 0; i < obj.length; i++) { HSSFCell cell = row.createCell(i); String header = obj[i] == null ? "" : obj[i].toString(); cell.setCellStyle(headerStyle); if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) { // 公式 header = obj[i].toString().replace("formula", ""); } HSSFRichTextString text = new HSSFRichTextString(header); cell.setCellValue(text); } } // 遍历数据集合,产生数据行 Iterator<T> it = dataSet.iterator(); for (int i = 1; it.hasNext(); i++) { row = sheet.createRow(i); T t = it.next(); // BeanMap bm = new BeanMap(t); Map b = PropertyUtils.describe(t); Map c = new TreeMap(); // 排序 c.putAll(b); writeRow(row, c, map, dateFormat, t); } workbook.write(out); }
private void createDetailSheet() { sheet = workbook.createSheet("Report"); for (int i = 0; i < lbls.length; i++) { sheet.setDefaultColumnStyle(i, whiteStyle); } // the first row createRow(_rowcnt++); int _idx = lbls.length - 6; setHeaderCell(_idx, null, 3); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, "Similarity Status", 4); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 2); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 3); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, "T13y Status", 4); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 2); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); for (int i = 0; i < lbls.length - 6; i++) { setHeaderCell(i, lbls[i], 0); // row.getCell(i).getCellStyle() // .setBorderBottom(HSSFCellStyle.BORDER_NONE); } createRow(_rowcnt++); // set background color for (int i = 0; i < lbls.length - 6; i++) { setHeaderCell(i, null, 1); // row.getCell(i).getCellStyle() // .setBorderTop(HSSFCellStyle.BORDER_NONE); } for (int i = lbls.length - 6; i < lbls.length; i++) { setHeaderCell(i, lbls[i], -1); } sheet.createFreezePane(0, 2, 0, 2); for (int i = 0; i < 11; i++) _sims[i] = 0; }
public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) { this.data = data; this.wb = wb; final HSSFSheet sheet = wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList")); utils = new ExcelUtils(wb); int rowIndex = -1; int cellIndex = 0; // empty row utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // title utils.putMainTitle( sheet, ++rowIndex, data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(), data.getNumbOfCols()); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // column headers row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value")); // empty row utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // freeze pane sheet.createFreezePane(0, rowIndex); for (final IndicatorGroup group : data.getIndicators().getGroups()) { row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); putGroupCell(sheet, rowIndex, group.getName()); for (final IndicatorDTO indicator : group.getIndicators()) { // indicator's detail sheet createDetailSheet(indicator); row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); // ind name utils.createLinkCell( row.createCell(1), indicator.getName(), ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(), true); // code utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode()); // target putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective()); // current value putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator)); } } sheet.setColumnWidth(0, 256 * 2); sheet.setColumnWidth(1, 256 * 45); sheet.setColumnWidth(2, 256 * 27); sheet.setColumnWidth(3, 256 * 27); sheet.setColumnWidth(4, 256 * 27); }