List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel_Multiple(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null;/* w ww . j a v a 2 s . c o m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("OVCC ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled OVCC plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } else { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } else { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.idega.block.datareport.business.SimpleReportBusinessBean.java
License:Open Source License
public void writeSimpleExcelFile(JRDataSource reportData, String nameOfReport, String filePathAndName, ReportDescription description) throws IOException { if (nameOfReport == null || "".equals(nameOfReport)) { nameOfReport = NAME_OF_REPORT;/* w ww. ja v a2s .c o m*/ } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(TextSoap.encodeToValidExcelSheetName(nameOfReport)); int rowIndex = 0; //-- Report Name --// // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow((short) rowIndex++); // Create a cell and put a value in it. HSSFCell cell = row.createCell((short) 0); // Create a new font and alter it. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(REPORT_FONT); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Fonts are set into a style so create a new one to use. HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. cell.setCellValue(nameOfReport); cell.setCellStyle(style); //-- Report Parameters --// rowIndex++; HSSFRow row1 = null; String parameterString = ""; List labels = description.getListOfHeaderParameterLabelKeys(); List parameters = description.getListOfHeaderParameterKeys(); Iterator labelIter = labels.iterator(); Iterator parameterIter = parameters.iterator(); boolean newLineForeEachParameter = description.doCreateNewLineForEachParameter(); while (labelIter.hasNext() && parameterIter.hasNext()) { String label = description.getParameterOrLabelName((String) labelIter.next()); String parameter = description.getParameterOrLabelName((String) parameterIter.next()); if (newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(label + " " + parameter); } else { parameterString += label + " " + parameter + " "; } } if (!newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(parameterString); } rowIndex++; //-- Report ColumnHeader --// List fields = description.getListOfFields(); HSSFRow headerRow = sheet.createRow((short) rowIndex++); HSSFCellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setWrapText(true); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFFont headerCellFont = wb.createFont(); //headerCellFont.setFontHeightInPoints((short)12); headerCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(headerCellFont); int colIndex = 0; int columnWithUnit = 256; // the unit is 1/256 of a character int numberOfCharactersPerLineInLongTextFields = 60; int numberOfCharactersPerLineInRatherLongTextFields = 35; int numberOfCharactersPerLineInUndifinedTextFields = 20; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell headerCell = headerRow.createCell((short) colIndex); headerCell.setCellValue(description.getColumnName(field)); headerCell.setCellStyle(headerCellStyle); //column width int fieldsMaxChar = field.getMaxNumberOfCharacters(); int colWith = numberOfCharactersPerLineInRatherLongTextFields * columnWithUnit; //default, can be rather long text if (fieldsMaxChar > 0 && fieldsMaxChar < numberOfCharactersPerLineInRatherLongTextFields) { colWith = (fieldsMaxChar + 1) * columnWithUnit; // short fields } else if (fieldsMaxChar > 500) { // when the field is set to be able to contain very long text colWith = numberOfCharactersPerLineInLongTextFields * columnWithUnit; //can be very long text } else if (fieldsMaxChar < 0) { colWith = numberOfCharactersPerLineInUndifinedTextFields * columnWithUnit; } sheet.setColumnWidth((short) colIndex, (short) colWith); } //-- Report ColumnDetail --// try { HSSFCellStyle dataCellStyle = wb.createCellStyle(); dataCellStyle.setWrapText(true); dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); sheet.createFreezePane(0, rowIndex); while (reportData.next()) { HSSFRow dataRow = sheet.createRow((short) rowIndex++); colIndex = 0; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell dataCell = dataRow.createCell((short) colIndex); Object fieldValue = reportData.getFieldValue(field); if (fieldValue != null) { dataCell.setCellValue(String.valueOf(fieldValue)); } dataCell.setCellStyle(dataCellStyle); } } } catch (JRException e) { //-- Exception fetching data --// HSSFRow exceptionRow = sheet.createRow((short) rowIndex++); HSSFCell exceptionCell = exceptionRow.createCell((short) 0); // Create a new font and alter it. HSSFFont exceptionFont = wb.createFont(); exceptionFont.setFontName(REPORT_FONT); exceptionFont.setItalic(true); // Fonts are set into a style so create a new one to use. HSSFCellStyle exceptionStyle = wb.createCellStyle(); exceptionStyle.setFont(exceptionFont); // Create a cell and put a value in it. exceptionCell.setCellValue("Error occurred while getting data. Check log for more details."); exceptionCell.setCellStyle(exceptionStyle); e.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(filePathAndName); wb.write(fileOut); fileOut.close(); }
From source file:com.insoul.ti.controller.ContestProjectEntryController.java
@RequestMapping("/download") public ModelAndView download(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook();// Excel HSSFSheet sheet = workbook.createSheet();// ExcelSheet sheet.createFreezePane(1, 2);// // //from www.j a v a 2 s . c o m sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet? // HSSFCellStyle sheetStyle = workbook.createCellStyle(); // // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // ? // for (int i = 0; i <= 14; i++) { // sheet.setDefaultColumnStyle((short) i, sheetStyle); // } // HSSFFont headfont = workbook.createFont(); headfont.setFontName(""); headfont.setFontHeightInPoints((short) 22);// ? headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// // ?? HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// headstyle.setLocked(true); headstyle.setWrapText(true);// ? // ?? HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName(""); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ? HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// columnHeadStyle.setBorderLeft((short) 1);// ? columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ? columnHeadStyle.setBorderRight((short) 1);// ? columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? // ???? columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?? HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? style.setBottomBorderColor(HSSFColor.BLACK.index); // ? style.setFillForegroundColor(HSSFColor.WHITE.index);// ? // ?? HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ? try { // HSSFRow row0 = sheet.createRow(0); // row0.setHeight((short) 900); // HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("???")); cell0.setCellStyle(headstyle); /** * ?? ??0 ??0 ??0 ??0 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(range); // HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 750); HSSFCell cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(1); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(2); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(3); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(4); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(5); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(6); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(7); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(8); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(9); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(10); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(11); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(12); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(13); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(14); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); List<ContestEntry> projectList = contestEntryDAO.findAll(); int m = 2; int len = projectList.size(); for (int i = 0; i < len; i++) { ContestEntry c = projectList.get(i); HSSFRow row = sheet.createRow(m + i); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(c.getId() + "")); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(c.getLocation())); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(c.getInstance())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(c.getIndustry())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(c.getLegalFormation())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(c.getRegtime())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + "")); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(c.getLegalPerson())); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(new HSSFRichTextString(c.getUserCategory())); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(new HSSFRichTextString(c.getContact())); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(new HSSFRichTextString(c.getIdNumber())); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(new HSSFRichTextString(c.getBankName())); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue(new HSSFRichTextString(c.getBankUserName())); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue(new HSSFRichTextString(c.getBankAccount())); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue(new HSSFRichTextString(c.getSupportMoney())); cell.setCellStyle(style); } String filename = System.nanoTime() + ".xls";// Excel?? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { log.error("download excel Error.", e); } return null; }
From source file:com.jk.framework.util.ExcelUtil.java
License:Apache License
/** *///from ww w .j a v a 2 s. com protected void createColumnHeaders() { final HSSFRow headersRow = this.sheet.createRow(0); final HSSFFont font = this.workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); final HSSFCellStyle style = this.workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); int counter = 1; for (int i = 0; i < this.model.getColumnCount(); i++) { final HSSFCell cell = headersRow.createCell(counter++); // cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(this.model.getColumnName(i)); cell.setCellStyle(style); } }
From source file:com.joeyturczak.jtscanner.utils.CreateExcelSpreadsheet.java
License:Apache License
public CreateExcelSpreadsheet(List<List> rowLists) { mDate = Utility.getTodayDateString(); mFileName = Utility.getTodayDateAndTimeString(); mWorkbook = new HSSFWorkbook(); mSheet = mWorkbook.createSheet(mDate); HSSFCellStyle dateRowStyle = mWorkbook.createCellStyle(); HSSFCellStyle headerRowStyle = mWorkbook.createCellStyle(); HSSFCellStyle defaultStyle = mWorkbook.createCellStyle(); HSSFCellStyle defaultAltStyle = mWorkbook.createCellStyle(); HSSFFont headerFont = mWorkbook.createFont(); HSSFFont defaultFont = mWorkbook.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 14); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); defaultFont.setFontName("Arial"); defaultFont.setFontHeightInPoints((short) 14); dateRowStyle.setFont(headerFont); headerRowStyle.setFont(headerFont);// ww w. j av a 2 s . c o m defaultStyle.setFont(defaultFont); defaultAltStyle.setFont(defaultFont); dateRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); dateRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); dateRowStyle.setAlignment(CellStyle.ALIGN_CENTER); headerRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); headerRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerRowStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultAltStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); defaultAltStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); defaultAltStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setAlignment(CellStyle.ALIGN_CENTER); try { Row dateRow = mSheet.createRow(0); dateRow.setHeight((short) 500); Cell dateCell = dateRow.createCell(0); dateCell.setCellValue(mDate); dateCell.setCellStyle(dateRowStyle); mSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); for (int colIndex = 0; colIndex < rowLists.size(); colIndex++) { Row row = mSheet.createRow(mRowNum); mSheet.setColumnWidth(0, 1200); List<String> nextRowList = rowLists.get(colIndex); for (int rowIndex = 0; rowIndex < nextRowList.size(); rowIndex++) { Cell cell = row.createCell(rowIndex); cell.setCellValue(nextRowList.get(rowIndex)); row.setHeight((short) 400); cell.setCellStyle(defaultStyle); if (colIndex % 2 == 0) { cell.setCellStyle(defaultAltStyle); } if (colIndex == 0) { row.setHeight((short) 500); cell.setCellStyle(headerRowStyle); } if (rowIndex > 0) { mSheet.setColumnWidth(rowIndex, 4400); } } mRowNum++; } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.kahlon.guard.controller.DocumentManager.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from www . ja v a 2s. c om HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:com.kcs.action.ForwardContractAction.java
@Override @SuppressWarnings("empty-statement") public String export() throws Exception { list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate())); getLogger().debug("exportExcel : begin..."); setFileName("Forward contract Report.xls"); DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFCellStyle style = myWorkBook.createCellStyle(); HSSFCellStyle styleFont = myWorkBook.createCellStyle(); HSSFFont fontB = myWorkBook.createFont(); fontB.setBoldweight(Font.BOLDWEIGHT_BOLD); styleFont.setFont(fontB); list = sortSheet(list);//from w w w. jav a2s .c o m List<String> listSheet = countSheet(list); for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) { List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet)); HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet)); List<String> currencyList = getCurrency(sheetObject); //------------------------- ROW 1 -----------------------------// Row FW1_row_0 = FW1.createRow(0); FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT"); CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER); HSSFCellStyle styleRow1 = myWorkBook.createCellStyle(); styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow1.setFont(fontB); FW1_row_0.getCell(0).setCellStyle(styleRow1); FW1_row_0.createCell(1) .setCellValue(" ? ?()"); FW1_row_0.getCell(1).setCellStyle(styleRow1); // FW1_row_0.getCell(8).setCellStyle(styleFont); //------------------------- ROW 2 -----------------------------// Row FW1_row_1 = FW1.createRow(1); FW1_row_1.createCell(0).setCellValue("FW"); // FW1_row_1.getCell(0).setCellStyle(styleBR); // FW1.addMergedRegion(new CellRangeAddress(1,1,7,8)); FW1.autoSizeColumn(0); FW1.autoSizeColumn(7); FW1.autoSizeColumn(8); Row FW1_row_2 = FW1.createRow(2); FW1_row_2.createCell(0).setCellValue("?"); //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT); HSSFCellStyle style2 = myWorkBook.createCellStyle(); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); FW1_row_2.getCell(0).setCellStyle(style2); Row FW1_row_3 = FW1.createRow(3); FW1_row_3.createCell(0).setCellValue("?"); HSSFCellStyle style3 = myWorkBook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_3.getCell(0).setCellStyle(style3); FW1.createRow(4).createCell(0).setCellValue("?"); FW1.createRow(5).createCell(0).setCellValue("? (129027)"); FW1.createRow(6).createCell(0).setCellValue( "???"); FW1.createRow(7).createCell(0).setCellValue(" ?"); FW1.createRow(8).createCell(0).setCellValue(" - ?? (129030)"); FW1.createRow(9).createCell(0) .setCellValue(" - ??? (129031)"); FW1.createRow(10).createCell(0).setCellValue(" ??"); FW1.createRow(11).createCell(0).setCellValue(" "); FW1.createRow(12).createCell(0).setCellValue(" - ?? (129034)"); FW1.createRow(13).createCell(0) .setCellValue(" - ??? (129035)"); FW1.createRow(14).createCell(0).setCellValue(" ???"); FW1.createRow(15).createCell(0).setCellValue(" - ?? (129037)"); FW1.createRow(16).createCell(0) .setCellValue(" - ??? (129038) "); FW1.createRow(17).createCell(0) .setCellValue(" ?"); FW1.createRow(18).createCell(0).setCellValue(" - ?? (129040)"); FW1.createRow(19).createCell(0) .setCellValue(" - ??? (129041) "); FW1.createRow(20).createCell(0) .setCellValue(" ??"); FW1.createRow(21).createCell(0).setCellValue(" - ?? (129043)"); FW1.createRow(22).createCell(0) .setCellValue(" - ??? (129044)"); FW1.createRow(23).createCell(0) .setCellValue(" ?"); FW1.createRow(24).createCell(0).setCellValue(" - ?? (129046)"); FW1.createRow(25).createCell(0) .setCellValue(" - ??? (129047) "); FW1.createRow(26).createCell(0).setCellValue("????"); FW1.createRow(27).createCell(0).setCellValue(" ??"); FW1.createRow(28).createCell(0).setCellValue(" - ?? (129050)"); FW1.createRow(29).createCell(0) .setCellValue(" - ??? (129051)"); FW1.createRow(30).createCell(0) .setCellValue(" "); FW1.createRow(31).createCell(0).setCellValue(" - ?? (129053)"); FW1.createRow(32).createCell(0) .setCellValue(" - ??? (129054)"); FW1.createRow(33).createCell(0).setCellValue(" ? "); FW1.createRow(34).createCell(0).setCellValue(" - ?? (129056) "); FW1.createRow(35).createCell(0) .setCellValue(" - ??? (129057) "); FW1.createRow(36).createCell(0).setCellValue(" "); FW1.createRow(37).createCell(0).setCellValue(" - ?? (129059) "); FW1.createRow(38).createCell(0) .setCellValue(" - ??? (129060) "); FW1.createRow(39).createCell(0).setCellValue("? (129061) "); FW1.createRow(40).createCell(0) .setCellValue("? (129062) "); // FW1.getRow(40).getCell(0).setCellStyle(styleBR); FW1.getRow(4).getCell(0).setCellStyle(styleFont); FW1.getRow(5).getCell(0).setCellStyle(styleFont); FW1.getRow(6).getCell(0).setCellStyle(styleFont); FW1.getRow(7).getCell(0).setCellStyle(styleFont); FW1.getRow(11).getCell(0).setCellStyle(styleFont); FW1.getRow(14).getCell(0).setCellStyle(styleFont); FW1.getRow(17).getCell(0).setCellStyle(styleFont); FW1.getRow(20).getCell(0).setCellStyle(styleFont); FW1.getRow(23).getCell(0).setCellStyle(styleFont); FW1.getRow(26).getCell(0).setCellStyle(styleFont); FW1.getRow(30).getCell(0).setCellStyle(styleFont); FW1.getRow(33).getCell(0).setCellStyle(styleFont); FW1.getRow(36).getCell(0).setCellStyle(styleFont); FW1.getRow(39).getCell(0).setCellStyle(styleFont); FW1.getRow(40).getCell(0).setCellStyle(styleFont); int cellCurrency = 1; for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); String currencyName = currencyList.get(iCurrencyList); FW1_row_2.createCell(cellCurrency).setCellValue(currencyName); FW1_row_3.createCell(cellCurrency).setCellValue(""); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style); FW1_row_3.getCell(cellCurrency).setCellStyle(style); List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName); for (ForwardContract objCCY : tmpGroupCCY) { FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + ""); if ("????" .equals(objCCY.getTRANS_TYPE())) { if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(8).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true)); FW1.getRow(8).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(9).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false)); FW1.getRow(9).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false)); } } else if ("????" .equals(objCCY.getTRANS_TYPE().trim())) { if ("New Forward".equals(objCCY.getLIST_ITEM())) { FW1.getRow(28).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true)); FW1.getRow(28).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(29).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false)); FW1.getRow(29).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false)); } } else if ("?".equals(objCCY.getTRANS_TYPE())) { } FW1.getRow(39).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false)); FW1.getRow(39).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false)); } // // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+""); CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency)); FW1_row_3.createCell(cellCurrency).setCellValue(""); HSSFCellStyle styleTop = style; styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN); FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop); cellCurrency++; } System.out.println("cellCurrency ++++ " + cellCurrency); int finishM = cellCurrency - 3; int noOfColumns = FW1.getRow(2).getLastCellNum(); int noOfRow = FW1.getLastRowNum(); // HSSFPalette palette = myWorkBook.getCustomPalette(); // HSSFColor hssfColor = null; // // // palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255); // hssfColor = palette.getColor(HSSFColor.WHITE.index); // // // // HSSFCellStyle styleBG = myWorkBook.createCellStyle(); // styleBG.setFillForegroundColor(hssfColor.getIndex()); // styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND); for (int i = 0; i < noOfRow; i++) { for (int j = 0; j < noOfColumns; j++) { FW1.autoSizeColumn(i); // row.getCell(j).setCellStyle(styleBG); } } for (int i = 4; i < noOfRow + 1; i++) { for (int j = 1; j < noOfColumns; j++) { HSSFCellStyle style6 = myWorkBook.createCellStyle(); try { if (j == 1) { style6.setBorderLeft(HSSFCellStyle.BORDER_THIN); style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } else { style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } if (i == noOfRow) { style6.setBorderBottom(HSSFCellStyle.BORDER_THIN); } FW1.getRow(i).getCell(j).setCellStyle(style6); } catch (Exception e) { FW1.getRow(i).createCell(j).setCellStyle(style6); } } } HSSFFont fontBold = myWorkBook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle(); borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN); borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); borderRightCenterFontBold.setFont(fontBold); HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold; borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleR = myWorkBook.createCellStyle(); styleR.setBorderRight(HSSFCellStyle.BORDER_THIN); // Forward contract // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold); FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM)); FW1_row_0.createCell(finishM + 1) .setCellValue(" " + dateFormat.format(new Date()) + ""); HSSFCellStyle style4 = myWorkBook.createCellStyle(); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_0.getCell(finishM + 1).setCellStyle(style4); FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont); FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2)); FW1_row_1.createCell(finishM + 1).setCellValue( ":"); FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2)); FW1_row_1.getCell(finishM + 1).setCellStyle(style4); FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont); FW1_row_0.createCell(finishM + 2).setCellStyle(styleR); FW1_row_1.createCell(finishM + 2).setCellStyle(styleR); FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold); HSSFCellStyle borderBottom = myWorkBook.createCellStyle(); borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN); borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT); borderBottom.setFont(fontBold); FW1.getRow(40).getCell(0).setCellStyle(borderBottom); HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch(); /* Here is the thing: the line will go from top left in cell (0,0) to down left of cell (0,1) */ // int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3); HSSFSimpleShape shape = patriarch.createSimpleShape(anchor); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); shape.setLineStyleColor(10, 10, 10); shape.setFillColor(90, 10, 200); shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT); shape.setLineStyle(HSSFShape.LINESTYLE_SOLID); // createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); // FW1.createFreezePane(0,4); } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kiwisoft.db.export.ExcelExporter.java
License:Open Source License
public void exportTable(JTable table, SQLStatement statement, File file, ExportConfiguration configuration) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Query"); TableColumnModel columnModel = table.getColumnModel(); ResultSetTableModel tableModel = (ResultSetTableModel) table.getModel(); HSSFFont headerFont = workbook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); int rowNum = 0; HSSFRow row = sheet.createRow(rowNum++); sheet.createFreezePane(0, 1);//from w w w . j ava 2 s . c o m int columnCount = columnModel.getColumnCount(); for (short i = 0; i < columnCount; i++) { TableColumn column = columnModel.getColumn(i); int columnIndex = column.getModelIndex(); HSSFCell cell = row.createCell(i); cell.setCellStyle(headerStyle); String columnName = tableModel.getColumnName(columnIndex); cell.setCellValue(columnName); } for (int j = 0; j < tableModel.getRowCount(); j++) { row = sheet.createRow(rowNum++); for (short i = 0; i < columnCount; i++) { TableColumn column = columnModel.getColumn(i); int columnIndex = column.getModelIndex(); Object cellValue = tableModel.getValueAt(j, columnIndex); HSSFCell cell = row.createCell(i); if (cellValue instanceof Number) cell.setCellValue(((Number) cellValue).doubleValue()); else if (cellValue instanceof Date) { HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat((short) 14); cell.setCellValue((Date) cellValue); cell.setCellStyle(style); } else if (cellValue instanceof Boolean) cell.setCellValue(((Boolean) cellValue).booleanValue()); else if (cellValue != null) cell.setCellValue(cellValue.toString()); } } FileOutputStream out = new FileOutputStream(file); workbook.write(out); Field field = ClassLoader.class.getDeclaredField("classes"); field.setAccessible(true); Vector classes = (Vector) field.get(HSSFWorkbook.class.getClassLoader()); field.setAccessible(false); System.out.println("classes = " + StringUtils.enumerate(classes, "\n")); out.close(); }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w ww. j a v a 2 s. co m */ private static void testCreateSampleSheet(String outputFilename) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); int rownum; for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue( rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet try (FileOutputStream out = new FileOutputStream(outputFilename)) { wb.write(out); } } }
From source file:com.leosys.core.utils.ExcelUtil.java
public void exportExcel(List<?> dataList, OutputStream out) throws Exception { HSSFWorkbook workbook = null;//from w w w . java 2 s . co m HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle titleStyle = null; int rowIndex = 0; try { workbook = new HSSFWorkbook();// sheet = workbook.createSheet("?");// ? sheet.setDefaultColumnWidth((short) 30);// 15 titleStyle = workbook.createCellStyle();//? titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setFillForegroundColor(HSSFColor.WHITE.index); titleStyle.setFillBackgroundColor(HSSFColor.WHITE.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 17); titleStyle.setFont(font); row = sheet.createRow(rowIndex++); row.setHeight((short) 600); for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); //? if (i == 0) cell.setCellValue(new HSSFRichTextString(title)); cell.setCellStyle(titleStyle); } // ??? sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headArr.length - 1))); titleStyle = workbook.createCellStyle(); titleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setFontHeightInPoints((short) 13); titleStyle.setFont(font);// ?? row = sheet.createRow(rowIndex++);// for (short i = 0; i < headArr.length; i++) { cell = row.createCell(i); cell.setCellStyle(titleStyle); cell.setCellValue(new HSSFRichTextString(headArr[i])); } //? titleStyle = workbook.createCellStyle(); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); if (dataList == null || dataList.isEmpty()) return; short dataType = 0;// if (dataList.get(0) instanceof Map<?, ?>) dataType = 1; else if (dataList.get(0) instanceof List<?>) dataType = 2; if (dataType == 0) { String[] dataArr = null; for (Object data : dataList) { dataArr = (String[]) data; if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 1) { Map<?, ?> map = null; for (Object data : dataList) { map = (Map<?, ?>) data; if (map == null) continue; Object[] dataArr = map.values().toArray(); if (dataArr == null) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < dataArr.length) { Object val = dataArr[i]; if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(dataArr[i], i, dataArr); fillCell(row, titleStyle, font, i, val); } } } } else if (dataType == 2) { List<?> list = null; for (Object data : dataList) { list = (List<?>) data; if (list == null || list.isEmpty()) continue; row = sheet.createRow(rowIndex++); for (short i = 0; i < headArr.length; i++) { if (i < list.size()) { Object val = list.get(i); if (rendererArr != null && rendererArr[i] != null) val = rendererArr[i].renderer(list.get(i), i, list); fillCell(row, titleStyle, font, i, val); } } } } else throw new Exception("excel???"); workbook.write(out); } catch (Exception e) { throw new Exception("excel" + e.getMessage()); } finally { // if(out != null){ // try { // out.close(); // } catch (IOException e) {} // } } }