asdasd

asdasd 关注TA

asdasd

该文章投稿至Nemo社区   Java  板块 复制链接


封装POI操作Excel工具

发布于 2017/10/12 22:44 1,514浏览 4回复 11,624

引入 POI:

<dependency>

     <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>3.14</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>3.14</version>

</dependency>



然后进入正题:EXCELUtil.java

import java.awt.Color;

import java.awt.image.BufferedImage;

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.Calendar;

import java.util.Date;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFFont;

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.CellValue;

import org.apache.poi.ss.usermodel.ClientAnchor;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;

import org.apache.poi.ss.usermodel.Drawing;

import org.apache.poi.ss.usermodel.RichTextString;

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.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import org.apache.poi.xssf.usermodel.XSSFColor;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**

 * 操作XLSX文件工具类

 * @author LFQ

 * @DATE 2016-04-11

 */

public class XlsxUtil {

private XSSFWorkbook xssfWorkbook;

private XSSFSheet sheet;

private String filePath;

//边框

public static final short BORDER_TOP = 0;	//上边框

public static final short BORDER_RIGHT = 1;	//右边框

public static final short BORDER_BOTTOM = 2;	//下边框

public static final short BORDER_LEFT = 3;	//左边框

public static final short BORDER_ALL = 4;	//所有边框

public static final short BORDER_THIN = CellStyle.BORDER_THIN;	//细边框

public static final short BORDER_DASHED = CellStyle.BORDER_DASHED;	//虚边框

public static final short BORDER_MEDIUM = CellStyle.BORDER_MEDIUM;	//粗边框

//对齐

public static final short ALIGN_CENTER = CellStyle.ALIGN_CENTER;//中

public static final short ALIGN_LEFT = CellStyle.ALIGN_LEFT;	//左

public static final short ALIGN_RIGHT = CellStyle.ALIGN_RIGHT;	//右

public static final short ALIGN_VERTICAL_CENTER = CellStyle.VERTICAL_CENTER;	//垂直居中

//值类型

public static final short CELL_BOOLEAN = Cell.CELL_TYPE_BOOLEAN;	//boolean

public static final short CELL_STRING = Cell.CELL_TYPE_STRING;	//String

public static final short CELL_FORMULA = Cell.CELL_TYPE_FORMULA;	//公式型

public static final short CELL_NUMERIC = Cell.CELL_TYPE_NUMERIC;	//数字

//字体粗细

public static final short FONT_BOLD = HSSFFont.BOLDWEIGHT_BOLD;	//粗体

public static final short FONT_NORMAL = HSSFFont.BOLDWEIGHT_NORMAL;	//正常

//颜色:

public static final int[] COLOR_WHITE = {255,255,255};	//白色

public static final int[] COLOR_BLACK = {0,0,0};	//白色

//字体

public static final String FONT_NAME_SONGTI = "宋体";	//宋体

public static final String FONT_NAME_CALIBRI = "Calibri";	//Calibri

//数字格式样式

public static final String NUMBER_FORMAT_THOUSANDS = "#,##0";	//千分位

public static final String NUMBER_FORMAT_PERCENT = "0.00%";	//两位百分比

public static final String NUMBER_FORMAT_TWO_DECIMALS = "0.00";	//两位小数

public static final String Y = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

FileOutputStream fos = null;

/**

 * 初始化工作簿,如果存在文件则读取,否则新建

 * @param filePath  Excel文件路径

 * @throws FileNotFoundException

 * @throws IOException

 */

public XlsxUtil(String filePath) throws FileNotFoundException,IOException{

this.filePath = filePath;

System.out.println(filePath);

File f = new File(filePath);

if(f.exists()){

xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));

try{

sheet = xssfWorkbook.getSheetAt(0);

}catch(Exception e){

sheet = xssfWorkbook.createSheet();

}

}else{

xssfWorkbook = new XSSFWorkbook();

sheet = xssfWorkbook.createSheet();

}

}//end Constructor()

public XlsxUtil(XSSFWorkbook xssfWorkbook){

this.xssfWorkbook = xssfWorkbook;

try{

this.sheet = xssfWorkbook.getSheetAt(0);

}catch(Exception e){

sheet = xssfWorkbook.createSheet();

}

}

/**

 * 根据文件流创建

 * @param fis

 * @throws FileNotFoundException

 * @throws IOException

 */

public XlsxUtil(InputStream fis) throws FileNotFoundException,IOException{

xssfWorkbook = new XSSFWorkbook(fis);

sheet = xssfWorkbook.getSheetAt(0);

}

/**

 * 选择指定sheet进行操作

 *  @param 

 *      int n  :  第几个sheet

 *      String newName : 设置sheet的新名称,  为null 时不设置名称

 */

public void setSheet(int n,String newName){

try{

sheet = xssfWorkbook.getSheetAt(n);

}catch(Exception e){

sheet = xssfWorkbook.createSheet();

}

if(newName != null){

xssfWorkbook.setSheetName(n,newName);

}

}//end setSheet

/**

 * 设置指定单元格背景颜色

 * @param row	从0开始算第几行

 * @param column	从0开始算第几列

 * @param rgb	RGB颜色值

 */

public XSSFCellStyle setCellBgColor(int row,int column,int[] rgb,boolean ... isFlush){

XSSFCellStyle cellStyle = getCellStyle(row,column,isFlush);

setCellBgColor(cellStyle, rgb);

getCell(row, column).setCellStyle(cellStyle);

return cellStyle;

}//end setCellBgColor

/**

 * 设置背景颜色

 * @param cellStyle

 * @param rgb

 * @return

 */

public XSSFCellStyle setCellBgColor(XSSFCellStyle cellStyle,int[] rgb){

if(cellStyle == null){

cellStyle = xssfWorkbook.createCellStyle();

}

cellStyle.setFillForegroundColor(new XSSFColor(new Color(rgb[0], rgb[1], rgb[2])));

cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

return cellStyle;

}//end setCellBgColor

private XSSFCellStyle getCellStyle(int row,int column,boolean[] isFlush){

XSSFCellStyle cellStyle = null;

if(isFlush.length == 0 || isFlush[0] == false){

cellStyle = getCell(row, column).getCellStyle();

}else{

cellStyle = xssfWorkbook.createCellStyle();

}

return cellStyle;

}//end getCellStyle

/**

 * 创建样式

 * @param bgColor	背景颜色 RGB

 * @param format	内容格式化

 * @param fontName	字体名称

 * @param fontSize	字体大小

 * @param fontColor	字体颜色

 * @param fontBold	字体粗细

 * @param align	水平居中

 * @param valign	垂直居中

 * @param borderStyle	边框样式

 * @param border	边框位置

 */

public XSSFCellStyle createCellStyle(

int[] bgColor,String format,

String fontName,Short fontSize,int[] fontColor,Short fontBold,

Short align,Short valign,

Short borderStyle,short... border){

XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();

if(bgColor != null && bgColor.length == 3){

setCellBgColor(cellStyle, bgColor);

}

if(format != null &&  !"".equals(format)){

format(cellStyle,format);

}

if(fontName != null && !"".equals(fontName)){

setFont(cellStyle, fontName,fontSize);

}

if(align != null){

setAlign(cellStyle,align);

}

if(valign != null){

setAlign(cellStyle,valign);

}

if(bgColor != null){

setCellBgColor(cellStyle,bgColor);

}

if(fontColor != null){

setFontColor(cellStyle,fontColor);

}

if(borderStyle != null && border != null){

//setMergedRegionBorder(x,y,borderStyle,border);

setBorder(cellStyle, borderStyle, border);

}

if(fontBold != null){

setFontBold(cellStyle, fontBold);

}

return cellStyle;

}

/**

 * 克隆样式

 * @param row	目标行

 * @param column	目标列

 * @param cellStyle	样式

 */

public void cloneCellStyle(int row,int column,XSSFCellStyle cellStyle){

XSSFCell cell = getCell(row, column);

cell.setCellStyle(cellStyle);

}//end cloneCellStyle

/**

 * 设置默认列样式

 * @param indexColumn	目标列

 * @param style	目标行

 */

public void setDefaultColumnStyle(int indexColumn,CellStyle style){

sheet.setDefaultColumnStyle(indexColumn, style);

}//end setDefaultColumnStyle()

/**

 * 格式化单元格数值格式

 * @return

 */

//	public XSSFCellStyle format(String fmt,XSSFCellStyle cellStyle){

//	XSSFDataFormat format = xssfWorkbook.createDataFormat();

//

//	if(cellStyle == null){

//	cellStyle = xssfWorkbook.createCellStyle();

//	}

//

//	cellStyle.setDataFormat(format.getFormat("#,###")); 

//	return cellStyle;

//	}

/**

 * 设置目标单元格内容格式

 * @param r

 * @param c

 * @param fmt

 */

public XSSFCellStyle format(int r,int c,String fmt,boolean ... isFlush){

XSSFCellStyle cellStyle = getCellStyle(r, c, isFlush);

format(cellStyle,fmt);

getCell(r, c).setCellStyle(cellStyle);

return cellStyle;

}//end format

/**

 * 内容格式化

 * @param cellStyle

 * @param fmt

 * @return

 */

public XSSFCellStyle format(XSSFCellStyle cellStyle,String fmt){

if(cellStyle == null){

cellStyle = xssfWorkbook.createCellStyle(); 

}

if(fmt.contains("y") || fmt.contains("m") || fmt.contains("d") ||

fmt.contains("h") || fmt.contains("s") || fmt.contains("$") ||

fmt.contains("¥")){

//日期yyyy年m月d日  货币  ¥#,##0

cellStyle.setDataFormat(xssfWorkbook.createDataFormat().getFormat(fmt));

}else{

//数字  0.00   百分比  0.00%  科学计数法  0.00E+00

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(fmt));

}

return cellStyle;

}//end format

/**

 * 获取当前SHEET最后一行的行号

 * @return

 */

public int getLastRow(){

return sheet.getLastRowNum();

}//end getLastRow

/**

 * 获取指定行最后一列

 * @return

 */

public int getLastCell(int rowNum){

XSSFRow row = sheet.getRow(rowNum);

if(row == null){

return 0;

}

return row.getLastCellNum();

}//end getLastCell

/**

 * 获取整个sheet的最后一列

 * @return

 */

public int getLastCell(){

int lastRow = getLastRow();

int lastCell = 0;

int cell;

for (int i = 0; i < lastRow; i++) {

cell = getLastCell(i);

if(cell > lastCell){

lastCell = cell;

}

}

return lastCell;

}//end getLastCell

/**

 * 图片未缩放进行插入,

 * @param col1	图片左上角坐标

 * @param row1	图片右上角坐标

 * @param imgPath	图片路径

 */

public void insertImage(int row1,int col1,int row2,int col2,String imgPath) {  

         BufferedImage bufferImg = null;     

        //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray    

        try {  

            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();     

            bufferImg = ImageIO.read(new File(imgPath));

            String imgType = "";

            int pictureType = 0;

            

            if(imgPath.endsWith("jpg") || imgPath.endsWith("JPG") || imgPath.endsWith("JPEG") || imgPath.endsWith("jpeg")){

            	imgType = "jpg";

            	pictureType = HSSFWorkbook.PICTURE_TYPE_JPEG;

            }

            else if(imgPath.endsWith("png") || imgPath.endsWith("gif")){

            	imgType = "png";

            	pictureType = XSSFWorkbook.PICTURE_TYPE_PNG;

            }else{

            	throw new RuntimeException("image is false");

            }

            

            ImageIO.write(bufferImg, imgType, byteArrayOut);nullnull
点赞(0)
点了个评