List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getDefaultColumnWidth
@Override public int getDefaultColumnWidth()
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;/* ww w .j a v a 2 s . c o m*/ dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:eionet.gdem.conversion.excel.writer.ExcelConversionHandler.java
License:Mozilla Public License
@Override public void addCell(String type, String str_value, String style_name) { HSSFSheet _sheet = wb.getSheetAt(currentSheet); HSSFRow _row = _sheet.getRow(currentRow); HSSFCell _cell = _row.createCell((currentCell)); Double number_value = null;/*ww w . j a va 2s . c o m*/ Boolean boolean_value = null; boolean isNumber = false; boolean isBoolean = false; if (type == null) { type = (String) getDefaultParams("data_type"); } if (type != null) { if (type.equals("float") || type.equals("number")) { if (str_value != null) { try { number_value = new Double(str_value); isNumber = true; } catch (Exception e) { // the value is not number, it will be inserted as a string // System.out.println(e.toString()); } } else { isNumber = true; } } else if (type.equals("boolean")) { if (str_value != null) { try { boolean_value = new Boolean(str_value); isBoolean = true; } catch (Exception e) { // the value is not boolean, it will be inserted as a string // System.out.println(e.toString()); } } else { isBoolean = true; } } else if (type.equals("date")) { if (str_value != null) { try { // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyymmdd")); /* * * The way how to handle user defined formats not supported right now HSSFDataFormat format = * wb.createDataFormat(); HSSFCellStyle style = wb.createCellStyle(); * style.setDataFormat(format.getFormat("yyyymmdd")); _cell.setCellStyle(style); */ // cellStyle.setDataFormat(new HSSFDataFormat("yyyymmdd")); /* * try{ l_value=Long.parseLong(value); System.out.println(String.valueOf(l_value)); isLong=true; } * catch(Exception e){ System.out.println(e.toString()); } */ /* * if (isLong){ Date d = new Date(); _cell.setCellStyle(cellStyle); //_cell.setCellValue(d); * _cell.setCellValue(value); //System.out.println(d.toString()); isDate=true; } else * _cell.setCellValue(value); */ // System.out.println("hh"); } catch (Exception e) { System.out.println(e.toString()); } } } } if (isNumber) { if (number_value != null) { _cell.setCellValue(number_value.doubleValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); } else if (isBoolean) { if (boolean_value != null) { _cell.setCellValue(boolean_value.booleanValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else { _cell.setCellType(HSSFCell.CELL_TYPE_STRING); // _cell.setEncoding(HSSFCell.ENCODING_UTF_16 );// _cell.setCellValue(str_value); } short idx = -1; if (style_name != null) { idx = getStyleIdxByName(style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); } if (idx < 0) { Short short_idx = (Short) getDefaultParams("style"); if (short_idx != null) { idx = short_idx.shortValue(); } } if (idx > -1) { _cell.setCellStyle(wb.getCellStyleAt(idx)); } // calculates the col with according to the first row if (currentRow == 0 && idx > -1) { short colStyleWidth = 0; HSSFCellStyle style = wb.getCellStyleAt(idx); int f_i = style.getFontIndex(); HSSFFont font = wb.getFontAt((short) f_i); // character size short size = font.getFontHeightInPoints(); if (columns.size() > currentCell) { RowColumnDefinition column = columns.get(currentCell); String column_style_name = column.getStyleName() == null ? "" : column.getStyleName(); ExcelStyleIF definedStyle = getStyleByName(column_style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); if (definedStyle != null) { colStyleWidth = definedStyle.getColumnWidth(); } } short width = (short) (_sheet.getDefaultColumnWidth() * size * 25); if (colStyleWidth > 0) { width = colStyleWidth; } else if (str_value.length() > 0) { width = (short) (str_value.length() * size * 50); } _sheet.setColumnWidth(currentCell, width); } currentCell = _cell.getColumnIndex() + 1; // System.out.println("Cell" + currentCell+ "-" + value); }
From source file:tournoi.ExcelToBDD.java
public boolean insertJoueur(String filePath, String table) { boolean flag = true; db = new DBConnection(); con = db.getConnection();/*w ww .j a va2s . co m*/ try { // ??excel FileInputStream fin = new FileInputStream(filePath); HSSFWorkbook workbook = new HSSFWorkbook(fin);// HSSFSheet sheet = workbook.getSheetAt(0);// HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; int totalCol = sheet.getDefaultColumnWidth();// obtenir le nbr de column for (int i = 1; i <= totalCol; i++) { } int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_joueur = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); cell4 = row.getCell(3); int national_point = (int) cell4.getNumericCellValue(); cell5 = row.getCell(4); String categorie = cell5.getRichStringCellValue().toString(); cell6 = row.getCell(5); String federation = cell6.getRichStringCellValue().toString(); cell7 = row.getCell(6); String ligne = cell7.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','" + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }