引入 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