Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
The following examples show how to use
org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: TestExportExcel.java From poi with Apache License 2.0 | 6 votes |
@Test public void exportExcelWithStyle() { try { String filePath = TestUtil.DOC_PATH + File.separator + Globals.EXPORT_PRODUCT; OutputStream os = new FileOutputStream(filePath); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(Globals.SHEETNAME); HSSFRichTextString richString = new HSSFRichTextString( TestUtil.RICH_TEXT_STRING); HSSFFont font = wb.createFont(); font.setColor(IndexedColors.BLUE.index); richString.applyFont(font); sheet.createRow(0).createCell(0).setCellValue(richString); wb.write(os); os.close(); } catch (Exception e) { e.printStackTrace(); } }
Example 2
Source File: Excel97Producer.java From ureport with Apache License 2.0 | 6 votes |
private Sheet createSheet(HSSFWorkbook wb,Paper paper,String name){ Sheet sheet = null; if(name==null){ sheet=wb.createSheet(); }else{ sheet=wb.createSheet(name); } PaperType paperType=paper.getPaperType(); HSSFPrintSetup printSetup=(HSSFPrintSetup)sheet.getPrintSetup(); Orientation orientation=paper.getOrientation(); if(orientation.equals(Orientation.landscape)){ printSetup.setLandscape(true); } setupPaper(paperType, printSetup); int leftMargin=paper.getLeftMargin(); int rightMargin=paper.getRightMargin(); int topMargin=paper.getTopMargin(); int bottomMargin=paper.getBottomMargin(); sheet.setMargin(Sheet.LeftMargin, UnitUtils.pointToInche(leftMargin)); sheet.setMargin(Sheet.RightMargin, UnitUtils.pointToInche(rightMargin)); sheet.setMargin(Sheet.TopMargin, UnitUtils.pointToInche(topMargin)); sheet.setMargin(Sheet.BottomMargin, UnitUtils.pointToInche(bottomMargin)); return sheet; }
Example 3
Source File: ExportUtil.java From jumbune with GNU Lesser General Public License v3.0 | 6 votes |
/** * Adds a sheet in the Workbook * @param worksheet the worksheet * @param sheetName name of sheet */ public static void addSheet(Worksheet worksheet, String sheetName) { HSSFWorkbook workbook = null; HSSFSheet sheet= null; String classSymbol = null; workbook = worksheet.getWorkbook(); if (workbook.getSheet(sheetName) != null) { return; } sheet = workbook.createSheet(sheetName); Map<String, HSSFSheet> sheets = worksheet.getSheets(); Map<String, String> sheetSymbol = worksheet.getSheetSymbol(); classSymbol = sheetSymbol.get(sheetName); if ( classSymbol != null) { sheets.put(classSymbol, sheet); } else { sheets.put(sheetName, sheet); } }
Example 4
Source File: BoundaryPointXlsParserTest.java From powsybl-core with Mozilla Public License 2.0 | 5 votes |
private HSSFWorkbook createWorkbook() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // Add dummy lines sheet.createRow(0).createCell(0).setCellValue("First dummy row"); sheet.createRow(1).createCell(0).setCellValue("Second dummy row"); HSSFRow row = sheet.createRow(2); row.createCell(13).setCellValue("BoundaryPoint FR-BE"); row.createCell(14).setCellValue("France"); row.createCell(15).setCellValue("Belgium"); return workbook; }
Example 5
Source File: ExportUtils.java From tech-gallery with Apache License 2.0 | 5 votes |
private static Workbook createUsersProfileWorkbook(List<UserProfile> usersProfile) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(workbook.getSheetIndex(sheet), SHEET_NAME); int rownum = 0; Row headerRow = sheet.createRow(rownum++); for (int rn = 0; rn < SHEET_HEADERS.length; rn++) { headerRow.createCell(rn).setCellValue(SHEET_HEADERS[rn]); } makeRowBold(workbook, headerRow); for (UserProfile userProfile : usersProfile) { Ref<TechGalleryUser> techGalleryUserRef = userProfile.getOwner(); TechGalleryUser techGalleryUser = techGalleryUserRef.get(); if (techGalleryUser != null) { for (UserProfileItem tec : userProfile.getAllItems()) { if (tec.getSkillLevel() > 0 || tec.getEndorsementQuantity() > 0) { Row row = sheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue(createLoginByEmail(techGalleryUser.getEmail())); row.createCell(cellnum++).setCellValue(techGalleryUser.getName()); row.createCell(cellnum++).setCellValue(tec.getTechnologyName()); row.createCell(cellnum++).setCellValue(tec.getEndorsementQuantity()); row.createCell(cellnum++).setCellValue(tec.getSkillLevel()); } } } } return workbook; }
Example 6
Source File: EventWorkbookBuilder.java From lams with GNU General Public License v2.0 | 5 votes |
public HSSFWorkbook getStubHSSFWorkbook() { // Create a base workbook HSSFWorkbook wb = HSSFWorkbook.create(getStubWorkbook()); // Stub the sheets, so sheet name lookups work for (BoundSheetRecord bsr : boundSheetRecords) { wb.createSheet(bsr.getSheetname()); } // Ready for Formula use! return wb; }
Example 7
Source File: ExcelFileUtils.java From SWET with MIT License | 5 votes |
public static void writeXLSFile() throws Exception { HSSFWorkbook hddfwb = new HSSFWorkbook(); HSSFSheet sheet = hddfwb.createSheet(sheetName); for (int row = 0; row < tableData.size(); row++) { HSSFRow hssfrow = sheet.createRow(row); rowData = tableData.get(row); for (int col = 0; col < rowData.size(); col++) { HSSFCell hssfcell = hssfrow.createCell(col); hssfcell.setCellValue(rowData.get(col)); } } try (OutputStream fileOutputStream = new FileOutputStream( excelFileName)) { hddfwb.write(fileOutputStream); hddfwb.close(); fileOutputStream.flush(); fileOutputStream.close(); } catch (IOException e) { String message = String.format("Exception saving XLS file %s\n", excelFileName) + e.getMessage(); logger.info(message); // NOTE: throw exceptions with user friendly messages to be rendered // by the master app throw new Exception(message); } }
Example 8
Source File: JU_Excel2003Color.java From hy.common.report with Apache License 2.0 | 5 votes |
@Test public void test_001() { HSSFWorkbook v_Workbook = new HSSFWorkbook(); HSSFSheet v_Sheet = v_Workbook.createSheet("测试单元格颜色"); v_Sheet.setColumnWidth(0 ,2560); for (int v_RowIndex=0; v_RowIndex<4000; v_RowIndex++) { HSSFRow v_Row = v_Sheet.createRow(v_RowIndex); for (int v_ColIndex=0; v_ColIndex<1; v_ColIndex++) { HSSFCell v_Cell = v_Row.createCell(v_ColIndex); HSSFCellStyle v_CellStyle = v_Workbook.createCellStyle(); v_CellStyle.setFillForegroundColor((short)(v_RowIndex + 1)); v_CellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); v_Cell.setCellStyle(v_CellStyle); v_Cell.setCellValue("" + (v_RowIndex + 1)); } } ExcelHelp.save(v_Workbook ,"/Users/hy/Downloads/测试2003版本的单元格颜色"); }
Example 9
Source File: ExcelExportUtil.java From jeewx with Apache License 2.0 | 5 votes |
public static void createSheetInUserModel2File(HSSFWorkbook workbook, ExcelTitle entity, Class<?> pojoClass, Collection<?> dataSet) { try { Sheet sheet = workbook.createSheet(entity.getSheetName()); //创建表格属性 Map<String,HSSFCellStyle> styles = createStyles(workbook); Drawing patriarch = sheet.createDrawingPatriarch(); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); // 得到所有字段 Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.id(); } getAllExcelField(targetId, fileds, excelParams, pojoClass, null); sortAllParams(excelParams); int index = 0; int feildWidth = getFieldWidth(excelParams); if (entity.getTitle() != null) { int i = createHeaderRow(entity, sheet, workbook, feildWidth); sheet.createFreezePane(0, 2+i, 0, 2+i); index += i; } else { sheet.createFreezePane(0, 2, 0, 2); } createTitleRow(entity,sheet, workbook, index, excelParams); index += 2; setCellWith(excelParams, sheet); Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch,index, t, excelParams, sheet, workbook ,styles); } } catch (Exception e) { e.printStackTrace(); } }
Example 10
Source File: HRExcelBuilder.java From Spring-MVC-Blueprints with MIT License | 4 votes |
@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 @SuppressWarnings("unchecked") List<HrmsLogin> users = (List<HrmsLogin>) model.get("allUsers"); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("User List"); sheet.setDefaultColumnWidth(30); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Employee ID"); header.getCell(0).setCellStyle(style); header.createCell(1).setCellValue("Username"); header.getCell(1).setCellStyle(style); header.createCell(2).setCellValue("Password"); header.getCell(2).setCellStyle(style); header.createCell(3).setCellValue("Role"); header.getCell(3).setCellStyle(style); // create data rows int rowCount = 1; for (HrmsLogin account : users) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(account.getHrmsEmployeeDetails().getEmpId()); aRow.createCell(1).setCellValue(account.getUsername()); aRow.createCell(2).setCellValue(account.getPassword()); aRow.createCell(3).setCellValue(account.getRole()); } }
Example 11
Source File: XLSPrinter.java From unitime with Apache License 2.0 | 4 votes |
public XLSPrinter(OutputStream output, boolean checkLast) { iOutput = output; iCheckLast = checkLast; iWorkbook = new HSSFWorkbook(); iSheet = iWorkbook.createSheet(); iSheet.setDisplayGridlines(false); iSheet.setPrintGridlines(false); iSheet.setFitToPage(true); iSheet.setHorizontallyCenter(true); PrintSetup printSetup = iSheet.getPrintSetup(); printSetup.setLandscape(true); iSheet.setAutobreaks(true); printSetup.setFitHeight((short)1); printSetup.setFitWidth((short)1); iStyles = new HashMap<String, CellStyle>(); CellStyle style; style = iWorkbook.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(true, false, false, Color.BLACK)); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setWrapText(true); iStyles.put("header", style); style = iWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(false, false, false, Color.BLACK)); style.setWrapText(true); iStyles.put("plain", style); style = iWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFont(getFont(false, false, false, Color.BLACK)); iStyles.put("number", style); }
Example 12
Source File: SpreadsheetExporter.java From sakai with Educational Community License v2.0 | 4 votes |
ExcelExporter(String title, String gradeType) { gradesWorkbook = new HSSFWorkbook(); dataSheet = gradesWorkbook.createSheet(Validator.escapeZipEntry(title)); }
Example 13
Source File: ExcelUtil.java From util with Apache License 2.0 | 4 votes |
/** * 根据条件,生成工作薄对象到内存。 * @param sheetName 工作表对象名称 * @param fieldName 首列列名称 * @param data 数据 * @return HSSFWorkbook */ private HSSFWorkbook makeWorkBook(String sheetName,String[] fieldName , List<Object[]> data){ //用来记录最大列宽,自动调整列宽。 Integer collength[]=new Integer[fieldName.length]; // 产生工作薄对象 HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作表对象 HSSFSheet sheet = workbook.createSheet(); // 为了工作表能支持中文,设置字符集为UTF_16 workbook.setSheetName(0, sheetName); // 产生一行 HSSFRow row = sheet.createRow(0); // 产生单元格 HSSFCell cell; // 写入各个字段的名称 for (int i = 0; i < fieldName.length; i++) { // 创建第一行各个字段名称的单元格 cell = row.createCell((short) i); // 设置单元格内容为字符串型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 为了能在单元格中输入中文,设置字符集为UTF_16 // cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 给单元格内容赋值 cell.setCellValue(new HSSFRichTextString(fieldName[i])); //初始化列宽 collength[i]=fieldName[i].getBytes().length; } //临时单元格内容 String tempCellContent=""; // 写入各条记录,每条记录对应excel表中的一行 for (int i = 0; i < data.size(); i++) { Object[] tmp = data.get(i); // 生成一行 row = sheet.createRow(i + 1); for (int j = 0; j < tmp.length; j++) { cell = row.createCell((short) j); //设置单元格字符类型为String cell.setCellType(HSSFCell.CELL_TYPE_STRING); tempCellContent=(tmp[j] == null) ? "" : tmp[j].toString(); cell.setCellValue(new HSSFRichTextString(tempCellContent)); //如果自动调整列宽度。 if(autoColumnWidth){ if(j>=collength.length){ // 标题列数小于数据列数时。 collength=CollectionUtil.addObjectToArray(collength, tempCellContent.getBytes().length); }else{ //如果这个内容的宽度大于之前最大的,就按照这个设置宽度。 if(collength[j]<tempCellContent.getBytes().length){ collength[j]=tempCellContent.getBytes().length; } } } } } //自动调整列宽度。 if(autoColumnWidth){ //调整列为这列文字对应的最大宽度。 for(int i=0 ; i< fieldName.length ; i++){ sheet.setColumnWidth(i,collength[i]*2*256); } } return workbook; }
Example 14
Source File: ExcelUtils.java From job with MIT License | 4 votes |
public static void outputResumeProcessResult(Map<File, Resume> map, File outFile) throws IOException { //工作簿 HSSFWorkbook excel = new HSSFWorkbook(); FileOutputStream fileoutputstream = new FileOutputStream(outFile); try { HSSFSheet sheet = excel.createSheet("简历处理结果"); // 写表头 HSSFRow headerRow = sheet.createRow(0); for(int i = 0; i < HEADERS.length; i++) { headerRow.createCell(i).setCellValue(HEADERS[i]); } // 写内容 int rowNum = 1; for(Map.Entry<File, Resume> entry : map.entrySet()) { Resume resume = entry.getValue(); HSSFRow row = sheet.createRow(rowNum); row.createCell(0).setCellValue(rowNum); // 序号 row.createCell(1).setCellValue(resume.getName()); // 姓名 row.createCell(2).setCellValue(resume.getJob()); // 应聘职位 row.createCell(3).setCellValue(resume.getSource()); // 简历来源 row.createCell(4).setCellValue(resume.getSchool()); // 毕业院校 row.createCell(5).setCellValue(resume.getEducation()); // 学历 row.createCell(6).setCellValue(resume.getAge()); // 年龄 row.createCell(7).setCellValue(resume.getWorkDuration()); // 工作经验 row.createCell(8).setCellValue(resume.getCompany()); // 现工作单位 row.createCell(9).setCellValue(resume.getPhone()); // 电话 row.createCell(10).setCellValue(resume.getMail()); // 邮箱 row.createCell(11).setCellValue(resume.getSex()); // 性别 row.createCell(12).setCellValue(resume.getBirthday()); // 生日 row.createCell(13).setCellValue(resume.getCity()); // 居住城市 row.createCell(14).setCellValue(resume.getAddress()); // 地址 rowNum++; } excel.write(fileoutputstream); } finally { fileoutputstream.close(); excel.close(); } }
Example 15
Source File: ExcelUtil.java From phone with Apache License 2.0 | 4 votes |
/** * 将数据表对象转换为workbook * @param key 配置文件key * @param sheetName sheet页名称,不填默认为自动导出 * @param ts 数据集合 * @param rowNumber 开始行 * @return HSSFWorkbook */ public static <T> HSSFWorkbook dataToWorkbook(String propertiesPath,String key,String sheetName,List<T> ts,int rowNumber){ logger.info("dataToWorkbook#key="+key); HSSFWorkbook workbook = new HSSFWorkbook(); if (StringUtils.isNotEmpty(key)) { //将写入excel的数据不能为空 if (ts!=null&&!ts.isEmpty()) { //读取properties配置文件 PropertiesModel properties = PropertiesCacheUtil.loadProperties(propertiesPath); if (properties!=null) { //从properties中获取导出对应的配置json String columnJsonString = properties.get(key); try { //创建一个sheet sheetName=(sheetName==null?"自动导出":sheetName); HSSFSheet sheet = workbook.createSheet(sheetName); //定义sheet的行码,并保存列信息 //List<String> columnList = new ArrayList<String>(); JSONObject columnJson = JSONObject.parseObject(columnJsonString); //写入标题 writeSheetHead(sheet, columnJson, rowNumber++); //写入内容 rowNumber = writeSheetBody(sheet, columnJson, rowNumber,ts); autoSheetWidth(sheet, columnJson); } catch (Exception e) { logger.error("dataToWorkbook#parse json error",e); } }else{ logger.warn("dataToWorkbook#properties is null"); } }else{ logger.warn("dataToWorkbook#ts is null"); } }else{ logger.warn("dataToWorkbook#key is null"); } logger.info("dataToWorkbook#end."); return workbook; }
Example 16
Source File: SpreadsheetExporter.java From sakai with Educational Community License v2.0 | 4 votes |
ExcelExporter(String title, String gradeType) { gradesWorkbook = new HSSFWorkbook(); dataSheet = gradesWorkbook.createSheet(Validator.escapeZipEntry(title)); }
Example 17
Source File: XlsWriterImpl.java From ganttproject with GNU General Public License v3.0 | 4 votes |
XlsWriterImpl(OutputStream stream) { myStream = Preconditions.checkNotNull(stream); myWorkbook = new HSSFWorkbook(); mySheet = myWorkbook.createSheet(); }
Example 18
Source File: AgentManageServiceImpl.java From DrivingAgency with MIT License | 4 votes |
private String derivedStudentInfo(List<StudentVo> studentVos){ String agentEmail = SecurityContextHolder.getAgent().getAgentEmail(); if (StringUtils.isBlank(agentEmail)){ throw new ParamException("你的邮箱为空,不能导出Excel数据"); } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("获取学员信息Excel表格"); HSSFRow row = null; row = sheet.createRow(0); row.setHeight((short) (26.25 * 20)); row.createCell(0).setCellValue("学员信息列表"); CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(rowRegion); row = sheet.createRow(1); row.setHeight((short) (22.50 * 20));//设置行高 row.createCell(0).setCellValue("学员Id");//为第一个单元格设值 row.createCell(1).setCellValue("学院学号");//为第一个单元格设值 row.createCell(2).setCellValue("学员姓名");//为第二个单元格设值 row.createCell(3).setCellValue("学员手机号");//为第三个单元格设值 row.createCell(4).setCellValue("学员身份证照片地址");//为第四个单元格设值 row.createCell(5).setCellValue("学员学费");//为第五个单元格设值 row.createCell(6).setCellValue("学员学校");//为第六个单元格设值 row.createCell(7).setCellValue("学员添加者");//为第七个单元格设值 row.createCell(8).setCellValue("学员");//为第八个单元格设值 for (int i = 0; i < studentVos.size(); i++) { row = sheet.createRow(i + 2); StudentVo studentVo = studentVos.get(i); row.createCell(0).setCellValue(studentVo.getId()); row.createCell(1).setCellValue(studentVo.getStudentId()); row.createCell(2).setCellValue(studentVo.getStudentName()); row.createCell(3).setCellValue(studentVo.getStudentPhone()); row.createCell(4).setCellValue(studentVo.getStudentImg()); if (studentVo.getStudentPrice()==null){ row.createCell(5).setCellValue(0); }else{ row.createCell(5).setCellValue(studentVo.getStudentPrice().doubleValue()); } row.createCell(6).setCellValue(studentVo.getStudentSchool()); row.createCell(7).setCellValue(studentVo.getOperator()); row.createCell(8).setCellValue(DateTimeUtil.dateToStr(studentVo.getUpdateTime())); } sheet.setDefaultRowHeight((short) (16.5 * 20)); for (int i = 0; i <= 13; i++) { sheet.autoSizeColumn(i); } try { String folder=System.getProperty("java.io.tmpdir"); File file=new File(folder,UUID.randomUUID().toString()+".xls"); if (!file.exists()){ file.createNewFile(); } wb.write(file); mailSenderUtil.sendAttachmentMail(agentEmail,"【驾校全部学员数据】", "驾校代理小程序中的全部学员数据,Excel在附件中",file); return "Excel导出成功"; } catch (IOException e) { e.printStackTrace(); } return null; }
Example 19
Source File: ReportStudentsUTLCandidates.java From fenixedu-academic with GNU Lesser General Public License v3.0 | 3 votes |
public HSSFWorkbook generateReport() { HSSFWorkbook wb = new HSSFWorkbook(); headerStyle = headerBackgroundStyle(wb); HSSFSheet sheet = wb.createSheet("Dados Academicos"); addHeaders(sheet); addValues(sheet); return wb; }
Example 20
Source File: ReportStudentsUTLCandidates.java From fenixedu-academic with GNU Lesser General Public License v3.0 | 3 votes |
public HSSFWorkbook generateErrors() { HSSFWorkbook wb = new HSSFWorkbook(); headerStyle = headerBackgroundStyle(wb); HSSFSheet sheet = wb.createSheet("Errors"); addHeadersForErrors(sheet); addValuesForErrors(sheet); return wb; }