poi 导出复杂表头 execl
1添加依赖(或导入jar包)
2编辑Excel表格
package com.hikvision.test.poi;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* 导出复杂表头execl
* @author zhangdke
* 2019年8月16日
*/
public class PoiUtil {
private PoiUtil() {
}
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private String bDate;
private int year;
/**
* 创建行元素
* @param style 样式
* @param height 行高
* @param value 行显示的内容
* @param row1 起始行
* @param row2 结束行
* @param col1 起始列
* @param col2 结束列
*/
private void createRow(HSSFCellStyle style, int height, String value, int row1, int row2, int col1, int col2){
sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2)); //设置从第row1行合并到第row2行，第col1列合并到col2列
HSSFRow rows = sheet.createRow(row1); //设置第几行
rows.setHeight((short) height); //设置行高
HSSFCell cell = rows.createCell(col1); //设置内容开始的列
cell.setCellStyle(style); //设置样式
cell.setCellValue(value); //设置该行的值
}
/**
* 创建样式
* @param fontSize 字体大小
* @param align 水平位置 左右居中2 居右3 默认居左 垂直均为居中
* @param bold 是否加粗
* @return
*/
private HSSFCellStyle getStyle(int fontSize,int align,boolean bold,boolean border){
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) fontSize);// 字体大小
if (bold){
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font); //设置字体
style.setAlignment((short) align); // 左右居中2 居右3 默认居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中1
if (border){
style.setBorderRight((short) 2);
style.setBorderLeft((short) 2);
style.setBorderBottom((short) 2);
style.setBorderTop((short) 2);
style.setLocked(true);
}
return style;
}
/**
* 根据数据集生成Excel，并返回Excel文件流
* @param data 数据集
* @param sheetName Excel中sheet单元名称
* @param headNames 列表头名称数组
* @param colKeys 列key,数据集根据该key进行按顺序取值
* @return
* @throws IOException
*/
public InputStream getExcelFile(List<Map> data, String sheetName, String[] headNames,
String[] colKeys, int colWidths[],String bDate) throws IOException {
this.bDate = bDate;
workbook = new HSSFWorkbook();
sheet = workbook.createSheet(sheetName);
// 创建表头 startRow代表表体开始的行
int startRow = createHeadCell( headNames, colWidths);
// 创建表体数据
HSSFCellStyle cellStyle = getStyle(14,2,false,true); // 建立新的cell样式
setCellData(data, cellStyle, startRow, colKeys);
//创建表尾
createTailCell(data,data.size()+4,colWidths,colKeys);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
byte[] ba = baos.toByteArray();
ByteArrayInputStream bais = new ByteArrayInputStream(ba);
return bais;
}
/**
* 创建表头
*
* @param headNames
* @param colWidths
*/
private int createHeadCell( String[] headNames, int colWidths[]) {
// 表头标题
HSSFCellStyle titleStyle = getStyle(16,2,true,false);//样式
createRow(titleStyle,0x220,"冶化、防腐公司等外委维修项目结算失效统计表",0,0,0,headNames.length-1);
//第二行
year = Integer.parseInt(bDate.substring(0,4));
String month = bDate.substring(4,6);
int m = Integer.parseInt(month)-1;
Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR,year);
cal.set(Calendar.MONTH,m);//从0开始 0代表一月 11代表12月
int maxDate = cal.getActualMaximum(Calendar.DATE);
String value = "统计时段:"+year+"年"+month+"月"+"01日至"+year+"年"+month+"月"+maxDate+"日";
HSSFCellStyle unitStyle = getStyle(12,2,true,false);//样式
createRow(unitStyle,0x190,value,1,1,0,headNames.length-1);
//第三行表头
HSSFRow row2 = sheet.createRow(2);
row2.setHeight((short) 0x289);
HSSFCellStyle cellStyle = getStyle(15,2,false,true); // 建立新的cell样式
String[] row_three = {"序号", "委托书编号", "工程名称", "委托单位", "外委单位", "提交工作量日期", "受委单位审核","","","车间审核","","","经营决算审核","","","结算中心审核","","","风控部审核","","","总时长"};
for (int i = 0; i < row_three.length; i++) {
Cell tempCell = row2.createCell(i);
tempCell.setCellValue(row_three[i]);
tempCell.setCellStyle(cellStyle);
if (colWidths != null && i < colWidths.length) {
sheet.setColumnWidth(i, 10 * colWidths[i]);
}
}
sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 8));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 15, 17));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 18, 20));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 21, 21));
//第四行表头
HSSFRow row3 = sheet.createRow(3);
row3.setHeight((short) 0x289);
String[] row_four = {"", "", "", "", "", "", "审核时长","规定时长","超时时长","审核时长","规定时长","超时时长","审核时长","规定时长","超时时长","审核时长","规定时长","超时时长","审核时长","规定时长","超时时长","总时长"};
for (int i = 0; i < row_four.length; i++) {
Cell tempCell = row3.createCell(i);
tempCell.setCellValue(row_four[i]);
tempCell.setCellStyle(cellStyle);
// if (colWidths != null && i < colWidths.length) {
// sheet.setColumnWidth(i, 10*colWidths[i]);
// }
}
return 4; //从哪一行开始渲染表体
}
/**
* 创建表体数据
* @param data 表体数据
* @param cellStyle 样式
* @param startRow 开始行
* @param colKeys 值对应map的key
*/
private void setCellData(List<Map> data, HSSFCellStyle cellStyle, int startRow,
String[] colKeys) {
// 创建数据
HSSFRow row = null;
HSSFCell cell = null;
int i = startRow;
if (data != null && data.size() > 0) {
// DecimalFormat df = new DecimalFormat("#0.00");
for (Map<String, Object> rowData : data) {
row = sheet.createRow(i);
row.setHeight((short) 0x279);
int j = 0;
for (String key : colKeys) {
Object colValue = rowData.get(key);
// if (key.equalsIgnoreCase("CITYNAME")){
// colValue = colValue+"XX科技有限公司";
// }else if (key.equalsIgnoreCase("ORDERSUM")||key.equalsIgnoreCase("TRANSFEE")||key.equalsIgnoreCase("ORDREALSUM")){
// colValue = df.format(colValue);
// }
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
if (colValue != null) {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(colValue.toString());
}
j++;
}
i++;
}
}
}
/**
* 创建表尾
* @param size
* @param length
*/
private void createTailCell(List<Map> data,int size,int colWidths[],String[] colKeys) {
HSSFRow row2 = sheet.createRow(size);
row2.setHeight((short) 0x379);
HSSFCellStyle cellStyle = getStyle(15,2,false,true); // 建立新的cell样式
String[] row_three = {"超时小计:", "", "", "", "", "", "rwsum","","","cjsum","","","jysum","","","jssum","","","fksum","","",""};
for (int i = 0; i < row_three.length; i++) {
Cell tempCell = row2.createCell(i);
if (i==6 || i==9 || i==12 || i==15 || i==18) {
int num=0;
int sum=0;
for (int j = i; j < i+3; j++) {
for (Map<String, Object> map : data) {
num = (int) map.get(colKeys[j]);
}
sum+=num;
}
tempCell.setCellValue(sum);
tempCell.setCellStyle(cellStyle);
continue;
}
tempCell.setCellValue(row_three[i]);
tempCell.setCellStyle(cellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(size, size, 0, 5));
sheet.addMergedRegion(new CellRangeAddress(size, size, 6, 8));
sheet.addMergedRegion(new CellRangeAddress(size, size, 9, 11));
sheet.addMergedRegion(new CellRangeAddress(size, size, 12, 14));
sheet.addMergedRegion(new CellRangeAddress(size, size, 15, 17));
sheet.addMergedRegion(new CellRangeAddress(size, size, 18, 20));
}
// 测试
public static void main(String[] args) throws IOException {
PoiUtil excel = new PoiUtil();
List<Map> data = new ArrayList<Map>();
LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
e.put("xh", "1");
e.put("bm", "19182456");
e.put("name", "测试");
e.put("unit", "生产部");
e.put("wwdw", "电解车间");
e.put("date", "2019-08-01");
e.put("swsh", 1);
e.put("swgd", 3);
e.put("swcs", 5);
e.put("cjsh", 4);
e.put("cjgd", 2);
e.put("cjcs", 4);
e.put("jysh", 5);
e.put("jygd", 3);
e.put("jycs", 3);
e.put("jssh", 5);
e.put("jsgd", 2);
e.put("jscs", 2);
e.put("fksh", 1);
e.put("fkgd", 1);
e.put("fkcs", 4);
e.put("time", 6);
data.add(e);
String[] headNames = { "序号", "委托书编码", "工程名称", "委托单位", "外委单位","提交工作日期","审核时长", "规定时长", "超时时长", "审核时长", "规定时长", "超时时长","审核时长", "规定时长", "超时时长", "审核时长", "规定时长", "超时时长", "审核时长", "规定时长", "超时时长","备注" };
String[] keys = { "xh", "bm", "name","unit","wwdw","date","swsh","swgd","swcs","cjsh","cjgd","cjcs","jysh","jygd","jycs","jssh","jsgd","jscs","fksh","fkgd","fkcs","time"};
int colWidths[] = { 200, 400, 400, 400, 400,300,300, 300, 300, 300, 300,300,300, 300, 300, 300, 300,300, 300, 300, 300,200 };
String bDate = "201708";
InputStream input = (excel.getExcelFile(data, "单位", headNames, keys, colWidths,bDate));
File f = new File("e:\\excel.xls");
if (f.exists())
f.delete();
f.createNewFile();
FileOutputStream out = new FileOutputStream(f);
HSSFWorkbook book = new HSSFWorkbook(input);
book.write(out);
out.flush();
out.close();
}
}
3合并规则
对于row1行到row2行如果每行都有值则取第row1行的值为合并后的值;
col1列到col2的合并规则与行合并规则相同，取col1列的值为合并后的值
