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 }
private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // 报表标题字体 Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // 标题单元格格式 HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("学生列表"); cellTitle.setCellStyle(cellStyleTitle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列 Date date = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date)); }
private void writeHeader(HSSFSheet sheet) { this.rowCount = 0; short column = 0; // first row HSSFRow row = sheet.createRow(rowCount++); write(row, column++, "Project Name", style); write(row, column++, project.getName()); // Second row column = 0; row = sheet.createRow(rowCount++); write(row, column++, "Version", style); write(row, column++, "Field", style); write(row, column++, "Type", style); // write locales for (Locale locale : getLocales()) { String displayStr = LanguageUtils.getShortDisplayString(locale, Locale.ENGLISH); write(row, column++, displayStr, style); } assert rowCount == 2; }
/** * Builds the report title and the date header * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Compensation Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("This report was generated at " + new Date()); }
public static void writeExcel(String excelPath, String testCase) throws Exception { /** * create Excel file in excelPath build two sheet which names are "TestSummary" and testcase * detailinfo */ FileOutputStream fos = new FileOutputStream(excelPath); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s1 = wb.createSheet(); wb.setSheetName(0, "TestSummary"); HSSFSheet s2 = wb.createSheet(); wb.setSheetName(1, testCase + "_DetailInfo"); HSSFRow s1row = s1.createRow(0); HSSFRow s2row = s2.createRow(0); s1row.createCell(0).setCellValue("Total TCs"); s1row.createCell(1).setCellValue("Run TCs"); s1row.createCell(2).setCellValue("Passed TCs"); s1row.createCell(3).setCellValue("Failure TCs"); s1row.createCell(4).setCellValue("NoRun TCs"); s2row.createCell(0).setCellValue("SubTestCase"); s2row.createCell(1).setCellValue("TestResult"); s2row.createCell(2).setCellValue("AssertPointCheck"); s2row.createCell(3).setCellValue("ScreenShot"); s2row.createCell(4).setCellValue("Processor"); wb.write(fos); fos.close(); }
public static void writeExcel(List<TongJiModel> list) throws Exception { // 创建Excel的工作书册 Workbook,对应到一个excel文档 HSSFWorkbook wb = new HSSFWorkbook(); int columnCount = 5; // 创建Excel的工作sheet,对应到一个excel文档的tab HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow first = sheet.createRow(0); // 下标为0的行开始 String[] firstRow = new String[columnCount]; firstRow[0] = "姓名"; firstRow[1] = "组名"; firstRow[2] = "邀请投资次数"; firstRow[3] = "邀请投资总金额"; firstRow[4] = "邀请注册人数"; for (int i = 0; i < columnCount; i++) { first.createCell(i).setCellValue(new HSSFRichTextString(firstRow[i])); } for (int i = 0; i < list.size(); i++) { HSSFRow row = sheet.createRow(i + 1); TongJiModel tongJiModel = list.get(i); row.createCell(0).setCellValue(new HSSFRichTextString(tongJiModel.getName())); row.createCell(1).setCellValue(new HSSFRichTextString(tongJiModel.getGroupName())); row.createCell(2).setCellValue(new HSSFRichTextString(tongJiModel.getInvestAcount())); row.createCell(3).setCellValue(new HSSFRichTextString(tongJiModel.getInvestSumAmount())); row.createCell(4).setCellValue(new HSSFRichTextString(tongJiModel.getRegisteCount())); } FileOutputStream os = new FileOutputStream("/Users/pansen/Downloads/20160428.xls"); wb.write(os); os.close(); }
public void testRemoveNewRow_bug46312() { // To make bug occur, rowIndex needs to be >= ValueRecordsAggregate.records.length int rowIndex = 30; ValueRecordsAggregate vra = new ValueRecordsAggregate(); try { vra.removeAllCellsValuesForRow(rowIndex); } catch (IllegalArgumentException e) { if (e.getMessage().equals("Specified rowIndex 30 is outside the allowable range (0..30)")) { throw new AssertionFailedError("Identified bug 46312"); } throw e; } if (false) { // same bug as demonstrated through usermodel API HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(rowIndex); if (false) { // must not add any cells to the new row if we want to see the bug row.createCell(0); // this causes ValueRecordsAggregate.records to auto-extend } try { sheet.createRow(rowIndex); } catch (IllegalArgumentException e) { throw new AssertionFailedError("Identified bug 46312"); } } }
@SuppressWarnings("unchecked") @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { resp.setContentType("application/vnd.ms-excel"); resp.setHeader("Content-Disposition", "attachment;filename=Rezultati.xls"); OutputStream out = resp.getOutputStream(); HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("results"); HSSFRow headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Voting option"); headerRow.createCell(1).setCellValue("Votes received"); int t = 1; for (VotingResultEntry vre : (List<VotingResultEntry>) req.getSession().getAttribute("results")) { HSSFRow row = sheet.createRow(t++); row.createCell(0).setCellValue(vre.getName()); row.createCell(1).setCellValue(vre.getVotes()); } hwb.write(out); out.flush(); out.close(); }
@Override public ModelAndView getReportTemplate() { List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report"); int rownum = 0; Row row0 = sheet.createRow(rownum++); row0.createCell(0).setCellValue("Source Country"); row0.createCell(1).setCellValue("Service"); row0.createCell(2).setCellValue("Destination Country"); row0.createCell(3).setCellValue("Total Call Minutes"); for (MonthlyTraffic mt : monthlyTrafficReports) { Row row = sheet.createRow(rownum++); row.createCell(0).setCellValue(mt.getFromCountry()); row.createCell(1).setCellValue(mt.getServiceName()); row.createCell(2).setCellValue(mt.getToCountry()); row.createCell(3).setCellValue(mt.getMinutesOfCalls()); } Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>(); parameterMap.put("excelBook", workbook); ModelAndView modelAndView = new ModelAndView(); modelAndView.addAllObjects(parameterMap); return modelAndView; }
@SuppressWarnings("deprecation") public void Daochu() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("库存盘点表"); HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("快递编号"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("目的地"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("入库日期"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("区号"); cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("排号"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("架号"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("位号"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("中转中心"); cell.setCellStyle(style); FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt"); ObjectInputStream ois = new ObjectInputStream(fis); @SuppressWarnings("unchecked") List<InStoringpo> list = (List<InStoringpo>) ois.readObject(); ois.close(); InStoringpo[] sp = new InStoringpo[list.size()]; for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); sp[i] = list.get(i); // 第四步,创建单元格,并设置值 row.createCell((short) 0).setCellValue(sp[i].bianhao); row.createCell((short) 1).setCellValue(sp[i].destination); row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day); row.createCell((short) 3).setCellValue(sp[i].quhao); row.createCell((short) 4).setCellValue(sp[i].paihao); row.createCell((short) 5).setCellValue(sp[i].jiahao); row.createCell((short) 6).setCellValue(sp[i].weihao); row.createCell((short) 7).setCellValue(sp[i].zhongzhuan); } try { FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls"); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } System.out.println("导出成功!"); }
/** * to build the response as excel file. * * @see org.springframework.web.servlet.view.document.AbstractExcelView# * buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, * javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse) */ @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED) @Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook myWorkBook, HttpServletRequest arg2, HttpServletResponse arg3) throws Exception { List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions"); HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME); HSSFRow headerRow = questionsSheet.createRow(0); HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle(); wrapCellStyle.setWrapText(true); wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle); createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle); createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle); createNewCell( headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle); HSSFRow dataRow = null; if (!CoreUtil.isEmpty(questionDTOs)) { int i = 0; for (QuestionDTO questionDTO : questionDTOs) { dataRow = questionsSheet.createRow(i + 1); createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null); createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle); createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle); createNewCell( dataRow, CATEGORIES_CELLNUM, parseCategoryDTOs(questionDTO.getCategoryDTOs()), wrapCellStyle); // Increment row i = i + 1; } } questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM); questionsSheet.autoSizeColumn(QUESTION_CELLNUM); questionsSheet.autoSizeColumn(ANSWER_CELLNUM); questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM); questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle); }
/** * Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件] * * @param sheetName 写入excel的sheet名称 * @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件 * @param map 要导出的字段。(格式:字段名 <-> 字段中文名)其中字段名应该是对应javaBean中的某属性。<br> * 若该字段为公式的话,必须符合一定的格式:公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符(,:)&操作符<br> * 如:SUM&A&C&2&,&- 它返回的公司是:SUM(A3,-C3)。具体参照: <code>com.integral.util.office.ExcelFormula * </code> * @param dateFormat 日期格式(可选),默认:yyyy-MM-dd * @author:[代超] * @throws IOException * @throws Exception * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ @SuppressWarnings("deprecation") public void writExcelFile( String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat) throws Exception { if (dataSet == null || dataSet.size() < 1) { return; } if (map == null || map.size() < 1) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } sheetName = sheetName == null ? "" : sheetName; // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 设置表格的样式 CellStyle headerStyle = setRootSheetSysle(workbook); // 产生表格标题行 HSSFRow row = sheet.createRow(0); // 将第一行冻结 sheet.createFreezePane(1, 1); if (map != null) { Object[] obj = map.values().toArray(); for (int i = 0; i < obj.length; i++) { HSSFCell cell = row.createCell(i); String header = obj[i] == null ? "" : obj[i].toString(); cell.setCellStyle(headerStyle); if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) { // 公式 header = obj[i].toString().replace("formula", ""); } HSSFRichTextString text = new HSSFRichTextString(header); cell.setCellValue(text); } } // 遍历数据集合,产生数据行 Iterator<T> it = dataSet.iterator(); for (int i = 1; it.hasNext(); i++) { row = sheet.createRow(i); T t = it.next(); // BeanMap bm = new BeanMap(t); Map b = PropertyUtils.describe(t); Map c = new TreeMap(); // 排序 c.putAll(b); writeRow(row, c, map, dateFormat, t); } workbook.write(out); }
/** * @see javax.servlet.http.HttpServlet#doGet( javax.servlet.http.HttpServletRequest, * javax.servlet.http.HttpServletResponse) * <p>Method requires 3 parameters: a, b and n. It creates a Microsoft excel file with n * sheets, each sheet containing 2 columns. 1st Column contains numbers from a to b and the * 2nd column consists of the 1st column number's power depending on the sheet number. */ @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { Integer a = null; Integer b = null; Integer n = null; try { a = Integer.valueOf(req.getParameter("a")); } catch (Exception e) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } if (a < -100 || a > 100) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } try { b = Integer.valueOf(req.getParameter("b")); } catch (Exception e) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } if (b < -100 || b > 100) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } try { n = Integer.valueOf(req.getParameter("n")); } catch (Exception e) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } if (n < 1 || n > 5) { req.getRequestDispatcher("/WEB-INF/pages/powerError.jsp").forward(req, resp); } HSSFWorkbook hwb = new HSSFWorkbook(); for (int i = 0; i < n; i++) { HSSFSheet sheet = hwb.createSheet("Sheet " + (i + 1)); HSSFRow rowhead = sheet.createRow(0); rowhead.createCell(0).setCellValue("Value"); rowhead.createCell(1).setCellValue("Power " + (i + 1)); for (int j = a; j <= b; j++) { HSSFRow row = sheet.createRow(j - a + 1); row.createCell(0).setCellValue(j); row.createCell(1).setCellValue(Math.pow(j, i + 1)); } } resp.setContentType("application/vnd.ms-excel; charset=UTF-8"); resp.setHeader("Content-Disposition", "inline"); ServletOutputStream fileOut = resp.getOutputStream(); hwb.write(fileOut); fileOut.close(); }
public static boolean saveAsExcelFile(File file, List<StatementsEx> statementsExs) { final HSSFWorkbook wb = new HSSFWorkbook(); boolean needToWrite = false; for (StatementsEx statementsEx : statementsExs) { final String title = statementsEx.title; final Statements statements = statementsEx.statements; assert (statements != null); if (statements.getType() == Statement.Type.Unknown) { continue; } needToWrite = true; final HSSFSheet sheet = wb.createSheet(title); // Do not obtain "type" through statements, as there is possible that // statements is empty. final List<String> strings = Statement.typeToStrings(statements.getType(), statements.getGUIBundleWrapper()); final int columnCount = strings.size(); // First row. Print out table header. { final HSSFRow row = sheet.createRow(0); for (int i = 0; i < columnCount; i++) { row.createCell(i).setCellValue(new HSSFRichTextString(strings.get(i))); } } final int rowCount = statements.size(); for (int i = 0; i < rowCount; i++) { final HSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < columnCount; j++) { // Value shouldn't be null, as we prevent atom with null value. final Object value = statements.get(i).getAtom(j).getValue(); final HSSFCell cell = row.createCell(j); POIUtils.invokeSetCellValue(cell, value); } } } if (needToWrite == false) { return needToWrite; } boolean status = false; FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(file); wb.write(fileOut); status = true; } catch (FileNotFoundException ex) { log.error(null, ex); } catch (IOException ex) { log.error(null, ex); } finally { org.yccheok.jstock.gui.Utils.close(fileOut); } return status; }
/** * @param excel_name 生成的Excel文件路径+名称 * @param headList Excel文件Head标题集合 * @param field_list Excel文件Field标题集合 * @param dataList Excel文件数据内容部分 * @throws Exception */ public static void createExcel( String excel_name, List<String> headList, List<String> fieldList, List<Map<String, Object>> dataList) throws Exception { // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.size(); i++) { // 在索引0的位置创建单元格(左上端) HSSFCell cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList.get(i)); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行(最顶端的行) HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.size(); i++) { // 在索引0的位置创建单元格(左上端) HSSFCell cell = row_value.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(objToString(dataMap.get(fieldList.get(i)))); } // =============================================================== } // 新建一输出文件流 FileOutputStream fOut = new FileOutputStream(excel_name); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); // System.out.println("[" + excel_name + "]" + "文件生成..."); }
public static void writeDataToExcelFile( final String commaDelimitedHeaderColumns, final String commaDelimitedModelAttributes, final List<?> modelList, final String fileName) { FileOutputStream out = null; try { String[] headerColumns = commaDelimitedHeaderColumns.split(COMMA); String[] modelAttributes = commaDelimitedModelAttributes.split(COMMA); HSSFWorkbook excelWorkBook = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(excelWorkBook); HSSFSheet excelSheet = excelWorkBook.createSheet(); HSSFRow excelHeaderRow; HSSFCell excelHeaderCell; HSSFRow excelRow; HSSFCell excelCell; excelHeaderRow = excelSheet.createRow(0); for (int columnIndex = 0; columnIndex < headerColumns.length; columnIndex++) { excelHeaderCell = excelHeaderRow.createCell(columnIndex); excelHeaderCell.setCellValue(new HSSFRichTextString(headerColumns[columnIndex])); excelHeaderCell.setCellStyle(styles.get("header")); } if (TGPObjectUtil.isDefined(modelList)) { for (int rowIndex = 0; rowIndex < modelList.size(); rowIndex++) { excelRow = excelSheet.createRow(rowIndex + 1); for (int columnIndex = 0; columnIndex < modelAttributes.length; columnIndex++) { excelCell = excelRow.createCell(columnIndex); Object object = modelList.get(rowIndex); Class<?> objectClass = object.getClass(); Field objectField = objectClass.getDeclaredField(modelAttributes[columnIndex]); objectField.setAccessible(true); excelCell.setCellValue(new HSSFRichTextString((String) objectField.get(object))); } } } for (int i = TGPProcurementConstants.TGP_INTEGER_ZERO; i < headerColumns.length; i++) { excelSheet.autoSizeColumn(i); } out = new FileOutputStream(fileName); excelWorkBook.write(out); } catch (Exception e) { LOGGER.error(e.toString()); } finally { IOUtils.closeQuietly(out); } }
/** * 生成文件 * * @throws IOException */ public byte[] run() throws IOException { // 初始化参数 initParameter(); // 实例化decorator initDecorator(); // 初始化 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度 try { wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16); } catch (Exception ex) { logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况"); } baseCellStyle = wb.createCellStyle(); baseCellStyle.setAlignment(align); baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat)); // 创建标题行 HSSFRow row = sheet.createRow((short) 0); // 创建标题 for (int i = 0; i < nameList.size(); i++) { HSSFCell cell = row.createCell((short) i); writeCell(cell, (String) nameList.get(i)); } // 创建数据 for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow((short) i + 1); for (int j = 0; j < fieldList.size(); j++) { HSSFCell cell = row.createCell((short) j); Object value = getValue(dataList.get(i), (String) fieldList.get(j)); writeCell(cell, value); } } // 写入文件 // FileOutputStream fileOut = new FileOutputStream(fileName); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); return out.toByteArray(); // fileOut.close(); }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { /* * if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else { */ HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle ordinStyle = workbook.createCellStyle(); ordinStyle.setBorderRight(CellStyle.BORDER_THIN); ordinStyle.setBorderLeft(CellStyle.BORDER_THIN); HSSFCellStyle lineStyle = workbook.createCellStyle(); lineStyle.setBorderTop(CellStyle.BORDER_THIN); short currentRow = 1; Report report = (Report) model.get(SEARCH_LIST_KEY); if (report != null) { List<ReportAPIUsage> list = report.getRepotAPIUsageList(); HSSFRow row = sheet.getRow(0); row.createCell(0) .setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo()); for (ReportAPIUsage object : list) { currentRow++; row = sheet.createRow(currentRow); HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN); cellBrName.setCellValue(object.getClientKey()); cellBrName.setCellStyle(ordinStyle); HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN); cellAnyPac.setCellValue(object.getClientName()); cellAnyPac.setCellStyle(ordinStyle); HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN); cellSpecPac.setCellValue(object.getCountOfQueries()); cellSpecPac.setCellStyle(ordinStyle); } currentRow++; row = sheet.createRow(currentRow); HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN); cellBrName.setCellStyle(lineStyle); HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN); cellAnyPac.setCellStyle(lineStyle); HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN); cellSpecPac.setCellStyle(lineStyle); } /* } */ }
private HSSFRow createRow(HSSFSheet sheet, int rownum) { HSSFRow newRow = sheet.createRow(rownum); if (rownum == 0) { newRow.setRowStyle(newStyle); } return newRow; }
public Boolean insertRow(int startRow, int rows) { Boolean flag = false; sheet.shiftRows(startRow + 1, sheet.getLastRowNum(), rows, true, false); startRow = startRow - 1; for (int i = 0; i < rows; i++) { HSSFRow sourceRow = null; HSSFRow targetRow = null; HSSFCell sourceCell = null; HSSFCell targetCell = null; short m; startRow = startRow + 1; sourceRow = sheet.getRow(startRow); targetRow = sheet.createRow(startRow + 1); targetRow.setHeight(sourceRow.getHeight()); for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) { sourceCell = sourceRow.getCell(m); targetCell = targetRow.createCell(m); // targetCell.setEncoding(sourceCell.get); targetCell.setCellStyle(sourceCell.getCellStyle()); targetCell.setCellType(sourceCell.getCellType()); } } flag = true; return flag; }
private HSSFRow getRow(int rowIndex) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); } return row; }
@Override public void writeRow(List<?> row) { Row record = sheet.createRow(sheet.getLastRowNum() + 1); for (int i = 0; i < row.size(); i++) { Cell cell = record.createCell(i); Object value = row.get(i); if (value == null) { cell.setCellValue(""); } else if (value instanceof String) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue((String) value); } else if (value instanceof Number) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(converters.convert(value, Double.class)); } else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateCellStyle); cell.setCellValue(converters.convert(value, Date.class)); } else if (value instanceof Boolean) { cell.setCellType(Cell.CELL_TYPE_BOOLEAN); cell.setCellValue((Boolean) value); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(converters.convert(value, String.class)); } } }
/** * 业务数据类型生成 * * @param wb excel工作页 * @param btList 模型对象数组 */ public void BusTypeWriter(HSSFWorkbook wb, List<PDMBusinessDataType> btList) { HSSFSheet sheet = wb.getSheet(BT_SHEET); for (int i = 0; i < btList.size(); i++) { HSSFRow row = sheet.createRow(i + 2); PDMBusinessDataType bt = btList.get(i); // 1 HSSFCell cell = row.createCell(1); cell.setCellValue(bt.getTypeName()); cell.setCellStyle(getTextStyle(wb)); // 2 cell = row.createCell(2); cell.setCellValue(bt.getTypeChineseName()); cell.setCellStyle(getTextStyle(wb)); // 3 cell = row.createCell(3); cell.setCellValue(bt.getStandardTypeName()); cell.setCellStyle(getTextStyle(wb)); // 4 cell = row.createCell(4); cell.setCellValue(bt.getLength()); cell.setCellStyle(getTextStyle(wb)); // 5 cell = row.createCell(5); cell.setCellValue(bt.getPrecision()); cell.setCellStyle(getTextStyle(wb)); // 6 cell = row.createCell(6); cell.setCellValue(bt.getDefaultValue()); cell.setCellStyle(getTextStyle(wb)); // 7 cell = row.createCell(7); cell.setCellValue(bt.getComment()); cell.setCellStyle(getTextStyle(wb)); } }
/** Print header */ private void printHeader(HSSFSheet sheet) { HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(MEASURE); row.createCell(1).setCellValue(TYPE); row.createCell(2).setCellValue(DESCRIPTION); row.createCell(3).setCellValue(VALUE); }
private void addOrderHeader(final HSSFSheet sheet, final Locale locale) { HSSFRow header = sheet.createRow(0); HSSFCell cell0 = header.createCell(0); cell0.setCellValue( translationService.translate( "qualityControls.qualityControl.report.product.number", locale)); xlsHelper.setCellStyle(sheet, cell0); HSSFCell cell1 = header.createCell(1); cell1.setCellValue( translationService.translate( "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.number.label", locale)); xlsHelper.setCellStyle(sheet, cell1); HSSFCell cell2 = header.createCell(2); cell2.setCellValue( translationService.translate( "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.controlledQuantity.label", locale)); xlsHelper.setCellStyle(sheet, cell2); HSSFCell cell3 = header.createCell(3); cell3.setCellValue( translationService.translate( "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.rejectedQuantity.label", locale)); xlsHelper.setCellStyle(sheet, cell3); HSSFCell cell4 = header.createCell(4); cell4.setCellValue( translationService.translate( "qualityControls.qualityControlForUnitDetails.window.mainTab.qualityControlForUnit.acceptedDefectsQuantity.label", locale)); xlsHelper.setCellStyle(sheet, cell4); }
public void body(TableModel model, Column column) { if (column.isFirstColumn()) { rownum++; cellnum = 0; hssfRow = sheet.createRow(rownum); } String value = column.getCellDisplay(); if (StringUtils.isNumeric(value)) { value = column.getValue() == null ? "" : column.getValue().toString(); } value = value.replaceAll("\t", "").replaceAll("\n", ""); value = ExportViewUtils.parseXLS(value); HSSFCell hssfCell = hssfRow.createCell(cellnum); // modify by springside // hssfCell.setEncoding(HSSFCell.ENCODING_UTF_16); // end of modify by springside if (column.isEscapeAutoFormat()) { writeToCellAsText(hssfCell, value); } else { writeToCellFormatted(hssfCell, value); } cellnum++; }
/** * @param * @return void */ private void generateColumn( HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum, HSSFCellStyle headerstyle) { HSSFRow row = sheet.getRow(rownum); if (row == null) row = sheet.createRow(rownum); HSSFCell cell = row.createCell(colnum); cell.setCellValue(tc.getDisplay()); if (headerstyle != null) cell.setCellStyle(headerstyle); if (tc.isComplex()) { CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); int cn = colnum; for (int i = 0; i < tc.getChildren().size(); i++) { if (i != 0) { cn = cn + tc.getChildren().get(i - 1).getLength(); } generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle); } } else { CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); } sheet.autoSizeColumn(colnum, true); }
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle) throws ContainerException { RowSet searchResult = executeFrm.getSearchResult(); if (searchResult != null && !searchResult.isEmpty()) { List<ReportViewFrm> definedViews = executeFrm.getDefinedViews(); int fieldCount = definedViews.size(); int dataCount = searchResult.getSize(); int xlsRowNum = 0; for (int i = 0; i < dataCount; i++) { Row row = searchResult.getRow(i); xlsRowNum = i; if (isFirstFile) { xlsRowNum++; } HSSFRow xlsRow = sheet.createRow(xlsRowNum); xlsRow.setHeightInPoints(18); for (short j = 0; j < fieldCount; j++) { ReportViewFrm viewFrm = definedViews.get(j); String fieldName = viewFrm.getFieldName(); HSSFCell xlsCell = xlsRow.createCell(j); xlsCell.setCellStyle(dataStyle); HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName)); xlsCell.setCellValue(richText); } } } }
private void writeHeaderRow(Entity entity, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); int colnum = 0; for (Map.Entry<String, Object> component : getComponents(entity)) { String componentName = component.getKey(); headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName)); ComponentDescriptor cd = entity.descriptor().getComponent(componentName); PrimitiveType primitiveType; if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor) primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType(); else throw new UnsupportedOperationException( "Can only export simple type attributes, " + "failed to export " + entity.type() + '.' + cd.getName()); Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class); String formatString = null; if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern(); else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern(); else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern(); else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern(); else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern(); if (formatString != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(formatString)); sheet.setDefaultColumnStyle(colnum, columnStyle); } colnum++; } }
public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("planilha um"); for (int i = 0; i < qtdLinhas; i++) { HSSFRow row = sheet1.createRow(i); HSSFCellStyle style = wb.createCellStyle(); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY); for (int j = 0; j < qtdColunas; j++) { HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j); HSSFCell cell = row.createCell(j); cell.setCellStyle(style); cell.setCellValue(strValor); } } FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls"); wb.write(stream); }