List of usage examples for org.apache.poi.xssf.usermodel XSSFPrintSetup setFitWidth
public void setFitWidth(short width)
From source file:coverageqc.functions.MyExcelEditor.java
public static void excelFormator(XSSFSheet currentSheet, File variantTsvFile, String tsvHeadingLine) throws IOException { String[] headingsArray = tsvHeadingLine.split("\t"); HashMap<String, Integer> headings = new HashMap<String, Integer>(); for (int x = 0; x < headingsArray.length; x++) { headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); }//from w ww. j ava2 s. c om XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 1, currentSheet.getRow(0).getPhysicalNumberOfCells(), 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(headings.get("Consequence").intValue() + 2, 3500); currentSheet.setColumnHidden(headings.get("Classification").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Inherited From").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allelic Depths").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Custom Gene Annotation").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Num Transcripts").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("cDNA Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("CDS Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Protein Position").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Amino Acids").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Codons").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Transcript HGNC").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Canonical").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Sift").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("PolyPhen").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("ENSP").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSc").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("HGVSp").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("dbSNP ID").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Ancestral Allele").intValue() + 2, true); currentSheet.setColumnHidden(headings.get("Allele Freq").intValue() + 2, true); //everything beyond this is hidden for (int x = headings.get("Global Minor Allele").intValue() + 2; x < currentSheet.getRow(0) .getPhysicalNumberOfCells(); x++) { currentSheet.setColumnHidden(x, true); } }
From source file:coverageqc.functions.MyExcelGenerator.java
public void excelFormator(String sheetName, File variantTsvFile) throws IOException { // String[] headingsArray = tsvHeadingLine.split("\t"); // HashMap<String, Integer> headings = new HashMap<String, Integer>(); // for(int x = 0; x < headingsArray.length; x++) { // headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x); // }//from ww w .jav a 2 s . c om XSSFSheet currentSheet = this.workbookcopy.getSheet(sheetName); XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup(); File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx"); currentSheet.getHeader().setLeft(xslxTempFile.getName()); currentSheet.getHeader().setRight("DO NOT DISCARD!!! Keep with patient folder."); //in Dr. Carter's VBA was set at points 18 which is .25 inches currentSheet.setMargin(Sheet.RightMargin, .25); currentSheet.setMargin(Sheet.LeftMargin, .25); printSetup.setOrientation(PrintOrientation.LANDSCAPE); //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this //is the best workaround I can do, it will only looked cramped for those with a lot of calls printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 3); currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1")); currentSheet.setFitToPage(true); //making it by default not print the fellow's interp currentSheet.getWorkbook().setPrintArea(0, 2, 20, 0, currentSheet.getLastRowNum()); for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) { currentSheet.autoSizeColumn(x); if (x > 33) { currentSheet.setColumnHidden(x, true); } } currentSheet.setColumnWidth(0, 10000); currentSheet.setColumnWidth(1, 10000); currentSheet.setColumnWidth(2, 10000); // currentSheet.setColumnWidth(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, 3500); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Gene"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Variant"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Chr"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Coordinate"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Genotype"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Exonic"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Filters"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Quality"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("GQX"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Variant Freq"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Read Depth"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Global Minor"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Classification"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Inherited From"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allelic Depths"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Gene Annotation"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Num Transcripts"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("cDNA Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("CDS Position"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Protein Position"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Amino Acids"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Codons"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript HGNC"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Canonical"))+3, true); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false); // //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ENSP"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSc"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSp"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("dbSNP ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Ancestral Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Global Minor Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Amr"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Asn"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Af"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Eur"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Evs"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Coverage"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Samples"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Conserved Sequence"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC ID"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Wildtype"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Allele"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Gene"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Primary Site"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Histology"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Accession"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Ref"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Alleles"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Allele Type"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Significance"))+3, false); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alternate Alleles"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Google Scholar"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PubMed"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("UCSC Browser"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar RS"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Disease Name"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar MedGen"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar OMIM"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Orphanet"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar GeneReviews"))+3, true); // currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar SnoMedCt ID"))+3, true); }
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/* www . j a v a2 s. com*/ if (groepen == null) return; // sheetindx geeft index in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] sheetindx = new int[] { -1, -1, -1, -1, 5, -1, 4, -1, 3, -1, 2, -1, 1, -1, 0, -1, -1, -1 }; // columnsize geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] columnsize = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; // pagelngth geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] pagelngth = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; int sheet2row = 2; int sheet3row = 2; FileInputStream file = new FileInputStream("Indeling.xlsm"); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFCellStyle style1 = workbook.createCellStyle(); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(180, 180, 180))); XSSFCellStyle my_style = workbook.createCellStyle(); XSSFColor my_foreground = new XSSFColor(Color.ORANGE); XSSFColor my_background = new XSSFColor(Color.RED); my_style.setFillPattern(FillPatternType.SOLID_FOREGROUND); my_style.setFillForegroundColor(my_foreground); my_style.setFillBackgroundColor(my_background); XSSFSheet sheet2 = workbook.getSheet("Groepsindeling"); XSSFSheet sheet3 = workbook.getSheet("Deelnemerslijst"); updateCell(sheet3, sheet3row, 0, "Naam", style1); updateCell(sheet3, sheet3row, 1, "KNSB nr", style1); updateCell(sheet3, sheet3row, 2, "rating", style1); updateCell(sheet3, sheet3row, 3, "groep", style1); sheet3row++; for (Groep groep : groepen) { logger.log(Level.INFO, "Exporteer groep : " + groep.getNaam()); XSSFSheet sheet = workbook.cloneSheet(sheetindx[groep.getGrootte()], groep.getNaam()); updateCell(sheet, 0, 6, groep.getNaam()); updateCell(sheet2, sheet2row, 1, groep.getNaam()); sheet2row++; updateCell(sheet2, sheet2row, 0, "nr", style1); updateCell(sheet2, sheet2row, 1, "Naam", style1); updateCell(sheet2, sheet2row, 2, "KNSB nr", style1); updateCell(sheet2, sheet2row, 3, "rating", style1); sheet2row++; for (int i = 0; i < groep.getGrootte(); i++) { updateCell(sheet, 3 + i, 2, groep.getSpeler(i).getNaam()); updateCell(sheet, 3 + i, 3, groep.getSpeler(i).getKnsbnummer()); updateCell(sheet, 3 + i, 5, groep.getSpeler(i).getRating()); updateCell(sheet2, sheet2row, 0, i + 1); updateCell(sheet2, sheet2row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet2, sheet2row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet2, sheet2row, 3, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); if (groep.getSpeler(i).getNaam() != "Bye") { updateCell(sheet3, sheet3row, 0, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet3, sheet3row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet3, sheet3row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); updateCell(sheet3, sheet3row, 3, groep.getNaam()); } sheet2row++; sheet3row++; } sheet2row++; sheet.setForceFormulaRecalculation(true); // Set print margins XSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setAutobreaks(false); workbook.setPrintArea(workbook.getSheetIndex(sheet), 1, 26, 0, pagelngth[groep.getGrootte()]); sheet.setColumnBreak(18); sheet.protectSheet(password); sheet.enableLocking(); } XSSFSheet sheet4 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (naam)"); sortSheet(sheet4, 1, 3, 62); //XSSFSheet sheet5 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (rating)"); //sortSheet(sheet5, 1,4); sheet2.protectSheet(password); sheet3.protectSheet(password); sheet4.protectSheet(password); //sheet5.protectSheet(password); // Remove template sheets for (int i = 0; i < 6; i++) { workbook.removeSheetAt(0); } // Close input file file.close(); // Store Excel to new file String filename = "Indeling resultaat.xlsm"; File outputFile = new File(filename); FileOutputStream outFile = new FileOutputStream(outputFile); workbook.write(outFile); // Close output file workbook.close(); outFile.close(); // And open it in the system editor Desktop.getDesktop().open(outputFile); } catch (IOException e) { logger.log(Level.SEVERE, "Fout bij maken indeling excel : " + e.getMessage()); } }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFSheet createTableListSheet(final DatabaseModel datasource, final XSSFSheet sheet) { // // www . j a v a 2s . c o m sheet.setColumnWidth(0, 640 * 1); sheet.setColumnWidth(1, 640 * 2); // No sheet.setColumnWidth(2, 640 * 10); // ??? sheet.setColumnWidth(3, 640 * 10); // ??? sheet.setColumnWidth(4, 640 * 15); // sheet.setColumnWidth(5, 640 * 1); ///////////////////////////////////////////////////////////////////// XSSFCellStyle styleLabel = workbook.createCellStyle(); styleLabel.setFillPattern(CellStyle.SOLID_FOREGROUND); styleLabel.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); styleLabel.setFont(fontLabel); styleLabel.setBorderTop(CellStyle.BORDER_THIN); styleLabel.setBorderBottom(CellStyle.BORDER_THIN); styleLabel.setBorderLeft(CellStyle.BORDER_THIN); styleLabel.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue1 = workbook.createCellStyle(); styleValue1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue1.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue1.setFont(fontValue); styleValue1.setBorderTop(CellStyle.BORDER_DOTTED); styleValue1.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue1.setBorderLeft(CellStyle.BORDER_THIN); styleValue1.setBorderRight(CellStyle.BORDER_THIN); XSSFCellStyle styleValue2 = workbook.createCellStyle(); styleValue2.setFillPattern(CellStyle.SOLID_FOREGROUND); styleValue2.setFillForegroundColor(IndexedColors.WHITE.getIndex()); styleValue2.setFont(fontLink); styleValue2.setBorderTop(CellStyle.BORDER_DOTTED); styleValue2.setBorderBottom(CellStyle.BORDER_DOTTED); styleValue2.setBorderLeft(CellStyle.BORDER_THIN); styleValue2.setBorderRight(CellStyle.BORDER_THIN); ///////////////////////////////////////////////////////////////////// XSSFRow row = null; @SuppressWarnings("unused") XSSFCell cell = null; int rowIndex = 1; //////////////////////////////////////////////////////////////////////////// // //////////////////////////////////////////////////////////////////////////// row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.table_list"), styleTitle, row); List<TableModel> tables = datasource.getTables(); short top = BD_RECT; short bottom = CellStyle.BORDER_THIN; if (0 == tables.size()) { bottom = BD_RECT; } rowIndex++; row = sheet.createRow(rowIndex); /////////////////////////////////////////// cell = createCell(1, Strings.get("doc.no"), styleManager.get(defStyleLabel, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, Strings.get("doc.logic_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, Strings.get("doc.physical_table_name"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(4, Strings.get("doc.memo"), styleManager.get(defStyleLabel, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); rowIndex++; for (int i = 0; i < tables.size(); i++) { int bufRowIndex = rowIndex + i; TableModel table = tables.get(i); Hyperlink link = createTableLink(table.getName()); top = CellStyle.BORDER_DOTTED; bottom = CellStyle.BORDER_DOTTED; if (i == 0) { top = CellStyle.BORDER_THIN; } if (i + 1 == tables.size()) { bottom = BD_RECT; } row = sheet.createRow(bufRowIndex); /////////////////////////////////////////// cell = createCell(1, String.format("%d", i + 1), styleManager.get(defStyleListValueNo, top, bottom, BD_RECT, CellStyle.BORDER_THIN), row); cell = createCell(2, table.getLabel(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), row); cell = createCell(3, table.getName(), styleManager.get(defStyleListValueLink, top, bottom, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN), link, row); cell = createCell(4, table.getComment(), styleManager.get(defStyleListValue, top, bottom, CellStyle.BORDER_THIN, BD_RECT), row); } rowIndex += tables.size(); workbook.setPrintArea(workbook.getSheetIndex(getTableListSheetName()), 0, 5, 0, rowIndex); sheet.setAutobreaks(true); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setFitWidth((short) 1); printSetup.setScale((short) 95); return sheet; }
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) { // // ww w. j a va 2 s . c o m 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; }