Java tutorial
/* * * Copyright 2018 FJN Corp. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * * Author Date Issue * fs1194361820@163.com 2015-01-01 Initial Version * */ package com.fjn.helper.common.io.file.office.excel; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.util.Calendar; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.log4j.Logger; import org.apache.log4j.Priority; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.fjn.helper.common.exception.FileDirFaultException; import com.fjn.helper.common.io.file.common.FileUtil; import com.fjn.helper.common.io.file.office.excel.annotation.ExcelHeader; import com.fjn.helper.common.util.StreamUtil; import com.fjn.helper.common.util.StringUtil; /*** * @author fjn POI?? */ @SuppressWarnings({ "deprecation", "rawtypes" }) public class ExcelUtil { public static final String XLS = "xls"; public static final String XLSX = "xlsx"; public static final String SXLSX = "sxlsx"; private static Logger logger = Logger.getLogger(ExcelUtil.class); /** * @param excelFormat * ???<br> * XLS 97-2003?Excel<br> * XLSX 2007?2010?Excel<br> * SXLSX Excel * @return */ public static Workbook newWorkbook(String excelFormat) { Workbook wb = null; if (XLSX.equals(excelFormat)) { wb = new XSSFWorkbook(); } else if (XLS.equals(excelFormat)) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(); } return wb; } /** * ??java Bean * * @param sheet * @param headers */ public static void addHeaderRow(Sheet sheet, int rowIndex, List<String> headers) { Row row = null; Cell cell = null; // Field[] fields=clazz.getDeclaredFields(); // Excelheader,? row = sheet.createRow(rowIndex); for (int i = 0; i < headers.size(); i++) { cell = row.createCell(i); cell.setCellValue(headers.get(i)); } } /** * ??java Bean * * @param sheet * @param headers */ public static void addHeaderRow(Sheet sheet, List<String> headers) { addHeaderRow(sheet, 0, headers); } /** * ??java Bean ???@ExcelHeader, * * @param sheet * @param clazz * @param fieldnames */ public static void addHeaderRow(Sheet sheet, int rowIndex, Class clazz, List<String> fieldnames) { Row row = null; Cell cell = null; // Field[] fields=clazz.getDeclaredFields(); // Excelheader,? row = sheet.createRow(rowIndex); for (int i = 0; i < fieldnames.size(); i++) { Field tempfield = null; try { tempfield = clazz.getDeclaredField(fieldnames.get(i)); } catch (Exception ex) { if (logger.isEnabledFor(Priority.ERROR)) { logger.error("??"); } } if (tempfield != null) { ExcelHeader excelheader = tempfield.getAnnotation(ExcelHeader.class); if (excelheader != null) { cell = row.createCell(i); cell.setCellValue(excelheader.value()); } } else { logger.warn("" + fieldnames.get(i) + "@ExcelHeader ???"); fieldnames.remove(i); } } } /** * ??java Bean * * @param sheet * @param clazz * @param fieldnames */ public static void addHeaderRow(Sheet sheet, Class clazz, List<String> fieldnames) { addHeaderRow(sheet, 0, clazz, fieldnames); } /** * ?? * * @param sheet * @param rowIndex * @param colIndex * @param value */ public static void setHeader(Sheet sheet, int rowIndex, int colIndex, Object value) { setValue(sheet, rowIndex, colIndex, value); } /** * sheetrowIndexcolIndex? * * @param sheet * @param rowIndex * @param colIndex * @param value */ public static void setValue(Sheet sheet, int rowIndex, int colIndex, Object value) { Row row = sheet.getRow(rowIndex); Cell cell = row.getCell(colIndex); setCellValue(cell, value); } /** * ? * * @param cell * @param value */ private static void setCellValue(Cell cell, Object value) { if (value == null) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else { if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue((Date) value); } else if (value instanceof String) { cell.setCellValue(value.toString()); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Number) { cell.setCellValue(Double.valueOf(value.toString())); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); } } } /** * ?? * * @param sheet * @param clazz * ???java bean * @param fieldnames * java bean???ExeclHeader * @param list * ? * @return */ @SuppressWarnings("unused") public static Sheet addDataToSheet(Sheet sheet, Class clazz, List<String> fieldnames, List<?> list) { // ? Row row = null; Cell cell = null; int lastRowNum = sheet.getLastRowNum(); for (int i = 0; i < list.size(); i++) { row = sheet.createRow(lastRowNum + 1 + i); for (int j = 0; j < fieldnames.size(); j++) { try { Field field = clazz.getDeclaredField(fieldnames.get(j)); field.setAccessible(true); cell = row.createCell(j); Class fieldType = field.getType(); // cell.setCellType(Cell.); Object value = field.get(list.get(i)); setCellValue(cell, value); } catch (Exception ex) { if (logger.isEnabledFor(Priority.ERROR)) { logger.error("" + (i + 1) + "" + j + "", ex); } } } } return sheet; } /** * workbook * * @param workbook * @param sheetname * @return */ public static Sheet addSheet(Workbook workbook, String sheetname) { return workbook.createSheet(sheetname); } /** * ?Sheet?? ??? * * @param fieldnames * bean????? * @param list * listbean??List<bean> list * @param sheetname * ?sheet??sheetname * @return Sheet?? */ public static Sheet addSheetWithData(Workbook workbook, List<String> fieldnames, List list, String sheetname) { if (fieldnames == null && list == null) { return null; } if (StringUtil.isNull(sheetname)) { if (logger.isEnabledFor(Priority.ERROR)) { logger.error("Sheet??"); } return null; } Sheet sheet = workbook.createSheet(sheetname); Class clazz = list.get(0).getClass(); // addHeaderRow(sheet, 0, clazz, fieldnames); // ? addDataToSheet(sheet, clazz, fieldnames, list); // ?{???} return sheet; } /** * ?? * * @param sheet * @param columnIndex * @param style * @return */ public static boolean setColumnStyle(Sheet sheet, short columnIndex, int rowFirstIndex, int rowLastIndex, CellStyle style) { if (sheet == null) return false; int rowNum = sheet.getLastRowNum(); CellStyle newCellStyle = sheet.getWorkbook().createCellStyle(); // ?? if (rowFirstIndex < rowLastIndex) { int temp = rowFirstIndex; rowFirstIndex = rowLastIndex; rowLastIndex = temp; } // TODO if (rowNum < rowFirstIndex) {// ? return false; } // for (int i = rowFirstIndex; i <= rowNum; i++) { Row row = sheet.getRow(i); if (row == null) return false; Cell cell = row.getCell(columnIndex); if (cell == null) return false; newCellStyle.cloneStyleFrom(cell.getCellStyle());// ?? newCellStyle.cloneStyleFrom(style); // ?? cell.setCellStyle(newCellStyle); } return true; } /** * ???? * * @param sheet * @param rowIndex * @param style * @return */ public static boolean setRowStyle(Sheet sheet, int rowIndex, CellStyle style) { if (sheet != null) { Row row = sheet.getRow(rowIndex); if (row != null) { short firstColumnIndex = row.getFirstCellNum(); short lastColumnIndex = row.getLastCellNum(); for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Cell cell = row.getCell(colunmIndex); if (cell != null) { cellStyle.cloneStyleFrom(cell.getCellStyle()); cellStyle.cloneStyleFrom(style); cell.setCellStyle(cellStyle); } } } } return true; } /** * ??? * * @param sheet * @param rowIndex * @param columnIndex * @param style * @return */ public static boolean setCellStyle(Sheet sheet, int rowIndex, int columnIndex, CellStyle style) { if (sheet == null) return false; if (rowIndex < 0 || columnIndex <= 0) return false; Cell cell = sheet.getRow(rowIndex).getCell(columnIndex); if (cell == null) return false; CellStyle newCellStyle = sheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(cell.getCellStyle()); newCellStyle.cloneStyleFrom(style); cell.setCellStyle(newCellStyle); return true; } /** * * * @param path * @param fileName * @param workbook */ public static void exportToFile(String path, final String fileName, Workbook workbook) { FileOutputStream out = null; // ? File dir = new File(path); boolean exist = dir.exists(); if (exist) { try { dir = new File(dir + "/" + fileName);// if (!dir.exists()) dir.createNewFile(); } catch (Exception e) { } } else { // ??? try { exist = dir.mkdirs(); dir = new File(dir + "/" + fileName);// dir.createNewFile(); exist = true; } catch (Exception e) { throw new FileDirFaultException(path); } } if (!exist) { throw new FileDirFaultException(path); } try { out = new FileOutputStream(path + "/" + fileName); workbook.write(out); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (out != null) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } } /** * * * @param response * @param fileName * @param workbook */ public static void exportExcelToBrowser(HttpServletResponse response, String fileName, Workbook workbook) { response.setCharacterEncoding("UTF-8"); response.setContentType("application/octet-stream;charset=UTF-8"); response.addHeader("Content-Disposition", "attachment;filename=" + FileUtil.getFileNameForDownload(fileName)); OutputStream out = null; BufferedOutputStream bos = null; try { out = response.getOutputStream(); bos = new BufferedOutputStream(out); workbook.write(out); bos.flush(); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { StreamUtil.close(bos); StreamUtil.close(out); } } /** * ? * * @param out * @param fileName * @param workbook */ public static void exportExcelToOutputStream(FileOutputStream out, String fileName, Workbook workbook) { BufferedOutputStream bos = null; try { bos = new BufferedOutputStream(out); workbook.write(out); bos.flush(); out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { StreamUtil.close(bos); StreamUtil.close(out); } } /* * public static void main(String[] args) { Workbook workbook = * newWorkbook(ExcelUtil.XLS); * * Sheet sheet=allSheet(workbook,MyDataSet.getFieldHeaders(), * MyDataSet.getUserList(), "?"); // ????? CellStyle style = * workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_FILL); * setColumnStyle(workbook.getSheetAt(0), (short) 1, style); * * // 4?yyyyMMdd DataFormat df = workbook.createDataFormat(); * style = workbook.createCellStyle(); * style.setDataFormat(df.getFormat("yyyyMMdd")); * style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); setColumnStyle(sheet, * (short) 3, style); * * // 20*256 20px sheet.setColumnWidth(3, 20 * 256); * exportToFile("WebRoot/excel/", "userinfo.xls", workbook); } */ }