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:mn.tsagaangeruud.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* w w w . ja v a 2s .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("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:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java
License:Open Source License
private File createExcelTempFile(Map<AbstractEntity, Map<Service, List<ArticleDemande>>> map, AbstractEntity abstractEntity, String nomFichierSansExtension) throws IOException { Workbook wb = new XSSFWorkbook(); Map<Service, List<ArticleDemande>> mapServiceListeArticleDemande = map.get(abstractEntity); for (Service service : mapServiceListeArticleDemande.keySet()) { Sheet sheet = wb .createSheet(service.getDirection().getLibelleCourt() + "-" + service.getLibelleCourt()); sheet.protectSheet(configService.getMotDePasseVerrouExcel()); construitEntete(wb, mapServiceListeArticleDemande, abstractEntity, service, sheet); remplitLigneArticle(wb, mapServiceListeArticleDemande.get(service), sheet); // Taille automatique des colonnes selon le contenu for (int i = 0; i < 20; i++) { sheet.autoSizeColumn(i);//from w w w . ja v a 2s . c o m sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 768); } } // Ecriture dans le fichier File result = File.createTempFile(nomFichierSansExtension, ".xlsx"); FileOutputStream fileStream = new FileOutputStream(result); wb.write(fileStream); return result; }
From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java
License:Open Source License
@Override public void exportCatalogue(Catalogue catalogue) throws IOException { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Feuil1"); construitEnteteExportCatalogue(wb, sheet); int i = 1;//from ww w . j a v a 2 s. co m for (Famille famille : catalogue.getListeFamille()) { for (SousFamille sousFamille : famille.getListeSousFamille()) { for (ArticleCatalogue articleCatalogue : sousFamille.getListeArticleCatalogue()) { remplitLigneArticleExportCatalogue(wb, sheet, articleCatalogue, i++); } } } // Taille automatique des colonnes selon le contenu for (int colonne = 0; colonne <= 12; colonne++) { sheet.autoSizeColumn(colonne); sheet.setColumnWidth(colonne, sheet.getColumnWidth(colonne) + 768); } // Ecriture dans le fichier File result = File.createTempFile("Export Catalogue", ".xlsx"); FileOutputStream fileStream = new FileOutputStream(result); wb.write(fileStream); downloadService.downloadToUser(result, "Export Catalogue " + AppockUtil.replaceAllSpecialCharacter(catalogue.getLibelle()) + ".xlsx"); result.delete(); }
From source file:net.algem.planning.export.PlanningExportService.java
License:Open Source License
/** * Export to Excel destination file.// ww w.j a va 2 s . co m * * @param dayPlan list of day schedules * @param destFile destination file * @throws IOException */ public void exportPlanning(List<DayPlan> dayPlan, File destFile) throws IOException { GemLogger.info("Exporting planning to " + destFile); Hour defStartTime = new Hour(ConfigUtil.getConf(ConfigKey.START_TIME.getKey())); int offset = defStartTime.getHour(); int totalh = 24 - offset; // total time length in hours HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Planning"); if (dayPlan.size() > 0) { DateFormat df = new SimpleDateFormat("EEEE dd MMM yyyy"); Header header = sheet.getHeader(); String hd = df.format(dayPlan.get(0).getSchedule().get(0).getDate().getDate()); header.setCenter(HSSFHeader.fontSize((short) 12) + HSSFHeader.startBold() + hd + HSSFHeader.endBold()); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(paperSize); sheet.setFitToPage(true); sheet.setHorizontallyCenter(false);// was true before 2.15.8 sheet.setMargin(Sheet.TopMargin, 0.75); // 1.905 sheet.setMargin(Sheet.BottomMargin, 0.4); // 0.4 inch = 1.016 cm sheet.setMargin(Sheet.LeftMargin, 0.4); sheet.setMargin(Sheet.RightMargin, 0.4); Map<String, CellStyle> styles = createStyles(workbook); Row headerRow = sheet.createRow(0); for (int i = 0; i < dayPlan.size(); i++) { Cell roomCell = headerRow.createCell(i + 1); // Set the width (in units of 1/256th of a character width) //sheet.setColumnWidth(i + 1, totalh * 256);// max number of characters must not depend of time length sheet.setColumnWidth(i + 1, 24 * 256); // cours.titre character varying(32) roomCell.setCellValue(dayPlan.get(i).getLabel()); roomCell.setCellStyle(styles.get("header")); } int offsetMn = offset * 60;// offset in minutes List<Row> rows = new ArrayList<>(); System.out.println(" offset = " + offset + " totalh = " + totalh); for (int t = 0, rowNumber = 1; t < totalh * 60; t += 5, rowNumber++) { // 1 row = 5mn Hour hour = new Hour(offsetMn + t); Row row = sheet.createRow(rowNumber); //row.setHeightInPoints(25); row.setHeightInPoints(PrintSetup.A3_PAPERSIZE == paperSize ? 12 : 6); // TIME SUBDIVISIONS if (t % 15 == 0) { Cell cell = row.createCell(0); if (t % 30 == 0) { cell.setCellValue(hour.toString());//show time if (t % 60 == 0) { cell.setCellStyle(styles.get("hour")); } else { cell.setCellStyle(styles.get("hour-half")); } } else { cell.setCellStyle(styles.get("hour-quarter")); } } else { // BETWEEN SUBDIVISION Cell cell = row.createCell(0); if ("23:55".equals(hour.toString())) { // last slice cell.setCellStyle(styles.get("hour-last")); } else { cell.setCellStyle(styles.get("hour")); } if (rowNumber % 3 == 0) { // merge every 3 rows sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber, 0, 0)); } } rows.add(row); } Map<java.awt.Color, CellStyle> coursStyleCache = new HashMap<>(); for (int i = 0; i < dayPlan.size(); i++) { DayPlan plan = dayPlan.get(i); int col = i + 1; for (ScheduleObject event : plan.getSchedule()) { // if event starts before default starting time if (event.getStart().toMinutes() < offsetMn) { event.setStart(new Hour(offset * 60)); } int startRowPos = (event.getStart().toMinutes() - offsetMn) / 5 + 1; int endRowPos = (event.getEnd().toMinutes() - offsetMn) / 5; Cell courseCell = rows.get(startRowPos - 1).createCell(col); courseCell.setCellValue(getLabel(event, workbook));// title text CellStyle style = getCourseStyle(workbook, event, coursStyleCache); courseCell.setCellStyle(style); if (startRowPos != endRowPos) { sheet.addMergedRegion(new CellRangeAddress(startRowPos, endRowPos, col, col)); for (int row = startRowPos; row < endRowPos; row++) { rows.get(row).createCell(col).setCellStyle(style); } } } } try (FileOutputStream out = new FileOutputStream(destFile)) { workbook.write(out); } }
From source file:net.rrm.ehour.ui.common.report.AbstractExcelReport.java
License:Open Source License
/** * Create the workbook/*from w ww . j a v a2s. c om*/ */ protected ExcelWorkbook createWorkbook(Report treeReport) { ExcelWorkbook wb = new ExcelWorkbook(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(getExcelReportName().getObject())); int rowNumber = 0; short column; for (column = 0; column < 4; column++) { sheet.setColumnWidth(column, 5000); } for (; column < 7; column++) { sheet.setColumnWidth(column, 3000); } rowNumber = createHeaders(rowNumber, sheet, treeReport, wb); rowNumber = addColumnHeaders(rowNumber, sheet, wb); fillReportSheet(treeReport, sheet, rowNumber, wb); return wb; }
From source file:net.rrm.ehour.ui.timesheet.export.TimesheetExcelExport.java
License:Open Source License
private ExcelWorkbook createWorkbook(Report report) { ExcelWorkbook workbook = new ExcelWorkbook(); String sheetName = WebUtils.formatDate("MMMM yyyy", report.getReportRange().getDateStart()); Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName)); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.DATE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.CUSTOMER_CODE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.PROJECT_CODE.getColumn())); sheet.autoSizeColumn((short) (CELL_BORDER + ExportReportColumn.HOURS.getColumn())); sheet.setColumnWidth(0, 1024); int rowNumber = 9; rowNumber = new ExportReportHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportBodyHeader(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportBody(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); rowNumber = new ExportReportTotal(CELL_BORDER, sheet, report, workbook).createPart(rowNumber); if (isInclSignOff(report)) { new ExportReportSignOff(CELL_BORDER, sheet, report, workbook).createPart(rowNumber + 1); }//from ww w .j a v a 2 s .c o m return workbook; }
From source file:org.aio.handy.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;// w ww . j av a2 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("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 = "e:/timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * /*from w ww.j a va2 s . com*/ * Note, this method will not update any formula references. * * @param sheet * @param column */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) continue; int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) maxColumn = lastColumn; // if the row doesn't have this many columns then we are good; next! if (lastColumn < columnToDelete) continue; for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) row.removeCell(oldCell); Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = 0; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body.//from w w w . j a v a 2 s. co m */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER); CSVStrategy reqCSVstrategy = null; if (delimiterParam != null && !delimiterParam.isEmpty()) { reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED); } // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle(dictionaryService) != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(dictionaryService); descriptions[i] = pd.getDescription(dictionaryService); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy()); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); sheet.createFreezePane(0, 1); Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ?// w ww. j a v a2 s .c om * * @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); } }