private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) { /*Font bold = workBook.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); bold.setFontHeightInPoints((short) 10); bold.setColor(Font.COLOR_RED);*/ HSSFFont hssfFont = workBook.createFont(); hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD); hssfFont.setColor(Font.COLOR_RED); boldStyle = workBook.createCellStyle(); boldStyle.setBorderBottom(CellStyle.BORDER_THIN); boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); boldStyle.setFont(hssfFont); boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index); defaultFont = workBook.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setFontName("Arial"); defaultFont.setColor(IndexedColors.BLACK.getIndex()); defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD); defaultFont.setItalic(true); newStyle = workBook.createCellStyle(); // newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex()); // newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); newStyle.setAlignment(CellStyle.ALIGN_CENTER); newStyle.setFont(defaultFont); /* hssfCellStyle = workBook.createCellStyle(); hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN); hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); hssfCellStyle.setFont(bold);*/ }
public HSSFCellStyle createLastCellStyle(boolean white) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.DARK_BLUE.index); style.setLeftBorderColor(HSSFColor.DARK_BLUE.index); style.setBottomBorderColor(HSSFColor.DARK_BLUE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); if (white) { style.setFillBackgroundColor(HSSFColor.WHITE.index); style.setFillForegroundColor(HSSFColor.WHITE.index); } else { style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); } HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); style.setFont(font); return style; }
private CoverageResults() { fileName = "Coverage-results.xls"; workbook = new HSSFWorkbook(); allElementsSheet = workbook.createSheet("All Elements"); packagesSheet = workbook.createSheet("Packages"); boundClassesSheet = workbook.createSheet("Bound Classes"); allClassesSheet = workbook.createSheet("All Classes"); boundMethodsSheet = workbook.createSheet("Bound Methods"); allMethodsSheet = workbook.createSheet("All Methods"); normalFont = workbook.createFont(); normalFont.setFontName(HSSFFont.FONT_ARIAL); normalFont.setFontHeightInPoints((short) 10); boldFont = workbook.createFont(); boldFont.setFontName(HSSFFont.FONT_ARIAL); boldFont.setFontHeightInPoints((short) 10); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); centerAlignmentCellStyle = workbook.createCellStyle(); centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); leftAlignmentCellStyle = workbook.createCellStyle(); leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); rightAlignmentCellStyle = workbook.createCellStyle(); rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); dataFormatCellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); }
public void createExcelFile(String filename) throws Exception { out = new FileOutputStream(filename); wb = new HSSFWorkbook(); ws = this.wb.createSheet(); cs1 = this.wb.createCellStyle(); cs2 = this.wb.createCellStyle(); cs3 = this.wb.createCellStyle(); df = this.wb.createDataFormat(); f1 = this.wb.createFont(); f2 = this.wb.createFont(); f1.setFontHeightInPoints((short) 10); f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); f2.setFontHeightInPoints((short) 13); f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); f2.setColor(HSSFFont.COLOR_RED); cs1.setFont(f1); cs1.setDataFormat(df.getFormat("text")); cs2.setFont(f2); cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cs3.setFont(f1); cs3.setDataFormat(df.getFormat("#,##0.0")); wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16); }
/** * Fills given row with headers * * @param wb * @param sheet * @param row */ private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) { // create style of header font HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 11); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // here we could create array of strings, I mean headers String[] headers = { this.iwrb.getLocalizedString("school.child", "Child"), this.iwrb.getLocalizedString("school.personal_id", "Personal ID"), this.iwrb.getLocalizedString("school.e-mail", "Email"), this.iwrb.getLocalizedString("school.address", "Address"), this.iwrb.getLocalizedString("school.zip_code", "Zip code"), this.iwrb.getLocalizedString("school.city", "City"), this.iwrb.getLocalizedString("school.phone", "Phone"), this.iwrb.getLocalizedString("school.gender", "Gender"), this.iwrb.getLocalizedString("school.last_provider", "Last provider"), this.iwrb.getLocalizedString("school.rejection_date", "Rejection date") }; int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16}; HSSFCell cell; for (int i = 0; i < headers.length; i++) { cell = row.createCell((short) i); cell.setCellValue(headers[i]); cell.setCellStyle(style); sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256)); } }
public HSSFCellStyle createScriptStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); font.setColor(HSSFColor.BLACK.index); style.setFont(font); return style; }
public HSSFCellStyle createInfoCellStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLACK.index); style.setFont(font); return style; }
public HSSFCellStyle createTitleCellStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 16); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setUnderline(HSSFFont.U_SINGLE); font.setColor(HSSFColor.GREY_80_PERCENT.index); style.setFont(font); return style; }
public HSSFCellStyle createSubTitleCellStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); return style; }
protected HSSFCellStyle createInternalCellStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.DARK_BLUE.index); style.setLeftBorderColor(HSSFColor.DARK_BLUE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillBackgroundColor(HSSFColor.WHITE.index); style.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); style.setFont(font); return style; }
private void initHeadCellStyle() { style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); }
private void writeSheet( List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) { // Set column widths HSSFSheet sheet = wb.createSheet(worksheetName); sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000")); sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000")); // header style HSSFCellStyle headerStyle; HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); // header row HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); HSSFCell headerCell0 = headerRow.createCell((short) 0); HSSFCell headerCell1 = headerRow.createCell((short) 1); headerCell0.setCellStyle(headerStyle); setText(headerCell0, "Layer Name"); headerCell1.setCellStyle(headerStyle); setText(headerCell1, "Message"); int counter = 1; for (Map<String, String> valueMap : valueMaps) { HSSFRow dataRow = sheet.createRow(counter); String layer = valueMap.get("layer"); String status = valueMap.get("status"); status = HtmlUtils.htmlUnescape(status); HSSFCell currentCell0 = dataRow.createCell((short) 0); HSSFCell currentCell1 = dataRow.createCell((short) 1); setText(currentCell0, layer); setText(currentCell1, status); counter++; } }
public ExcelSupport() { super(); workbook = new HSSFWorkbook(); numericCellStyle = workbook.createCellStyle(); numericData = workbook.createDataFormat(); numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00")); percentageCellStyle = workbook.createCellStyle(); percentageCellStyle.setDataFormat((short) 4); decimalCellStyle = workbook.createCellStyle(); decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####")); multiLineTextStyle = workbook.createCellStyle(); multiLineTextStyle.setWrapText(true); multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); genericStyle = workbook.createCellStyle(); genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); dollarStyle = workbook.createCellStyle(); dollarStyle.setDataFormat((short) 8); headlineStyle = workbook.createCellStyle(); HSSFFont headlineFont = workbook.createFont(); // set font 1 to 12 point type headlineFont.setFontHeightInPoints((short) 24); // make it bold // arial is the default font headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headlineStyle.setFont(headlineFont); boldFont = workbook.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldStyle = workbook.createCellStyle(); boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); boldStyle.setFont(boldFont); }
public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) { HSSFCell cell = getCell(rowIndex, columnIndex); if (cell.getCellComment() != null) { cell.removeCellComment(); } ClientAnchor anchor = getCreationHelper().createClientAnchor(); anchor.setCol1(columnIndex + 2); anchor.setCol2(columnIndex + 2 + cols); anchor.setRow1(rowIndex + 1); anchor.setRow2(rowIndex + 1 + rows); RichTextString str = getCreationHelper().createRichTextString(text); HSSFFont font = createFont(); font.setFontHeightInPoints((short) 10); str.applyFont(font); Comment comment = getDrawing().createCellComment(anchor); comment.setString(str); cell.setCellComment(comment); }
/** 單元格的字体 workbook excel font --字体模型 */ public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) { HSSFFont font = workbook.createFont(); // 字体名称 font.setFontName(f.getFontNm()); // 字号 font.setFontHeightInPoints(f.getFontSize()); // 字体颜色 font.setColor(f.getFontColor()); // 下划线 font.setUnderline(f.getUnderline()); // 上标下标 font.setTypeOffset(f.getOffset()); // 删除线 font.setStrikeout(f.getDelLine()); // 加粗 font.setBoldweight(f.getBold()); // 斜线 font.setItalic(f.getItalic()); // 字体高度 // font.setFontHeight(arg0); return font; }
protected HSSFCellStyle createSummaryCellStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.DARK_BLUE.index); style.setLeftBorderColor(HSSFColor.DARK_BLUE.index); style.setTopBorderColor(HSSFColor.DARK_BLUE.index); style.setBottomBorderColor(HSSFColor.DARK_BLUE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFFont font = workbook.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 15); font.setColor(HSSFColor.BLACK.index); style.setFont(font); return style; }
/** * 读取xml定义的模板,创建excel,感觉没啥用 * * @author David * @param args */ public static void main(String[] args) { // 获取解析xml文件路径 String path = System.getProperty("user.dir") + "/bin/student2.xml"; File file = new File(path); SAXBuilder builder = new SAXBuilder(); try { // 解析xml文件 Document parse = builder.build(file); // 创建Excel HSSFWorkbook wb = new HSSFWorkbook(); // 创建sheet HSSFSheet sheet = wb.createSheet("Sheet0"); // 获取xml文件跟节点 Element root = parse.getRootElement(); // 获取模板名称 String templateName = root.getAttribute("name").getValue(); int rownum = 0; int column = 0; // 设置列宽 Element colgroup = root.getChild("colgroup"); setColumnWidth(sheet, colgroup); // 设置标题 Element title = root.getChild("title"); List<Element> trs = title.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); List<Element> tds = tr.getChildren("td"); HSSFRow row = sheet.createRow(rownum); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (column = 0; column < tds.size(); column++) { Element td = tds.get(column); HSSFCell cell = row.createCell(column); Attribute rowSpan = td.getAttribute("rowspan"); Attribute colSpan = td.getAttribute("colspan"); Attribute value = td.getAttribute("value"); if (value != null) { String val = value.getValue(); cell.setCellValue(val); int rspan = rowSpan.getIntValue() - 1; int cspan = colSpan.getIntValue() - 1; // 设置字体 HSSFFont font = wb.createFont(); font.setFontName("仿宋_GB2312"); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗 // font.setFontHeight((short)12); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); cell.setCellStyle(cellStyle); // 合并单元格居中 sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan)); } } rownum++; } // 设置表头 Element thead = root.getChild("thead"); trs = thead.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); HSSFRow row = sheet.createRow(rownum); List<Element> ths = tr.getChildren("th"); for (column = 0; column < ths.size(); column++) { Element th = ths.get(column); Attribute valueAttr = th.getAttribute("value"); HSSFCell cell = row.createCell(column); if (valueAttr != null) { String value = valueAttr.getValue(); cell.setCellValue(value); } } rownum++; } // 设置数据区域样式 Element tbody = root.getChild("tbody"); Element tr = tbody.getChild("tr"); int repeat = tr.getAttribute("repeat").getIntValue(); List<Element> tds = tr.getChildren("td"); for (int i = 0; i < repeat; i++) { HSSFRow row = sheet.createRow(rownum); for (column = 0; column < tds.size(); column++) { Element td = tds.get(column); HSSFCell cell = row.createCell(column); setType(wb, cell, td); } rownum++; } // 生成Excel导入模板 File tempFile = new File("e:/" + templateName + ".xls"); tempFile.delete(); tempFile.createNewFile(); FileOutputStream stream = FileUtils.openOutputStream(tempFile); wb.write(stream); stream.close(); } catch (Exception e) { e.printStackTrace(); } }
private Map initStyles(HSSFWorkbook wb, short fontHeight) { Map result = new HashMap(); HSSFCellStyle titleStyle = wb.createCellStyle(); HSSFCellStyle textStyle = wb.createCellStyle(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFCellStyle numericStyle = wb.createCellStyle(); HSSFCellStyle numericStyleBold = wb.createCellStyle(); HSSFCellStyle moneyStyle = wb.createCellStyle(); HSSFCellStyle moneyStyleBold = wb.createCellStyle(); HSSFCellStyle percentStyle = wb.createCellStyle(); HSSFCellStyle percentStyleBold = wb.createCellStyle(); result.put("titleStyle", titleStyle); result.put("textStyle", textStyle); result.put("boldStyle", boldStyle); result.put("numericStyle", numericStyle); result.put("numericStyleBold", numericStyleBold); result.put("moneyStyle", moneyStyle); result.put("moneyStyleBold", moneyStyleBold); result.put("percentStyle", percentStyle); result.put("percentStyleBold", percentStyleBold); HSSFDataFormat format = wb.createDataFormat(); // Global fonts HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setColor(HSSFColor.BLACK.index); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints(fontHeight); HSSFFont fontBold = wb.createFont(); fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontBold.setColor(HSSFColor.BLACK.index); fontBold.setFontName(HSSFFont.FONT_ARIAL); fontBold.setFontHeightInPoints(fontHeight); // Money Style moneyStyle.setFont(font); moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle.setDataFormat(format.getFormat(moneyFormat)); // Money Style Bold moneyStyleBold.setFont(fontBold); moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyleBold.setDataFormat(format.getFormat(moneyFormat)); // Percent Style percentStyle.setFont(font); percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle.setDataFormat(format.getFormat(percentFormat)); // Percent Style Bold percentStyleBold.setFont(fontBold); percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyleBold.setDataFormat(format.getFormat(percentFormat)); // Standard Numeric Style numericStyle.setFont(font); numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Standard Numeric Style Bold numericStyleBold.setFont(fontBold); numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Title Style titleStyle.setFont(font); titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBottomBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setLeftBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setRightBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setTopBorderColor(HSSFColor.BLACK.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Standard Text Style textStyle.setFont(font); textStyle.setWrapText(true); // Standard Text Style boldStyle.setFont(fontBold); boldStyle.setWrapText(true); return result; }
public void exportExcel( String headline, String title, String[] headers, List<Object[]> dataset, OutputStream out, short[] width) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); if (row != null) { HSSFCell cell = row.createCell(0); HSSFFont f = workbook.createFont(); f.setColor(HSSFColor.DARK_BLUE.index); f.setFontHeightInPoints((short) 24); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 HSSFCellStyle s = workbook.createCellStyle(); s.setAlignment(HSSFCellStyle.ALIGN_CENTER); s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); s.setFont(f); cell.setCellStyle(s); cell.setCellValue(headline); } sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1))); row = sheet.createRow(1); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + 2); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void exportJgjExcel( String title, List<String[]> header, List<Object[]> dataset, OutputStream out, short[] width, List<int[]> merge) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { row = sheet.createRow(i); for (int m = 0; m < header.get(i).length; m++) { HSSFCell cell = row.createCell(m); cell.setCellStyle(style); String headValue = header.get(i)[m]; // HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]); cell.setCellValue(headValue); } } if (merge != null && merge.size() > 0) { for (int m = 0; m < merge.size(); m++) { sheet.addMergedRegion( new CellRangeAddress( merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行 } } // sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); // sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + header.size()); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
@SuppressWarnings("deprecation") public Reporteconcepto( Rgenerador objeto, Rgenerador objeto2, Boolean siinicial, String partida, String ruta, String ruta2, String path) throws IOException { this.FILE = path; // creacion del libro que contedra nuestro reporte libro = new HSSFWorkbook(); // cracion de la hoja que estara contenida en nuestro libro hoja = libro.createSheet("new sheet"); // Definicion de estilo que contendra nuestro encabezado // ***************************************************************************************************************************************************** // definicion estilos de celdas, establecimineto del tipo de fuente fuenteen = libro.createFont(); fuenteen.setFontHeightInPoints((short) 12); fuenteen.setFontName(fuenteen.FONT_ARIAL); fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldaen = libro.createCellStyle(); esceldaen.setWrapText(true); esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT); esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldaen.setFont(fuenteen); // establecimiento de sombreado de nuestra celda esceldaen.setFillForegroundColor((short) 44); esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // Definicion de estilo que contendra los tiulos // ***************************************************************************************************************************************************** // definicion estilos de celdas, establecimineto del tipo de fuente fuentet = libro.createFont(); fuentet.setFontHeightInPoints((short) 11); fuentet.setFontName(fuentet.FONT_ARIAL); fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldat = libro.createCellStyle(); esceldat.setWrapText(true); esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER); esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldat.setFont(fuentet); // establecimiento de bordes esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); esceldat.setBottomBorderColor((short) 8); esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); esceldat.setLeftBorderColor((short) 8); esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); esceldat.setRightBorderColor((short) 8); esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); esceldat.setRightBorderColor((short) 8); // establecimiento de sombreado de nuestra celda esceldat.setFillForegroundColor((short) 22); esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // ***************************************************************************************************************************************************** // Definicion del estilo de la celda de nuestros datos que contendra el // reporte // definicion estilos de celdas, establecimineto del tipo de fuente fuentein = libro.createFont(); fuentein.setFontHeightInPoints((short) 10); fuentein.setFontName(fuentein.FONT_ARIAL); fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldain = libro.createCellStyle(); esceldain.setWrapText(true); esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER); esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldain.setFont(fuentet); // establecimiento de bordes esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); esceldain.setBottomBorderColor((short) 8); esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); esceldain.setLeftBorderColor((short) 8); esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); esceldain.setRightBorderColor((short) 8); esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); esceldain.setRightBorderColor((short) 8); // definimos el numero de filas que contedra nuestro decumento. encabezado = hoja.createRow((short) 5); Cencabezado = encabezado.createCell((short) 2); Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")"); hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4)); Cencabezado.setCellStyle(esceldaen); // ******************************************************************************************************************** HSSFRow fila1 = hoja.createRow((short) 2); HSSFCell ccontrato = fila1.createCell((short) 8); ccontrato.setCellValue("contrato:"); ccontrato.setCellStyle(esceldain); HSSFCell rcontrato = fila1.createCell((short) 9); rcontrato.setCellValue(" "); rcontrato.setCellStyle(esceldain); HSSFCell cgenrencia = fila1.createCell((short) 10); cgenrencia.setCellValue("Gerencia:"); cgenrencia.setCellStyle(esceldain); HSSFCell rgerencia = fila1.createCell((short) 11); rgerencia.setCellValue(" "); rgerencia.setCellStyle(esceldain); HSSFCell choja = fila1.createCell((short) 12); choja.setCellValue("Hoja:"); choja.setCellStyle(esceldain); HSSFCell rhoja = fila1.createCell((short) 13); rhoja.setCellValue(" "); rhoja.setCellStyle(esceldain); // ******************************************************************************************************************************************************* HSSFRow fila2 = hoja.createRow((short) 3); HSSFCell cnc = fila2.createCell((short) 8); cnc.setCellValue("N.C. :"); cnc.setCellStyle(esceldain); HSSFCell rcnc = fila2.createCell((short) 9); rcnc.setCellValue(" "); rcnc.setCellStyle(esceldain); HSSFCell ctipo = fila2.createCell((short) 10); ctipo.setCellValue("Tipo de obra:"); ctipo.setCellStyle(esceldain); HSSFCell rtipo = fila2.createCell((short) 11); rtipo.setCellValue(" "); rtipo.setCellStyle(esceldain); HSSFCell cunidad = fila2.createCell((short) 12); cunidad.setCellValue("Unidad:"); cunidad.setCellStyle(esceldain); HSSFCell runidad = fila2.createCell((short) 13); runidad.setCellValue(" "); runidad.setCellStyle(esceldain); // ******************************************************************************************************************************************************* HSSFRow fila3 = hoja.createRow((short) 4); HSSFCell clocalidad = fila3.createCell((short) 8); clocalidad.setCellValue("localidad:"); clocalidad.setCellStyle(esceldain); HSSFCell rlocalidad = fila3.createCell((short) 9); rlocalidad.setCellValue(" "); rlocalidad.setCellStyle(esceldain); hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11)); rlocalidad.setCellStyle(esceldain); HSSFCell runo = fila3.createCell((short) 10); runo.setCellValue(" "); runo.setCellStyle(esceldain); HSSFCell rdos = fila3.createCell((short) 11); rdos.setCellValue(" "); rdos.setCellStyle(esceldain); HSSFCell cfecha = fila3.createCell((short) 12); cfecha.setCellValue("Fecha:"); cfecha.setCellStyle(esceldain); HSSFCell rfecha = fila3.createCell((short) 13); rfecha.setCellValue(" "); rfecha.setCellStyle(esceldain); // *************************************************************************************************************************************************************** HSSFRow fila4 = hoja.createRow((short) 5); HSSFCell ccontratista = fila4.createCell((short) 8); ccontratista.setCellValue("Contratista:"); ccontratista.setCellStyle(esceldain); HSSFCell rcontratista = fila4.createCell((short) 9); rcontratista.setCellValue(" "); rcontratista.setCellStyle(esceldain); hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13)); rcontratista.setCellStyle(esceldain); HSSFCell runoc = fila4.createCell((short) 10); runoc.setCellValue(" "); runoc.setCellStyle(esceldain); HSSFCell rdosc = fila4.createCell((short) 11); rdosc.setCellValue(" "); rdosc.setCellStyle(esceldain); HSSFCell rtres = fila4.createCell((short) 12); rtres.setCellValue(" "); rtres.setCellStyle(esceldain); HSSFCell rcuatro = fila4.createCell((short) 13); rcuatro.setCellValue(" "); rcuatro.setCellStyle(esceldain); // *************************************************************************************************************************************************************** HSSFRow fila5 = hoja.createRow((short) 6); HSSFCell cperiodo = fila5.createCell((short) 8); cperiodo.setCellValue("Consultor:"); cperiodo.setCellStyle(esceldain); HSSFCell rperiodo = fila5.createCell((short) 9); rperiodo.setCellValue(" "); rperiodo.setCellStyle(esceldain); hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13)); rperiodo.setCellStyle(esceldain); HSSFCell runop = fila5.createCell((short) 10); runop.setCellValue(" "); runop.setCellStyle(esceldain); HSSFCell rdosp = fila5.createCell((short) 11); rdosp.setCellValue(" "); rdosp.setCellStyle(esceldain); HSSFCell rtresp = fila5.createCell((short) 12); rtresp.setCellValue(" "); rtresp.setCellStyle(esceldain); HSSFCell rcuatrop = fila5.createCell((short) 13); rcuatrop.setCellValue(" "); rcuatrop.setCellStyle(esceldain); // crear un una columna HSSFRow row1 = hoja.createRow((short) 7); // create de las celdas HSSFCell cc = row1.createCell((short) 2); HSSFCell cd = row1.createCell((short) 3); HSSFCell cu = row1.createCell((short) 4); HSSFCell cx = row1.createCell((short) 5); HSSFCell cy = row1.createCell((short) 6); HSSFCell cz = row1.createCell((short) 7); HSSFCell ca = row1.createCell((short) 8); HSSFCell cl = row1.createCell((short) 9); HSSFCell cal = row1.createCell((short) 10); HSSFCell cca = row1.createCell((short) 11); HSSFCell cpz = row1.createCell((short) 12); HSSFCell cim = row1.createCell((short) 13); // writing data to the cells cc.setCellValue("Clave"); cc.setCellStyle(esceldat); cd.setCellValue("Descripción"); cd.setCellStyle(esceldat); cu.setCellValue("Unidad"); cu.setCellStyle(esceldat); cx.setCellValue("X"); cx.setCellStyle(esceldat); cy.setCellValue("Y"); cy.setCellStyle(esceldat); cz.setCellValue("Z"); cz.setCellStyle(esceldat); ca.setCellValue("Alto"); ca.setCellStyle(esceldat); cl.setCellValue("Largo"); cl.setCellStyle(esceldat); cal.setCellValue("ancho"); cal.setCellStyle(esceldat); cca.setCellValue("Cantidad"); cca.setCellStyle(esceldat); cpz.setCellValue("Piezas"); cpz.setCellStyle(esceldat); cim.setCellValue("Importe"); cim.setCellStyle(esceldat); // crear un una columna HSSFRow row = hoja.createRow((short) 8); // create de las celdas HSSFCell cc1 = row.createCell((short) 2); HSSFCell cd1 = row.createCell((short) 3); HSSFCell cu1 = row.createCell((short) 4); HSSFCell cx1 = row.createCell((short) 5); HSSFCell cy1 = row.createCell((short) 6); HSSFCell cz1 = row.createCell((short) 7); HSSFCell ca1 = row.createCell((short) 8); HSSFCell cl1 = row.createCell((short) 9); HSSFCell cal1 = row.createCell((short) 10); HSSFCell cca1 = row.createCell((short) 11); HSSFCell cpz1 = row.createCell((short) 12); HSSFCell cim1 = row.createCell((short) 13); cc1.setCellValue(objeto.getClave()); cc1.setCellStyle(esceldain); cd1.setCellValue(objeto.getDescripcion()); cd1.setCellStyle(esceldain); cu1.setCellValue(objeto.getUnidad()); cu1.setCellStyle(esceldain); cx1.setCellValue(objeto.getX()); cx1.setCellStyle(esceldain); cy1.setCellValue(objeto.getY()); cy1.setCellStyle(esceldain); cz1.setCellValue(objeto.getZ()); cz1.setCellStyle(esceldain); ca1.setCellValue(objeto.getAlto()); ca1.setCellStyle(esceldain); cl1.setCellValue(objeto.getLargo()); cl1.setCellStyle(esceldain); cal1.setCellValue(objeto.getAncho()); cal1.setCellStyle(esceldain); cca1.setCellValue(objeto.getCantidad()); cca1.setCellStyle(esceldain); cpz1.setCellValue(objeto.getPiezas()); cpz1.setCellStyle(esceldain); cim1.setCellValue(objeto.getImporte()); cim1.setCellStyle(esceldain); HSSFSheet sheet = libro.getSheetAt(0); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // contendor que contiene las imagenes HSSFClientAnchor anchor; if (ruta != null) { File fis = new File(ruta); anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24); anchor.setAnchorType(2); HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro)); } if (ruta2 != null) { File fis2 = new File(ruta2); anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24); anchor.setAnchorType(2); HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro)); } sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.autoSizeColumn((short) 7); sheet.autoSizeColumn((short) 8); sheet.autoSizeColumn((short) 9); sheet.autoSizeColumn((short) 10); sheet.autoSizeColumn((short) 11); sheet.autoSizeColumn((short) 12); sheet.autoSizeColumn((short) 13); if (siinicial == true) { // ************************************************************************************ HSSFRow encabezado2 = hoja.createRow((short) 26); HSSFCell Cencabezado2 = encabezado2.createCell((short) 2); Cencabezado2.setCellValue("Datos: Partida(" + partida + ")"); hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4)); Cencabezado2.setCellStyle(esceldaen); // crear un una columna HSSFRow row27 = hoja.createRow((short) 28); // create de las celdas HSSFCell cc2 = row27.createCell((short) 2); HSSFCell cd2 = row27.createCell((short) 3); HSSFCell cu2 = row27.createCell((short) 4); HSSFCell cx2 = row27.createCell((short) 5); HSSFCell cy2 = row27.createCell((short) 6); HSSFCell cz2 = row27.createCell((short) 7); HSSFCell ca2 = row27.createCell((short) 8); HSSFCell cl2 = row27.createCell((short) 9); HSSFCell cal2 = row27.createCell((short) 10); HSSFCell cca2 = row27.createCell((short) 11); HSSFCell cpz2 = row27.createCell((short) 12); HSSFCell cim2 = row27.createCell((short) 13); // writing data to the cells cc2.setCellValue("Clave"); cc2.setCellStyle(esceldat); cd2.setCellValue("Descripción"); cd2.setCellStyle(esceldat); cu2.setCellValue("Unidad"); cu2.setCellStyle(esceldat); cx2.setCellValue("X"); cx2.setCellStyle(esceldat); cy2.setCellValue("Y"); cy2.setCellStyle(esceldat); cz2.setCellValue("Z"); cz2.setCellStyle(esceldat); ca2.setCellValue("Alto"); ca2.setCellStyle(esceldat); cl2.setCellValue("Largo"); cl2.setCellStyle(esceldat); cal2.setCellValue("ancho"); cal2.setCellStyle(esceldat); cca2.setCellValue("Cantidad"); cca2.setCellStyle(esceldat); cpz2.setCellValue("Piezas"); cpz2.setCellStyle(esceldat); cim2.setCellValue("Importe"); cim2.setCellStyle(esceldat); // crear un una columna HSSFRow row28 = hoja.createRow((short) 29); // create de las celdas HSSFCell cc12 = row28.createCell((short) 2); HSSFCell cd12 = row28.createCell((short) 3); HSSFCell cu12 = row28.createCell((short) 4); HSSFCell cx12 = row28.createCell((short) 5); HSSFCell cy12 = row28.createCell((short) 6); HSSFCell cz12 = row28.createCell((short) 7); HSSFCell ca12 = row28.createCell((short) 8); HSSFCell cl12 = row28.createCell((short) 9); HSSFCell cal12 = row28.createCell((short) 10); HSSFCell cca12 = row28.createCell((short) 11); HSSFCell cpz12 = row28.createCell((short) 12); HSSFCell cim12 = row28.createCell((short) 13); cc12.setCellValue(objeto2.getClave()); cc12.setCellStyle(esceldain); cd12.setCellValue(objeto2.getDescripcion()); cd12.setCellStyle(esceldain); cu12.setCellValue(objeto2.getUnidad()); cu12.setCellStyle(esceldain); cx12.setCellValue(objeto2.getX()); cx12.setCellStyle(esceldain); cy12.setCellValue(objeto2.getY()); cy12.setCellStyle(esceldain); cz12.setCellValue(objeto2.getZ()); cz12.setCellStyle(esceldain); ca12.setCellValue(objeto2.getAlto()); ca12.setCellStyle(esceldain); cl12.setCellValue(objeto2.getLargo()); cl12.setCellStyle(esceldain); cal12.setCellValue(objeto2.getAncho()); cal12.setCellStyle(esceldain); cca12.setCellValue(objeto2.getCantidad()); cca12.setCellStyle(esceldain); cpz12.setCellValue(objeto2.getPiezas()); cpz12.setCellStyle(esceldain); cim12.setCellValue(objeto2.getImporte()); cim12.setCellStyle(esceldain); float uno = 0, dos = 0, resultado = 0; uno = Float.parseFloat(objeto.getImporte()); dos = Float.parseFloat(objeto2.getImporte()); if (uno > dos) { HSSFRow row31 = hoja.createRow((short) 33); // create de las celdas HSSFCell cc131 = row31.createCell((short) 3); resultado = uno - dos; cc131.setCellValue("Execedente: " + String.valueOf(resultado)); cc131.setCellStyle(esceldain); } else { if (dos > uno) { HSSFRow row31 = hoja.createRow((short) 33); // create de las celdas HSSFCell cc131 = row31.createCell((short) 3); resultado = dos - uno; cc131.setCellValue("Restante: " + String.valueOf(resultado)); cc131.setCellStyle(esceldain); } } // **************************************************************************************** } else { HSSFRow encabezado2 = hoja.createRow((short) 26); HSSFCell Cencabezado2 = encabezado2.createCell((short) 2); Cencabezado2.setCellValue( "El aspecto:" + objeto.getDescripcion() + " no esta comtemplado en la estimacion inicial"); hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4)); Cencabezado2.setCellStyle(esceldaen); HSSFRow row28 = hoja.createRow((short) 29); // create de las celdas HSSFCell cc12 = row28.createCell((short) 3); cc12.setCellValue("Execedente: " + objeto.getImporte()); cc12.setCellStyle(esceldain); } try { FileOutputStream elFichero = new FileOutputStream(FILE); libro.write(elFichero); elFichero.close(); } catch (Exception e) { e.printStackTrace(); } }
/** * Generating excel sheet * * @param mapping * @param form * @param request * @param response * @return * @throws Exception */ @SuppressWarnings("all") public ActionForward createExcel( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { String status = null; UserDetails userDetails = (UserDetails) request.getSession(false).getAttribute("userDetails"); // Store user details in TransactionContext object new TransactionContext(userDetails); AshaLogger.logInfo(logger, this.getClass().getName(), "createExcel", "executing"); // Arraylist al=(ArrayList)request.getAttribute("hidden_al"); Date d1 = new Date(); Format formatter = new SimpleDateFormat("dd-MMM-yy"); // System.out.println( "reformatted = " + sdf.format(d1) ); String count1 = request.getParameter("count"); Integer count = Integer.parseInt(count1); String type = request.getParameter("type"); if (type.indexOf(' ') != -1) { type = type.substring(0, type.indexOf(' ')); } FileInputStream fstream = null; DataInputStream in = null; BufferedReader br = null; String strLine = null; String pathContext = null; HSSFWorkbook workbook = null; HSSFSheet firstSheet = null; HSSFRow row = null; HSSFCell cell = null; StringTokenizer st = null; Properties props = null; HSSFCellStyle style = null; HSSFFont font = null; HSSFCellStyle style1 = null; HSSFFont font1 = null; workbook = new HSSFWorkbook(); // Heading Style style = workbook.createCellStyle(); // style.setBorderTop((short) 6); // double lines border // style.setBorderBottom((short) 1); // single line border // style.setFillBackgroundColor(HSSFColor.BLACK.index); // style.setFillForegroundColor(HSSFColor.BLACK.index); font = workbook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLUE.index); style.setFont(font); // Data Style style1 = workbook.createCellStyle(); // style.setBorderTop((short) 6); // double lines border // style.setBorderBottom((short) 1); // single line border // style.setFillBackgroundColor(HSSFColor.BLACK.index); // style.setFillForegroundColor(HSSFColor.BLACK.index); font1 = workbook.createFont(); font1.setFontName(HSSFFont.FONT_ARIAL); font1.setFontHeightInPoints((short) 11); // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setColor(HSSFColor.BLACK.index); style1.setFont(font1); int count2 = 1; if (type.equalsIgnoreCase("advances")) { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("PID")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Patient Name")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Bill No")); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount")); for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } count2++; } } catch (Exception e) { logger.info("e"); } } else if (type.equalsIgnoreCase("discount(-)")) { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("BillNo")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Patient Name")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Billable Amount")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Discount")); /*cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount"));*/ for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } /*cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request .getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); }*/ count2++; } } catch (Exception e) { logger.info("e"); } } // this is for pharmacy details else if (type.equalsIgnoreCase("pharmacy")) { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Name")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Mobile")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Bill No")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Discount")); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Billable Amount")); /*cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount"));*/ for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } /*cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request .getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); }*/ count2++; } } catch (Exception e) { logger.info("e"); } } else if (type.equalsIgnoreCase("refund(-)")) { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Patient Name")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Mobile")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Bill No.")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Discount")); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Billable")); /*cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount"));*/ for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } count2++; } } catch (Exception e) { logger.info("e"); } } else if (type.equalsIgnoreCase("unrealized")) { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("PID")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Patient Name")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Bill No")); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount")); /*cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount"));*/ for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } /*cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request .getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); }*/ count2++; } } catch (Exception e) { logger.info("e"); } } else { try { firstSheet = workbook.createSheet("Today"); row = firstSheet.createRow(0); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Category")); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("PID")); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Patient Name")); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Bill No")); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(new HSSFRichTextString("Amount")); for (int i = 1; i < count; i++) { row = firstSheet.createRow(count2); row.setHeight((short) 300); cell = row.createCell(0); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(1); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(2); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(3); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } cell = row.createCell(4); cell.setCellStyle(style1); try { cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i))); } catch (NoSuchElementException nsee) { cell.setCellValue(new HSSFRichTextString("")); } count2++; } } catch (Exception e) { logger.info("e"); } } // String filename = "E:\\ItemIssueDetails"+"_"+sdf.format(d1)+".xls"; firstSheet.autoSizeColumn((short) 0); firstSheet.autoSizeColumn((short) 1); firstSheet.autoSizeColumn((short) 2); firstSheet.autoSizeColumn((short) 3); firstSheet.autoSizeColumn((short) 4); try { logger.info("type in create excel is " + type); response.setContentType("x-application/vnd.ms-excel"); response.setHeader("Pragma", "no-cache"); response.setHeader( "Content-Disposition", "attachment;filename=" + type.trim() + "_" + formatter.format(d1) + ".xls"); ServletOutputStream outstream = response.getOutputStream(); workbook.write(outstream); // outstream.write(workbook.getBytes()); } catch (IOException e) { AshaLogger.logException( logger, this.getClass().getName(), "createExcel", "Exception Raised:", e); } finally { fstream = null; in = null; br = null; strLine = null; pathContext = null; workbook = null; firstSheet = null; row = null; cell = null; st = null; props = null; style = null; font = null; style1 = null; font1 = null; } request.setAttribute("status", "saved"); return mapping.findForward(null); }
private void initNormalFont() { normalFont = wb.createFont(); normalFont.setFontName("Arial"); normalFont.setFontHeightInPoints((short) 8); }
public static boolean writeArray2ExcelFile1( String FileName, String[] titleNames, List<String[]> arry) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet 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))); // } HSSFCellStyle headStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment HSSFFont headFont = wb.createFont(); headFont.setFontName("Courier New"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headFont.setColor(HSSFColor.BLACK.index); headStyle.setAlignment(HSSFCellStyle.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); HSSFCellStyle contentStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment HSSFFont contentFont = wb.createFont(); contentFont.setFontName("Courier New"); contentFont.setFontHeightInPoints((short) 9); // headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); contentFont.setColor(HSSFColor.BLACK.index); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFillForegroundColor(HSSFColor.BLACK.index); contentStyle.setFont(contentFont); // create titile row HSSFRow row = sheet1.createRow(0); int heandLine = 0; HSSFCell 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.?hssf.?usermodel.?HSSFCell.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; }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // 取得模型数据 SqlRowSet table = (SqlRowSet) model.get("table"); String title = model.get("title").toString(); // 创建工作表和标题 HSSFSheet sheet = workbook.createSheet(title); // 创建工作区 HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象 HSSFFont title_font = workbook.createFont(); // 创建标题的字体 title_font.setFontHeightInPoints((short) 8); title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL); title_font.setColor((short) (HSSFFont.COLOR_RED)); HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式 title_style.setFont(title_font); HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象 cell_title.setCellStyle(title_style); cell_title.setCellValue(title); // 创建数据表头 String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"}; HSSFRow row = sheet.createRow((short) 1); HSSFCellStyle items_style = workbook.createCellStyle(); items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER); HSSFFont celltbnamefont = workbook.createFont(); celltbnamefont.setFontHeightInPoints((short) 10); celltbnamefont.setColor((short) (HSSFFont.COLOR_RED)); items_style.setFont(celltbnamefont); items_style.setWrapText(true); for (int i = 0; i < titles.length; i++) { HSSFCell cell = row.createCell(i); if (i == 3 || i == 6 || i == 2) { sheet.setColumnWidth(i, 5335); } else { sheet.setColumnWidth(i, 3335); } cell.setCellValue(titles[i]); cell.setCellStyle(items_style); } HSSFCellStyle datestyle = workbook.createCellStyle(); HSSFDataFormat df = workbook.createDataFormat(); datestyle.setDataFormat(df.getFormat("yyyy-mm-dd")); int i = 0; while (table.next()) { HSSFRow dataRow = sheet.createRow((short) (i + 2)); for (int j = 0; j < 9; j++) { HSSFCell cell = dataRow.createCell(j); String data = table.getString(j + 2); cell.setCellStyle(datestyle); cell.setCellValue(data); } i++; } }
public static boolean writeArray2ExcelSheet1( HSSFSheet sheet1, HSSFWorkbook 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(i, (int) ((30 * 8) / ((double) 1 / 20))); } HSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); HSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); HSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row HSSFRow row = sheet1.createRow(rowIndex); HSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(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.?hssf.?usermodel.?HSSFCell.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 writeMultArray2ExcelFile1( 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; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment HSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); HSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); String lastKey = null; int switcher = -1; HSSFCell cell = null; String[] titleNames = null; int d = 0; for (List<String[]> arry : arrys) { HSSFSheet 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, (int) ((30 * 6) / ((double) 1 / 20))); } int rowIndex = 0; // create titile row HSSFRow 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.?hssf.?usermodel.?HSSFCell.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; }
// 导出报表到 excel public void toExcel() throws Exception { String date = Common.getSystemDate(); // 定义excel以及sheet HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(date + "学工部设备表"); // header sheet.setColumnWidth((short) 0, (short) (8 * 256)); sheet.setColumnWidth((short) 1, (short) (20 * 256)); sheet.setColumnWidth((short) 2, (short) (20 * 256)); sheet.setColumnWidth((short) 3, (short) (16 * 256)); sheet.setColumnWidth((short) 4, (short) (20 * 256)); sheet.setColumnWidth((short) 5, (short) (20 * 256)); sheet.setColumnWidth((short) 6, (short) (20 * 256)); sheet.setColumnWidth((short) 7, (short) (16 * 256)); sheet.setColumnWidth((short) 8, (short) (20 * 256)); sheet.setColumnWidth((short) 9, (short) (20 * 256)); sheet.setColumnWidth((short) 10, (short) (20 * 256)); sheet.setColumnWidth((short) 11, (short) (20 * 256)); HSSFRow titleRow = sheet.createRow((short) 0); HSSFCell titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("重庆邮电大学学工部设备表"); // 设置表头样式 HSSFCellStyle titleStyle = workBook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 表头字体 HSSFFont titleFont = workBook.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setFontName("黑体"); titleStyle.setFont(titleFont); // 表头边框 titleStyle.setBorderBottom(CellStyle.BORDER_THIN); // titleStyle.setBorderLeft(CellStyle.BORDER_THIN); titleStyle.setBorderRight(CellStyle.BORDER_THIN); // titleStyle.setBorderTop(CellStyle.BORDER_THIN); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(titleStyle); // 设置单元格边框 titleRow = sheet.createRow((short) 1); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(titleStyle); // 合并单元格 0行0列 1行9列 sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 11)); // 内容样式 HSSFCellStyle contentStyle = workBook.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 内容字体 HSSFFont contentFont = workBook.createFont(); contentFont.setFontHeightInPoints((short) 11); contentFont.setFontName("宋体"); contentStyle.setFont(contentFont); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); // contentStyle.setBorderLeft(CellStyle.BORDER_THIN); // contentStyle.setBorderTop(CellStyle.BORDER_THIN); // 第二行表头 titleRow = sheet.createRow((short) 2); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("编号"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("名称"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("设备价值(元)"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("申请部门"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("申请人"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("购买时间"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("购买地点"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("设备状态"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("生产厂商"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("使用部门"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("使用人"); titleCell.setCellStyle(contentStyle); List<Equipment> equipmentListtemp = new ArrayList<Equipment>(); equipmentListtemp = this.equipmentService.getEquipmentBycondtion("where 1=1"); int i = 0; Double totalMoney = 0d; String totalString = ""; for (i = 0; i < equipmentListtemp.size(); ++i) { titleRow = sheet.createRow((short) (i + 3)); // 第一列 编号 titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getEquipmentcode() != null) titleCell.setCellValue(equipmentListtemp.get(i).getEquipmentcode()); else titleCell.setCellValue(i + 1); titleCell.setCellStyle(contentStyle); // 第二列 名称 titleCell = titleRow.createCell((short) 1); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getName() != null) titleCell.setCellValue(equipmentListtemp.get(i).getName()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第三列 设备价值 titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getValueof().toString() != null) { titleCell.setCellValue(equipmentListtemp.get(i).getValueof().toString()); totalMoney += equipmentListtemp.get(i).getValueof(); } else { titleCell.setCellValue("0"); totalMoney += 0; } titleCell.setCellStyle(contentStyle); // 第四列 申请部门 titleCell = titleRow.createCell((short) 3); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); String officeIdTemp = equipmentListtemp.get(i).getDepartment(); if (officeIdTemp != null) { Studentoffice studentofficetemp = this.studentofficeService.findById(officeIdTemp); if (studentofficetemp != null) { titleCell.setCellValue(studentofficetemp.getName()); } } else { titleCell.setCellValue("请检查办公室"); } titleCell.setCellStyle(contentStyle); // 第五列 申请人 titleCell = titleRow.createCell((short) 4); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getBuyers() != null) titleCell.setCellValue(equipmentListtemp.get(i).getBuyers()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第六列 购买时间 titleCell = titleRow.createCell((short) 5); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getPurchasetime() != null) titleCell.setCellValue(equipmentListtemp.get(i).getPurchasetime().trim()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第七列 购买地点 titleCell = titleRow.createCell((short) 6); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getAddress() != null) titleCell.setCellValue(equipmentListtemp.get(i).getAddress()); else titleCell.setCellValue("请检查岗位名称"); titleCell.setCellStyle(contentStyle); // 第八列 设备状态 titleCell = titleRow.createCell((short) 7); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getStates() == 1) { titleCell.setCellValue("正常"); } else titleCell.setCellValue("报废"); titleCell.setCellStyle(contentStyle); // 第九列 生产厂商 titleCell = titleRow.createCell((short) 8); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getFactory() != null) { titleCell.setCellValue(equipmentListtemp.get(i).getFactory()); } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第十列 使用部门 titleCell = titleRow.createCell((short) 9); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); Studentoffice Studentofficenow = new Studentoffice(); List<Equipmentdepartment> equipmentdepartmentlisttemp = this.equipmentDepartmentService.findByEquipmentId(equipmentListtemp.get(i).getId()); Equipmentdepartment Equipmentdepartmentnow = new Equipmentdepartment(); for (int j = 0; j < equipmentdepartmentlisttemp.size(); j++) { if (equipmentdepartmentlisttemp.get(j).getFlowouttime() == null || equipmentdepartmentlisttemp.get(j).getFlowouttime().length() == 0) { Equipmentdepartmentnow = equipmentdepartmentlisttemp.get(j); Studentofficenow = this.studentofficeService.findById( equipmentdepartmentlisttemp.get(j).getDepartment()); break; } } if (Studentofficenow.getName() != null) { titleCell.setCellValue(Studentofficenow.getName()); } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第十一列 使用人 titleCell = titleRow.createCell((short) 10); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); Officeteacher officeteachernow = new Officeteacher(); if (Equipmentdepartmentnow != null) { if (Equipmentdepartmentnow != null) { List<Equipmentuser> equipmentUserNowList = this.equipmentuserService.findByEquipmentdepId(Equipmentdepartmentnow.getId()); for (int j = 0; j < equipmentUserNowList.size(); j++) { if (equipmentUserNowList.get(j).getFlowouttime2() == null || equipmentUserNowList.get(j).getFlowouttime2().length() == 0) { officeteachernow = this.officeteacherService.findById( equipmentUserNowList.get(j).getEquipmentuser()); break; } } } } if (officeteachernow != null) { if (officeteachernow.getTeacher() != null) { if (officeteachernow.getTeacher().getName() != null) { titleCell.setCellValue(officeteachernow.getTeacher().getName()); } } } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); } titleRow = sheet.createRow((short) (i + 3)); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("合计"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellStyle(contentStyle); titleCell.setCellValue("¥" + totalMoney + "元"); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 4)); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); sheet.addMergedRegion(new Region((i + 3), (short) 0, (i + 4), (short) 1)); sheet.addMergedRegion(new Region((i + 3), (short) 2, (i + 4), (short) 11)); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 5)); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("大写"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); // 转换大写方式 totalString = szTzf(totalMoney); titleCell.setCellValue(totalString); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 6)); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); sheet.addMergedRegion(new Region((i + 5), (short) 0, (i + 6), (short) 1)); sheet.addMergedRegion(new Region((i + 5), (short) 2, (i + 6), (short) 11)); // 表尾样式 HSSFCellStyle endStyle = workBook.createCellStyle(); endStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); endStyle.setBorderBottom(CellStyle.BORDER_THIN); endStyle.setBorderRight(CellStyle.BORDER_THIN); endStyle.setFont(contentFont); // sheet.getRow(0).getCell((short)0).setCellStyle(titleStyle); // sheet.getRow(1).getCell((short)0).setCellStyle(contentStyle); // sheet.getRow(1).getCell((short)1).setCellStyle(contentStyle); // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle); // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle); // 导出 HttpServletResponse resp = ServletActionContext.getResponse(); resp.setContentType("application/vnd.ms-excel"); resp.setCharacterEncoding("UFT-8"); String fileName = date + "学工部设备表.xls"; fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); resp.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); ServletOutputStream op = resp.getOutputStream(); op.flush(); workBook.write(op); op.close(); // return "toExcel"; /* * String fileName = date.toLocaleString().substring(0, 10)+ * "勤工助学酬劳发放表.xls"; FileOutputStream fileOut = new * FileOutputStream(fileName); workBook.write(fileOut); fileOut.close(); */ }