private void printPreferencias() { int rowNum; XSSFSheet sheet = report.createSheet("Preferencias"); rowNum = 0; for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) { XSSFRow row = sheet.createRow(rowNum++); int cellNum = 0; if (rowNum == 1) { XSSFCell cell = row.createCell(cellNum++); cell.setCellValue("Id 1"); cell = row.createCell(cellNum++); cell.setCellValue("Id 2"); cell = row.createCell(cellNum++); cell.setCellValue("Satisfecha?"); cell = row.createCell(cellNum++); cellNum = 0; row = sheet.createRow(rowNum++); } XSSFCell cell = row.createCell(cellNum++); cell.setCellValue(p.getAlumno1().getId()); cell = row.createCell(cellNum++); cell.setCellValue(p.getAlumno2().getId()); cell = row.createCell(cellNum++); cell.setCellValue(p.getIsSatisfecha().toString()); cell = row.createCell(cellNum++); } }
private XSSFSheet createTypeSheet(XSSFWorkbook workBook) { XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME); List<ProductType> allTypes = productTypeDao.getAll(); Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。 List<AdjacencyNode<ProductType>> adjacencyNodes = new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes); int rownum = 0; XSSFRow typeTitleRow = typeSheet.createRow(rownum++); typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称"); typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号"); typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号"); for (AdjacencyNode<ProductType> productType : adjacencyNodes) { XSSFRow row = typeSheet.createRow(rownum); XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING); typeCell.setCellValue(productType.getNode().getName()); XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING); itemCell.setCellValue(productType.getNode().getItem()); if (productType.getParent() != null) { XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING); parentItemCell.setCellValue(productType.getParent().getItem()); } rownum++; } return typeSheet; }
private void printCurso(Curso c) { int rowNum; XSSFSheet sheet = report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)")); rowNum = 0; for (Alumno a : c.getAlumnos()) { XSSFRow row = sheet.createRow(rowNum++); int cellNum = 0; if (rowNum == 1) { XSSFCell cell = row.createCell(cellNum++); cell.setCellValue("Id"); cell = row.createCell(cellNum++); cell.setCellValue("Apellido"); cell = row.createCell(cellNum++); cell.setCellValue("Nombre"); cell = row.createCell(cellNum++); cellNum = 0; row = sheet.createRow(rowNum++); } XSSFCell cell = row.createCell(cellNum++); cell.setCellValue(a.getId()); cell = row.createCell(cellNum++); cell.setCellValue(a.getApellido()); cell = row.createCell(cellNum++); cell.setCellValue(a.getNombres()); cell = row.createCell(cellNum++); } }
public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx"); wb.write(fileOut); fileOut.close(); }
/** * 指定した行番号の<b>XSSFRow</b>を取得する。有効範囲外の行の場合は新規作成する。 * * @param sheet <b>XSSFSheet</b> * @param nRow 取得したい行の行番号 * @return <b>XSSFRow</b> */ public static XSSFRow getRowAnyway(XSSFSheet sheet, int nRow) { assert sheet != null; XSSFRow row = sheet.getRow(nRow); if (row == null) { row = sheet.createRow(nRow); } return row; }
// returns true if data is set successfully else false public boolean setCellData( String sheetName, String colName, int rowNum, String data, String url) { // System.out.println("setCellData setCellData******************"); try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); if (rowNum <= 0) return false; int index = workBook.getSheetIndex(sheetName); int colNum = -1; if (index == -1) return false; sheet = workBook.getSheetAt(index); // System.out.println("A"); row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { // System.out.println(row.getCell(i).getStringCellValue().trim()); if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i; } if (colNum == -1) return false; sheet.autoSizeColumn(colNum); // ashish row = sheet.getRow(rowNum - 1); if (row == null) row = sheet.createRow(rowNum - 1); cell = row.getCell(colNum); if (cell == null) cell = row.createCell(colNum); cell.setCellValue(data); XSSFCreationHelper createHelper = workBook.getCreationHelper(); // cell style for hyperlinks // by default hypelrinks are blue and underlined CellStyle hlink_style = workBook.createCellStyle(); XSSFFont hlink_font = workBook.createFont(); hlink_font.setUnderline(XSSFFont.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // hlink_style.setWrapText(true); XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE); link.setAddress(url); cell.setHyperlink(link); cell.setCellStyle(hlink_style); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
public void generate4Dispatches( List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents) throws IOException { for (int i = 0; i < sheetNames.size(); i++) { // Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i)); // Create row object XSSFRow row; // This data needs to be written (Object[]) Map<Integer, String[]> contentMap = new TreeMap<>(); contentMap.put(1, headersList.get(i)); for (int j = 0; j < contents.get(i).size(); j++) { contentMap.put(j + 2, contents.get(i).get(j)); } // Iterate over data and write to sheet Set<Integer> keyid = contentMap.keySet(); int rowid = 0; for (Integer key : keyid) { row = spreadsheet.createRow(rowid++); String[] objectArr = contentMap.get(key); int cellid = 0; for (String obj : objectArr) { if (obj.contains("\r\n")) { String[] strArray = obj.split("\r\n"); for (String str : strArray) { Cell cell = row.createCell(cellid); cell.setCellValue(str); row = spreadsheet.createRow(rowid++); } } else { Cell cell = row.createCell(cellid++); cell.setCellValue(obj); } } } // for (int c = 0; c < headersList.get(i).length; c++) { // spreadsheet.autoSizeColumn(c); // } // Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } }
public <S, L> void rulewriter(Map<S, L> map) throws IOException { FileOutputStream fos = new FileOutputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet xsheet = workbook.createSheet(); int count = 0; XSSFRow row = xsheet.createRow(count++); row.createCell(0).setCellValue("Error ID"); row.createCell(1).setCellValue("Rule ID"); for (Entry<S, L> entry : map.entrySet()) { row = xsheet.createRow(count); row.createCell(0).setCellValue((String) entry.getKey()); row.createCell(1).setCellValue(entry.getValue().toString()); count++; } workbook.write(fos); fos.close(); }
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) { XSSFRow r = sheet.getRow(row); if (null == r) { r = sheet.createRow(row); } XSSFCell cell = r.getCell(col); if (null == cell) { cell = sheet.getRow(row).createCell(col); } cell.setCellStyle(style); }
public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) { XSSFRow r = sheet.getRow(row); if (null == r) { r = sheet.createRow(row); } XSSFCell cell = r.getCell(col); if (null == cell) { cell = sheet.getRow(row).createCell(col); } setCellValueOfCnX(cell, colValue); cell.getCellStyle().setFont(font); }
public static void addErrorDescriptionRow(int rowNum, String field, String errorDescription) { try { XSSFRow destRow = sheetWrite2.createRow(rowCount); XSSFCell cell = destRow.createCell(0); cell.setCellValue(Integer.toString(rowNum)); cell = destRow.createCell(1); cell.setCellValue(field); cell = destRow.createCell(2); cell.setCellValue(errorDescription); } catch (Exception ex) { System.out.println( "Error in writing descriptionRow in reject excel file :: " + ex.getMessage()); ex.printStackTrace(); } }
public void fillData(List<Mobile> list, XSSFWorkbook workbook, XSSFSheet sheet) { int j = 0; int rowIndex = 2; int stt = 1; // XSSFCellStyle style1 = workbook.createCellStyle(); // style1.setFillForegroundColor(HSSFColor.AQUA.index); // style1.setFillPattern(CellStyle.SOLID_FOREGROUND); // XSSFCreationHelper createHelper = workbook.getCreationHelper(); // XSSFCellStyle style2 = workbook.createCellStyle(); // style2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); ExcelUtil ex = new ExcelUtil(workbook); for (int i = 0; i < list.size(); i++) { Row row = sheet.createRow(rowIndex++); j = 0; row.createCell(j++).setCellValue(stt++); row.createCell(j++).setCellValue(list.get(i).getWeb()); row.createCell(j++).setCellValue(list.get(i).getType()); row.createCell(j++).setCellValue(list.get(i).getBrand()); if (list.get(i).getPriceNumber() != null) { ex.formatNumber(row, j++, list.get(i).getPriceNumber(), ex.number); } else { j++; } row.createCell(j++).setCellValue(list.get(i).getItemCode()); row.createCell(j++).setCellValue(list.get(i).getName()); row.createCell(j++).setCellValue(list.get(i).getModel()); row.createCell(j++).setCellValue(list.get(i).getStorage()); row.createCell(j++).setCellValue(list.get(i).getRam()); row.createCell(j++).setCellValue(list.get(i).getScreen()); row.createCell(j++).setCellValue(list.get(i).getCpu()); row.createCell(j++).setCellValue(list.get(i).getBackCamera()); row.createCell(j++).setCellValue(list.get(i).getFrontCamera()); row.createCell(j++).setCellValue(list.get(i).getOs()); row.createCell(j++).setCellValue(list.get(i).getBattery()); row.createCell(j++).setCellValue(list.get(i).getSim()); row.createCell(j++).setCellValue(list.get(i).getColor()); row.createCell(j++).setCellValue(list.get(i).getPromotion()); // Cell cell = row.createCell(j++); // cell.setCellValue(list.get(i).getLastUpdate()); // cell.setCellStyle(style2); ex.formatDate(row, j++, list.get(i).getLastUpdate(), ex.date); row.createCell(j++).setCellValue(list.get(i).getLink()); } }
public boolean setCellData(String sheetName, String colName, int rowNum, String data) { try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); if (rowNum <= 0) return false; int index = workBook.getSheetIndex(sheetName); int colNum = -1; if (index == -1) return false; sheet = workBook.getSheetAt(index); row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { // System.out.println(row.getCell(i).getStringCellValue().trim()); if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i; } if (colNum == -1) return false; sheet.autoSizeColumn(colNum); row = sheet.getRow(rowNum - 1); if (row == null) row = sheet.createRow(rowNum - 1); cell = row.getCell(colNum); if (cell == null) cell = row.createCell(colNum); // cell style // CellStyle cs = workbook.createCellStyle(); // cs.setWrapText(true); // cell.setCellStyle(cs); cell.setCellValue(data); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
private XSSFRow createRowFrom(IRecordable o) { XSSFRow lastRow, r; String[] data; if (dicoLocations.containsKey(o.getClassement())) lastRow = dicoLocations.get(o.getClassement()); else { XSSFSheet logSheet = wb.createSheet(o.getClassement()); lastRow = logSheet.createRow(0); data = o.getTitles(); fillRowWith(lastRow, data); dicoLocations.put(o.getClassement(), lastRow); } r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1); data = o.getRecords(); fillRowWith(r, data); dicoLocations.replace(o.getClassement(), r); return r; }
/** * @param newSheet the sheet to create from the copy. * @param sheet the sheet to copy. * @param copyStyle true copy the style. */ public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
public static void addErrorRow(XSSFRow srcRow) { try { XSSFRow destRow = sheetWrite.createRow(rowCount); // XSSFRow destRow =null; for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { XSSFCell oldCell = srcRow.getCell(j); // ancienne cell XSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } copyCell(oldCell, newCell); } } } catch (Exception ex) { System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage()); ex.printStackTrace(); } }
public static void main(String[] args) throws IOException { File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx"); FileInputStream fis = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = sheet.getRow(0); XSSFCell cell = row.getCell(0); System.out.println(cell); XSSFSheet write = workbook.createSheet(); XSSFRow rowwrite = write.createRow(0); XSSFCell cell1 = rowwrite.createCell(1); cell1.setCellValue("Yamini"); FileOutputStream file1 = new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx"); workbook.write(file1); file1.close(); }
// returns true if column is created successfully public boolean addColumn(String sheetName, String colName) { // System.out.println("**************addColumn*********************"); try { fis = new FileInputStream(path); workBook = new XSSFWorkbook(fis); int index = workBook.getSheetIndex(sheetName); if (index == -1) return false; XSSFCellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); sheet = workBook.getSheetAt(index); row = sheet.getRow(0); if (row == null) row = sheet.createRow(0); // cell = row.getCell(); // if (cell == null) // System.out.println(row.getLastCellNum()); if (row.getLastCellNum() == -1) cell = row.createCell(0); else cell = row.createCell(row.getLastCellNum()); cell.setCellValue(colName); cell.setCellStyle(style); fos = new FileOutputStream(path); workBook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); return false; } return true; }
public static void WriteCol(String QueryType, String num) throws IOException { Map<String, Object[]> data = new HashMap<String, Object[]>(); List<String> sqlDML = new ArrayList<String>(); File myfile = new File("C://Users/Administrator/Documents/Heckathon/Result.xlsx"); FileInputStream fis = new FileInputStream(myfile); XSSFWorkbook myworkbook = new XSSFWorkbook(fis); XSSFSheet mysheet = myworkbook.getSheetAt(2); data.put(num, new Object[] {QueryType, num}); Set<String> newRows = data.keySet(); int rownum = mysheet.getPhysicalNumberOfRows(); for (String Key : newRows) { Row row = mysheet.createRow(rownum++); Object[] objArr = data.get(Key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue((String) obj); } } FileOutputStream os = new FileOutputStream(myfile); myworkbook.write(os); System.out.println("Record Entered"); }
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet overall = workbook.createSheet("Overall"); XSSFRow row = overall.createRow(0); XSSFCellStyle topStyle = workbook.createCellStyle(); topStyle.setAlignment(CellStyle.ALIGN_CENTER); XSSFCell theme = row.createCell(0); theme.setCellValue("Theme"); overall.autoSizeColumn(0); XSSFCell occurs = row.createCell(1); occurs.setCellValue("Occurrences"); overall.autoSizeColumn(1); XSSFCell prev = row.createCell(2); prev.setCellValue("Prevalence"); overall.autoSizeColumn(2); theme.setCellStyle(topStyle); occurs.setCellStyle(topStyle); prev.setCellStyle(topStyle); for (int i = 0; i < themes.size(); i++) { XSSFRow r = overall.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(themes.get(i).getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(themes.get(i).getTotalOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount)); } // This could be done in the previous loop but since we don't need // indices as much, we may as well use the cleaner for each loop for (Theme t : themes) { XSSFSheet themeSheet = workbook.createSheet(t.getName()); XSSFRow row1 = themeSheet.createRow(0); XSSFCell keyword = row1.createCell(0); keyword.setCellValue("Keyword"); keyword.setCellStyle(topStyle); XSSFCell occ = row1.createCell(1); occ.setCellValue("Occurrences"); occ.setCellStyle(topStyle); XSSFCell themePrev = row1.createCell(2); themePrev.setCellValue("Prevalence"); themePrev.setCellStyle(topStyle); for (int i = 0; i < t.getKeywords().size(); i++) { Keyword k = t.getKeywords().get(i); XSSFRow r = themeSheet.createRow((i + 1)); XSSFCell c = r.createCell(0); c.setCellValue(k.getName()); XSSFCell c1 = r.createCell(1); c1.setCellValue(k.getNumOccurs()); XSSFCell c2 = r.createCell(2); c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs())); } } FileOutputStream output = new FileOutputStream(docName); workbook.write(output); output.close(); }
public static boolean writeArray2XLSXFile( String FileName, String[] titleNames, List<String[]> arry) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = titleNames.length; int rowNum = arry.size(); // for (int i = 0; i < columnNum; i++) { // sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20))); // } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont headFont = wb.createFont(); headFont.setFontName("Courier New"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headFont.setColor(HSSFColor.BLACK.index); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headStyle.setFillForegroundColor(HSSFColor.BLACK.index); headStyle.setLocked(true); headStyle.setFont(headFont); headStyle.setBorderTop((short) 2); headStyle.setBorderBottom((short) 1); XSSFCellStyle contentStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont contentFont = wb.createFont(); contentFont.setFontName("Courier New"); contentFont.setFontHeightInPoints((short) 9); // headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); contentFont.setColor(HSSFColor.BLACK.index); contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); contentStyle.setFillForegroundColor(HSSFColor.BLACK.index); contentStyle.setFont(contentFont); // create titile row XSSFRow row = sheet1.createRow(0); int heandLine = 0; XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } heandLine++; } for (int i = 0; i < rowNum; i++) { row = sheet1.createRow((i + heandLine)); String[] line = (String[]) arry.get(i); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(FileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeArray2XLSXSheet( XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception { int rowNum = arry.size(); if (rowNum == 0) { System.err.println("No input data!"); return false; } String[] titleNames = null; if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20))); } XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); XSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell((short) i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[0] != null) { cell.setCellStyle(markedBodyStyle); } else { cell.setCellStyle(bodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue(""); } } } return true; }
public static boolean convertTextFile2XLSXFile( String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception { BufferedReader br = LocalFileFunc.getBufferedReader(inFileName); String line = br.readLine(); if (line == null) { return false; } String[] cells1 = Util.tokenize(line, '\t'); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = cells1.length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); String lastKey = null; int switcher = -1; XSSFCell cell = null; if (hasHead) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(headStyle); } } else { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(bodyStyle); } } rowIndex++; while ((line = br.readLine()) != null) { cells1 = Util.tokenize(line, '\t'); row = sheet1.createRow((rowIndex)); columnNum = cells1.length; if (indexKey >= 0) { if (lastKey == null && cells1[indexKey] != null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey != null && cells1[indexKey] == null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey == null && cells1[indexKey] == null) { } else { if (!lastKey.equals(cells1[indexKey])) { switcher *= -1; lastKey = cells1[indexKey]; } } } else { switcher = 1; } // System.out.println(cells1[0]); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (cells1[j] != null) { if (Util.isNumeric(cells1[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(cells1[j])); } else { cell.setCellValue(cells1[j]); } } else { cell.setCellValue("."); } } rowIndex++; } br.close(); // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(outFileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeMultArray2XLSXFile( String fileName, List<List<String[]>> arrys, List<String> sheetLabels, boolean hasHead, int indexKey) throws Exception { if (arrys.isEmpty()) { System.err.println("No input data!"); return false; } XSSFWorkbook wb = new XSSFWorkbook(); XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); String lastKey = null; int switcher = -1; XSSFCell cell = null; String[] titleNames = null; int d = 0; for (List<String[]> arry : arrys) { XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d)); if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } int rowNum = arry.size(); for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; if (indexKey >= 0) { if (lastKey == null && line[indexKey] != null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey != null && line[indexKey] == null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey == null && line[indexKey] == null) { } else { if (!lastKey.equals(line[indexKey])) { switcher *= -1; lastKey = line[indexKey]; } } } else { switcher = 1; } for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } d++; } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); return true; }
private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) { XSSFWorkbook wb = null; try { if (isXssf) { XSSFWorkbook w = new XSSFWorkbook(); } else { HSSFWorkbook w = new HSSFWorkbook(); } wb = new XSSFWorkbook(); XSSFDataFormat format = wb.createDataFormat(); XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象 XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式 XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式 XSSFDataFormat passwordFormat = wb.createDataFormat(); passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;")); List<ExcelHeader> headers = getHeaderList(clz); Collections.sort(headers); sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1))); Row r0 = sheet.createRow(0); Cell cell = r0.createCell(0); r0.setHeightInPoints(28); cell.setCellValue(message + "备份记录"); Row r = sheet.createRow(1); r.setHeightInPoints(25); cell.setCellStyle(cellStyle); // 输出标题 for (int i = 0; i < headers.size(); i++) { Cell cell1 = r.createCell(i); if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle); else cell1.setCellStyle(cellStyle); cell1.setCellValue(headers.get(i).getTitle()); } Object obj = null; // 输出用户资料信息 if (message.indexOf("用户资料 ") > 0) { sheet.setColumnWidth(3, 32 * 150); sheet.setColumnWidth(4, 32 * 110); sheet.setColumnWidth(7, 32 * 120); for (int i = 0; i < objs.size(); i++) { r = sheet.createRow(i + 2); obj = objs.get(i); for (int j = 0; j < headers.size(); j++) { Cell cell2 = r.createCell(j); copyDefaultCellStyle(null, cell2, cellStyle, 0); if (getMethodName(headers.get(j)).equals("nabled")) cell2.setCellValue(BeanUtils.getProperty(obj, "enabled")); else if (getMethodName(headers.get(j)).equals("password")) { cell2.setCellStyle(passwordCellStyle); cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j)))); } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j)))); } } } // 输出房间使用信息数据 else { sheet.setColumnWidth(0, 32 * 80); sheet.setColumnWidth(2, 32 * 100); sheet.setColumnWidth(3, 32 * 190); sheet.setColumnWidth(4, 32 * 190); sheet.setColumnWidth(5, 32 * 190); sheet.setColumnWidth(10, 32 * 130); for (int i = 0; i < objs.size(); i++) { r = sheet.createRow(i + 2); obj = objs.get(i); for (int j = 0; j < headers.size(); j++) { Cell cell2 = r.createCell(j); if (j == 3 || j == 4 || j == 5) { XSSFCellStyle cs3 = wb.createCellStyle(); cell2.setCellValue(new Date()); copyDefaultCellStyle(format, cell2, cs3, 1); } if (j == 10) { XSSFCellStyle cs2 = wb.createCellStyle(); copyDefaultCellStyle(format, cell2, cs2, 2); } copyDefaultCellStyle(null, cell2, cellStyle, 0); cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j)))); } } } // 设置行列的默认宽度和高度 } catch (IllegalAccessException e) { e.printStackTrace(); logger.error(e); } catch (InvocationTargetException e) { e.printStackTrace(); logger.error(e); } catch (NoSuchMethodException e) { e.printStackTrace(); logger.error(e); } return wb; }
@Command public void exportar() throws CloneNotSupportedException { // if(itemSel == null){ // Clientes.showWarning("Seleccione una factura para realizar esta operacion"); // return; // } XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet XSSFSheet sheet = workbook.createSheet("Articulos"); // This data needs to be written (Object[]) // Map<String, Object[]> data = new TreeMap<String, Object[]>(); // data.put("1", new Object[] { "ID", "COD.GRUPO" // , "GRUPO" // , "COD.ART" // , "COD.ART" // , "ARTICULO" // , "MARCA" // , "PROCEDENCIA" // , "U.MEDIDA" // , "CANT" // }); Filtro f = new Filtro(); // f.setId_facturacion(itemSel.getId()); f.setInicio(0); f.setFin(0); java.util.List itemsModel = itemsDao.selectPagos(f); // detalleFacturasMapper.selectDetalle(f); Iterator<Articulos> det = itemsModel.iterator(); int rownum = 1; // cabeceras Row row0 = sheet.createRow(0); int i = 0; row0.createCell(i++).setCellValue("COD.GRUPO"); row0.createCell(i++).setCellValue("GRUPO"); row0.createCell(i++).setCellValue("COD.ART"); row0.createCell(i++).setCellValue("ARTICULO"); row0.createCell(i++).setCellValue("MARCA"); row0.createCell(i++).setCellValue("PROCEDENCIA"); row0.createCell(i++).setCellValue("U.MEDIDA"); row0.createCell(i++).setCellValue("CANTIDAD"); // FacturasItemsExample fie = new FacturasItemsExample(); // fie.createCriteria().andId_factEqualTo(itemSel.getId()); // List<FacturasItems> flist = facturasItemsMapper.selectByExample(fie); // Iterator<FacturasItems> flistItem = flist.iterator(); // int i = 5; // while (flistItem.hasNext()) { // FacturasItems ff = flistItem.next(); // row0.createCell(i++).setCellValue(ff.getDescripcion()); // } // datos while (det.hasNext()) { Articulos df = det.next(); Row row = sheet.createRow(rownum++); i = 0; row.createCell(i++).setCellValue(df.getCodigogrupo()); row.createCell(i++).setCellValue(df.getGrupo()); row.createCell(i++).setCellValue(df.getCodigo()); row.createCell(i++).setCellValue(df.getArticulo()); row.createCell(i++).setCellValue(df.getMarca()); row.createCell(i++).setCellValue(df.getProcedencia()); row.createCell(i++).setCellValue(df.getUnidadmedida()); row.createCell(i++).setCellValue(new BigDecimal("0").doubleValue()); // DetalleFacturasItemsExample de = new DetalleFacturasItemsExample(); // de.createCriteria().andId_dfEqualTo(df.getId()); // List<DetalleFacturasItems> dflist = detalleFacturasItemsMapper // .selectByExample(de); // Iterator<DetalleFacturasItems> dfit = dflist.iterator(); // i = 5; // while (dfit.hasNext()) { // DetalleFacturasItems dfi = dfit.next(); // row.createCell(i++).setCellValue(dfi.getValor().doubleValue()); // } } try { // Write the workbook in file system ParametrosKey pk = new ParametrosKey(); pk.setId("DATA_FOLDER"); pk.setId_app(UsuarioLogueado.getIdApp()); String dataFolder = parametrosMapper.selectByPrimaryKey(pk).getValor(); String path = dataFolder + File.separatorChar + "export_articulos_tmp.xlsx"; FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); out.close(); File iof = new File(path); if (!iof.exists()) { Clientes.showWarning("No se puede encontrar el archivo generado"); return; } java.io.InputStream is = new FileInputStream(iof); // desktop.getWebApp().getResourceAsStream(path); if (is != null) { AMedia am = new AMedia(iof, null, null); SimpleDateFormat fmt = new SimpleDateFormat("MM-yyyy"); String fe = fmt.format(new java.util.Date()); String me = fe.substring(0, 7); Filedownload.save(is, am.getContentType(), "Db_productos_" + me); } else { Clientes.showWarning("No se puede encontrar el archivo generado"); } // System.out.println("howtodoinjava_demo.xlsx written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } }
@Override public Workbook writeWorkbook( Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) { if (workbook == null) workbook = new XSSFWorkbook(); int exportRecordNum = tableDataList.size(); // 导出的总记录数 int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数 int sheetNum = exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum) + 1; // 工作表的页数 List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>(); if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet"; for (int i = 0; i < sheetNum; i++) { // 在Excel工作簿中建一工作表 String sTitle = sheetTitle + (i + 1); XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle); sheet.setSelected(true); // 设置工作薄为选中 sheet.setAutobreaks(true); sheet.setPrintGridlines(true); sheetList.add(sheet); } /** ***********************输出表头********************************* */ if (headerNum > 0) { for (XSSFSheet sheet : sheetList) { XSSFRow headRow = sheet.createRow(0); headRow.setHeightInPoints(20); for (int i = 0; i < headerNum; i++) { List<String> headerRowDataList = tableDataList.get(i); for (int j = 0; j < headerRowDataList.size(); j++) { XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式 createCell(headRow, j, headerRowDataList.get(j), cellStyle); } } // 固定表头 sheet.createFreezePane(0, 1); } } /** *********************输出表体内容************************* */ // 设置列样式 XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle(); columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index); columnStyle.setWrapText(true); if (tableDataList.size() > (tableStartRowNum + 1)) { for (int i = tableStartRowNum; i < exportRecordNum; i++) { List<String> rowDataList = tableDataList.get(i); XSSFRow row = null; int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数 XSSFSheet sheet = sheetList.get(currentSheet); int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet; row = sheet.createRow(rowIndex); for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) { createCell(row, colIndex, rowDataList.get(colIndex)); } } } // 调整列的宽度(取第一列为基准) for (XSSFSheet sheet : sheetList) { for (int i = 0; i < tableDataList.get(0).size(); i++) { sheet.autoSizeColumn((short) i); sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000)); } } return workbook; }
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); // Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted); Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } // Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { // Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }
@Override public XSSFWorkbook exportAllStatistics( List<StatisticData> statistics, Interview interview, ByteArrayOutputStream out) throws IOException { /*NULL is correct value*/ try (XSSFWorkbook book = new XSSFWorkbook()) { XSSFSheet sheet = book.createSheet(SHEET_NAME); int rowNumber = 0; XSSFRow mainHeader = sheet.createRow(rowNumber++); XSSFCell cell = mainHeader.createCell(0); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(HEADER_PREFIX + interview.getName() + "\""); makeCellAutosizeAndBold(book, mainHeader); rowNumber++; for (StatisticData statistic : statistics) { /*Add question text*/ XSSFRow row = sheet.createRow(rowNumber++); XSSFCell questionText = row.createCell(0); questionText.setCellType(Cell.CELL_TYPE_STRING); questionText.setCellValue(QUESTION_PREFIX + statistic.getQuestionText()); XSSFRow tableHeader = sheet.createRow(rowNumber++); XSSFCell c1 = tableHeader.createCell(0); XSSFCell c2 = tableHeader.createCell(1); XSSFCell c3 = tableHeader.createCell(2); XSSFCell c4 = tableHeader.createCell(3); c1.setCellType(Cell.CELL_TYPE_STRING); c2.setCellType(Cell.CELL_TYPE_NUMERIC); c3.setCellType(Cell.CELL_TYPE_NUMERIC); c4.setCellType(Cell.CELL_TYPE_STRING); c1.setCellValue(ANSWER_CELL_HEADER); c2.setCellValue(PEOPLE_COUNT_HEADER); c3.setCellValue(PERCENT_HEADER); c4.setCellValue(RESPONDENTS_HEADER); makeCellAutosizeAndBold(book, tableHeader); Map<String, Object[]> answerData = statistic.getAnswerData(); for (String key : answerData.keySet()) { XSSFRow answer = sheet.createRow(rowNumber++); XSSFCell answerText = answer.createCell(0); XSSFCell peopleResponded = answer.createCell(1); XSSFCell percentResponded = answer.createCell(2); XSSFCell respondents = answer.createCell(3); answerText.setCellType(Cell.CELL_TYPE_STRING); peopleResponded.setCellType(Cell.CELL_TYPE_NUMERIC); percentResponded.setCellType(Cell.CELL_TYPE_NUMERIC); respondents.setCellType(Cell.CELL_TYPE_STRING); answerText.setCellValue(key); Object[] values = answerData.get(key); peopleResponded.setCellValue(values[0].toString()); percentResponded.setCellValue(values[1].toString().replace(",", ".")); respondents.setCellValue(userAnswerService.getRespondentListHowLine(interview, key)); makeCellsAutosize(book, answer); } rowNumber++; } book.write(out); return book; } }
public XSSFWorkbook writeCTGI( XSSFWorkbook cTGWorkbook, MultipartFile fileIn, String classId, MultipartFile staplesMasterStyleGuide, MultipartFile attributeReport) { try { List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId); List<String> attributes = getAttributes(attributeReport, classId); XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream()); XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0); XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0); int i = 1; int j = 4; XSSFCell cell = null; for (i = 1; i < worksheetIn.getLastRowNum(); i++) { XSSFRow rowIn = worksheetIn.getRow(i); XSSFRow rowOut = worksheetOut.createRow(j); if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) { XSSFCell cell1 = rowOut.createCell(13); if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue()); XSSFCell cell2 = rowOut.createCell(15); cell2.setCellValue(classId); XSSFCell cell3 = rowOut.createCell(19); if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue()); XSSFCell cell4 = rowOut.createCell(20); if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue()); XSSFCell cell5 = rowOut.createCell(24); if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue()); XSSFCell cell6 = rowOut.createCell(179); if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue()); XSSFCell cell7 = rowOut.createCell(26); cell7 = attributeCell.get(0); int z = 1; for (int k = 36; k < 48; k++) { cell = rowOut.createCell(k); cell.setCellValue((attributeCell.get(z)).getStringCellValue()); z++; } int counter = 1; int cellCount = 63; for (String attribute : attributes) { if (counter <= 50) { String lable = attribute.split("###")[0]; String value = attribute.split("###")[1]; cell = rowOut.createCell(cellCount); cell.setCellValue(lable); cell = rowOut.createCell(cellCount + 1); cell.setCellValue(value); counter++; cellCount += 2; } } } j++; } } catch (IOException e) { e.printStackTrace(); } return cTGWorkbook; }