List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);/*from w w w .java2 s .c o m*/ final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableSheet(final DatabaseModel datasource, final TableModel table, final XSSFSheet sheet) { // /*from w w w .j av a2s. com*/ for (int i = 0; i < 39; i++) { sheet.setColumnWidth(i, 640); } ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_info"), styleTitle, row); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.system_name"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSystemName(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.creator"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getCreateUser(), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.create_day"), styleManager.get(defStyleLabel, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getCreateDate()), styleManager.get(defStyleHeadValue, BD_RECT, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.sub_system_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, option.getSubSystemName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, Strings.get("doc.updater"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 6, option.getUpdateUser(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(29, 3, Strings.get("doc.update_day"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(32, 6, toString(option.getUpdateDate()), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 28)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 29, 31)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.schema_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getSchema().getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getLabel(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 7, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(8, 12, table.getName(), styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(20, 3, "", styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(23, 15, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 7)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 19)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 20, 22)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 23, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, Strings.get("doc.comment"), styleManager.get(defStyleLabel, CellStyle.BORDER_THIN, CellStyle.BORDER_DOTTED, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 37)); rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 1); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, BD_RECT, BD_RECT), row); row = sheet.createRow(rowIndex + 2); /////////////////////////////////////////// cell = createCell(1, 37, "", styleManager.get(defStyleHeadValue, CellStyle.BORDER_THIN, BD_RECT, BD_RECT, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, 1, 37)); rowIndex += 3; } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.column_info"), styleTitle, row); List<FieldModel> fields = table.getFields(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == fields.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.logic_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, Strings.get("doc.physical_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, Strings.get("doc.column_type"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.not_null"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.default_value"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); IndexModel primaryIndex = table.getPrimaryIndex(); rowIndex++; for (int i = 0; i < fields.size(); i++) { int bufRowIndex = rowIndex + i; FieldModel field = fields.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == fields.size()) { bottom = BD_RECT; } XSSFCellStyle defStylePK = defStyleListValue; if (null != primaryIndex) { if (null != primaryIndex.getField(field.getName())) { defStylePK = defStyleListValuePK; } } String type = field.getType().getLabel(); if (StringUtility.isNotEmpty(field.getExtra())) { type += " " + field.getExtra(); } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, field.getLabel(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 6, field.getName(), styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(15, 6, type, styleManager.get(defStylePK, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(field.isNotNull()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toDefault(field), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, field.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 14)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 15, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += fields.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.index_info"), styleTitle, row); List<IndexModel> indexs = table.getIndexs(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == indexs.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.index_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, Strings.get("doc.primary_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, Strings.get("doc.unique_key"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.comment"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < indexs.size(); i++) { int bufRowIndex = rowIndex + i; IndexModel index = indexs.get(i); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == indexs.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, index.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toColumnList(index), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 3, toTrue(index.isPrimaryKey()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(24, 3, toTrue(index.isUnique()), styleManager.get(defStyleListValueCenter, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, index.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 23)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 24, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += indexs.size(); } //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info"), styleTitle, row); List<ForeignKeyModel> foreignKeys = table.getForeignKeys(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == foreignKeys.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); rowIndex++; for (int i = 0; i < foreignKeys.size(); i++) { int bufRowIndex = rowIndex + i; ForeignKeyModel foreignKey = foreignKeys.get(i); Hyperlink link = createTableLink(foreignKey.getReferenceTableName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == foreignKeys.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, foreignKey.getReferenceTableName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyRefColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); } rowIndex += foreignKeys.size(); } //////////////////////////////////////////////////////////////////////////// // (Ref) //////////////////////////////////////////////////////////////////////////// { rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.foreign_key_info_ref"), styleTitle, row); int size = 0; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } size++; } } short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == size) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, 2, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, Strings.get("doc.foreign_key_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, Strings.get("doc.column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, Strings.get("doc.ref_former_table"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(27, 11, Strings.get("doc.ref_former_column_list"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 27, 37)); int cnt = 0; rowIndex++; for (TableModel targetTable : datasource.getTables()) { if (targetTable.equals(table)) { continue; } List<ForeignKeyModel> foreignKeys = targetTable.getForeignKeys(); for (ForeignKeyModel foreignKey : foreignKeys) { if (!foreignKey.getReferenceTableName().equals(table.getName())) { continue; } int bufRowIndex = rowIndex + cnt; Hyperlink link = createTableLink(targetTable.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (cnt == 0) { top = CellStyle.BORDER_THIN; } if (cnt + 1 == size) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, 2, String.format("%d", cnt + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(3, 6, foreignKey.getName(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(9, 12, toForeignKeyRefColumnList(foreignKey), styleManager.get( defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(21, 6, targetTable.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(27, 11, toForeignKeyColumnList(foreignKey), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 3, 8)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 9, 20)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 21, 26)); sheet.addMergedRegion(new CellRangeAddress(bufRowIndex, bufRowIndex, 27, 37)); cnt++; } } rowIndex += cnt; } workbook.setPrintArea(workbook.getSheetIndex(getTableSheetName(table.getName())), 0, 38, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ?/* w ww.j a v a 2s. co m*/ * * @param fromSheet * @param rangeAddress * @param toSheet * @param toRowNum * @param toColumnNum * @param clearFromRange */ public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange) { if (fromSheet == null || rangeAddress == null || toSheet == null) { return; } int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int rowNumOffset = toRowNum - fromRowIndex; int columnNumOffset = toColumnNum - fromColumnIndex; // CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset, rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset, rangeAddress.getLastColumn() + columnNumOffset); Workbook fromWorkbook = fromSheet.getWorkbook(); Sheet baseSheet = fromSheet; Sheet tmpSheet = null; // ????? if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) { // ? tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME); if (tmpSheet == null) { tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME); } baseSheet = tmpSheet; int lastColNum = getLastColNum(fromSheet); for (int i = 0; i <= lastColNum; i++) { tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i)); } copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), false); // ? if (clearFromRange) { clearRange(fromSheet, rangeAddress); } } // ???? Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress); // ??? clearRange(toSheet, toAddress); // ??? for (CellRangeAddress mergeAddress : targetCellSet) { toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset, mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset, mergeAddress.getLastColumn() + columnNumOffset)); } for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) { // Row fromRow = baseSheet.getRow(i); if (fromRow == null) { continue; } Row row = toSheet.getRow(i + rowNumOffset); if (row == null) { row = toSheet.createRow(i + rowNumOffset); row.setHeight((short) 0); } // ?????? int fromRowHeight = fromRow.getHeight(); int toRowHeight = row.getHeight(); if (toRowHeight < fromRowHeight) { row.setHeight(fromRow.getHeight()); } ColumnHelper columnHelper = null; if (toSheet instanceof XSSFSheet) { XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook() .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet)); CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet(); columnHelper = new ColumnHelper(ctWorksheet); } for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) { Cell fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns(); if (toSheet instanceof XSSFSheet) { maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns(); } if (j + columnNumOffset >= maxColumn) { break; } Cell cell = row.getCell(j + columnNumOffset); if (cell == null) { cell = row.createCell(j + columnNumOffset); if (toSheet instanceof XSSFSheet) { // XSSF?????????? CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false); if (col == null || !col.isSetWidth()) { toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j)); } } } // ? copyCell(fromCell, cell); // ?????? int fromColumnWidth = baseSheet.getColumnWidth(j); int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset); if (toColumnWidth < fromColumnWidth) { toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j)); } } } if (tmpSheet != null) { // fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet)); } else if (clearFromRange) { // ???? clearRange(fromSheet, rangeAddress); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ???/*from w ww .ja v a2s . c om*/ * * @param sheet * @param rangeAddress */ public static void insertRangeDown(Sheet sheet, CellRangeAddress rangeAddress) { // ?? int rangeLastRowNum = getLastRowNum(sheet, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); // if (rangeLastRowNum != -1 && rangeAddress.getFirstRow() <= rangeLastRowNum) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeLastRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); copyRange(sheet, fromAddress, sheet, rangeAddress.getLastRow() + 1, rangeAddress.getFirstColumn(), true); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ????/*from www. j a v a 2s. c om*/ * * @param sheet * @param rangeAddress */ public static void insertRangeRight(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastColumn = getLastColumnNum(sheet, rangeAddress.getFirstRow(), rangeAddress.getLastRow()); // if (rangeLastColumn != -1 && rangeAddress.getFirstColumn() <= rangeLastColumn) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeAddress.getLastRow(), rangeAddress.getFirstColumn(), rangeLastColumn); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getLastColumn() + 1, true); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ???/*from www.j a v a2 s. co m*/ * * @param sheet * @param rangeAddress */ public static void deleteRangeUp(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastRowNum = getLastRowNum(sheet, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); // if (rangeLastRowNum != -1 && rangeAddress.getFirstRow() <= rangeLastRowNum) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getLastRow() + 1, rangeLastRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), true); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ???/*from w w w .j a v a 2 s . c o m*/ * * @param sheet * @param rangeAddress */ public static void deleteRangeLeft(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastColumn = getLastColumnNum(sheet, rangeAddress.getFirstRow(), rangeAddress.getLastRow()); // if (rangeLastColumn != -1 && rangeAddress.getFirstColumn() <= rangeLastColumn) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeAddress.getLastRow(), rangeAddress.getLastColumn() + 1, rangeLastColumn); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), true); } }
From source file:org.bbreak.excella.core.util.PoiUtilTest.java
License:Open Source License
@Test public void testPoiUtil1() throws IOException, ParseException { Workbook workbook = getWorkbook();//from w w w. ja v a2 s.co m Sheet sheet_1 = workbook.getSheetAt(0); Date expectedDate = DateFormat.getDateInstance().parse("2009/4/16"); String expectedString = "???"; // =============================================== // isCellDateFormatted( Cell cell) // =============================================== assertEquals(Boolean.FALSE, PoiUtil.isCellDateFormatted(null)); // =============================================== // getJavaDate( double excelDate) // =============================================== double excelDate = 39919; // 2009/4/16 --> 39919 Date javaDate = PoiUtil.getJavaDate(excelDate); assertEquals(expectedDate, javaDate); // =============================================== // getCellValue(Cell cell) // =============================================== Object object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0)); assertEquals(new Double(10.0), object); // =============================================== // getCellValue(Cell cell, Class<?> propertyClass) // =============================================== // --> Short object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Short.class); assertEquals(Short.class, object.getClass()); // --> Integer object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Integer.class); assertEquals(Integer.class, object.getClass()); // --> Long object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Long.class); assertEquals(Long.class, object.getClass()); // --> Float object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Float.class); assertEquals(Float.class, object.getClass()); // --> Double object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Double.class); assertEquals(Double.class, object.getClass()); // --> BigDecimal object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), BigDecimal.class); assertEquals(BigDecimal.class, object.getClass()); // --> Byte object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Byte.class); assertEquals(Byte.class, object.getClass()); // --> Date object = PoiUtil.getCellValue(sheet_1.getRow(5).getCell(0), Date.class); assertEquals(Date.class, object.getClass()); // --> String object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), String.class); assertEquals(String.class, object.getClass()); // --> Boolean object = PoiUtil.getCellValue(sheet_1.getRow(3).getCell(0), Boolean.class); assertEquals(Boolean.class, object.getClass()); // --> boolean object = PoiUtil.getCellValue(sheet_1.getRow(3).getCell(0), boolean.class); assertEquals(Boolean.class, object.getClass()); // --> byte object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), byte.class); assertEquals(Byte.class, object.getClass()); // --> short object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), short.class); assertEquals(Short.class, object.getClass()); // --> int object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), int.class); assertEquals(Integer.class, object.getClass()); // --> long object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), long.class); assertEquals(Long.class, object.getClass()); // --> float object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), float.class); assertEquals(Float.class, object.getClass()); // --> double object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), double.class); assertEquals(Double.class, object.getClass()); // --> other object = PoiUtil.getCellValue(sheet_1.getRow(0).getCell(0), Object.class); assertEquals(null, object); // =============================================== // getSheetName(Sheet sheet) // =============================================== String sheetname = PoiUtil.getSheetName(sheet_1.getRow(0).getCell(0)); assertEquals("Sheet1", sheetname); // =============================================== // getSheetName(Sheet sheet) // =============================================== sheetname = PoiUtil.getSheetName(workbook.getSheetAt(0)); assertEquals("Sheet1", sheetname); // =============================================== // getCellValue(Sheet sheet, int rowIndex, int columnIndex) // =============================================== // CELL_TYPE_NUMERIC Object cellValue = PoiUtil.getCellValue(sheet_1, 0, 0); assertEquals(10.0, cellValue); // CELL_TYPE_FORMULA cellValue = PoiUtil.getCellValue(sheet_1, 1, 0); assertEquals(10000.0, cellValue); // CELL_TYPE_STRING cellValue = PoiUtil.getCellValue(sheet_1, 2, 0); assertEquals(expectedString, cellValue); // CELL_TYPE_BOOLEAN cellValue = PoiUtil.getCellValue(sheet_1, 3, 0); assertEquals(Boolean.TRUE, cellValue); // CELL_TYPE_ERROR cellValue = PoiUtil.getCellValue(sheet_1, 4, 0); if (workbook instanceof HSSFWorkbook) { // #N/A? assertEquals(new Byte("42"), cellValue); } else if (workbook instanceof XSSFWorkbook) { // XSSF?????? assertEquals("#N/A", cellValue); } // CELL_TYPE_NUMERIC -> Date cellValue = PoiUtil.getCellValue(sheet_1, 5, 0); assertEquals(expectedDate, cellValue); // CELL_TYPE_BLANK cellValue = PoiUtil.getCellValue(sheet_1, 6, 0); assertEquals(null, cellValue); // CELL_TYPE_FORMULA -> CELL_TYPE_NUMERIC cellValue = PoiUtil.getCellValue(sheet_1, 7, 0); assertEquals(10.0, cellValue); // CELL_TYPE_FORMULA -> CELL_TYPE_STRING cellValue = PoiUtil.getCellValue(sheet_1, 8, 0); assertEquals(expectedString, cellValue); // CELL_TYPE_FORMULA -> CELL_TYPE_BOOLEAN cellValue = PoiUtil.getCellValue(sheet_1, 9, 0); assertEquals(Boolean.TRUE, cellValue); // CELL_TYPE_FORMULA -> CELL_TYPE_ERROR cellValue = PoiUtil.getCellValue(sheet_1, 10, 0); // #N/A? if (workbook instanceof HSSFWorkbook) { // #N/A? assertEquals(new Byte("42"), cellValue); } else if (workbook instanceof XSSFWorkbook) { // XSSF?????? assertEquals("#N/A", cellValue); } // CELL_TYPE_FORMULA -> Date cellValue = PoiUtil.getCellValue(sheet_1, 11, 0); assertEquals(expectedDate, cellValue); // CELL_TYPE_FORMULA -> CELL_TYPE_BLANK cellValue = PoiUtil.getCellValue(sheet_1, 12, 0); assertEquals(new Double(0.0), cellValue); // =============================================== // crossRangeAddress( CellRangeAddress baseAddress, CellRangeAddress targetAddress) // =============================================== // CellRangeAddress baseAddress = new CellRangeAddress(2, 3, 2, 3); // ---????????--- CellRangeAddress targetAddress1 = new CellRangeAddress(2, 3, 2, 3); assertTrue(PoiUtil.crossRangeAddress(baseAddress, targetAddress1)); // ---????????--- // ????????? CellRangeAddress targetAddress2 = new CellRangeAddress(1, 2, 2, 3); assertTrue(PoiUtil.crossRangeAddress(baseAddress, targetAddress2)); // ????????? CellRangeAddress targetAddress3 = new CellRangeAddress(3, 4, 2, 3); assertTrue(PoiUtil.crossRangeAddress(baseAddress, targetAddress3)); // ????????? CellRangeAddress targetAddress4 = new CellRangeAddress(2, 3, 1, 2); assertTrue(PoiUtil.crossRangeAddress(baseAddress, targetAddress4)); // ?????????? CellRangeAddress targetAddress5 = new CellRangeAddress(2, 3, 3, 4); assertTrue(PoiUtil.crossRangeAddress(baseAddress, targetAddress5)); // ---????????????--- // ????? CellRangeAddress targetAddress6 = new CellRangeAddress(0, 1, 2, 3); assertFalse(PoiUtil.crossRangeAddress(baseAddress, targetAddress6)); // ????? CellRangeAddress targetAddress7 = new CellRangeAddress(4, 5, 2, 3); assertFalse(PoiUtil.crossRangeAddress(baseAddress, targetAddress7)); // ????? CellRangeAddress targetAddress8 = new CellRangeAddress(2, 3, 0, 1); assertFalse(PoiUtil.crossRangeAddress(baseAddress, targetAddress8)); // ?????? CellRangeAddress targetAddress9 = new CellRangeAddress(2, 3, 4, 5); assertFalse(PoiUtil.crossRangeAddress(baseAddress, targetAddress9)); // =============================================== // containCellRangeAddress( CellRangeAddress baseAddress, CellRangeAddress targetAddress) // =============================================== CellRangeAddress rangeAddress1 = new CellRangeAddress(0, 2, 0, 2); CellRangeAddress rangeAddress2 = new CellRangeAddress(3, 4, 3, 4); CellRangeAddress rangeAddress3 = new CellRangeAddress(0, 1, 0, 1); // ?????? assertFalse(PoiUtil.containCellRangeAddress(rangeAddress1, rangeAddress2)); // ????? assertTrue(PoiUtil.containCellRangeAddress(rangeAddress1, rangeAddress3)); // =============================================== // writeBook( Workbook workbook, String filename) // =============================================== String extension = BookController.HSSF_SUFFIX; if (workbook instanceof XSSFWorkbook) { extension = BookController.XSSF_SUFFIX; } PoiUtil.writeBook(workbook, CoreTestUtil.getTestOutputDir() + "PoiUtilTest" + System.currentTimeMillis() + extension); }
From source file:org.bbreak.excella.core.util.PoiUtilTest.java
License:Open Source License
@Test public void testPoiUtil3() throws IOException, ParseException { Workbook workbook = getWorkbook();/*from www .j a v a 2s .c om*/ Sheet sheet_1 = workbook.getSheetAt(0); Sheet sheet_2 = workbook.getSheetAt(1); Sheet sheet_3 = workbook.getSheetAt(2); Sheet sheet_4 = workbook.getSheetAt(3); Sheet sheet_5 = workbook.getSheetAt(4); Sheet sheet_6 = workbook.getSheetAt(5); Sheet sheet_7 = workbook.getSheetAt(6); // =============================================== // copyCell( Cell fromCell, Cell toCell) // =============================================== // No.1 ? Cell fromCellNumeric = sheet_1.getRow(0).getCell(0); Cell fromCellFormula = sheet_1.getRow(1).getCell(0); Cell fromCellString = sheet_1.getRow(2).getCell(0); Cell fromCellBoolean = sheet_1.getRow(3).getCell(0); Cell fromCellError = sheet_1.getRow(4).getCell(0); Cell fromCellDate = sheet_1.getRow(5).getCell(0); Cell fromCellBlank = sheet_1.getRow(6).getCell(0); Cell toCellNumeric = sheet_1.getRow(0).createCell(9); Cell toCellFormula = sheet_1.getRow(1).createCell(9); Cell toCellString = sheet_1.getRow(2).createCell(9); Cell toCellBoolean = sheet_1.getRow(3).createCell(9); Cell toCellError = sheet_1.getRow(4).createCell(9); Cell toCellDate = sheet_1.getRow(5).createCell(9); Cell toCellBlank = sheet_1.getRow(6).createCell(9); Cell fromCellNumericFrml = sheet_1.getRow(7).getCell(0); Cell fromCellStringFrml = sheet_1.getRow(8).getCell(0); Cell fromCellBooleanFrml = sheet_1.getRow(9).getCell(0); Cell fromCellErrorFrml = sheet_1.getRow(10).getCell(0); Cell fromCellDateFrml = sheet_1.getRow(11).getCell(0); Cell fromCellBlankFrml = sheet_1.getRow(12).getCell(0); Cell toCellNumericFrml = sheet_1.getRow(7).createCell(9); Cell toCellStringFrml = sheet_1.getRow(8).createCell(9); Cell toCellBooleanFrml = sheet_1.getRow(9).createCell(9); Cell toCellErrorFrml = sheet_1.getRow(10).createCell(9); Cell toCellDateFrml = sheet_1.getRow(11).createCell(9); Cell toCellBlankFrml = sheet_1.getRow(12).createCell(9); PoiUtil.copyCell(fromCellNumeric, toCellNumeric); PoiUtil.copyCell(fromCellFormula, toCellFormula); PoiUtil.copyCell(fromCellString, toCellString); PoiUtil.copyCell(fromCellBoolean, toCellBoolean); PoiUtil.copyCell(fromCellError, toCellError); PoiUtil.copyCell(fromCellDate, toCellDate); PoiUtil.copyCell(fromCellBlank, toCellBlank); PoiUtil.copyCell(fromCellNumericFrml, toCellNumericFrml); PoiUtil.copyCell(fromCellStringFrml, toCellStringFrml); PoiUtil.copyCell(fromCellBooleanFrml, toCellBooleanFrml); PoiUtil.copyCell(fromCellErrorFrml, toCellErrorFrml); PoiUtil.copyCell(fromCellDateFrml, toCellDateFrml); PoiUtil.copyCell(fromCellBlankFrml, toCellBlankFrml); try { // ? TestUtil.checkCell(fromCellNumeric, toCellNumeric); TestUtil.checkCell(fromCellFormula, toCellFormula); TestUtil.checkCell(fromCellString, toCellString); TestUtil.checkCell(fromCellBoolean, toCellBoolean); TestUtil.checkCell(fromCellError, toCellError); TestUtil.checkCell(fromCellDate, toCellDate); TestUtil.checkCell(fromCellBlank, toCellBlank); TestUtil.checkCell(fromCellNumericFrml, toCellNumericFrml); TestUtil.checkCell(fromCellStringFrml, toCellStringFrml); TestUtil.checkCell(fromCellBooleanFrml, toCellBooleanFrml); TestUtil.checkCell(fromCellErrorFrml, toCellErrorFrml); TestUtil.checkCell(fromCellDateFrml, toCellDateFrml); TestUtil.checkCell(fromCellBlankFrml, toCellBlankFrml); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.2 fromCell?null Cell toCell = sheet_1.getRow(0).createCell(10); PoiUtil.copyCell(null, toCell); // No.3 toCell?null try { PoiUtil.copyCell(fromCellNumeric, null); fail(); } catch (NullPointerException ex) { // toCell?null???? } // No.4 ? Cell toCellNumeric2 = sheet_2.getRow(0).createCell(0); PoiUtil.copyCell(fromCellNumeric, toCellNumeric2); // =============================================== // copyRange( Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange) // =============================================== // No.5 ? PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 3, false); try { TestUtil.checkCell(sheet_1.getRow(0).getCell(0), sheet_2.getRow(0).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.6 PoiUtil.copyRange(sheet_1, new CellRangeAddress(1, 12, 0, 1), sheet_2, 9, 0, false); try { TestUtil.checkCell(sheet_1.getRow(1).getCell(0), sheet_2.getRow(9).getCell(0)); TestUtil.checkCell(sheet_1.getRow(2).getCell(0), sheet_2.getRow(10).getCell(0)); TestUtil.checkCell(sheet_1.getRow(3).getCell(0), sheet_2.getRow(11).getCell(0)); TestUtil.checkCell(sheet_1.getRow(4).getCell(0), sheet_2.getRow(12).getCell(0)); TestUtil.checkCell(sheet_1.getRow(5).getCell(0), sheet_2.getRow(13).getCell(0)); TestUtil.checkCell(sheet_1.getRow(6).getCell(0), sheet_2.getRow(14).getCell(0)); TestUtil.checkCell(sheet_1.getRow(7).getCell(0), sheet_2.getRow(15).getCell(0)); TestUtil.checkCell(sheet_1.getRow(8).getCell(0), sheet_2.getRow(16).getCell(0)); TestUtil.checkCell(sheet_1.getRow(9).getCell(0), sheet_2.getRow(17).getCell(0)); TestUtil.checkCell(sheet_1.getRow(10).getCell(0), sheet_2.getRow(18).getCell(0)); TestUtil.checkCell(sheet_1.getRow(11).getCell(0), sheet_2.getRow(19).getCell(0)); TestUtil.checkCell(sheet_1.getRow(12).getCell(0), sheet_2.getRow(20).getCell(0)); TestUtil.checkCell(sheet_1.getRow(1).getCell(1), sheet_2.getRow(9).getCell(1)); TestUtil.checkCell(sheet_1.getRow(2).getCell(1), sheet_2.getRow(10).getCell(1)); TestUtil.checkCell(sheet_1.getRow(3).getCell(1), sheet_2.getRow(11).getCell(1)); TestUtil.checkCell(sheet_1.getRow(4).getCell(1), sheet_2.getRow(12).getCell(1)); TestUtil.checkCell(sheet_1.getRow(5).getCell(1), sheet_2.getRow(13).getCell(1)); TestUtil.checkCell(sheet_1.getRow(6).getCell(1), sheet_2.getRow(14).getCell(1)); TestUtil.checkCell(sheet_1.getRow(7).getCell(1), sheet_2.getRow(15).getCell(1)); TestUtil.checkCell(sheet_1.getRow(8).getCell(1), sheet_2.getRow(16).getCell(1)); TestUtil.checkCell(sheet_1.getRow(9).getCell(1), sheet_2.getRow(17).getCell(1)); TestUtil.checkCell(sheet_1.getRow(10).getCell(1), sheet_2.getRow(18).getCell(1)); TestUtil.checkCell(sheet_1.getRow(11).getCell(1), sheet_2.getRow(19).getCell(1)); TestUtil.checkCell(sheet_1.getRow(12).getCell(1), sheet_2.getRow(20).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.7 ?null PoiUtil.copyRange(null, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 0, false); PoiUtil.copyRange(sheet_1, null, sheet_2, 0, 0, false); PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), null, 0, 0, false); // No.8 ?? try { PoiUtil.copyRange(sheet_1, new CellRangeAddress(-1, 0, 0, 0), sheet_2, 0, 0, false); } catch (IllegalArgumentException ex) { // ??????? } // No.9 ?? PoiUtil.copyRange(sheet_1, new CellRangeAddress(23, 23, 0, 1), sheet_2, 22, 0, false); try { TestUtil.checkCell(sheet_1.getRow(23).getCell(0), sheet_2.getRow(22).getCell(0)); TestUtil.checkCell(sheet_1.getRow(23).getCell(1), sheet_2.getRow(22).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.10 ?? PoiUtil.copyRange(sheet_1, new CellRangeAddress(25, 26, 0, 0), sheet_2, 24, 0, false); try { TestUtil.checkCell(sheet_1.getRow(25).getCell(0), sheet_2.getRow(24).getCell(0)); TestUtil.checkCell(sheet_1.getRow(26).getCell(0), sheet_2.getRow(25).getCell(0)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.11 null PoiUtil.copyRange(sheet_1, new CellRangeAddress(30, 30, 0, 1), sheet_2, 29, 0, false); try { TestUtil.checkCell(sheet_1.getRow(30).getCell(0), sheet_2.getRow(29).getCell(0)); TestUtil.checkCell(sheet_1.getRow(30).getCell(1), sheet_2.getRow(29).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.12 null PoiUtil.copyRange(sheet_1, new CellRangeAddress(34, 34, 0, 3), sheet_2, 33, 0, false); assertNull(sheet_2.getRow(33)); // No.13 ?? Cell copyFrom1 = sheet_2.getRow(40).getCell(0); Cell copyFrom2 = sheet_2.getRow(40).getCell(1); Cell copyFrom3 = sheet_2.getRow(40).getCell(2); Cell copyFrom4 = sheet_2.getRow(41).getCell(0); Cell copyFrom5 = sheet_2.getRow(41).getCell(1); Cell copyFrom6 = sheet_2.getRow(41).getCell(2); PoiUtil.copyRange(sheet_2, new CellRangeAddress(40, 41, 0, 2), sheet_2, 41, 1, false); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(41).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_2.getRow(41).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_2.getRow(41).getCell(3)); TestUtil.checkCell(copyFrom4, sheet_2.getRow(42).getCell(1)); TestUtil.checkCell(copyFrom5, sheet_2.getRow(42).getCell(2)); TestUtil.checkCell(copyFrom6, sheet_2.getRow(42).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.14 ??? copyFrom1 = sheet_2.getRow(49).getCell(0); PoiUtil.copyRange(sheet_2, new CellRangeAddress(49, 49, 0, 0), sheet_2, 49, 2, true); assertNull(sheet_2.getRow(49).getCell(0)); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(49).getCell(2)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.15 ?? copyFrom1 = new CellClone(sheet_2.getRow(55).getCell(0)); copyFrom2 = new CellClone(sheet_2.getRow(55).getCell(1)); copyFrom3 = new CellClone(sheet_2.getRow(55).getCell(2)); copyFrom4 = new CellClone(sheet_2.getRow(56).getCell(0)); copyFrom5 = new CellClone(sheet_2.getRow(56).getCell(1)); copyFrom6 = new CellClone(sheet_2.getRow(56).getCell(2)); PoiUtil.copyRange(sheet_2, new CellRangeAddress(55, 56, 0, 2), sheet_2, 56, 1, true); assertNull(sheet_2.getRow(55).getCell(0)); assertNull(sheet_2.getRow(55).getCell(1)); assertNull(sheet_2.getRow(55).getCell(2)); assertNull(sheet_2.getRow(56).getCell(0)); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(56).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_2.getRow(56).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_2.getRow(56).getCell(3)); TestUtil.checkCell(copyFrom4, sheet_2.getRow(57).getCell(1)); TestUtil.checkCell(copyFrom5, sheet_2.getRow(57).getCell(2)); TestUtil.checkCell(copyFrom6, sheet_2.getRow(57).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // insertRangeDown( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.16 insertRangeDown copyFrom1 = sheet_3.getRow(1).getCell(1); copyFrom2 = sheet_3.getRow(1).getCell(2); copyFrom3 = sheet_3.getRow(2).getCell(1); copyFrom4 = sheet_3.getRow(2).getCell(2); PoiUtil.insertRangeDown(sheet_3, new CellRangeAddress(1, 2, 1, 2)); assertNull(sheet_3.getRow(1).getCell(1)); assertNull(sheet_3.getRow(1).getCell(2)); assertNull(sheet_3.getRow(2).getCell(1)); assertNull(sheet_3.getRow(2).getCell(2)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(3).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(3).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(4).getCell(1)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(4).getCell(2)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // insertRangeRight( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.17 insertRangeRight copyFrom1 = sheet_3.getRow(6).getCell(5); copyFrom2 = sheet_3.getRow(6).getCell(6); copyFrom3 = sheet_3.getRow(7).getCell(5); copyFrom4 = sheet_3.getRow(7).getCell(6); PoiUtil.insertRangeRight(sheet_3, new CellRangeAddress(6, 7, 5, 6)); assertNull(sheet_3.getRow(6).getCell(5)); assertNull(sheet_3.getRow(6).getCell(6)); assertNull(sheet_3.getRow(7).getCell(5)); assertNull(sheet_3.getRow(7).getCell(6)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(6).getCell(7)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(6).getCell(8)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(7).getCell(7)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(7).getCell(8)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // deleteRangeUp( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.18 deleteRangeUp copyFrom1 = sheet_3.getRow(13).getCell(9); copyFrom2 = sheet_3.getRow(13).getCell(10); copyFrom3 = sheet_3.getRow(14).getCell(9); copyFrom4 = sheet_3.getRow(14).getCell(10); PoiUtil.deleteRangeUp(sheet_3, new CellRangeAddress(11, 12, 9, 10)); assertNull(sheet_3.getRow(13).getCell(9)); assertNull(sheet_3.getRow(13).getCell(10)); assertNull(sheet_3.getRow(14).getCell(9)); assertNull(sheet_3.getRow(14).getCell(10)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(11).getCell(9)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(11).getCell(10)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(12).getCell(9)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(12).getCell(10)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // deleteRangeLeft( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.19 deleteRangeLeft copyFrom1 = sheet_3.getRow(16).getCell(15); copyFrom2 = sheet_3.getRow(16).getCell(14); copyFrom3 = sheet_3.getRow(17).getCell(15); copyFrom4 = sheet_3.getRow(17).getCell(14); PoiUtil.deleteRangeLeft(sheet_3, new CellRangeAddress(16, 17, 13, 14)); assertNull(sheet_3.getRow(16).getCell(15)); assertNull(sheet_3.getRow(16).getCell(16)); assertNull(sheet_3.getRow(17).getCell(15)); assertNull(sheet_3.getRow(17).getCell(16)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(16).getCell(13)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(16).getCell(14)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(17).getCell(13)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(17).getCell(14)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // clearRange( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.20 ???? PoiUtil.clearRange(sheet_4, new CellRangeAddress(0, 2, 0, 0)); assertNull(sheet_4.getRow(0).getCell(0)); assertNull(sheet_4.getRow(1).getCell(0)); assertNull(sheet_4.getRow(2).getCell(0)); assertEquals("4", sheet_4.getRow(3).getCell(0).getStringCellValue()); // No.21 ???? PoiUtil.clearRange(sheet_4, new CellRangeAddress(4, 5, 0, 1)); assertNull(sheet_4.getRow(4).getCell(0)); assertNull(sheet_4.getRow(5).getCell(0)); assertNull(sheet_4.getRow(4).getCell(1)); assertNull(sheet_4.getRow(5).getCell(1)); assertEquals("5C", sheet_4.getRow(4).getCell(2).getStringCellValue()); assertEquals("6C", sheet_4.getRow(5).getCell(2).getStringCellValue()); // No.22 ??? PoiUtil.clearRange(sheet_4, new CellRangeAddress(8, 8, 0, 1)); assertNull(null, sheet_4.getRow(8).getCell(0)); // No.23 ??? try { PoiUtil.clearRange(sheet_4, new CellRangeAddress(10, 10, 0, 0)); fail(); } catch (IllegalArgumentException ex) { // ???????????? } // ???????? assertEquals("11", sheet_4.getRow(10).getCell(0).getStringCellValue()); assertNotNull(sheet_4.getRow(10).getCell(1).getStringCellValue()); // No.24 ??? try { PoiUtil.clearRange(sheet_4, new CellRangeAddress(12, 12, 0, 0)); fail(); } catch (IllegalArgumentException ex) { // ???????????? } // ???????? assertEquals("13", sheet_4.getRow(12).getCell(0).getStringCellValue()); assertNotNull(sheet_4.getRow(13).getCell(0).getStringCellValue()); // =============================================== // clearCell( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.25 clearCell PoiUtil.clearCell(sheet_4, new CellRangeAddress(15, 16, 0, 0)); assertNull(sheet_4.getRow(15).getCell(0)); assertNull(sheet_4.getRow(15).getCell(0)); // =============================================== // setHyperlink( Cell cell, int type, String address) // =============================================== // No.26 setHyperlink Cell cellHyperlink = sheet_5.getRow(0).getCell(0); String address = "http://sourceforge.jp/projects/excella-core/"; PoiUtil.setHyperlink(cellHyperlink, HyperlinkType.URL, address); Hyperlink hyperLink = cellHyperlink.getHyperlink(); if (hyperLink instanceof HSSFHyperlink) { assertEquals(address, ((HSSFHyperlink) hyperLink).getTextMark()); } else if (hyperLink instanceof XSSFHyperlink) { assertEquals(address, ((XSSFHyperlink) hyperLink).getAddress()); } // =============================================== // setCellValue( Cell cell, Object value) // =============================================== // No.27 setCellValue Cell cellString = sheet_5.getRow(1).getCell(0); Cell cellNumber = sheet_5.getRow(1).getCell(1); Cell cellFloat = sheet_5.getRow(1).getCell(2); Cell cellDate = sheet_5.getRow(1).getCell(3); Cell cellBoolean = sheet_5.getRow(1).getCell(4); Cell cellNull = sheet_5.getRow(1).getCell(5); String stringValue = "aaa"; Number numberValue = new Double(10); Float floatValue = new Float(10f); Date dateValue = new Date(); Boolean booleanValue = Boolean.TRUE; PoiUtil.setCellValue(cellString, stringValue); PoiUtil.setCellValue(cellNumber, numberValue); PoiUtil.setCellValue(cellFloat, floatValue); PoiUtil.setCellValue(cellDate, dateValue); PoiUtil.setCellValue(cellBoolean, booleanValue); PoiUtil.setCellValue(cellNull, null); assertEquals(stringValue, cellString.getStringCellValue()); assertEquals(numberValue, cellNumber.getNumericCellValue()); assertEquals(new Double(String.valueOf(floatValue)), (Double) cellFloat.getNumericCellValue()); assertEquals(dateValue, cellDate.getDateCellValue()); assertEquals(booleanValue, cellBoolean.getBooleanCellValue()); assertNull(PoiUtil.getCellValue(cellNull)); // No.28 ?null try { PoiUtil.setCellValue(null, stringValue); fail(); } catch (NullPointerException ex) { // ?null???? } // =============================================== // getLastColNum( Sheet sheet) // =============================================== // No.29 int lastColNum1 = PoiUtil.getLastColNum(sheet_6); assertEquals(-1, lastColNum1); // No.30 ? int lastColNum2 = PoiUtil.getLastColNum(sheet_7); assertEquals(10, lastColNum2); }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ?//from w ww. j a v a 2 s . c om * * @param sheet * @param rowIndex */ private void removeControlRow(Sheet sheet, int rowIndex) { // ???? if (rowIndex == sheet.getLastRowNum()) { sheet.removeRow(sheet.getRow(rowIndex)); } else { sheet.removeRow(sheet.getRow(rowIndex)); // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.deleteRangeUp(sheet, rangeAddress); // sheet.shiftRows( rowIndex + 1, sheet.getLastRowNum(), -1, true, true); } }