List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) { ListHashMap<Integer, Integer> col2lens = new ListHashMap<>(); for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) { Row r = sheet.getRow(row);/* w ww.j a v a2 s . com*/ if (r == null || r.getFirstCellNum() < 0) continue; short maxH = 0; for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) { Cell c = r.getCell(col); if (c == null || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC)) continue; Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex()); String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue() : "" + c.getNumericCellValue(); String[] lines = MiscUtils.split(s, "\n"); int maxLen = 1; for (int i = 0; i < lines.length; i++) { maxLen = Math.max(lines[i].length(), maxLen); } if (font.getFontHeightInPoints() < 12) { col2lens.add(col, 700 + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20); } maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2)); } if (resizeHeight) r.setHeight(maxH); } for (int col : col2lens.keySet()) { List<Integer> lens = col2lens.get(col); Collections.sort(lens); int len = lens.get(lens.size() - 1); if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) { len = lens.get(lens.size() - 2); } sheet.setColumnWidth(col, Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500)); } }
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
void autosize(Sheet sheet, int lastColumnIndex) { for (int i = 0; i <= lastColumnIndex; i++) { try {// w w w . ja v a 2 s . c o m sheet.autoSizeColumn(i); } catch (Exception e) { // autosize depends on AWT stuff and can fail, but it should not be fatal LOG.warn("autoSizeColumn(" + i + ") failed: " + e.getMessage()); } int cw = sheet.getColumnWidth(i); // increase width to accommodate drop-down arrow in the header if (cw / 256 < 20) { sheet.setColumnWidth(i, 256 * 12); } else if (cw / 256 > 120) { sheet.setColumnWidth(i, 256 * 120); } } }
From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java
License:Apache License
private void adjustColumnWidth(Sheet sheet, int lastColumn) { assert sheet != null; for (int i = 0; i <= lastColumn; i++) { sheet.autoSizeColumn(i);/* w w w .ja v a 2 s. c o m*/ int width = sheet.getColumnWidth(i); if (width < MINIMUM_COLUMN_WIDTH) { sheet.setColumnWidth(i, MINIMUM_COLUMN_WIDTH); } } }
From source file:com.automaster.autoview.server.servlet.ExcelServlet.java
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { /*response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=filename.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); // ...//w w w . j a v a 2s . c o m // Now populate workbook the usual way. // ... OutputStream arqSaida = response.getOutputStream(); workbook.write(arqSaida); // Write workbook to response. arqSaida.flush(); arqSaida.close();*/ //getServletContext().getRealPath("/") String tempoDecorrido = " 0"; String url = getServletContext().getRealPath("/"); String placa = request.getParameter("placa"); //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC)); Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio"))); Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim"))); //String timeZoneInterface = request.getParameter("timeZone"); /*String timeZone = "Z"; if(timeZoneInterface.equalsIgnoreCase("0")){ timeZone = "Z"; } else { timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60)); } */ TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3")); System.out.println("Time zone Cliente : " + timeZonePadrao); //System.out.println("timeZoneInterface: "+timeZoneInterface); //System.out.println("timeZone: "+timeZone); //String ign = request.getParameter("ign"); ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO(); ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa, dataInicio, dataFim); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx"); Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Histrico - " + placa); int pictureIdx; try ( //add picture data to this workbook. InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) { byte[] bytes = IOUtils.toByteArray(is); pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); } CreationHelper helper = wb.getCreationHelper(); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(1); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(3, 3); //pict.resize(); //sheet.setColumnWidth(0, 200); Font fonte = wb.createFont(); fonte.setFontHeightInPoints((short) 24); fonte.setFontName("Arial"); fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fonte.setItalic(true); CellStyle estiloTitulo = wb.createCellStyle(); estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER); estiloTitulo.setFont(fonte); Font fonteCabecalho = wb.createFont(); fonteCabecalho.setFontHeightInPoints((short) 14); fonteCabecalho.setFontName("Arial"); fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //fonteCabecalho.setItalic(true); CellStyle estiloCabecalho = wb.createCellStyle(); estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER); estiloCabecalho.setFont(fonteCabecalho); Font fonteTituloTabela = wb.createFont(); //fonteTituloTabela.setFontHeightInPoints((short) 14); fonteTituloTabela.setFontName("Arial"); fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle estilo = wb.createCellStyle(); estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER); estilo.setFont(fonteTituloTabela); CellStyle estiloCorpo = wb.createCellStyle(); estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER); //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index); //estiloCorpo.setFont(fonteTituloTabela); XSSFRow linha6 = (XSSFRow) sheet.createRow(6); XSSFCell cell046 = linha6.createCell(3); cell046.setCellValue("Relatrio de Posies"); cell046.setCellStyle(estiloTitulo); //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3)); XSSFRow linha7 = (XSSFRow) sheet.createRow(7); XSSFCell cell047 = linha7.createCell(3); cell047.setCellValue("Veculo : " + placa); cell047.setCellStyle(estiloCabecalho); //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3)); XSSFRow linha8 = (XSSFRow) sheet.createRow(8); XSSFCell cell038 = linha8.createCell(3); //TimeZone.setDefault(timeZoneMundial); //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio"))); //TimeZone.setDefault(timeZoneCliente); //Date dataHoraInicio = new Date(dataHoraInicio0.getTime()); SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); dataFormatadaCabecalho.setTimeZone(timeZonePadrao); cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: " + dataFormatadaCabecalho.format(dataFim)); cell038.setCellStyle(estiloCabecalho); //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3)); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3500); //sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(3, 30000); //sheet.setColumnWidth(4, 4000); //sheet.setColumnWidth(4, 30000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 3000); //sheet.setColumnWidth(10, 20000); sheet.setColumnWidth(11, 3000); XSSFRow linha9 = (XSSFRow) sheet.createRow(10); XSSFCell cell0 = linha9.createCell(0); cell0.setCellValue("Data e hora"); cell0.setCellStyle(estilo); XSSFCell cell1 = linha9.createCell(1); cell1.setCellValue("Velocidade"); cell1.setCellStyle(estilo); XSSFCell cell2 = linha9.createCell(2); cell2.setCellValue("Ignio"); cell2.setCellStyle(estilo); //XSSFCell cell3 = linha9.createCell(3); //cell3.setCellValue("Latitude"); //cell3.setCellStyle(estilo); //XSSFCell cell4 = linha9.createCell(4); //cell4.setCellValue("Longitude"); //cell4.setCellStyle(estilo); //XSSFCell cell5 = linha9.createCell(5); //cell5.setCellValue("Satlite"); //cell5.setCellStyle(estilo); //XSSFCell cell6 = linha9.createCell(6); //cell6.setCellValue("GPS"); //cell6.setCellStyle(estilo); //XSSFCell cell7 = linha9.createCell(7); //cell7.setCellValue("Entrada"); //cell7.setCellStyle(estilo); //XSSFCell cell8 = linha9.createCell(8); //cell8.setCellValue("Sada"); //cell8.setCellStyle(estilo); //XSSFCell cell9 = linha9.createCell(9); //cell9.setCellValue("Evento"); //cell9.setCellStyle(estilo); XSSFCell cell10 = linha9.createCell(3); cell10.setCellValue("Endereo"); cell10.setCellStyle(estilo); //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8)); //XSSFCell cell11 = linha9.createCell(11); //cell11.setCellValue("Direo"); //cell11.setCellStyle(estilo); int linha = 0; int j = 11; double latAnt = 0; double lonAnt = 0; double latAtual = 0; double lonAtual = 0; double distancia = 0; double distanciaTotal = 0; for (int i = 0; i < posicoes.size(); i++) { XSSFRow row = (XSSFRow) sheet.createRow(j); if (i == 0) { distancia = 0; //System.out.println("linha 00 - EXCEL"); } else { //System.out.println("linha 01 - EXCEL"); latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon")); latAtual = Double.parseDouble(posicoes.get(i).get("lat")); lonAtual = Double.parseDouble(posicoes.get(i).get("lon")); //System.out.println("linha 02 - PDF"); if (latAnt == latAtual && lonAnt == lonAtual) { distancia = 0; } else { distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual); //System.out.println("linha 03 - PDF"); } } distanciaTotal = distanciaTotal + distancia; /*if(i==0) { latAnt = Double.parseDouble(posicoes.get(i).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i).get("lon")); } else{ latAnt = Double.parseDouble(posicoes.get(i-1).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon")); } double latAtual = Double.parseDouble(posicoes.get(i).get("lat")); double lonAtual = Double.parseDouble(posicoes.get(i).get("lon")); double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual); distanciaTotal = distanciaTotal + distancia;*/ for (int col = 0; col < posicoes.get(linha).size(); col++) { XSSFCell cell = row.createCell(col); cell.setCellStyle(estiloCorpo); switch (col) { case 0: Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora"))); Date dataHora = new Date(dataHora0.getTime()); SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); dataFormatada.setTimeZone(timeZonePadrao); cell.setCellValue(dataFormatada.format(dataHora)); break; case 1: cell.setCellValue(posicoes.get(linha).get("vel")); break; case 2: cell.setCellValue( posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada"); break; /*case 3: cell.setCellValue(posicoes.get(linha).get("lat")); break; case 4: cell.setCellValue(posicoes.get(linha).get("lon")); break; case 5: cell.setCellValue(posicoes.get(linha).get("sat")); break; case 6: cell.setCellValue(posicoes.get(linha).get("gps")); break; case 7: cell.setCellValue(posicoes.get(linha).get("entrada")); break; case 8: cell.setCellValue(posicoes.get(linha).get("saida")); break; case 9: cell.setCellValue(posicoes.get(linha).get("evento")); break;*/ case 3: cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo" : posicoes.get(linha).get("endereco")); break; /*case 11: cell.setCellValue(posicoes.get(linha).get("direcao")); break;*/ } } j = j + 1; linha = linha + 1; } tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")), Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora"))); int index = 0; String kms = "0"; String m = ""; double metros = 0; if (distanciaTotal > 0) { BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN); index = String.valueOf(decimalFormatado).indexOf("."); kms = String.valueOf(decimalFormatado).substring(0, index); m = "0" + (String.valueOf(decimalFormatado).substring(index)); metros = Double.parseDouble(m) * 1000; } //String formatted = NumberFormat.getFormat("000.00").format(metros); //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros"); XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12); XSSFCell cellX = linhaX.createCell(0); sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3)); cellX.setCellStyle(estilo); cellX.setCellValue( "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:com.b510.excel.client.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;// w w w . j a v a 2 s. co m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.b510.excel.client.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {/*from www . j a va2 s .c o m*/ calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.b510.excel.client.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w . ja v a 2 s . c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.b510.excel.client.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*www . j a v a2 s.com*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.cms.utils.ExportExcell.java
/** * Tao cac sheet. Chua export ra file excell * * @param sheetName//from w ww . j a v a 2 s. c o m * @param lstData * @param formatExcell * @param key */ public void buildSheet(String sheetName, List<?> lstData, List<FormatExcell> formatExcell, String key) { Sheet sheetTmp = wb.createSheet(sheetName); FormatExcell item; Row rowheader = sheetTmp.createRow(0); ExportExcellCelltype customCellType = new ExportExcellCelltype(wb); Cell cellStt = rowheader.createCell(0); cellStt.setCellValue(BundleUtils.getString("STT")); cellStt.setCellStyle(customCellType.cellTypeColumnStt()); for (int i = 0; i < formatExcell.size(); i++) { item = formatExcell.get(i); Cell cell = rowheader.createCell(i + 1); cell.setCellValue(getColumnTitle(item.getCellColumn(), key)); cell.setCellStyle(customCellType.cellTypeHeader(false)); sheetTmp.setColumnWidth(i + 1, item.getColumnWidth()); sheetTmp.setColumnHidden(i + 1, item.getColumnHiden()); if (item.getHeaderCommand() != null) { cell.setCellComment(getcellComment(sheetTmp, item, cell)); } setFormatCell(customCellType, item); } if (DataUtil.isListNullOrEmpty(formatExcell) || DataUtil.isListNullOrEmpty(lstData)) { return; } buildContentSheet(sheetTmp, customCellType, lstData, formatExcell); }
From source file:com.dbumama.market.web.core.render.excel.PoiExporter.java
License:Apache License
public Workbook export() { Preconditions.checkNotNull(data, "data can not be null"); Preconditions.checkNotNull(headers, "headers can not be null"); Preconditions.checkNotNull(columns, "columns can not be null"); Preconditions.checkArgument(/* w w w . j ava 2s.c om*/ data.length == sheetNames.length && sheetNames.length == headers.length && headers.length == columns.length, "data,sheetNames,headers and columns'length should be the same." + "(data:" + data.length + ",sheetNames:" + sheetNames.length + ",headers:" + headers.length + ",columns:" + columns.length + ")"); Preconditions.checkArgument(cellWidth >= 0, "cellWidth can not be less than 0"); Workbook wb; if (VERSION_2003.equals(version)) { wb = new HSSFWorkbook(); if (data.length > 1) { for (int i = 0; i < data.length; i++) { List<?> item = data[i]; Preconditions.checkArgument(item.size() < MAX_ROWS, "Data [" + i + "] is invalid:invalid data size (" + item.size() + ") outside allowable range (0..65535)"); } } else if (data.length == 1 && data[0].size() > MAX_ROWS) { data = dice(data[0], MAX_ROWS).toArray(new List<?>[] {}); String sheetName = sheetNames[0]; sheetNames = new String[data.length]; for (int i = 0; i < data.length; i++) { sheetNames[i] = sheetName + (i == 0 ? "" : (i + 1)); } String[] header = headers[0]; headers = new String[data.length][]; for (int i = 0; i < data.length; i++) { headers[i] = header; } String[] column = columns[0]; columns = new String[data.length][]; for (int i = 0; i < data.length; i++) { columns[i] = column; } } } else { wb = new XSSFWorkbook(); } if (data.length == 0) { return wb; } for (int i = 0; i < data.length; i++) { Sheet sheet = wb.createSheet(sheetNames[i]); Row row; Cell cell; if (headers[i].length > 0) { row = sheet.createRow(0); if (headerRow <= 0) { headerRow = HEADER_ROW; } headerRow = Math.min(headerRow, MAX_ROWS); for (int h = 0, lenH = headers[i].length; h < lenH; h++) { if (cellWidth > 0) { sheet.setColumnWidth(h, cellWidth); } cell = row.createCell(h); cell.setCellValue(headers[i][h]); } } for (int j = 0, len = data[i].size(); j < len; j++) { row = sheet.createRow(j + headerRow); Object obj = data[i].get(j); if (obj == null) { continue; } if (obj instanceof Map) { processAsMap(columns[i], row, obj); } else if (obj instanceof Model) { processAsModel(columns[i], row, obj); } else if (obj instanceof Record) { processAsRecord(columns[i], row, obj); } else { throw new RuntimeException("Not support type[" + obj.getClass() + "]"); } } } return wb; }