poi生成Excel及合并单元格示例

/** * */ package com.geloin.poi.main; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; 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.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.ss.util.CellRangeAddress; import com.geloin.poi.bean.Person; /** * @author Geloin * */ public class PoiTest { /** * * @param args * @throws Exception */ public static void main(String[] args) throws Exception { SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); List<Person> data = new ArrayList<Person>(); Person person1 = new Person(); person1.setName("张三"); person1.setAge(20); person1.setBirthday(format.parse("1989-11-12")); person1.setStudent(true); person1.setHeight(168.8); data.add(person1); Person person2 = new Person(); person2.setName("李四"); person2.setAge(21); person2.setBirthday(format.parse("1988-11-12")); person2.setStudent(false); person2.setHeight(169.8); data.add(person2); String exportPath = "d:/work/proTmp/geloin/poi/export.xls"; OutputStream out = new FileOutputStream(new File(exportPath)); // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet("sheet的名称"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(15); // 设置标题 HSSFCellStyle titleStyle = workbook.createCellStyle(); // 居中显示 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 标题字体 HSSFFont titleFont = workbook.createFont(); // 字体大小 titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); titleStyle.setFont(titleFont); HSSFCellStyle contentStyle = workbook.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont contentFont = workbook.createFont(); contentFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); contentStyle.setFont(contentFont); // 产生表格标题行 HSSFRow row = sheet.createRow(0); String[] headers = new String[] { "序号", "姓名", "年龄", "出生年月", "是否学生", "身高" }; for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); cell.setCellStyle(titleStyle); } int rowCount = 1; for (int i = 0; i < data.size(); i++, rowCount++) { HSSFRow dataRow = sheet.createRow(rowCount); Person person = data.get(i); // 序号 HSSFCell cell0 = dataRow.createCell(0); cell0.setCellValue((i + 1)); cell0.setCellStyle(contentStyle); // 姓名 HSSFCell cell1 = dataRow.createCell(1); cell1.setCellValue(person.getName()); cell1.setCellStyle(contentStyle); // 年龄，转化为String后放到cell里面 HSSFCell cell2 = dataRow.createCell(2); cell2.setCellValue(person.getAge().toString()); cell2.setCellStyle(contentStyle); // 出生年月，转化为String后放到cell里面 HSSFCell cell3 = dataRow.createCell(3); cell3.setCellValue(format.format(person.getBirthday())); cell3.setCellStyle(contentStyle); // 是否学生，转化为String后放到cell里面 HSSFCell cell4 = dataRow.createCell(4); String isStudent = person.isStudent() ? "是" : "否"; cell4.setCellValue(isStudent); cell4.setCellStyle(contentStyle); // 身高，转化为String后放到cell里面 HSSFCell cell5 = dataRow.createCell(5); cell5.setCellValue(String.valueOf(person.getHeight())); cell5.setCellStyle(contentStyle); } // 合并，从第一行到最后一行，从第七列到第七列 sheet.addMergedRegion(new CellRangeAddress(0, rowCount - 1, 6, 6)); // 合并单元格的内容，合并单元格后，仅会保留第一行，第七列的内容，所以设置第一行第七列的内容 HSSFCell cell6 = row.createCell(6); cell6.setCellStyle(contentStyle); cell6.setCellValue("合并单元格的内容"); workbook.write(out); } }