ExcelUtil

2019-11-06 06:38:47

字体：大中小

来源：转载

供稿：网友

import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.commons.io.IOUtils;import org.apache.commons.lang.StringUtils;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFColor;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil{ /* the log. */ PRivate static final Log log = LogFactory.getLog(ExcelUtil.class.getName()); /** * Get the Workbook Object By FilePath. * * @param filePath * <filePath>. * @return Workbook. */ public static Workbook getWorkbook(String filePath) { if (StringUtils.isBlank(filePath)) { log.error("the input param:[" + filePath + " ]is null,getWorkbook failed."); return null; } Workbook wb = null; try { if (isExcel2007(filePath)) { // vesion2007above. wb = new XSSFWorkbook(new FileInputStream(new File(filePath))); } else { // vesion2003. wb = new HSSFWorkbook(new FileInputStream(new File(filePath))); } } catch (FileNotFoundException e) { log.error("getWorkbook failed,FileNotFoundException.", e); } catch (IOException e) { log.error("getWorkbook failed,IOException.", e); } catch (Exception e) { log.error("getWorkbook failed.", e); } return wb; } /** * * Write Excel File for version2003 and version2007above. * * @param wb * <Workbook>. * @param filePath * ExcelFilePath. */ public static void writeExcel(Workbook wb, String filePath) { FileOutputStream fos = null; try { fos = new FileOutputStream(filePath); wb.write(fos); fos.flush(); } catch (FileNotFoundException e) { log.error("writeExcel failed,FileNotFoundException.", e); } catch (IOException e) { log.error("writeExcel failed,IOException.", e); } catch (Exception e) { log.error("writeExcel failed.", e); } finally { IOUtils.closeQuietly(fos); } } /** * Judge the excel file whether 2003 or 2007 above. * * @param filePath * <filePath>. * @return boolean<case <suffix> when 2003 then false else true>. */ public static boolean isExcel2007(String filePath) { /* the pointer index. */ int pointIndex = filePath.lastIndexOf(IConstants.POINT); /* the suffix. */ String suffix = filePath.substring(pointIndex); if (IConstants.EXCEL2007_SUFFIX.equals(suffix)) { return true; } return false; } /****************************** DealWith Excel Version2007above Methods ******************************/ /** * Create a excel sheet. * * @param wb * <XSSFWorkbook>. * @param sheetName * <sheetName>. * @return XSSFSheet <XSSFSheet>. */ public static XSSFSheet createSheet(XSSFWorkbook wb, String sheetName) { XSSFSheet sheet = wb.createSheet(sheetName); return sheet; } /** * Create a excel row. * * @param height * <height>. * @param index * <index>. * @param sheet * <sheet>. * @return XSSFRow <XSSFRow>. */ public static XSSFRow createRow(short height, int index, XSSFSheet sheet) { XSSFRow row = sheet.createRow(index); if (height != 0) { row.setHeight(height); } return row; } /** * Create Cell For XSSFRow. * * @param color * <XSSFColor>. * @param cellIndex * <cellIndex>. * @param value * <value>. * @param row * <XSSFRow>. * @param style * <XSSFCellStyle> * @return XSSFCell <XSSFCell>. */ public static XSSFCell createCell(XSSFColor color, int cellIndex, String value, XSSFRow row, XSSFCellStyle style) { XSSFCell cell = row.createCell(cellIndex); cell.setCellValue(value); cell.setCellStyle(cellStyle(style, color)); return cell; } /** * Create Cell Style For XSSFWorkbook. * * @param xfwb * <XSSFWorkbook>. * @return XSSFCellStyle <XSSFCellStyle>. */ public static XSSFCellStyle createCellStyle(XSSFWorkbook xfwb) { return xfwb.createCellStyle(); } /** * Create cellStyle. * * @param style * <XSSFCellStyle>. * @param color * <XSSFColor>. * @return XSSFCellStyle <XSSFCellStyle>. */ public static XSSFCellStyle cellStyle(XSSFCellStyle style, XSSFColor color) { XSSFCellStyle cellStyle = baseStyle(style); if (color != null) { cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(color); } return cellStyle; } /** * Create baseStyle for Cell. * * @param cellStyle * <XSSFCellStyle>. * @return XSSFCellStyle <XSSFCellStyle>. */ public static XSSFCellStyle baseStyle(XSSFCellStyle cellStyle) { cellStyle.setWrapText(true); cellStyle.setAlignment(HorizontalAlignment.LEFT); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /****************************** DealWith Excel Version2003 Methods ******************************/ /** * Create a excel sheet. * * @param wb * <HSSFWorkbook>. * @param sheetName * <sheetName>. * @return HSSFSheet <HSSFSheet>. */ public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName) { HSSFSheet sheet = wb.createSheet(sheetName); return sheet; } /** * Create a excel row. * * @param height * <height>. * @param index * <index>. * @param sheet * <sheet>. * @return HSSFRow <HSSFRow>. */ public static HSSFRow createRow(short height, int index, HSSFSheet sheet) { HSSFRow row = sheet.createRow(index); if (height != 0) { row.setHeight(height); } return row; } /** * Create Cell For Workbook. * * @param color * <color>. * @param cellIndex * <cellIndex>. * @param value * <value>. * @param row * <row>. * @param style * <style> * @return HSSFCell <HSSFCell>. */ public static HSSFCell createCell(short color, int cellIndex, String value, HSSFRow row, HSSFCellStyle style) { HSSFCell cell = row.createCell(cellIndex); cell.setCellValue(value); cell.setCellStyle(cellStyle(style, color)); return cell; } /** * Setup Column With For Cell. * * @param column * <column>. * @param width * <width>. * @param sheet * <Sheet>. */ public static void setColumnWith(int column, int width, Sheet sheet) { sheet.setColumnWidth(column, width); } /** * Create a cell style. * * @param hfwb * <hfwb>. * @return HSSFCellStyle <HSSFCellStyle>. */ public static HSSFCellStyle createCellStyle(HSSFWorkbook hfwb) { return hfwb.createCellStyle(); } /** * Create cellStyle. * * @param style * <HSSFCellStyle>. * @param color * <color>. * @return HSSFCellStyle <HSSFCellStyle>. */ public static HSSFCellStyle cellStyle(HSSFCellStyle style, short color) { HSSFCellStyle cellStyle = baseStyle(style); if (color != (short) IConstants.ZERO) { cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(color); } return cellStyle; } /** * Create baseStyle for Cell. * * @param cellStyle * <HSSFCellStyle> * @return HSSFCellStyle <HSSFCellStyle>. */ public static HSSFCellStyle baseStyle(HSSFCellStyle cellStyle) { cellStyle.setWrapText(true); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return cellStyle; }}

上一篇：生物信息学习——cufflinks

下一篇：golang获取命令行参数