按照模板导出Excle

1、一个工具类ExcelExportUtil 

 

public class ExcelExportUtil {
//表头
private String title;
//各个列的表头
private String[] heardList;
//各个列的元素key值
private String[] heardKey;
//需要填充的数据信息
private List<Map> data;
//字体大小
private int fontSize = 12;
//行高
private int rowHeight = 30;
//列宽
private int columWidth = 200;
//工作表
private String sheetName = "sheet1";

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}

public String[] getHeardList() {
return heardList;
}

public void setHeardList(String[] heardList) {
this.heardList = heardList;
}

public String[] getHeardKey() {
return heardKey;
}

public void setHeardKey(String[] heardKey) {
this.heardKey = heardKey;
}

public List<Map> getData() {
return data;
}

public void setData(List<Map> data) {
this.data = data;
}

public int getFontSize() {
return fontSize;
}

public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}

public int getRowHeight() {
return rowHeight;
}

public void setRowHeight(int rowHeight) {
this.rowHeight = rowHeight;
}

public int getColumWidth() {
return columWidth;
}

public void setColumWidth(int columWidth) {
this.columWidth = columWidth;
}

public String getSheetName() {
return sheetName;
}

public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}

/**
* 根据下标写入数据
* @param wb
* @param sheetNum
*/
public void addReportDatas(HSSFWorkbook wb,int sheetNum){
HSSFSheet wbSheet = wb.getSheetAt(sheetNum);
//开始写入实体数据信息
int a = 5;
for (int i = 0; i < data.size(); i++) {

HSSFRow roww = wbSheet.getRow(a);
Map map = data.get(i);
HSSFCell cell = null;
for (int j = 2; j < heardKey.length; j++) {
System.out.println("i=" + i + "j=" +j + "a=" + a);
cell = roww.getCell(j+4);
Object valueObject = map.get(heardKey[j]);
String value = null;
if (valueObject == null) {
valueObject = "";
}
if (valueObject instanceof String) {
//取出的数据是字符串直接赋值
value = (String) map.get(heardKey[j]);
} else if (valueObject instanceof Integer) {
//取出的数据是Integer
value = String.valueOf(((Integer) (valueObject)));
} else if (valueObject instanceof BigDecimal) {
//取出的数据是BigDecimal
value = String.valueOf(((BigDecimal) (valueObject)));
} else {
value = valueObject.toString();
}
cell.setCellValue(value==null ? "" : value);
}
a++;
}

}


/**
* 开始导出数据信息
*
*/
public void exportExport(HSSFWorkbook wb,int sheetNum, HttpServletRequest request, HttpServletResponse response) throws IOException {
//检查参数配置信息
checkConfig();
//创建工作表
HSSFSheet wbSheet = wb.createSheet();
wb.setSheetName(sheetNum,this.sheetName);
//设置默认行宽
wbSheet.setDefaultColumnWidth(30);

// 标题样式（加粗，垂直居中）
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFFont fontStyle = wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小
cellStyle.setFont(fontStyle);

//在第0行创建rows (表标题)
HSSFRow title = wbSheet.createRow((int) 0);
title.setHeightInPoints(40);//行高
HSSFCell cellValue = title.createCell(0);
cellValue.setCellValue(this.title);
cellValue.setCellStyle(cellStyle);
wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1)));
//设置表头样式，表头居中
HSSFCellStyle style = wb.createCellStyle();
//设置单元格样式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) this.fontSize);
style.setFont(font);
//在第1行创建rows
HSSFRow row = wbSheet.createRow((int) 1);
//设置列头元素
HSSFCell cellHead = null;
for (int i = 0; i < heardList.length; i++) {
cellHead = row.createCell(i);
cellHead.setCellValue(heardList[i]);
cellHead.setCellStyle(style);
}

//设置每格数据的样式 （字体红色）
HSSFCellStyle cellParamStyle = wb.createCellStyle();
HSSFFont ParamFontStyle = wb.createFont();
cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
ParamFontStyle.setColor(HSSFColor.DARK_RED.index); //设置字体颜色 (红色)
ParamFontStyle.setFontHeightInPoints((short) this.fontSize);
cellParamStyle.setFont(ParamFontStyle);
//设置每格数据的样式2（字体蓝色）
HSSFCellStyle cellParamStyle2 = wb.createCellStyle();
cellParamStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellParamStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont ParamFontStyle2 = wb.createFont();
ParamFontStyle2.setColor(HSSFColor.BLUE.index); //设置字体颜色 (蓝色)
ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);
cellParamStyle2.setFont(ParamFontStyle2);
//开始写入实体数据信息
int a = 2;
for (int i = 0; i < data.size(); i++) {
HSSFRow roww = wbSheet.createRow((int) a);
Map map = data.get(i);
HSSFCell cell = null;
for (int j = 0; j < heardKey.length; j++) {
cell = roww.createCell(j);
cell.setCellStyle(style);
Object valueObject = map.get(heardKey[j]);
String value = null;
if (valueObject == null) {
valueObject = "";
}
if (valueObject instanceof String) {
//取出的数据是字符串直接赋值
value = (String) map.get(heardKey[j]);
} else if (valueObject instanceof Integer) {
//取出的数据是Integer
value = String.valueOf(((Integer) (valueObject)));
} else if (valueObject instanceof BigDecimal) {
//取出的数据是BigDecimal
value = String.valueOf(((BigDecimal) (valueObject)));
} else {
value = valueObject.toString();
}
//设置单个单元格的字体颜色
/*if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){
if((Long)map.get("ddNum")!=null){
if((Long)map.get("sjNum")==null){
cell.setCellStyle(cellParamStyle);
} else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){
if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) {
cell.setCellStyle(cellParamStyle);
}
if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) {
cell.setCellStyle(cellParamStyle2);
}
}else {
cell.setCellStyle(style);
}
}
}*/
cell.setCellValue(value==null ? "" : value);
}
a++;
}

}

/**
* 检查数据配置问题
*
* @throws IOException 抛出数据异常类
*/
protected void checkConfig() throws IOException {
if (heardKey == null || heardList.length == 0) {
throw new IOException("列名数组不能为空或者为NULL");
}

if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
throw new IOException("字体、宽度或者高度不能为负值");
}

if (sheetName==null || sheetName.equals("")) {
throw new IOException("工作表表名不能为NULL");
}
}
}

2、调用方法

ExcelExportUtil exportUtil = new ExcelExportUtil();
File file = new File("E:\\xxxx.xls");
FileInputStream inputStream;
POIFSFileSystem fSystem;
HSSFWorkbook workbook = null;
try {
inputStream = new FileInputStream(file);
fSystem = new POIFSFileSystem(inputStream);
workbook = new HSSFWorkbook(fSystem);　　//实例化workbook
} catch (FileNotFoundException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


FileOutputStream oStream = null;

try {
oStream = new FileOutputStream("d:\\xxx.xls");
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

exportUtil.setData(comcostMap);
exportUtil.setHeardKey(costproperties);

try {

exportUtil.addCostDatas(workbook, 9);
workbook.write(oStream);
oStream.close();
} catch (IOException e) {
e.printStackTrace();
}