List of usage examples for org.apache.poi.ss.usermodel Workbook createDataFormat
DataFormat createDataFormat();
From source file:com.ssic.education.provider.controller.WaresController.java
@RequestMapping(value = "/excel") @ResponseBody// ww w. ja v a 2 s . c o m public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } proWaresDto.setSupplierId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("??"); titles.add("???"); titles.add(""); titles.add("?"); titles.add("?"); // titles.add("??"); // titles.add("???"); // titles.add("??"); titles.add("?"); titles.add("???"); titles.add(""); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getWaresName()); vpd.put("var2", expList.get(i).getAmountUnit()); vpd.put("var3", expList.get(i).getSpec()); vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType())); vpd.put("var5", expList.get(i).getManufacturer()); // vpd.put("var5", expList.get(i).getEnName()); // vpd.put("var6", expList.get(i).getBarCode()); // vpd.put("var7", expList.get(i).getCustomCode()); vpd.put("var6", expList.get(i).getShelfLife()); vpd.put("var7", expList.get(i).getUnit()); vpd.put("var8", expList.get(i).getPlace()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.toba.bll.admin.ReportsDownloadServlet.java
/** * Handles the HTTP <code>GET</code> method. * * @param request servlet request/*from www . jav a2 s. co m*/ * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Users Registered in the Past Month"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("User Name"); row.createCell(1).setCellValue("First Name"); row.createCell(2).setCellValue("Last Name"); row.createCell(3).setCellValue("Registration Date"); Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -1); Date oneMonthAgo = calendar.getTime(); List<User> usersRegisteredInThePastMonth = UserDB.selectUsersRegisteredAfter(oneMonthAgo); HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle(); short dateDataFormat = workbook.createDataFormat().getFormat("dd/MM/yyyy"); dateCellStyle.setDataFormat(dateDataFormat); for (int i = 0; i < usersRegisteredInThePastMonth.size(); i++) { User user = usersRegisteredInThePastMonth.get(i); row = sheet.createRow(1 + i); row.createCell(0).setCellValue(user.getUserName()); row.createCell(1).setCellValue(user.getFirstName()); row.createCell(2).setCellValue(user.getLastName()); row.createCell(3).setCellValue(user.getRegistrationDate()); row.getCell(3).setCellStyle(dateCellStyle); } workbook.write(response.getOutputStream()); workbook.close(); response.setHeader("content-disposition", "attachment; filename=users.xls"); response.setHeader("cache-control", "no-cache"); }
From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;//w w w .j a va 2 s . c o m OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new HSSFWorkbook(); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;/* w ww. j a v a2 s . c o m*/ OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new SXSSFWorkbook(100); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Updates the cell value and type, causes a recalculation of all the values * in the cell.//from ww w .j av a 2 s.co m * * If there is a {@link CellValueHandler} defined, then it is used. * * Cells starting with "=" or "+" will be created/changed into FORMULA type. * * Cells that are existing and are NUMERIC type will be parsed according to * their existing format, or if that fails, as Double. * * Cells not containing any letters and containing at least one number will * be created/changed into NUMERIC type (formatting is not changed). * * Existing Boolean cells will be parsed as Boolean. * * For everything else and if any of the above fail, the cell will get the * STRING type and the value will just be a string, except empty values will * cause the cell type to be BLANK. * * @param col * Column index of target cell, 1-based * @param row * Row index of target cell, 1-based * @param value * The new value to set to the target cell, formulas will start * with an extra "=" or "+" */ public void onCellValueChange(int col, int row, String value) { Workbook workbook = spreadsheet.getWorkbook(); // update cell value final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row - 1); if (r == null) { r = activeSheet.createRow(row - 1); } Cell cell = r.getCell(col - 1); String formattedCellValue = null; int oldCellType = -1; // capture cell value to history CellValueCommand command = new CellValueCommand(spreadsheet); command.captureCellValues(new CellReference(row - 1, col - 1)); spreadsheet.getSpreadsheetHistoryManager().addCommand(command); boolean updateHyperlinks = false; if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet, col - 1, row - 1, value, getFormulaEvaluator(), formatter)) { Exception exception = null; try { // handle new cell creation SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory(); final Locale spreadsheetLocale = spreadsheet.getLocale(); if (cell == null) { cell = r.createCell(col - 1); } else { // modify existing cell, possibly switch type formattedCellValue = getFormattedCellValue(cell); final String key = SpreadsheetUtil.toKey(col, row); oldCellType = cell.getCellType(); if (!sentCells.remove(key)) { sentFormulaCells.remove(key); } // Old value was hyperlink => needs refresh if (cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCellFormula().startsWith("HYPERLINK")) { updateHyperlinks = true; } } if (formulaFormatter.isFormulaFormat(value)) { if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) { spreadsheet.removeInvalidFormulaMark(col, row); getFormulaEvaluator().notifyUpdateCell(cell); cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale)); getFormulaEvaluator().notifySetFormula(cell); if (value.startsWith("=HYPERLINK(") && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) { // set the cell style to link cell CellStyle hyperlinkCellStyle; if (hyperlinkStyleIndex == -1) { hyperlinkCellStyle = styler.createHyperlinkCellStyle(); hyperlinkStyleIndex = -1; } else { hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex); } cell.setCellStyle(hyperlinkCellStyle); styler.cellStyleUpdated(cell, true); updateHyperlinks = true; } } else { // it's formula but invalid cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } else { spreadsheet.removeInvalidFormulaMark(col, row); Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale); Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale); if (value.isEmpty()) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else if (percentage != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); CellStyle cs = cell.getCellStyle(); if (cs == null) { cs = workbook.createCellStyle(); cell.setCellStyle(cs); } if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) { cs.setDataFormat(workbook.createDataFormat() .getFormat(spreadsheet.getDefaultPercentageFormat())); styler.cellStyleUpdated(cell, true); } cell.setCellValue(percentage); } else if (numVal != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numVal); } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) { cell.setCellValue(Boolean.parseBoolean(value)); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); } getFormulaEvaluator().notifyUpdateCell(cell); } } catch (FormulaParseException fpe) { try { exception = fpe; // parses formula cell.setCellFormula(value.substring(1).replace(" ", "")); } catch (FormulaParseException fpe2) { exception = fpe2; /* * We could force storing the formula even if it is invalid. * Instead, just store it as the value. Clearing the formula * makes sure the value is displayed as-is. */ cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } catch (NumberFormatException nfe) { exception = nfe; cell.setCellValue(value); } catch (Exception e) { exception = e; cell.setCellValue(value); } if (cell != null) { markCellForUpdate(cell); if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell)) || oldCellType != cell.getCellType()) { fireCellValueChangeEvent(cell); } } if (exception != null) { LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " (" + exception.getMessage() + ")", exception); } } spreadsheet.updateMarkedCells(); if (updateHyperlinks) { spreadsheet.loadHyperLinks(); } }
From source file:com.wabacus.system.assistant.StandardExcelAssistant.java
License:Open Source License
public boolean setCellValue(Workbook workbook, String align, Cell cell, Object objValue, IDataType typeObj, CellStyle cellStyleWithFormat) { if (objValue == null) { cell.setCellValue(""); return false; }//from w w w .j a va 2 s . c o m if (typeObj instanceof VarcharType) { cell.setCellValue((String) objValue); } else if (typeObj instanceof DoubleType) { cell.setCellValue((Double) objValue); } else if (typeObj instanceof FloatType) { cell.setCellValue((Float) objValue); } else if (typeObj instanceof IntType) { cell.setCellValue((Integer) objValue); } else if (typeObj instanceof LongType) { cell.setCellValue(((Long) objValue)); } else if (typeObj instanceof ShortType) { cell.setCellValue(((Short) objValue)); } else if (typeObj instanceof BigdecimalType) { cell.setCellValue((((BigDecimal) objValue)).doubleValue()); } else if (typeObj instanceof DateType || typeObj instanceof TimeType || typeObj instanceof TimestampType) { cellStyleWithFormat.setDataFormat( workbook.createDataFormat().getFormat(((AbsDateTimeType) typeObj).getDateformat())); if (align != null && !align.trim().equals("")) { cellStyleWithFormat = this.setCellAlign(cellStyleWithFormat, align); } cell.setCellValue(((Date) objValue)); cell.setCellStyle(cellStyleWithFormat); return true; } else if (typeObj instanceof CDateType || typeObj instanceof CTimeType || typeObj instanceof CTimestampType) { cellStyleWithFormat.setDataFormat( workbook.createDataFormat().getFormat(((AbsDateTimeType) typeObj).getDateformat())); if (align != null && !align.trim().equals("")) { cellStyleWithFormat = this.setCellAlign(cellStyleWithFormat, align); } cell.setCellValue(((Calendar) objValue)); cell.setCellStyle(cellStyleWithFormat); return true; } else {//??Excel cell.setCellValue(typeObj.value2label(objValue)); } return false; }
From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java
License:Apache License
static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;/*from w w w.ja v a 2 s.c o m*/ Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 14); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("header", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.RED.getIndex()); monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("red-bold", style); String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" }; for (String fmt : nfmt) { style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat(fmt)); styles.put(fmt, style); } return styles; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput?//from w ww. j av a 2s . c om * @param FileName ???????excel * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (FileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // jsonkey String[] cellNames; // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? cellNames = new String[curCellNum]; // ????JSONkey for (int m = 0; m < curCellNum; m++) { Cell cell = fisrtRow.getCell(m); // ? cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ? cellNames[m] = getCellValue(cell); } for (String s : cellNames) { System.out.print("" + i + " sheet " + s + ","); } System.out.println(); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ??? rowMap.put(cellNames[k], getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput?/* w w w. j a v a 2s . c o m*/ * @param FileName ???????excel * @param headers list,String-->Arrays.asList(); * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (fileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); // ??? rowMap.put(headers.get(k), getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
private CellStyle getFormat(final Workbook workbook, final Cell cell, String type, String value) { String format = (String) DATE_FORMATS_EXCEL.get(type.toLowerCase()); // Type is number if (format == null) { final String[] hlp = value.split("@@"); format = hlp.length > 1 ? hlp[1] : "0.00####"; }//from ww w.java2 s .co m CellStyle cellStyle = formatCache.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.createDataFormat().getFormat(format)); formatCache.put(format, cellStyle); } return cellStyle; }