package test;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
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;
/**
* 导出Excel
* 生成的文件格式是.xlsx 最低支持07版的Excel
*
*/
public class ExportExcel {
/** 表空间的名称 */
private String sheetName;
/** 文件路径 */
private String filePath;
/** Excel的首行名称 */
private String[] title;
/** 保存的数据 */
private List<String[]> rows;
/**
* 构造方法
* @param fileName 保存文件的名称
* @param rootPath 保存文件的路径
* @param sheetName 表空间的名称
* @param title Excel的首行名称
* @param rows 保存的数据
*/
public ExportExcel(String fileName, String rootPath, String sheetName, String[] title, List<String[]> rows) {
this.sheetName = sheetName;
this.title = title;
this.rows = rows;
StringBuffer sb = new StringBuffer();
sb.append(rootPath).append(File.separator).append(fileName).append(".xlsx");
this.filePath = sb.toString();
}
/**
* 导出数据
*/
public void export(){
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);//创建工作表
writeRowValue(wb,sheet,1);
}
/**
* 追加行
*/
public void addRows(){
if(exists()){
XSSFWorkbook wb = null;
try {
wb = new XSSFWorkbook((new BufferedInputStream (new FileInputStream(filePath))));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if(isSheetExists(wb)){
XSSFSheet sheet = wb.getSheet(sheetName);
int rowSum = sheet.getLastRowNum();//当前有多少行
writeRowValue(wb,sheet,rowSum+1);
}
}
}
/**
* 添加sheet
*/
public void addSheet(){
if(exists()){
XSSFWorkbook wb = null;
try {
wb = new XSSFWorkbook((new BufferedInputStream (new FileInputStream(filePath))));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
XSSFSheet sheet = wb.createSheet(sheetName);//创建工作表
writeRowValue(wb,sheet,1);
}
}
/**
*
* @param sheet
* @param rowIndex 从第几行开始写入数据
*/
private void writeRowValue(XSSFWorkbook wb, XSSFSheet sheet, int rowIndex){
XSSFCellStyle style = getStyle(wb);//列数据单元格样式
int columnNum = title.length;//定义所需要的列数
if(1 == rowIndex){
XSSFCellStyle columnToStyle = getColumnTopStyle(wb);//列头单元格样式
XSSFRow heard = sheet.createRow(0); //创建第一行
heard.setHeightInPoints(20);//设置行高
for(int n=0; n<columnNum; n++){
sheet.setColumnWidth(n, 24*255);
XSSFCell cell = heard.createCell(n);
cell.setCellValue(title[n]);
cell.setCellStyle(columnToStyle);
}
}
int rowSize = rows.size();
for(int i=0; i<rowSize;i++){
XSSFRow row = sheet.createRow(i + rowIndex);
row.setHeightInPoints(20);
String[] rowValue = rows.get(i);
for(int n=0; n<columnNum; n++){
XSSFCell cell = row.createCell(n);
cell.setCellValue(rowValue[n]);
cell.setCellStyle(style);
}
}
File file = new File(filePath);
try {
FileOutputStream fos = new FileOutputStream(file);
wb.write(fos);
fos.flush();
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 判断sheet是否存在
* 如果该sheet存在的话返回true
* 不存在的话返回false
* @param sheetName
* @param wb
* @return
*/
private boolean isSheetExists(XSSFWorkbook wb){
if(wb.getSheetIndex(sheetName) == -1){
return false;
}
return true;
}
/**
* 判断该文件是否存在
* 如果存在返回true
* @return
*/
private boolean exists(){
File file = new File(filePath);
if(file.exists()){//该文件如果已存在,则不作操作;
return true;
}
return false;
}
/**
* 列头单元格样式
* @param wb
* @return
*/
private XSSFCellStyle getColumnTopStyle(XSSFWorkbook wb){
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)11); //设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
font.setFontName("Courier New"); //设置字体名字
XSSFCellStyle style = wb.createCellStyle();
style.setFont(font); //应用字体
style.setWrapText(false); //设置自动换行
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平对齐的样式为居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐
return style;
}
/**
* 列数据信息单元格样式
* @param wb
* @return
*/
private XSSFCellStyle getStyle(XSSFWorkbook wb){
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)11); //设置字体大小
font.setFontName("Courier New"); //设置字体名字
XSSFCellStyle style = wb.createCellStyle();
style.setFont(font); //应用字体
style.setWrapText(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平对齐的样式为居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐
return style;
}
}
自定义单元格颜色
XSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 153)));
自定义字体颜色
XSSFFont font = wb.createFont();
font.setColor(new XSSFColor(new java.awt.Color(0, 112, 192)));
单元格合并
sheet.addMergedRegion(new CellRangeAddress(起始行号,终止行号, 起始列号,终止列号));
组合(树状结构)
sheet.groupRow(i, j);
sheet.setRowSumsBelow(false);
sheet.setRowSumsRight(false);