Пример #1
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
  @Override
  public void createCellStyles(Workbook wb) {
    if (wb == null) {
      return;
    }
    CreationHelper createHelper = wb.getCreationHelper();

    // Headers
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    headerStyle.setFont(font);

    cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
    cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
    cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
    cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);

    // Date and Time
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
    cellStyleMap.put(STYLE_DATE_CELL, dateStyle);

    CellStyle timeStyle = wb.createCellStyle();
    timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
    cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
  }
  private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;

    // Title Style
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(true);
    styles.put("title", style);

    // Sub Title Style
    Font subTitleFont = wb.createFont();
    subTitleFont.setFontHeightInPoints((short) 14);
    subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(subTitleFont);
    style.setWrapText(true);
    styles.put("subTitle", style);

    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    return styles;
  }
Пример #4
0
  /** Create a library of cell styles */
  private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
  }
 private Result populateLoansTable(Sheet savingsTransactionSheet) {
   Result result = new Result();
   Workbook workbook = savingsTransactionSheet.getWorkbook();
   CellStyle dateCellStyle = workbook.createCellStyle();
   short df = workbook.createDataFormat().getFormat("dd/mm/yy");
   dateCellStyle.setDataFormat(df);
   int rowIndex = 1;
   Row row;
   Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
   try {
     for (CompactSavingsAccount savingsAccount : savings) {
       row = savingsTransactionSheet.createRow(rowIndex++);
       writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
       writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
       writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
       writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
       writeDate(
           LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
           row,
           savingsAccount.getTimeline().getActivatedOnDate().get(2)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(1)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(0),
           dateCellStyle);
     }
   } catch (Exception e) {
     result.addError(e.getMessage());
     logger.error(e.getMessage());
   }
   return result;
 }
Пример #6
0
 // ����Excel
 public String ExcelExport() throws Exception {
   HttpServletRequest request = ServletActionContext.getRequest();
   String ids = request.getParameter("ids");
   List<Dise> list = new ArrayList<Dise>();
   String[] array = ids.split(",");
   int[] id = new int[array.length];
   for (int i = 0; i < id.length; i++) {
     Dise dise = DiseService.findById(Integer.valueOf(array[i]));
     list.add(dise);
   }
   Workbook workbook = new HSSFWorkbook();
   Sheet sheet = workbook.createSheet("ѧ����Ϣ");
   Row row = sheet.createRow(0);
   row.createCell(0).setCellValue("ѧ��");
   row.createCell(1).setCellValue("����");
   row.createCell(2).setCellValue("����");
   row.createCell(3).setCellValue("�Ա�");
   row.createCell(4).setCellValue("��ַ");
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
   for (int i = 1; i <= list.size(); i++) {
     Dise stu = list.get(i - 1);
     row = sheet.createRow(i);
     row.createCell(0).setCellValue(stu.getIds());
     row.createCell(1).setCellValue(stu.getName());
     row.createCell(2).setCellValue(stu.getA());
     row.createCell(3).setCellValue(stu.getB());
     row.createCell(4).setCellValue(stu.getC());
   }
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   excelFile = new ByteArrayInputStream(baos.toByteArray());
   baos.close();
   return "excel";
 }
Пример #7
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
 private CellStyle getAmtCellStyle(Workbook wb) {
   Font font = wb.createFont();
   font.setColor(IndexedColors.GREEN.getIndex());
   font.setBoldweight(Font.BOLDWEIGHT_BOLD);
   CellStyle cellStyle = wb.createCellStyle();
   cellStyle.setFont(font);
   getAllAroundBorder(cellStyle);
   return cellStyle;
 }
Пример #9
0
 protected CellStyle createMetaDataCellStyle(Workbook wb) {
   synchronized (syncObj) {
     //            if( isClosed() )throw new IllegalStateException("is closed");
     CellStyle cs = wb.createCellStyle();
     Font font = wb.createFont();
     font.setBold(true);
     cs.setFont(font);
     return cs;
   }
 }
  private void makeCellAutosizeAndBold(Workbook wb, Row row) {
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);

    for (int i = 0; i < row.getLastCellNum(); i++) {
      row.getCell(i).setCellStyle(style);
    }
    makeCellsAutosize(wb, row);
  }
 private CellStyle getHeaderStyle(Workbook wb) {
   Font font = wb.createFont();
   font.setBoldweight(Font.BOLDWEIGHT_BOLD);
   CellStyle headerStyle = wb.createCellStyle();
   headerStyle.setFont(font);
   getAllAroundBorder(headerStyle);
   headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
   headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
   headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
   headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
   return headerStyle;
 }
Пример #12
0
  private static void setStyle(Workbook wb) {
    titleFont = wb.createFont();
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
    titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
    titleStyle.setBorderRight(CellStyle.BORDER_THIN);
    titleStyle.setBorderTop(CellStyle.BORDER_THIN);
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    titleStyle.setFont(titleFont);

    contentStyle = wb.createCellStyle();
    contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contentStyle.setBorderRight(CellStyle.BORDER_THIN);
    contentStyle.setBorderTop(CellStyle.BORDER_THIN);
    contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  }
Пример #13
0
  private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
    CreationHelper creationHelper = workbook.getCreationHelper();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
    retVal.setCellStyle(cellStyle);

    if (null != cellObj) {
      Calendar calendar = Calendar.getInstance();
      calendar.setTime((Date) cellObj);
      retVal.setCellValue(calendar);
    }
  }
Пример #14
0
  /**
   * 创建表格样式
   *
   * @param wb 工作薄对象
   * @return 样式列表
   */
  private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //		style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
  }
  /**
   * This is used to get the cell style of the header cells. If you want a custom style for the
   * column header cells, customize this method.
   *
   * @param theWorkbook the Excel workbook we are working in
   * @return the cell style for the header
   */
  public static CellStyle GetHeaderCellStyle(Workbook theWorkbook) {

    CellStyle HeaderStyle = theWorkbook.createCellStyle();
    Font HeaderFont = theWorkbook.createFont();

    /*sets the style and font appropriately
     * NOTE: This is the code block where customization should be done
     */
    HeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    HeaderStyle.setFont(HeaderFont);

    return HeaderStyle;
  }
 private CellStyle buildCellStyle() {
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
   cellStyle.setBorderTop(CellStyle.BORDER_THIN);
   cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
   cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderRight(CellStyle.BORDER_THIN);
   cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
   cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
   return cellStyle;
 }
  /**
   * This is used to get the cell style of the body cells. If you want a custom style for the cells
   * that say the attribute values of the entries in the list, customize this method.
   *
   * @param theWorkbook the Excel workbook we are working in
   * @return the cell style for the body
   */
  public static CellStyle GetBodyCellStyle(Workbook theWorkbook) {

    CellStyle theStyle = theWorkbook.createCellStyle();

    /*set the style to wrap the text and center vertical alignment
     * NOTE: This is the code block where customization should be done
     */
    theStyle.setWrapText(true);
    theStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    theStyle.setAlignment(CellStyle.ALIGN_LEFT);

    return theStyle;
  }
Пример #18
0
 public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
   cell.setCellValue(NumberUtils.format(fieldValue, 0));
   cell.setCellStyle(cellStyle);
 }
Пример #19
0
 /**
  * 创建单元格的样式
  *
  * @param workbook 工作表
  * @param font 字代
  * @param alignment 水平对齐 如:CellStyle.ALIGN_CENTER
  * @param verticalAlignment 垂直对齐 如:CellStyle.VERTICAL_CENTER
  * @param Border 边框 如:XSSFCellStyle.BORDER_THIN
  * @param foregroundColor 前置背景色 如:CellStyle.BORDER_THIN
  * @param fillPattern 填充模式 如:
  * @return
  */
 public CellStyle createCellStyle(
     Workbook workbook,
     Font font,
     short alignment,
     short verticalAlignment,
     short Border,
     short foregroundColor,
     short fillPattern) {
   XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
   cellStyle.setFont(font);
   cellStyle.setAlignment(alignment);
   cellStyle.setVerticalAlignment(verticalAlignment);
   cellStyle.setBorderRight(Border);
   cellStyle.setFillForegroundColor(foregroundColor);
   cellStyle.setFillPattern(fillPattern);
   return cellStyle;
 }
 /**
  * Discription:[设置文件内容的显示样式]
  *
  * @param wb
  * @return
  * @author:[代超]
  * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
  */
 public CellStyle setContentSheetSysle(Workbook wb) {
   CellStyle style = wb.createCellStyle();
   // 生成并设置另一个样式
   style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
   style.setBorderTop(HSSFCellStyle.BORDER_THIN);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   // 生成一个字体
   Font font = wb.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
   // 把字体应用到当前的样式
   style.setFont(font);
   return style;
 }
 /**
  * Discription:[设置文件头显示样式]
  *
  * @param wb
  * @return
  * @author:[代超]
  * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
  */
 public CellStyle setRootSheetSysle(Workbook wb) {
   CellStyle style = wb.createCellStyle();
   // 设置这些样式
   style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
   style.setBorderTop(HSSFCellStyle.BORDER_THIN);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 对齐方式:居中对齐
   // 生成一个字体
   Font font = wb.createFont();
   font.setColor(HSSFColor.VIOLET.index);
   font.setFontHeightInPoints((short) 12);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   // 把字体应用到当前的样式
   style.setFont(font);
   return style;
 }
Пример #22
0
 public void outputNumericAdapter(
     DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   StringBuilder format = new StringBuilder("0");
   for (int i = 0; i < config.floatCount(); i++) {
     if (i == 0) format.append(".");
     format.append("0");
   }
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
   cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
   cell.setCellStyle(cellStyle);
 }
  private CellStyle getStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    // 设置单元格字体
    Font headerFont = workbook.createFont(); // 字体
    headerFont.setFontHeightInPoints((short) 14);
    headerFont.setColor(HSSFColor.BLACK.index);
    headerFont.setFontName("宋体");
    style.setFont(headerFont);
    style.setWrapText(true);

    // 设置单元格边框及颜色
    style.setBorderBottom((short) 1);
    style.setBorderLeft((short) 1);
    style.setBorderRight((short) 1);
    style.setBorderTop((short) 1);
    style.setWrapText(true);
    return style;
  }
Пример #24
0
 /**
  * 导出时间适配器
  *
  * @param fieldValue
  * @param fieldName
  * @return
  * @throws AdapterException
  */
 public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   Date date = null;
   if (fieldValue == null) {
     log.debug("fieldValue is null return");
     cell.setCellValue("");
     return;
   } else if (fieldValue instanceof Date) {
     log.debug("fieldValue instanceof Date ");
     date = (Date) fieldValue;
   } else if (fieldValue instanceof String) {
     log.debug("fieldValue instanceof String ");
     InputDateConfig config = dataBean.getInputConfig(fieldName);
     try {
       date = DateUtil.formatToDate((String) fieldValue, config.format());
     } catch (ParseException e) {
       throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
     }
   } else if (fieldValue instanceof Long) {
     log.debug("fieldValue instanceof Long ");
     date = new Date((Long) fieldValue);
   } else {
     throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
   }
   Workbook workbook = cell.getSheet().getWorkbook();
   OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
   CellStyle cellStyle = cell.getCellStyle();
   if (cellStyle == null) cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
   cell.setCellStyle(cellStyle);
   cell.setCellValue(date);
 }
Пример #25
0
  public void setCellMoney(int numRow, int numCol) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        DataFormat format = wb.createDataFormat();
        Row row = aSheet.getRow((short) numRow);
        Cell csCell = row.getCell((short) numCol);
        CellStyle style = wb.createCellStyle();
        style.setDataFormat(format.getFormat("0.00"));
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        csCell.setCellStyle(style);
      }
    } catch (Exception e) {
      System.out.println("insertDataToExcel" + e);
    }
  }
Пример #26
0
  /**
   * Creates a new instance.
   *
   * @param workbook target workbook
   * @throws IllegalArgumentException if some parameters were {@code null}
   */
  WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
      throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(BorderStyle.THIN);
    commonStyle.setBorderBottom(BorderStyle.THIN);
    commonStyle.setBorderLeft(BorderStyle.THIN);
    commonStyle.setBorderRight(BorderStyle.THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(HorizontalAlignment.CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
  }
 private CellStyle getFirsetCellStyle(Workbook wb) {
   CellStyle cellStyle = wb.createCellStyle();
   getAllAroundBorder(cellStyle);
   cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
   return cellStyle;
 }
 private CellStyle getCommonCellStyle(Workbook wb) {
   CellStyle cellStyle = wb.createCellStyle();
   getAllAroundBorder(cellStyle);
   return cellStyle;
 }
Пример #29
0
  private void saveExcelPoject(File file) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("timeplan");
    // Заголовок в 0 строке
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Филиал");
    cell = row.createCell(1);
    cell.setCellValue("Город");
    Calendar cal = Calendar.getInstance();
    cal.set(2017, 0, 5); // Начальная дата проекта
    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
    for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
      cell = row.createCell(i + 2);
      cell.setCellValue(sdf.format(cal.getTime()));
      cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
    }

    // sheet.setColumnWidth(0, 256);

    // Цвета ячеек
    CellStyle[] styles = new CellStyle[6];
    styles[0] = wb.createCellStyle();
    styles[0].setFillForegroundColor(HSSFColor.RED.index);
    styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[1] = wb.createCellStyle();
    styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
    styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[2] = wb.createCellStyle();
    styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
    styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[3] = wb.createCellStyle();
    styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
    styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[4] = wb.createCellStyle();
    styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[5] = wb.createCellStyle();
    styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);

    short rowIdx = 0;
    for (Region region : this.regions) {
      row = sheet.createRow(++rowIdx);
      cell = row.createCell(0);
      cell.setCellValue(region.filial);
      cell = row.createCell(1);
      cell.setCellValue(region.name);

      cal = Calendar.getInstance();
      cal.set(2017, 0, 5); // Начальная дата проекта
      for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
        short color = region.getDateColorIndex(cal.getTime());
        if (color >= 0) {
          cell = row.createCell(i + 2);
          cell.setCellStyle(styles[color]);
        }

        cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
      }
    }

    try (FileOutputStream fileOut = new FileOutputStream(file)) {
      wb.write(fileOut);
    }
  }
  @Test
  public void generateExcelPoiReport() throws IOException {

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Accounts");

    int rowNum = 1;
    Row row = sheet.createRow(rowNum++);

    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 24);
    font.setFontName(FONT_TYPE);
    font.setColor(FONT_COLOR_TITLE);

    /** * Header **** */
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillForegroundColor(BACKGROUND_COLOR);

    Cell cell = row.createCell((short) 1);
    cell.setCellValue("Account Report per Beneficiary");
    cell.setCellStyle(style);
    sheet.addMergedRegion(
        new CellRangeAddress(
            1, // first row (0-based)
            2, // last row  (0-based)
            1, // first column (0-based)
            16 // last column  (0-based)
            ));

    /** * Body **** */
    font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setFontName(FONT_TYPE);
    font.setColor(FONT_COLOR);

    style = wb.createCellStyle();
    style.setFont(font);

    rowNum = rowNum + 3;
    List<Account> accounts = accountManager.getAllAccounts();
    for (Account account : accounts) {

      row = sheet.createRow(rowNum++);

      cell = row.createCell((short) 1);
      cell.setCellStyle(style);
      cell.setCellValue(account.getName());

      cell = row.createCell((short) 2);
      cell.setCellValue(account.getNumber());
      cell.setCellStyle(style);

      cell = row.createCell((short) 3);
      cell.setCellValue(account.getEntityId());
      cell.setCellStyle(style);
    }

    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    sheet.autoSizeColumn(3);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls");
    wb.write(fileOut);
    fileOut.close();
  }