// removes a column and all the contents public boolean removeColumn(String sheetName, int colNum) { try { if (!isSheetExist(sheetName)) return false; fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); sheet = workBook.getSheet(sheetName); XSSFCellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); XSSFCreationHelper createHelper = workBook.getCreationHelper(); style.setFillPattern(HSSFCellStyle.NO_FILL); for (int i = 0; i < getRowCount(sheetName); i++) { row = sheet.getRow(i); if (row != null) { cell = row.getCell(colNum); if (cell != null) { cell.setCellStyle(style); row.removeCell(cell); } } } fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
/** * 创建副标题 * * @param workbook */ private void createSubHeads(XSSFWorkbook workbook, List subHeadList) { XSSFCellStyle style = null; int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数 int fristCol = 0; int columnCount = 26; String title = ""; XSSFRow row = null; XSSFCell cell = null; XSSFFont font = workbook.createFont(); // 创建字体对象 /** 副标题每行放两个,一个居左,一个居右,所以除以2,多出来的一个,另起一行, */ for (int i = 0; i < rowCount; i++) { row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题 for (int j = 0; j < 2; j++) { if (subHeadList.size() <= (i * 2 + j)) break; style = workbook.createCellStyle(); title = subHeadList.get(i * 2 + j).toString(); font.setFontHeightInPoints((short) 9); // 字号 style.setFont(font); if (j == 0) { /** 因为每行两个副标题,所以第一个标题占一半 */ fristCol = columnCount / 2; cell = row.createCell(j); // 创建单元格 cell.setCellValue(title); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 cell.setCellStyle(style); // 应用样式对象 workbook .getSheetAt(0) .addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格 } else { /** 因为每行两个副标题,第一个标题占一半,第二个占剩下的所有单元格 */ fristCol = columnCount - (columnCount / 2) - (columnCount % 2); cell = row.createCell(columnCount / 2); // 创建单元格 cell.setCellValue(title); // 字号 style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 cell.setCellStyle(style); // 应用样式对象 workbook .getSheetAt(0) .addMergedRegion( new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格 } } } }
/** * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する * * @param workbook ワークブック * @param sheet シート * @param nRow 行 * @param nColumn 列 * @param isBold 太字フラグ * @param fontSize 文字サイズ * @param fontHeight 行高 */ public static void setCellStyleForLabel( XSSFWorkbook workbook, XSSFSheet sheet, int nRow, int nColumn, boolean isBold, short fontSize, float fontHeight) { assert sheet != null; // style設定 XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); if (isBold) { font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字 } font.setFontHeightInPoints((short) fontSize); // 文字サイズ font.setFontName(DEFAULT_FONT_NAME); style.setFont(font); // 文字太字 と 文字サイズ style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準 style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め style.setWrapText(true); // 折り返して全体を表示する // セルに罫線を描画 XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellStyle(style); row.setHeightInPoints(fontHeight); // 行高設定 }
/** * セルに文字列を出力する * * @param cell 対象セル * @param object 出力データ * @param style セルスタイル * @param zeroValue 値が0の時に設定する値 */ private static void setData(XSSFCell cell, Object object, XSSFCellStyle style, String zeroValue) { if (style != null) { cell.setCellStyle(style); } if (object instanceof String) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue((String) object); } else if (object instanceof Integer) { Integer integer = (Integer) object; if (0 == integer) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(zeroValue); } else { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue((Integer) object); } } else if (object instanceof Double) { cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); if (Double.isNaN((Double) object)) { cell.setCellValue(zeroValue); } else { Double value = (Double) object; if (0 == value.compareTo((Double) 0.0)) { cell.setCellValue(zeroValue); } else { cell.setCellValue((Double) object); } } } }
// returns true if data is set successfully else false public boolean setCellData( String sheetName, String colName, int rowNum, String data, String url) { // System.out.println("setCellData setCellData******************"); try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); if (rowNum <= 0) return false; int index = workBook.getSheetIndex(sheetName); int colNum = -1; if (index == -1) return false; sheet = workBook.getSheetAt(index); // System.out.println("A"); row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { // System.out.println(row.getCell(i).getStringCellValue().trim()); if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i; } if (colNum == -1) return false; sheet.autoSizeColumn(colNum); // ashish row = sheet.getRow(rowNum - 1); if (row == null) row = sheet.createRow(rowNum - 1); cell = row.getCell(colNum); if (cell == null) cell = row.createCell(colNum); cell.setCellValue(data); XSSFCreationHelper createHelper = workBook.getCreationHelper(); // cell style for hyperlinks // by default hypelrinks are blue and underlined CellStyle hlink_style = workBook.createCellStyle(); XSSFFont hlink_font = workBook.createFont(); hlink_font.setUnderline(XSSFFont.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // hlink_style.setWrapText(true); XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE); link.setAddress(url); cell.setHyperlink(link); cell.setCellStyle(hlink_style); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
/** * セルの縮小して全体を表示する * * @param nRow 行データ * @return 有効列数 */ public static void setShrinkToFitForCell(XSSFWorkbook wb, XSSFSheet sheet, int nRow, int nCol) { XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow); XSSFCell cell = OoxmlUtil.getCellAnyway(row, nCol); CellStyle styletmp = cell.getCellStyle(); CellStyle newStyletmp = wb.createCellStyle(); newStyletmp.cloneStyleFrom(styletmp); newStyletmp.setWrapText(false); // 折り返して全体を表示する newStyletmp.setShrinkToFit(true); // 縮小して全体を表示する cell.setCellStyle(newStyletmp); }
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) { XSSFRow r = sheet.getRow(row); if (null == r) { r = sheet.createRow(row); } XSSFCell cell = r.getCell(col); if (null == cell) { cell = sheet.getRow(row).createCell(col); } cell.setCellStyle(style); }
/** * 创建单元格 * * @param row * @param style * @param colList * @param startCol * @param rowset */ private void createColumn( XSSFWorkbook workbook, XSSFRow row, XSSFCellStyle style, List<String> colList, int startCol, EFRowSet rowset, String type) { XSSFCell cell = null; for (int i = 0; i < colList.size(); i++) { /** 创建单元格、设置列名称 */ cell = row.createCell(startCol); cell.setCellStyle(style); if (type.equals("N")) { XSSFDataFormat df = workbook.createDataFormat(); cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00")); cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称 } else { if (colList.get(i).equals("F_CRFX")) { if (rowset.getString(colList.get(i), "").equals("R")) { cell.setCellValue("入库单"); } else if (rowset.getString(colList.get(i), "").equals("C")) { cell.setCellValue("出库单"); } else if (rowset.getString(colList.get(i), "").equals("T")) { cell.setCellValue("退库单"); } else if (rowset.getString(colList.get(i), "").equals("D")) { cell.setCellValue("调拨单"); } } else if (colList.get(i).equals("F_DJLX")) { if (rowset.getString(colList.get(i), "").equals("R0")) { cell.setCellValue("采购入库"); } else if (rowset.getString(colList.get(i), "").equals("R1")) { cell.setCellValue("更换入库"); } else if (rowset.getString(colList.get(i), "").equals("T0")) { cell.setCellValue("退库单"); } else if (rowset.getString(colList.get(i), "").equals("T1")) { cell.setCellValue("材料退货"); } else if (rowset.getString(colList.get(i), "").equals("C0")) { cell.setCellValue("正常出库"); } else if (rowset.getString(colList.get(i), "").equals("C1")) { cell.setCellValue("借调出库"); } else if (rowset.getString(colList.get(i), "").equals("C2")) { cell.setCellValue("被借调出库"); } else if (rowset.getString(colList.get(i), "").equals("D")) { cell.setCellValue("仓库调拨"); } } else { cell.setCellValue(rowset.getString(colList.get(i), "")); } } startCol++; } }
/** * 合并单元格后给合并后的单元格加边框 * * @param region * @param cs */ public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) { int toprowNum = region.getFirstRow(); for (int i = toprowNum; i <= region.getLastRow(); i++) { XSSFRow row = sheet.getRow(i); for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) { XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row, // (short) j); cell.setCellStyle(cs); } } }
/** * @param oldCell * @param newCell * @param styleMap */ public static void copyCell( XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
public static void setCellsx( int row, int col, Object colValue, XSSFSheet sheet, XSSFCellStyle style) { XSSFRow r = sheet.getRow(row); if (null == r) { r = sheet.createRow(row); } XSSFCell cell = r.getCell(col); if (null == cell) { cell = sheet.getRow(row).createCell(col); } setCellValueOfCnX(cell, colValue); cell.setCellStyle(style); }
/** 创建一个副标题 */ private void createSubHeadCell( XSSFWorkbook workbook, XSSFRow row, XSSFCellStyle style, String title, int rowIndex, int colIndex) { XSSFCell cell = row.createCell(colIndex); // 创建单元格 cell.setCellValue(title); workbook .getSheetAt(0) .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1)); // 合并单元格 cell.setCellStyle(style); }
/** * 罫線スタイルの<b>CellStyle</b>を生成 セル結合 * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param style 罫線style */ public static void setMerger( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, XSSFCellStyle style) { assert sheet != null; sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd)); XSSFRow row = getRowAnyway(sheet, nRowStart); XSSFCell cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(style); }
/** * 指定範囲にセルを新規作成 * * @param sheet シート * @param startRow 開始行番号 * @param endRow 終了行番号 * @param startColumn 開始列番号 * @param endColumn 終了列番号 * @param style セルスタイル */ public static void setStyle( XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn, XSSFCellStyle style) { for (int nRow = startRow; nRow <= endRow; nRow++) { XSSFRow row = getRowAnyway(sheet, nRow); for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) { XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellStyle(style); } } }
/** * 创建标题,并设置字体、字号、加粗、颜色 * * @param workbook */ private void setHeadCellFont(XSSFWorkbook workbook, String title) { XSSFCellStyle style = workbook.createCellStyle(); XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象 XSSFCell cell = row.createCell(0); // 创建单元格 XSSFFont font = workbook.createFont(); // 创建字体对象 font.setFontName(HSSFFont.FONT_ARIAL); // 字体 font.setFontHeightInPoints((short) 16); // 字号 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗 style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 row.setHeightInPoints(23); // 设置行高23像素 cell.setCellStyle(style); // 应用样式对象 workbook .getSheetAt(0) .addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是:起始行,结束行,起始列,结束列 cell.setCellValue(title); // 写入头标题 }
/** * ハイパーリンクの設定 * * @param sheet シート * @param nRow 対象行番号 * @param nColumn 対象列番号 * @param value ハイパーリンクテキスト * @param url ハイパーリンク先URL */ public static void setHyperLink( XSSFSheet sheet, int nRow, int nColumn, String value, String url) { assert sheet != null; XSSFWorkbook workbook = sheet.getWorkbook(); CreationHelper helper = workbook.getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL); hyperlink.setAddress(url); XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellValue(value); cell.setHyperlink(hyperlink); // ハイパーリンクテキストの装飾 XSSFFont font = workbook.createFont(); XSSFCellStyle style = workbook.createCellStyle(); // font.setColor(new XSSFColor(new Color(0, 0, 255))); font.setUnderline(XSSFFont.U_SINGLE); style.setFont(font); cell.setCellStyle(style); }
/** * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param isBorder 罫線描画フラグ * @param style 罫線style */ public static void setRowDataCellStyle( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, boolean isBorder, XSSFCellStyle style) { assert sheet != null; // Range内のすべてセルに罫線を描画 for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) { XSSFRow row = getRowAnyway(sheet, rIndex); for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) { XSSFCell cell = getCellAnyway(row, cIndex); cell.setCellStyle(style); } } }
// returns true if column is created successfully public boolean addColumn(String sheetName, String colName) { // System.out.println("**************addColumn*********************"); try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); int index = workBook.getSheetIndex(sheetName); if (index == -1) return false; XSSFCellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); sheet = workBook.getSheetAt(index); row = sheet.getRow(0); if (row == null) row = sheet.createRow(0); // cell = row.getCell(); // if (cell == null) // System.out.println(row.getLastCellNum()); if (row.getLastCellNum() == -1) cell = row.createCell(0); else cell = row.createCell(row.getLastCellNum()); cell.setCellValue(colName); cell.setCellStyle(style); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
/** * 创建单元格 * * @param row * @param style * @param colList * @param startCol * @param rowset */ private void createColumn( XSSFWorkbook workbook, XSSFRow row, XSSFCellStyle style, List<String> colList, int startCol, EFRowSet rowset, String type) { XSSFCell cell = null; for (int i = 0; i < colList.size(); i++) { /** 创建单元格、设置列名称 */ cell = row.createCell(startCol); cell.setCellStyle(style); if (type.equals("N")) { XSSFDataFormat df = workbook.createDataFormat(); cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00")); cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称 } else { cell.setCellValue(rowset.getString(colList.get(i), "")); } startCol++; } }
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet overall = workbook.createSheet("Overall"); XSSFRow row = overall.createRow(0); XSSFCellStyle topStyle = workbook.createCellStyle(); topStyle.setAlignment(CellStyle.ALIGN_CENTER); XSSFCell theme = row.createCell(0); theme.setCellValue("Theme"); overall.autoSizeColumn(0); XSSFCell occurs = row.createCell(1); occurs.setCellValue("Occurrences"); overall.autoSizeColumn(1); XSSFCell prev = row.createCell(2); prev.setCellValue("Prevalence"); overall.autoSizeColumn(2); theme.setCellStyle(topStyle); occurs.setCellStyle(topStyle); prev.setCellStyle(topStyle); for (int i = 0; i < themes.size(); i++) { XSSFRow r = overall.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(themes.get(i).getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(themes.get(i).getTotalOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount)); } // This could be done in the previous loop but since we don't need // indices as much, we may as well use the cleaner for each loop for (Theme t : themes) { XSSFSheet themeSheet = workbook.createSheet(t.getName()); XSSFRow row1 = themeSheet.createRow(0); XSSFCell keyword = row1.createCell(0); keyword.setCellValue("Keyword"); keyword.setCellStyle(topStyle); XSSFCell occ = row1.createCell(1); occ.setCellValue("Occurrences"); occ.setCellStyle(topStyle); XSSFCell themePrev = row1.createCell(2); themePrev.setCellValue("Prevalence"); themePrev.setCellStyle(topStyle); for (int i = 0; i < t.getKeywords().size(); i++) { Keyword k = t.getKeywords().get(i); XSSFRow r = themeSheet.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(k.getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(k.getNumOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs())); } } FileOutputStream output = new FileOutputStream(docName); workbook.write(output); output.close(); }
public static boolean writeArray2XLSXFile( String FileName, String[] titleNames, List<String[]> arry) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = titleNames.length; int rowNum = arry.size(); // for (int i = 0; i < columnNum; i++) { // sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20))); // } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont headFont = wb.createFont(); headFont.setFontName("Courier New"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headFont.setColor(HSSFColor.BLACK.index); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headStyle.setFillForegroundColor(HSSFColor.BLACK.index); headStyle.setLocked(true); headStyle.setFont(headFont); headStyle.setBorderTop((short) 2); headStyle.setBorderBottom((short) 1); XSSFCellStyle contentStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont contentFont = wb.createFont(); contentFont.setFontName("Courier New"); contentFont.setFontHeightInPoints((short) 9); // headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); contentFont.setColor(HSSFColor.BLACK.index); contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); contentStyle.setFillForegroundColor(HSSFColor.BLACK.index); contentStyle.setFont(contentFont); // create titile row XSSFRow row = sheet1.createRow(0); int heandLine = 0; XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } heandLine++; } for (int i = 0; i < rowNum; i++) { row = sheet1.createRow((i + heandLine)); String[] line = (String[]) arry.get(i); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(FileName); wb.write(fileOut); fileOut.close(); return true; }
@GET @Path("/getreport") @Produces("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") public Response exportExcel(@QueryParam("from") String from, @QueryParam("to") String to) throws Exception { String params = from + to; System.out.println(params); File file = new File("src/main/resources/template.xlsx"); FileInputStream inputStream = new FileInputStream(file); List<Customer> customerList = null; XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); // CellStyle dateStyle = wb.createCellStyle(); // CreationHelper createHelper = wb.getCreationHelper(); // dateStyle.setDataFormat(createHelper.createDataFormat().getFormat( // "yyyy/mm/dd hh:mm")); if (from != null && to != null) { Date startDate = Utils.formatDate(from); Date endDate = Utils.formatDate(to); customerList = dao.findByDate(startDate, endDate); // wb = Utils.fillReport(wb, customerList); } else { customerList = dao.findAll(); // wb = Utils.fillReport(wb, customerList); } for (int i = 0; i < customerList.size(); i++) { XSSFRow row = sheet.createRow(i + 3); Customer customer = customerList.get(i); for (int j = 0; j < 10; j++) { XSSFCell cell = row.createCell(j); switch (j) { case 0: cell.setCellValue(customer.getCreated()); cell.setCellStyle(sheet.getColumnStyle(j)); // cell.setCellStyle(dateStyle); break; case 1: cell.setCellValue(customer.getName()); break; case 2: cell.setCellValue(customer.getEmail()); break; case 3: cell.setCellValue(customer.getPhone()); break; case 4: cell.setCellValue(customer.getOrganisation()); break; case 5: cell.setCellValue(customer.getStartDate()); cell.setCellStyle(sheet.getColumnStyle(j)); break; case 6: cell.setCellValue(customer.getEndDate()); cell.setCellStyle(sheet.getColumnStyle(j)); break; case 7: cell.setCellValue(customer.getNumberOfPeople()); break; case 8: cell.setCellValue(customer.isCatering()); break; case 9: cell.setCellValue(customer.getAdditionalComments()); break; default: break; } } } StreamingOutput stream = new StreamingOutput() { public void write(OutputStream output) throws IOException, WebApplicationException { try { wb.write(output); wb.close(); } catch (Exception e) { throw new WebApplicationException(e); } } }; Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("YYYY_MM_dd"); String dateString = sdf.format(date); return Response.ok(stream) .header( "content-disposition", "attachment; filename = space-bookings-export_" + dateString + ".xlsx") .build(); }
public static boolean convertTextFile2XLSXFile( String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception { BufferedReader br = LocalFileFunc.getBufferedReader(inFileName); String line = br.readLine(); if (line == null) { return false; } String[] cells1 = Util.tokenize(line, '\t'); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = cells1.length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); String lastKey = null; int switcher = -1; XSSFCell cell = null; if (hasHead) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(headStyle); } } else { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(bodyStyle); } } rowIndex++; while ((line = br.readLine()) != null) { cells1 = Util.tokenize(line, '\t'); row = sheet1.createRow((rowIndex)); columnNum = cells1.length; if (indexKey >= 0) { if (lastKey == null && cells1[indexKey] != null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey != null && cells1[indexKey] == null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey == null && cells1[indexKey] == null) { } else { if (!lastKey.equals(cells1[indexKey])) { switcher *= -1; lastKey = cells1[indexKey]; } } } else { switcher = 1; } // System.out.println(cells1[0]); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (cells1[j] != null) { if (Util.isNumeric(cells1[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(cells1[j])); } else { cell.setCellValue(cells1[j]); } } else { cell.setCellValue("."); } } rowIndex++; } br.close(); // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(outFileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeArray2XLSXSheet( XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception { int rowNum = arry.size(); if (rowNum == 0) { System.err.println("No input data!"); return false; } String[] titleNames = null; if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20))); } XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); XSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell((short) i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[0] != null) { cell.setCellStyle(markedBodyStyle); } else { cell.setCellStyle(bodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue(""); } } } return true; }
public static boolean writeMultArray2XLSXFile( String fileName, List<List<String[]>> arrys, List<String> sheetLabels, boolean hasHead, int indexKey) throws Exception { if (arrys.isEmpty()) { System.err.println("No input data!"); return false; } XSSFWorkbook wb = new XSSFWorkbook(); XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); String lastKey = null; int switcher = -1; XSSFCell cell = null; String[] titleNames = null; int d = 0; for (List<String[]> arry : arrys) { XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d)); if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } int rowNum = arry.size(); for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; if (indexKey >= 0) { if (lastKey == null && line[indexKey] != null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey != null && line[indexKey] == null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey == null && line[indexKey] == null) { } else { if (!lastKey.equals(line[indexKey])) { switcher *= -1; lastKey = line[indexKey]; } } } else { switcher = 1; } for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } d++; } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); return true; }
/** * 罫線スタイルの<b>CellStyle</b>を生成 四角の範囲に周囲太線、中は細線を描画する * * @param workbook ワークブック * @param sheet シート * @param nRowStart 開始行 * @param nRowEnd 終了行 * @param nColumnStart 開始列 * @param nColumnEnd 終了列 * @param isBorder 罫線描画フラグ * @param styleMap 罫線style */ public static void setTableDataCellStyle( XSSFWorkbook workbook, XSSFSheet sheet, int nRowStart, int nRowEnd, int nColumnStart, int nColumnEnd, boolean isBorder, Map<String, XSSFCellStyle> styleMap) { assert sheet != null; // Range内のすべてセルに罫線を描画 for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) { XSSFRow row = getRowAnyway(sheet, rIndex); for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) { XSSFCell cell = getCellAnyway(row, cIndex); cell.setCellStyle(styleMap.get("normal")); } } // 初行のTopのみ太罫線 XSSFRow row = getRowAnyway(sheet, nRowStart); XSSFCell cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(styleMap.get("isTopAndLeft")); for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) { cell = getCellAnyway(row, cIndex); cell.setCellStyle(styleMap.get("isTop")); } cell = getCellAnyway(row, nColumnEnd); cell.setCellStyle(styleMap.get("isTopAndRight")); // 間の行 for (int cIndexCenter = nRowStart + 1; cIndexCenter < nRowEnd; cIndexCenter++) { row = getRowAnyway(sheet, cIndexCenter); cell = getCellAnyway(row, nColumnStart); cell.setCellStyle(styleMap.get("isLeft")); cell = getCellAnyway(row, nColumnEnd); cell.setCellStyle(styleMap.get("isRight")); } // 最後の行のBottomのみ太罫線 XSSFRow rowEnd = getRowAnyway(sheet, nRowEnd); cell = getCellAnyway(rowEnd, nColumnStart); cell.setCellStyle(styleMap.get("isBottomAndLeft")); for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) { cell = getCellAnyway(rowEnd, cIndex); cell.setCellStyle(styleMap.get("isBottom")); } cell = getCellAnyway(rowEnd, nColumnEnd); cell.setCellStyle(styleMap.get("isBottomAndRight")); // CellRangeAddress region=new // CellRangeAddress(nRowStart,nRowEnd,nColumnStart,nColumnEnd); // short border=XSSFCellStyle.BORDER_MEDIUM;//太罫線 // RegionUtil.setBorderTop(border,region,sheet,workbook); // RegionUtil.setBorderBottom(border,region,sheet,workbook); // RegionUtil.setBorderLeft(border, region, sheet, workbook); // RegionUtil.setBorderRight(border, region, sheet, workbook); }
/** * 创建列 * * @param workbook */ private void exportColumnData(XSSFWorkbook workbook, List subHeadList) { XSSFCellStyle style = null; XSSFRow row = null; XSSFCell cell = null; XSSFFont font = workbook.createFont(); // 创建字体对象 XSSFCellStyle topStyle = setColumnTopBorder(workbook); XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook); int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1; row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题 style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 font.setFontHeightInPoints((short) 10); // 字号 style.setFont(font); // 设置字体 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式 style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色 setColumnBorder(style, 0); // 设置列边框 workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度 /** 创建单元格、设置列名称、合并单元格 */ cell = row.createCell(0); cell.setCellValue("会计期间"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0)); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("单据编号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1)); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("分录编号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2)); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("材料入库信息"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8)); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellStyle(topStyle); cell = row.createCell(5); cell.setCellStyle(topStyle); cell = row.createCell(6); cell.setCellStyle(topStyle); cell = row.createCell(7); cell.setCellStyle(topStyle); cell = row.createCell(8); cell.setCellStyle(topStyle); cell = row.createCell(9); cell.setCellValue("材料出库信息"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14)); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellStyle(topStyle); cell = row.createCell(11); cell.setCellStyle(topStyle); cell = row.createCell(12); cell.setCellStyle(topStyle); cell = row.createCell(13); cell.setCellStyle(topStyle); cell = row.createCell(14); cell.setCellStyle(topStyle); cell = row.createCell(15); cell.setCellValue("材料编号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15)); cell.setCellStyle(style); cell = row.createCell(16); cell.setCellValue("材料名称"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16)); cell.setCellStyle(style); cell = row.createCell(17); cell.setCellValue("规格型号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17)); cell.setCellStyle(style); cell = row.createCell(18); cell.setCellValue("计量单位"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18)); cell.setCellStyle(style); cell = row.createCell(19); cell.setCellValue("供应商编号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19)); cell.setCellStyle(style); cell = row.createCell(20); cell.setCellValue("供应商名称"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20)); cell.setCellStyle(style); cell = row.createCell(21); cell.setCellValue("出入方向"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21)); cell.setCellStyle(style); cell = row.createCell(22); cell.setCellValue("单据类型"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22)); cell.setCellStyle(style); cell = row.createCell(23); cell.setCellValue("材料单价"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25)); cell.setCellStyle(style); cell = row.createCell(24); cell.setCellValue("材料数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23)); cell.setCellStyle(style); cell = row.createCell(25); cell.setCellValue("材料总价"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24)); cell.setCellStyle(style); row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题 /** 创建单元格、设置列名称、合并单元格 */ // 材料入库信息 cell = row.createCell(3); cell.setCellValue("仓库编号"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("仓库编号"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("仓库名称"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("项目编号"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("项目名称"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("产品编号"); cell.setCellStyle(style); // 材料出库信息 cell = row.createCell(9); cell.setCellValue("产品名称"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("仓库编号"); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue("仓库名称"); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("项目编号"); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue("项目名称"); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue("产品编号"); cell.setCellStyle(style); for (int i = 0; i < 3; i++) { cell = row.createCell(i); cell.setCellStyle(buttomStyle); } for (int i = 15; i < 26; i++) { cell = row.createCell(i); cell.setCellStyle(buttomStyle); } }
/** * 创建列 * * @param workbook */ private void exportColumnData(XSSFWorkbook workbook) { XSSFCellStyle style = null; XSSFRow row = null; XSSFCell cell = null; XSSFFont font = workbook.createFont(); // 创建字体对象 XSSFCellStyle topStyle = setColumnTopBorder(workbook); XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook); row = workbook.getSheetAt(0).createRow(5); // 创建一个行对象 列标题 style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 font.setFontHeightInPoints((short) 10); // 字号 style.setFont(font); // 设置字体 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式 style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色 setColumnBorder(style, 0); // 设置列边框 workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度 /** 创建单元格、设置列名称、合并单元格 */ cell = row.createCell(0); cell.setCellValue("材料编号"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 0, 0)); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("材料名称"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 1, 1)); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("型号规格"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 2, 2)); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("单位"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 3, 3)); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("申请数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 4, 4)); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("入库数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 5, 5)); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("出库数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 6, 8)); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellStyle(topStyle); cell = row.createCell(8); cell.setCellStyle(topStyle); cell = row.createCell(9); cell.setCellValue("退货数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 9, 10)); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellStyle(topStyle); cell = row.createCell(11); cell.setCellValue("调拨数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 11, 11)); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("库存数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 12, 12)); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue("领用数量"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 13, 13)); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue("领用总价"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 14, 14)); cell.setCellStyle(style); cell = row.createCell(15); cell.setCellValue("库存情况"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 15, 15)); cell.setCellStyle(style); cell = row.createCell(16); cell.setCellValue("备注"); workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 16, 16)); cell.setCellStyle(style); row = workbook.getSheetAt(0).createRow(6); // 创建一个行对象 列标题 /** 创建单元格、设置列名称、合并单元格 */ cell = row.createCell(6); cell.setCellValue("正常领用"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("借调"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("被借调"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("材料退货"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("厂商退货"); cell.setCellStyle(style); for (int i = 0; i < 5; i++) { cell = row.createCell(i); cell.setCellStyle(buttomStyle); } for (int i = 11; i < 16; i++) { cell = row.createCell(i); cell.setCellStyle(buttomStyle); } }
private void renderCell( BandElement bandElement, String bandName, Object value, int gridRow, int sheetRow, int sheetColumn, int rowSpan, int colSpan, boolean image) { if (bandElement instanceof ReportBandElement) { colSpan = 1; } XSSFCellStyle cellStyle = buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan); // if we have a subreport on the current grid row we have to take care of the sheetColumn if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow) && (prevSubreportLastColumn != -1)) { sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn; } XSSFCell c = xlsRow.createCell(sheetColumn); if (image) { if ((value == null) || "".equals(value)) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND)); } else { try { ImageBandElement ibe = (ImageBandElement) bandElement; byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight()); XSSFClientAnchor anchor = new XSSFClientAnchor( 0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize((String) value)[1]; if (ibe.isScaled()) { realImageHeight = ibe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); boolean doResize = false; if (imageHeight > height) { xlsRow.setHeight(imageHeight); } else { doResize = true; } Picture picture = patriarch.createPicture(anchor, index); if (doResize) { picture.resize(); } anchor.setAnchorType(2); } catch (Exception ex) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED)); } } if (cellStyle != null) { c.setCellStyle(cellStyle); } } else { if (bandElement instanceof HyperlinkBandElement) { Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink(); XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL); link.setAddress(hyp.getUrl()); c.setHyperlink(link); c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText())); c.setCellType(XSSFCell.CELL_TYPE_STRING); } else if (bandElement instanceof ReportBandElement) { Report report = ((ReportBandElement) bandElement).getReport(); ExporterBean eb = null; try { eb = getSubreportExporterBean(report, true); XlsxExporter subExporter = new XlsxExporter(eb, cellStyle); subExporter.export(); XSSFSheet subreportSheet = subExporter.getSubreportSheet(); if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)) { // other subreports on the same header line after the first sheetColumn = prevSubreportLastColumn; sheetRow -= addedPageRows; pageRow -= addedPageRows; addedPageRows = 0; } else { addedPageRows = subreportSheet.getLastRowNum(); pageRow += addedPageRows; // if subreport is not on the first column we merge all cells in the columns before, // between the rows subreport occupies if (sheetColumn > 0) { for (int i = 0; i <= sheetColumn - 1; i++) { CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i); regions.add(new XlsxRegion(cra, null)); } } } int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet); addRegions(xlsSheet, subExporter.getSubreportRegions(), wb); if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) { prevSubreportFirstRow = gridRow; prevSubreportFirstColumn = sheetColumn; prevSubreportLastColumn = sheetColumn + cols; } } catch (Exception e) { e.printStackTrace(); } finally { if ((eb != null) && (eb.getResult() != null)) { eb.getResult().close(); } } } else if (bandElement instanceof ImageColumnBandElement) { try { ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement; String v = StringUtil.getValueAsString(value, null); if (StringUtil.BLOB.equals(v)) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB)); } else { byte[] imageD = StringUtil.decodeImage(v); byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight()); XSSFClientAnchor anchor = new XSSFClientAnchor( 0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize(imageBytes)[1]; if (icbe.isScaled()) { realImageHeight = icbe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } Picture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } } catch (Exception e) { e.printStackTrace(); c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED)); } } else { if (value == null) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString("")); } else if (value instanceof Number) { c.setCellType(XSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(((Number) value).doubleValue()); } else { String pattern = null; if (bandElement instanceof FieldBandElement) { FieldBandElement fbe = (FieldBandElement) bandElement; pattern = fbe.getPattern(); } if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) { Date date; if (value instanceof java.sql.Date) { date = new Date(((java.sql.Date) value).getTime()); } else { date = (java.sql.Timestamp) value; } if (cellStyle != null) { if (pattern == null) { // use default pattern if none selected Locale locale = Locale.getDefault(); pattern = ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale)) .toPattern(); } else { pattern = StringUtil.getI18nString(pattern, getReportLanguage()); } cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern)); } c.setCellValue(date); } else { c.setCellType(XSSFCell.CELL_TYPE_STRING); String text = StringUtil.getValueAsString(value, pattern); if ((bandElement != null) && bandElement.isWrapText()) { // try to interpret new line characters // \\n is used here to be possible to add in designer grid cell with \n if (text.contains("\\n") || text.contains("\n") || text.contains("\r") || text.contains("\r\n")) { String crLf = Character.toString((char) 13) + Character.toString((char) 10); int lines = countLines(text); if (text.contains("\r\n")) { text = text.replaceAll("\r\n", crLf); } else { text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf); } c.setCellValue(text); cellStyle.setWrapText(true); xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3)); } else { c.setCellValue(wb.getCreationHelper().createRichTextString(text)); } } else { c.setCellValue(wb.getCreationHelper().createRichTextString(text)); } } } } if (cellStyle != null) { if (bandElement != null) { cellStyle.setRotation(bandElement.getTextRotation()); } if (!(bandElement instanceof ReportBandElement)) { c.setCellStyle(cellStyle); } } if ((rowSpan > 1) || (colSpan > 1)) { CellRangeAddress cra = new CellRangeAddress( sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1); Border beBorder = bandElement.getBorder(); if (hasRowRenderConditions(bandElement, gridRow, value)) { // for row render conditions we must keep the row border beBorder = border; } regions.add(new XlsxRegion(cra, beBorder)); } } }