private void createSummerySheet() { sheet0 = workbook.createSheet("Summary"); PrintSetup printSetup = sheet0.getPrintSetup(); printSetup.setLandscape(true); sheet0.setFitToPage(true); sheet0.setHorizontallyCenter(true); // title row Row titleRow = sheet0.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("File Health Report"); titleCell.setCellStyle(styles.get("title")); sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); for (int i = 0; i < titles.length; i++) { Row _row = sheet0.createRow(i + 1); Cell headerCell = _row.createCell(0); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); _row.setHeightInPoints(20); } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide }
/** * @param SheetName excel里面的sheet的名字 (可以任意写,一般和excelname一样) * @param response 要写的流 * @param HeadName 数据的表头 即列名 * @param data 数据 二维数组 * @return * @throws IOException */ public static OutputStream creatExcel( String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(SheetName); CellStyle s = new CellStyle(); Font f12 = new Font(); HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12); HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12); // sheet.setDefaultColumnWidth(50); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 18 * 256); OutputStream os = new FileOutputStream(file); // 设置excel的表头 for (int i = 0; i < HeadName.size(); i++) { ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style); } // 设置excel数据 for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style); } } // 设置excel 表格的列宽 for (int i = 0; i < HeadName.size(); i++) { sheet.setColumnWidth(i, ColumnWidth[i]); } workbook.write(os); return os; }
private void initSheet() { String[] messageFiles = MessageAssistant.getInstance().getMessageFiles(); sheet.setColumnWidth((short) 0, (short) 10000); for (short iColumn = 1; iColumn <= messageFiles.length; iColumn++) { sheet.setColumnWidth(iColumn, (short) 12000); } }
public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // 设置列宽 worksheet.setColumnWidth(0, 5200); worksheet.setColumnWidth(1, 5200); worksheet.setColumnWidth(2, 5200); buildTitle(worksheet, startRowIndex, startColIndex); buildHeaders(worksheet, startRowIndex, startColIndex); }
/** * 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)); } }
/** * Builds the report layout. * * <p>This doesn't have any data yet. This is your template. */ public static void buildReport( HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) { // Set column widths worksheet.setColumnWidth(0, 5000); // Build the title and date headers buildTitle(worksheet, startRowIndex, startColIndex); // Build the column headers buildHeaders(worksheet, startRowIndex, startColIndex, datasource); }
public static OutputStream creatExcel( String SheetName, String excelName, HttpServletResponse response, List<String> HeadName, String[][] data, List<Integer> colunmWidth) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(SheetName); CellStyle s = new CellStyle(); Font f12 = new Font(); HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12); HSSFCellStyle style = ExcelUtil.getNewStyles(workbook, s, font12); sheet.setDefaultColumnWidth(50); sheet.setDefaultRowHeight((short) 400); response.setContentType("application/ms-excel;"); response.setHeader( "Content-Disposition", "attachment;Filename=" + ExcelUtil.toUtf8String(excelName)); sheet.setColumnWidth(0, (HeadName.size()) * 256); OutputStream os = response.getOutputStream(); // 设置excel的表头 for (int i = 0; i < HeadName.size(); i++) { ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style); } // 设置excel数据 for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style); } } // 设置excel 表格的列宽 for (int i = 0; i < HeadName.size(); i++) { sheet.setColumnWidth(i, colunmWidth.get(i)); } workbook.write(os); return os; }
/** * 设置列宽 * * @author David * @param sheet * @param colgroup */ private static void setColumnWidth(HSSFSheet sheet, Element colgroup) { List<Element> cols = colgroup.getChildren("col"); for (int i = 0; i < cols.size(); i++) { Element col = cols.get(i); Attribute width = col.getAttribute("width"); String unit = width.getValue().replaceAll("[0-9,\\.]", ""); String value = width.getValue().replaceAll(unit, ""); int v = 0; if (StringUtils.isBlank(unit) || "px".endsWith(unit)) { v = Math.round(Float.parseFloat(value) * 37F); } else if ("em".endsWith(unit)) { v = Math.round(Float.parseFloat(value) * 267.5F); } sheet.setColumnWidth(i, v); } }
private void fixWidthAndPopulate(HSSFCell cell, double numeric, String value) { int valWidth = 0; if (numeric != NON_NUMERIC) { cell.setCellValue(numeric); valWidth = (cell.getNumericCellValue() + "$,.").length() * WIDTH_MULT; } else { cell.setCellValue(value); valWidth = (cell.getStringCellValue() + "").length() * WIDTH_MULT; if (valWidth < (WIDTH_MULT * MIN_CHARS)) { valWidth = WIDTH_MULT * MIN_CHARS; } } if (valWidth > sheet.getColumnWidth(cell.getCellNum())) { sheet.setColumnWidth(cell.getCellNum(), (short) valWidth); } }
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++; } }
private void createHeader(TableModel model) { rownum = 0; cellnum = 0; HSSFRow row = sheet.createRow(rownum); List columns = model.getColumnHandler().getHeaderColumns(); for (Iterator iter = columns.iterator(); iter.hasNext(); ) { Column column = (Column) iter.next(); String title = column.getCellDisplay(); HSSFCell cell = row.createCell(cellnum); // modify by springside // cell.setEncoding(HSSFCell.ENCODING_UTF_16); // end of modify by springside cell.setCellStyle((HSSFCellStyle) styles.get("titleStyle")); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); int valWidth = (title + "").length() * WIDTH_MULT; sheet.setColumnWidth(cell.getCellNum(), (short) valWidth); cellnum++; } }
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; }
/** * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param headers 表格属性列名数组 * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) */ @SuppressWarnings("unchecked") public Workbook exportExcel(String[] headers, Collection<T> dataset) { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth); // 生成一个样式 // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new HSSFRichTextString(docPrse)); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor(author); // 产生表格标题行 HSSFRow row = sheet.createRow(startRow); int colNum1 = startCol; for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(colNum1); if (useStyle) cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); colNum1++; } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = startRow; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); int colRow2 = startCol; for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(colRow2); if (useStyle) cell.setCellStyle(style2); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Boolean) { boolean bValue = (Boolean) value; textValue = "男"; if (!bValue) { textValue = "女"; } } else if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); textValue = sdf.format(date); } else if (value instanceof byte[]) { // 有图片时,设置行高为60px; row.setHeightInPoints(picCellHeight); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, picCellWidth); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index); anchor.setAnchorType(2); patriarch.createPicture( anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } colRow2++; } } return workbook; }
@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++; } }
// 导出报表到 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(); */ }
/** * Call each of the list creation methods. * * @param outputFilename A String that encapsulates the name of and path to the Excel spreadsheet * file this code will create. */ public void demonstrateMethodCalls(String outputFilename) { HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; File outputFile = null; FileOutputStream fos = null; ArrayList<MultiLevelListItem> multiLevelListItems = null; ArrayList<String> listItems = null; String listItem = null; try { workbook = new HSSFWorkbook(); sheet = workbook.createSheet("In Cell Lists"); row = sheet.createRow(0); // Create a cell at A1 and insert a single, bulleted, item into // that cell. cell = row.createCell(0); this.bulletedItemInCell(workbook, "List Item", cell); // Create a cell at A2 and insert a plain list - that is one // whose items are neither bulleted or numbered - into that cell. row = sheet.createRow(1); cell = row.createCell(0); listItems = new ArrayList<String>(); listItems.add("List Item One."); listItems.add("List Item Two."); listItems.add("List Item Three."); listItems.add("List Item Four."); this.listInCell(workbook, listItems, cell); // The row height and cell width are set here to ensure that the // list may be seen. row.setHeight((short) 1100); sheet.setColumnWidth(0, 9500); // Create a cell at A3 and insert a numbered list into that cell. // Note that a couple of items have been added to the listItems // ArrayList row = sheet.createRow(2); cell = row.createCell(0); listItems.add("List Item Five."); listItems.add("List Item Six."); this.numberedListInCell(workbook, listItems, cell, 1, 2); row.setHeight((short) 1550); // Create a cell at A4 and insert a numbered list into that cell. // Note that a couple of items have been added to the listItems // ArrayList row = sheet.createRow(3); cell = row.createCell(0); listItems.add("List Item Seven."); listItems.add("List Item Eight."); listItems.add("List Item Nine."); listItems.add("List Item Ten."); this.bulletedListInCell(workbook, listItems, cell); row.setHeight((short) 2550); // Insert a plain, multi-level list into cell A5. Note that // the major difference here is that the list items are passed as // an ArrayList of MultiLevelListItems. Note that an ArrayList // of instances of an inner class was used here in preference to // a Hashtable or HashMap as the ArrayList will preserve the // ordering of the items added to it; the first item added will // be the first item recovered and the last item added, the last // item recovered. row = sheet.createRow(4); cell = row.createCell(0); multiLevelListItems = new ArrayList<MultiLevelListItem>(); listItems = new ArrayList<String>(); listItems.add("ML List Item One - Sub Item One."); listItems.add("ML List Item One - Sub Item Two."); listItems.add("ML List Item One - Sub Item Three."); listItems.add("ML List Item One - Sub Item Four."); multiLevelListItems.add(new MultiLevelListItem("List Item One.", listItems)); // Passing either null or an empty ArrayList will signal that // there are no lower level items associated with the top level // item multiLevelListItems.add(new MultiLevelListItem("List Item Two.", null)); multiLevelListItems.add(new MultiLevelListItem("List Item Three.", null)); listItems = new ArrayList<String>(); listItems.add("ML List Item Four - Sub Item One."); listItems.add("ML List Item Four - Sub Item Two."); listItems.add("ML List Item Four - Sub Item Three."); multiLevelListItems.add(new MultiLevelListItem("List Item Four.", listItems)); this.multiLevelListInCell(workbook, multiLevelListItems, cell); row.setHeight((short) 2800); // Insert a numbered multi-level list into cell A6. Note that the // same ArrayList as constructed for the above plain multi-level // list example will be re-used row = sheet.createRow(5); cell = row.createCell(0); this.multiLevelNumberedListInCell(workbook, multiLevelListItems, cell, 1, 1, 1, 2); row.setHeight((short) 2800); // Insert a numbered multi-level list into cell A7. Note that the // same ArrayList as constructed for the plain multi-level list // example will be re-used row = sheet.createRow(6); cell = row.createCell(0); this.multiLevelBulletedListInCell(workbook, multiLevelListItems, cell); row.setHeight((short) 2800); // Save the completed workbook outputFile = new File(outputFilename); fos = new FileOutputStream(outputFile); workbook.write(fos); } catch (FileNotFoundException fnfEx) { System.out.println("Caught a: " + fnfEx.getClass().getName()); System.out.println("Message: " + fnfEx.getMessage()); System.out.println("Stacktrace follows..........."); fnfEx.printStackTrace(System.out); } catch (IOException ioEx) { System.out.println("Caught a: " + ioEx.getClass().getName()); System.out.println("Message: " + ioEx.getMessage()); System.out.println("Stacktrace follows..........."); ioEx.printStackTrace(System.out); } finally { if (fos != null) { try { fos.close(); } catch (IOException ioEx) { } } } }
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack) throws RuntimeException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); // create a sheet with specified name HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName()); for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) { sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256); } // create a title for sheet title sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1)); HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); // set font titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeight((short) (240)); titleFont.setColor(HSSFColor.AUTOMATIC.index); titleCellStyle.setFont(titleFont); titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow title = sheet.createRow(0); title.setHeight((short) 300); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = title.createCell(i); cell.setCellValue(xlscaCallBack.getTitle()); cell.setCellStyle(titleCellStyle); } HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); // set font headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("幼圆"); headerFont.setColor(HSSFColor.AUTOMATIC.index); headerCellStyle.setFont(headerFont); headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow header = sheet.createRow(1); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header HSSFCell cell = header.createCell(i); cell.setCellValue(xlscaCallBack.getHeaders()[i]); cell.setCellStyle(headerCellStyle); } HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont rowFont = workbook.createFont(); // set font rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); rowFont.setFontName("幼圆"); rowFont.setColor(HSSFColor.AUTOMATIC.index); rowCellStyle.setFont(rowFont); rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 2); String values[] = xlscaCallBack.getValue(lists.get(i)); for (int j = 0; j < values.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(values[j]); cell.setCellStyle(rowCellStyle); } } HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont bottomFont = workbook.createFont(); // set font bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); bottomFont.setFontName("幼圆"); bottomFont.setColor(HSSFColor.AUTOMATIC.index); bottomCellStyle.setFont(bottomFont); bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); sheet.addMergedRegion( new CellRangeAddress( lists.size() + 2, lists.size() + 2, 0, xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls HSSFRow bottomRow = sheet.createRow(lists.size() + 2); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = bottomRow.createCell(i); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date())); cell.setCellStyle(bottomCellStyle); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("create Excel failed due to some unkonw reasion~"); } }
public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) { this.data = data; this.wb = wb; final HSSFSheet sheet = wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList")); utils = new ExcelUtils(wb); int rowIndex = -1; int cellIndex = 0; // empty row utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // title utils.putMainTitle( sheet, ++rowIndex, data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(), data.getNumbOfCols()); // empty row utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); // column headers row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue")); utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value")); // empty row utils.putEmptyRow(sheet, ++rowIndex, 8.65f); // freeze pane sheet.createFreezePane(0, rowIndex); for (final IndicatorGroup group : data.getIndicators().getGroups()) { row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); putGroupCell(sheet, rowIndex, group.getName()); for (final IndicatorDTO indicator : group.getIndicators()) { // indicator's detail sheet createDetailSheet(indicator); row = sheet.createRow(++rowIndex); row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); // ind name utils.createLinkCell( row.createCell(1), indicator.getName(), ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(), true); // code utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode()); // target putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective()); // current value putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator)); } } sheet.setColumnWidth(0, 256 * 2); sheet.setColumnWidth(1, 256 * 45); sheet.setColumnWidth(2, 256 * 27); sheet.setColumnWidth(3, 256 * 27); sheet.setColumnWidth(4, 256 * 27); }
private void createDetailSheet(final IndicatorDTO indicator) { final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL; final HSSFSheet sheetEx = wb.createSheet( utils.normalizeAsLink(ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName())); int rowIndex = -1; final List<PivotTableData.Axis> leaves = data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves(); int numbOfLeaves = leaves.size(); int numbOfCols = 4; // back to list link row = sheetEx.createRow(++rowIndex); utils.createLinkCell( row.createCell(1), data.getLocalizedVersion("goToIndicatorsList"), data.getLocalizedVersion("flexibleElementIndicatorsList"), false); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // title utils.putMainTitle(sheetEx, ++rowIndex, indicator.getName(), numbOfCols); // empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // put details putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols); putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("group"), data.getGroupMap().get(indicator.getGroupId()), numbOfCols); // type String type = null; ; if (isQualitative) { // qualitative type = data.getLocalizedVersion("qualitative"); } else { // quantitative type = data.getLocalizedVersion("quantitative"); } putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols); // conditional if (isQualitative) { // qualitative // possible values row = sheetEx.createRow(++rowIndex); // key cell = utils.putHeader(row, 1, data.getLocalizedVersion("possibleValues")); cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT); // value final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels()); utils.putBorderedBasicCell(sheetEx, rowIndex, 2, itemText.text); row.setHeightInPoints(itemText.lineCount * defHeight); region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols); sheetEx.addMergedRegion(utils.getBorderedRegion(region, sheetEx, wb)); } else { // quantitative // aggregation method String aggrMethod = null; if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG) aggrMethod = data.getLocalizedVersion("average"); else aggrMethod = data.getLocalizedVersion("sum"); putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("aggregationMethod"), aggrMethod, numbOfCols); // units putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols); // target value putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("targetValue"), indicator.getObjective(), numbOfCols); } // source of ver putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("sourceOfVerification"), indicator.getSourceOfVerification(), numbOfCols); row.setHeightInPoints( utils.calculateLineCount(indicator.getSourceOfVerification(), 3 * 18) * defHeight); // comment putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("indicatorComments"), indicator.getDescription(), numbOfCols); row.setHeightInPoints(utils.calculateLineCount(indicator.getDescription(), 3 * 18) * defHeight); // value putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("value"), data.getFormattedValue(indicator), numbOfCols); // empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // data entry // header row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); int cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth")); Map<String, Integer> columnIndexMap = new HashMap<String, Integer>(); for (PivotTableData.Axis axis : leaves) { utils.putHeader(row, ++cellIndex, axis.getLabel()); columnIndexMap.put(axis.getLabel(), cellIndex); } // rows for (PivotTableData.Axis axis : data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) { row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); utils.putHeader(row, 1, axis.getLabel()); // populate empty cells for (int i = 0; i < numbOfLeaves; i++) { cell = utils.putBorderedBasicCell(sheetEx, rowIndex, i + 2, ""); } // insert values for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) { cellIndex = columnIndexMap.get(entry.getKey().getLabel()); Object value = null; boolean rightAligned = false; if (isQualitative) { value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue()); } else { value = new Long(Math.round(entry.getValue().getValue())); rightAligned = true; } putValueCell(sheetEx, rowIndex, cellIndex, value, rightAligned); } } // col width sheetEx.setColumnWidth(0, 256 * 2); sheetEx.autoSizeColumn(1); for (int i = 2; i < 2 + numbOfLeaves; i++) { sheetEx.setColumnWidth(i, 256 * 16); } }
public void setColumnWidth(int columnIndex, int width) { sheet.setColumnWidth(columnIndex, width); }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, HashMap<String, HSSFCellStyle> styles, String creator, List<TableData> tableDataLst) throws Exception { SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); int cnt = 1; for (TableData tableData : tableDataLst) { String sheetTitle = tableData.getSheetTitle(); sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + cnt : sheetTitle; cnt++; TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 HSSFSheet sheet = wb.createSheet(sheetTitle); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 wb.cloneSheet(0); // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(sheetTitle)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("创建人:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; createCell(row, tc, dataCells, i, index, styles); } rownum++; } // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); } return wb; }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles, String creator, TableData tableData, String subTitleName) throws Exception { TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); HSSFSheet sheet = wb.createSheet(title); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(title)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(subTitleName)); // updated by zdwang 2014-02-21 style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>(); HashMap<Integer, String> word = new HashMap<Integer, String>(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; String value = dataCells.get(i).getValue(); if (tc.isGrouped()) { String w = word.get(index); if (w == null) { word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } else { if (w.equals(value)) { counter.put(index, counter.get(index) + 1); } else { stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING")); word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } } } else { createCell(row, tc, dataCells, i, index, styles); } } rownum++; } stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING")); // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); return wb; }
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 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(); } }
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(); } }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Object> errorList = (List<Object>) model.get("errorList"); List<DonVi> donViError = (List<DonVi>) errorList.get(0); List<String> statusError = (List<String>) errorList.get(1); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Times New Roman"); font.setFontHeight((short) 260); style.setFont(font); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 12000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 5000); sheet.setDefaultColumnStyle(0, style); sheet.setDefaultColumnStyle(1, style); sheet.setDefaultColumnStyle(2, style); sheet.setDefaultColumnStyle(3, style); sheet.setDefaultColumnStyle(4, style); sheet.setDefaultColumnStyle(5, style); // create header row CellStyle style2 = workbook.createCellStyle(); Font font2 = workbook.createFont(); font2.setFontName("Times New Roman"); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeight((short) 260); style2.setFont(font2); style2.setAlignment(CellStyle.ALIGN_CENTER); HSSFRow row2 = sheet.createRow(0); row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ"); row2.getCell(0).setCellStyle(style); row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ"); row2.getCell(1).setCellStyle(style); // create header row HSSFRow header = sheet.createRow(1); response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls"); header.createCell(0).setCellValue("Mã BPSD"); header.getCell(0).setCellStyle(style2); header.createCell(1).setCellValue("Tên BPSD"); header.getCell(1).setCellStyle(style2); header.createCell(2).setCellValue("Địa chỉ"); header.getCell(2).setCellStyle(style2); header.createCell(3).setCellValue("Email"); header.getCell(3).setCellStyle(style2); header.createCell(4).setCellValue("Số điện thoại"); header.getCell(4).setCellStyle(style2); header.createCell(5).setCellValue("Lỗi"); header.getCell(5).setCellStyle(style2); // create data rows int rowCount = 2; int i = 0; for (DonVi dv : donViError) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(dv.getDvMa()); aRow.createCell(1).setCellValue(dv.getDvTen()); aRow.createCell(2).setCellValue(dv.getDiaChi()); aRow.createCell(3).setCellValue(dv.getEmail()); aRow.createCell(4).setCellValue(dv.getSdt()); aRow.createCell(5).setCellValue(statusError.get(i++)); } }