List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?.//from w ww. j a v a 2s. c om * * @param colDesc 1717?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???1717 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?1717 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?.// w ww . ja va2 s .c o m * * @param colDesc * "?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { // ??? List<String> colFieldList = getColumnFieldList(titlesList); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); // ? CellStyle dateCellStyle = getDateTimeCellStyle(wb); // ??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < titlesList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titlesList.get(i)); } ClassInfo classInfo = null; // ? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); if (classInfo == null) classInfo = ClassUtil.getClassInfo(obj.getClass()); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName); Object value = reflexField.getValue(obj); // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } // ?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date || value instanceof Timestamp) { cell.setCellStyle(dateCellStyle); cell.setCellValue(sdf.format((Date) value)); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } // ?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < titlesList.size(); i++) { // ?? sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./* w w w.jav a 2 s.co m*/ * * @param colDesc ?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //??? //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //?? XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.FuntionLibrary.java
public void writetoExcel(String Name, String EmailId, String Password, String MobileNo, XSSFSheet sheet, String MailPaswd) throws InvalidFormatException, IOException { try {//from ww w . ja v a 2 s . c om //String excelFileName = "";//name of excel file int r = sheet.getLastRowNum(); XSSFRow r1 = sheet.createRow(0); XSSFCell Srnocell = r1.createCell(0); Srnocell.setCellValue("Name"); XSSFCell actioncell = r1.createCell(1); actioncell.setCellValue("EmailId"); XSSFCell loccell = r1.createCell(2); loccell.setCellValue("Password"); XSSFCell valuecell = r1.createCell(3); valuecell.setCellValue("Mobile Number"); XSSFCell EmailPassword = r1.createCell(4); EmailPassword.setCellValue("Email Password"); XSSFRow row = sheet.createRow(r + 1); //iterating c number of columns XSSFCell cell = row.createCell(0); cell.setCellValue(Name); System.out.println(Name); XSSFCell cell1 = row.createCell(1); cell1.setCellValue(EmailId); System.out.println(EmailId); XSSFCell cell2 = row.createCell(2); cell2.setCellValue(Password); System.out.println(Password); XSSFCell cell3 = row.createCell(3); cell3.setCellValue(MobileNo); System.out.println(MobileNo); XSSFCell cell4 = row.createCell(4); cell4.setCellValue(MailPaswd); System.out.println(MailPaswd); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Oops Something Went wrong Error - > " + e.getMessage()); } }
From source file:com.FuntionLibrary.java
public static void updateExcel(int r, XSSFSheet sheet, String Name, String EmailId, String Password, String MobileNo, String MailPassswd) { XSSFRow row = sheet.getRow(r);//from w w w . jav a 2s. c om //iterating c number of columns XSSFCell cell = row.getCell(0); cell.setCellValue(Name); System.out.println(Name); XSSFCell cell1 = row.getCell(1); cell1.setCellValue(EmailId); System.out.println(EmailId); XSSFCell cell2 = row.getCell(2); cell2.setCellValue(Password); System.out.println(Password); XSSFCell cell3 = row.getCell(3); cell3.setCellValue(MobileNo); System.out.println(MobileNo); XSSFCell cell4 = row.getCell(4); cell4.setCellValue(MailPassswd); System.out.println(MailPassswd); }
From source file:com.griffinslogistics.book.BookForTransportationJSFController.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; try {/*from w ww. j a v a2s . com*/ XSSFSheet sheet = wb.getSheetAt(0); for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); row.setHeightInPoints(30); XSSFCell cell = sheet.getRow(i).getCell(0); String text = cell.getStringCellValue(); boolean value = Boolean.parseBoolean(text); if (value) { cell.setCellValue(""); } else { cell.setCellValue("?"); } } } catch (Exception e) { String message = e.getMessage(); e.printStackTrace(); } }
From source file:com.heimaide.server.common.utils.excel.ExportExcel.java
License:Open Source License
private void exportFile(List<String> list, String rootPath, HttpServletResponse response, List<List<Object>> bodyList) throws FileNotFoundException, IOException { String importFileName = rootPath + "WEB-INF" + File.separator + "xlsx" + File.separator + "order.xlsx"; String tmpDir = rootPath + "tmpDir" + File.separator; XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(importFileName)); this.styles = createStyles(wb); //?excel? /*from w w w .j a v a 2s . co m*/ //??sheet XSSFSheet sheet = wb.getSheetAt(0); XSSFCell cell1 = sheet.getRow(1).getCell(0); String cell1str = cell1.getStringCellValue(); //???? cell1str = cell1str.replace("X", list.get(0)); cell1.setCellValue(cell1str); XSSFCell cell2 = sheet.getRow(2).getCell(0); String cell2str = cell2.getStringCellValue(); //?? cell2str = cell2str.replace("X", list.get(1)); //? cell2str = cell2str.replace("Y", list.get(2)); //??? cell2str = cell2str.replace("Z", list.get(3)); cell2.setCellValue(cell2str); XSSFCell cell3 = sheet.getRow(3).getCell(12); String cell3str = cell3.getStringCellValue(); cell3str = cell3str.replace("XX", list.get(4)); cell3.setCellValue(cell3str); XSSFCell cell4 = sheet.getRow(sheet.getLastRowNum() - 1).getCell(0); String cell4str = cell4.getStringCellValue(); cell4str = cell4str.replace("X", list.get(5)); cell4.setCellValue(cell4str); XSSFCell cell5 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(12); XSSFCell cell6 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(13); String str1 = String.valueOf(list.get(6)); String str2 = String.valueOf(list.get(7)); str1 = str1.substring(0, str1.indexOf(".")); str2 = str2.substring(0, str2.indexOf(".")); cell5.setCellValue(str1); cell6.setCellValue(str2); XSSFCell cell7 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(12); XSSFCell cell8 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(13); cell7.setCellValue(Double.parseDouble(list.get(6)) / 10); cell8.setCellValue(Double.parseDouble(list.get(7)) / 10); // int columnNum =sheet.getRow(3).getLastCellNum(); // int column =0; // for(int i=0;i<columnNum;i++) // { // System.out.println("==="+sheet.getRow(3).getCell(i).getStringCellValue()); // column =i; // } int num = 5; for (int i = 0; i < bodyList.size(); i++) { List<Object> strList = bodyList.get(i); sheet.shiftRows(num, bodyList.size() + num, 1, true, false); sheet.createRow(num); for (int j = 0; j < strList.size(); j++) { Row row = sheet.getRow(num); addCell(row, j, strList.get(j)); // Cell cell = row.createCell(j); // cell.setCellValue(strList.get(j)); } num++; } //?? String tmpName = tmpDir + "tmp_hmd.xlsx"; File dirFile = new File(tmpDir); if (!dirFile.exists()) { dirFile.mkdir(); } File tmpFile = new File(tmpName); FileOutputStream fos = new FileOutputStream(tmpFile); wb.write(fos); XSSFWorkbook wb1 = new XSSFWorkbook(new FileInputStream(tmpName)); response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode("?" + list.get(0) + "?.xlsx")); wb1.write(response.getOutputStream()); tmpFile.delete(); }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to write the date graph data as a Excel workbook; required since PowerPoint charts actually * embed an Excel file with corresponding data. If invalid, it'll open in OpenOffice fine, but PowerPoint will * complain that the presentation is corrupted. * @param data the date graph data.//w ww. j a v a 2 s .c o m * @return a new Excel workbook with specified data on a new sheet. */ private static XSSFWorkbook writeChart(final DategraphData data) { final XSSFWorkbook wb = new XSSFWorkbook(); final XSSFSheet sheet = wb.createSheet("Sheet1"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat((short) 14); final List<DategraphData.Row> rows = data.getRows(); final long[] timestamps = data.getTimestamps(); final XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Timestamp"); for (int ii = 0; ii < rows.size(); ++ii) { header.createCell(ii + 1).setCellValue(rows.get(ii).getLabel()); } for (int rowIdx = 0; rowIdx < timestamps.length; ++rowIdx) { final XSSFRow row = sheet.createRow(rowIdx + 1); final XSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(new Date(timestamps[rowIdx] * 1000)); for (int ii = 0; ii < rows.size(); ++ii) { row.createCell(ii + 1).setCellValue(rows.get(ii).getValues()[rowIdx]); } } return wb; }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;// w ww . ja v a2s .co m StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;//from w ww. ja va2s . co m StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file2 = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA"); CreationHelper factory2 = libro.getCreationHelper(); hoja2 = libro.getSheetAt(1); XSSFCellStyle style2 = libro.createCellStyle(); Font font2 = libro.createFont(); Font font12 = libro.createFont(); Drawing drawing2 = hoja2.createDrawingPatriarch(); ClientAnchor anchor12 = factory2.createClientAnchor(); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeightInPoints((short) 8); font12.setFontHeightInPoints((short) 8); font12.setFontName("Arial"); font2.setFontName("Arial"); style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(CellStyle.VERTICAL_CENTER); style2.setAlignment(CellStyle.ALIGN_CENTER); style2.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 29; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 5); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMPRESA"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDREGISTROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMISOR"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 4) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 5) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDMOTIVOPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 6) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDDOCUMENTO"); celda.setCellComment(comment); } else if (f == 0 && c == 7) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SERIE"); celda.setCellComment(comment); } else if (f == 0 && c == 8) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMERO"); celda.setCellComment(comment); } else if (f == 0 && c == 9) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHA"); celda.setCellComment(comment); } else if (f == 0 && c == 10) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("PERIODO"); celda.setCellComment(comment); } else if (f == 0 && c == 11) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDESTADO"); celda.setCellComment(comment); } else if (f == 0 && c == 12) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV"); celda.setCellComment(comment); } else if (f == 0 && c == 13) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 14) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDENVASE"); celda.setCellComment(comment); } else if (f == 0 && c == 15) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDSUCURSAL"); celda.setCellComment(comment); } else if (f == 0 && c == 16) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDALMACEN"); celda.setCellComment(comment); } else if (f == 0 && c == 17) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMBALAJE"); celda.setCellComment(comment); } else if (f == 0 && c == 18) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCULTIVO"); celda.setCellComment(comment); } else if (f == 0 && c == 19) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDVARIEDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 20) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("OBSERVACIONES"); celda.setCellComment(comment); } else if (f == 0 && c == 21) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("VENTANA"); celda.setCellComment(comment); } else if (f == 0 && c == 22) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CANTIDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 23) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CERRADO"); celda.setCellComment(comment); } else if (f == 0 && c == 24) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SINCRONIZA"); celda.setCellComment(comment); } else if (f == 0 && c == 25) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHACREACION"); celda.setCellComment(comment); } else if (f == 0 && c == 26) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROMANUAL"); celda.setCellComment(comment); } else if (f == 0 && c == 27) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV_DESTINO"); celda.setCellComment(comment); } else if (f == 0 && c == 28) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter.."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("TIPO"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); hoja.autoSizeColumn((short) 3); hoja.autoSizeColumn((short) 4); hoja.autoSizeColumn((short) 5); hoja.autoSizeColumn((short) 6); hoja.autoSizeColumn((short) 7); hoja.autoSizeColumn((short) 8); hoja.autoSizeColumn((short) 9); hoja.autoSizeColumn((short) 10); hoja.autoSizeColumn((short) 11); hoja.autoSizeColumn((short) 12); hoja.autoSizeColumn((short) 13); hoja.autoSizeColumn((short) 14); hoja.autoSizeColumn((short) 15); hoja.autoSizeColumn((short) 16); hoja.autoSizeColumn((short) 17); hoja.autoSizeColumn((short) 18); hoja.autoSizeColumn((short) 19); hoja.autoSizeColumn((short) 20); hoja.autoSizeColumn((short) 21); hoja.autoSizeColumn((short) 22); hoja.autoSizeColumn((short) 23); hoja.autoSizeColumn((short) 24); hoja.autoSizeColumn((short) 25); hoja.autoSizeColumn((short) 26); hoja.autoSizeColumn((short) 27); hoja.autoSizeColumn((short) 28); for (int f = 0; f < 2; f++) { XSSFRow fila2 = hoja2.createRow(f); if (f == 0) { for (int c = 0; c < 15; c++) { XSSFCell celda2 = fila2.createCell(c); anchor12.setCol1(celda2.getColumnIndex()); anchor12.setCol2(celda2.getColumnIndex() + 8); anchor12.setRow1(fila2.getRowNum()); anchor12.setRow2(fila2.getRowNum() + 8); Comment comment2 = drawing2.createCellComment(anchor12); RichTextString str; switch (c) { case 0: celda2.setCellStyle(style2); str = factory2.createRichTextString( "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico."); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMPRESA"); celda2.setCellComment(comment2); break; case 1: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDREGISTROPALETA"); celda2.setCellComment(comment2); break; case 2: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("ITEM"); celda2.setCellComment(comment2); break; case 3: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCLIEPROV"); celda2.setCellComment(comment2); break; case 4: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTE"); celda2.setCellComment(comment2); break; case 5: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONDICION"); celda2.setCellComment(comment2); break; case 6: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDTALLA"); celda2.setCellComment(comment2); break; case 7: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCOLOR"); celda2.setCellComment(comment2); break; case 8: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("CANTIDAD"); celda2.setCellComment(comment2); break; case 9: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMBALAJE"); celda2.setCellComment(comment2); break; case 10: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRODUCTO"); celda2.setCellComment(comment2); break; case 11: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTEP"); celda2.setCellComment(comment2); break; case 12: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONSUMIDOR"); celda2.setCellComment(comment2); break; case 13: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTECAMPO"); celda2.setCellComment(comment2); break; case 14: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRESENTACION"); celda2.setCellComment(comment2); break; } } } } hoja2.autoSizeColumn((short) 0); hoja2.autoSizeColumn((short) 1); hoja2.autoSizeColumn((short) 2); hoja2.autoSizeColumn((short) 3); hoja2.autoSizeColumn((short) 4); hoja2.autoSizeColumn((short) 5); hoja2.autoSizeColumn((short) 6); hoja2.autoSizeColumn((short) 7); hoja2.autoSizeColumn((short) 8); hoja2.autoSizeColumn((short) 9); hoja2.autoSizeColumn((short) 10); hoja2.autoSizeColumn((short) 11); hoja2.autoSizeColumn((short) 12); hoja2.autoSizeColumn((short) 13); hoja2.autoSizeColumn((short) 14); hoja2.autoSizeColumn((short) 15); libro.write(file2); file2.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }