java导出excel多重表头
后台代码对多重表头要怎么排列？比如星期一下面还有早上，中午晚上，再下面才是动态数据！
package com.you.excel;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
请用一句话概括功能
- @ClassName:ExcelExport
- @Description:
- @Author:YouHaiDong
- @Date:2015年11月4日 下午2:23:49
*/
public class ExcelExport
{/**
- @Title:ExcelExport
- @Description:
- @param args
- @Date:2015年11月4日 下午2:23:49
- @return: void
@throws Exception
*/
@SuppressWarnings("resource")
public static void main(String[] args) throws Exception
{
// 创建一个Workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页
HSSFSheet sheet = workbook.createSheet("学生表");
// 创建第一行
HSSFRow row = sheet.createRow(0);
// 创建单元格
HSSFCell cell1 = row.createCell(0);
HSSFCell cell2 = row.createCell(1);
HSSFCell cell3 = row.createCell(2);
HSSFCell cell4 = row.createCell(2);
// 设置表头
cell1.setCellValue("学号");
cell2.setCellValue("姓名");
cell3.setCellValue("性别");
cell4.setCellValue("年龄");FileOutputStream stream = new FileOutputStream("d:/student.xls");
workbook.write(stream);
}
}
import java.sql.SQLException;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
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.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.CellRangeAddress;
public class Excel {
private HSSFWorkbook wb = null;
private HSSFSheet sheet = null;
private HSSFRow row = null;
private HSSFCell cell = null;
private HSSFCellStyle titleStyle = null;
private HSSFCellStyle headStyle = null;
private HSSFCellStyle bodyStyle = null;
private int num = 0;
private int headLength = 0;
public Excel() {
wb = new HSSFWorkbook();
sheet = wb.createSheet();
sheet.setDefaultColumnWidth(14);
sheet.setDefaultRowHeight((short)20);
//打印设置
HSSFPrintSetup hps = sheet.getPrintSetup();
hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 设置A4纸
hps.setLandscape(true); // 将页面设置为横向打印模式
// sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
// sheet.setVerticallyCenter(true); // 设置打印页面为垂直居中
//冻结第一行和第二行
sheet.createFreezePane( 0, 2, 0, 2 );
init();
}
/**
* 初始化样式
*/
private void init() {
titleFont();
headFont();
bodyFont();
}
/**
* 设置标题样式
*
*/
private void titleFont() {
HSSFFont titleFont = wb.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 18);
titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
// titleStyle.setBorderTop((short)1);
// titleStyle.setBorderRight((short)1);
titleStyle.setBorderBottom((short)1);
// titleStyle.setBorderLeft((short)1);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
/**
* 设置head样式
*
*/
private void headFont() {
HSSFFont headFont = wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 11);
headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setBorderTop((short)1);
headStyle.setBorderRight((short)1);
headStyle.setBorderBottom((short)1);
headStyle.setBorderLeft((short)1);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
/**
* 设置body样式
*
*/
private void bodyFont() {
HSSFFont bodyFont = wb.createFont();
bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bodyFont.setFontName("宋体");
bodyFont.setFontHeightInPoints((short) 9);
bodyStyle = wb.createCellStyle();
bodyStyle.setFont(bodyFont);
bodyStyle.setBorderTop((short)1);
bodyStyle.setBorderRight((short)1);
bodyStyle.setBorderBottom((short)1);
bodyStyle.setBorderLeft((short)1);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
}
/**
* 生成Excel主方法
* @param xml ：包括title和head的内容和设置
* @param list ：内容
* @param fit ：是否需要宽度自适应
* @return HSSFWorkbook
* @throws SQLException
*/
public HSSFWorkbook createWorkBook(final ExcelHeadXML xml, final List<Object[]> list, boolean fit) throws SQLException {
createTop(xml);
/*
List<Object[]> list1 = new LinkedList<Object[]>();
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
list1.add(new Object[]{"000003","武汉市XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
createBody(list1,true);
*/
createBody(list,true);
//宽度自适应
if(fit){
for(int i=0; i<headLength; i++){
sheet.autoSizeColumn(i);
}
}
return wb;
}
/**
* 生成Excel主方法
* @param xml ：包括title和head的内容和设置
* @param list ：内容
* @param fit ：是否需要宽度自适应
* @return HSSFWorkbook
* @throws SQLException
*/
public HSSFWorkbook createWorkBook(final ExcelHeadXML xml,HttpServletRequest request, final List<Object[]> list, boolean fit) throws SQLException {
createTop(xml,request);
createBody(list,true);
//宽度自适应
if(fit){
for(int i=0; i<headLength; i++){
sheet.autoSizeColumn(i);
}
}
return wb;
}
/**
* 创建Excel的Head和title部分
* @param xml ExcelHeadXML
*/
@SuppressWarnings("deprecation")
private void createTop(final ExcelHeadXML xml,HttpServletRequest request) {
//创建Title部分
if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
row = sheet.createRow(num++);
cell = row.createCell(0);
cell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
.getUniteRowCount()));
}
//创建Head部分
List<String> head = xml.getHead();
if(null != head && head.size() > 0){
headLength = xml.getHeadLength();
row = sheet.createRow(num++);
for(int i=0; i<head.size(); i++){
cell = row.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(new HSSFRichTextString(head.get(i)));
}
}
}
/**
* 创建Excel的Head和title部分
* @param xml ExcelHeadXML
*/
@SuppressWarnings("deprecation")
private void createTop(final ExcelHeadXML xml) {
//创建Title部分
if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
row = sheet.createRow(num++);
cell = row.createCell(0);
cell.setCellStyle(titleStyle);
cell.setCellValue(new HSSFRichTextString(xml.getTitle().trim()));
sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
.getUniteRowCount()));
}
//创建Head部分
List<String> head = xml.getHead();
if(null != head && head.size() > 0){
headLength = xml.getHeadLength();
row = sheet.createRow(num++);
for(int i=0; i<head.size(); i++){
cell = row.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(new HSSFRichTextString(head.get(i)));
}
}
}
/**
* 创建Excel的body部分
* @param list : body部分的数据
* @param useId : body部分是否要序号
* @throws SQLException
*/
private void createBody(final List<Object[]> list, boolean useId) throws SQLException {
if(null != list && list.size() > 0){
Object[] o;
for(int i=0; i<list.size(); i++){
row = sheet.createRow(num++);
row.setHeightInPoints(30);
o = list.get(i);
if(useId){
//序号
cell = row.createCell(0);
cell.setCellStyle(bodyStyle);
cell.setCellValue(new HSSFRichTextString((i+1)+""));
}
for(int j=0; j<o.length; j++){
if(useId){
cell = row.createCell(j+1);
}else{
cell = row.createCell(j);
}
cell.setCellStyle(bodyStyle);
cell.setCellValue(new HSSFRichTextString(emptyToString(o[j])));
}
}
}
}
开始列，开始行，结束列，结束行
和 excelUtil.mergeCellsAndInsertData(sheet, 14, 1, 14, 2, "", titleWcf);
中间的4个数字对应填写即可。
//创建标题(合并单元格) 开始列，开始行，结束列，结束行
excelUtil.mergeCellsAndInsertData(sheet, 0, 0, 14, 0, yearMonth+"表", themWcf);
//创建表头
excelUtil.mergeCellsAndInsertData(sheet, 0, 1, 0, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 1, 1, 1, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 2, 1, 2, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 3, 1, 3, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 4, 1, 4, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 5, 1, 5, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 6, 1, 6, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 7, 1, 8, 1, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 7, 2, 7, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 8, 2, 8, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 9, 1, 9, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 10, 1, 10, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 11, 1, 11, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 12, 1, 12, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 13, 1, 13, 2, "", titleWcf);
excelUtil.mergeCellsAndInsertData(sheet, 14, 1, 14, 2, "", titleWcf);
你可以根据几行几列这样填写，注意:都是0开始
只要表头固定跟普通excel解析一样的，有总共有多少列全部建出来，对应填充数据就可以
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(DateConverter.currentDate(new Date()));
sheet.setDefaultColumnWidth(15);
HSSFRow titleRow = sheet.createRow(0);
HSSFCell titleCell = titleRow.createCell(0);
titleCell.setCellValue("填报工时");
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //合并单元格
注意最后一行 合并单元格
建议使用excel模板做，表头自己定义就行，然后就是对应的填充数据了。