java动态生成excel_POI动态生成Excel

packagecom.epipe.plm.pdc;importjava.io.IOException;importjava.io.OutputStream;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFRichTextString;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.util.Region;importcom.rh.core.base.Bean;/*** 利用开源组件POI3.0.2动态导出EXCEL文档

*

*@authorliujunzhe

*@param

* 应用泛型，代表任意一个符合javabean风格的类

* 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()

* byte[]表jpg格式的图片数据*/

public class ExportExcel{public void exportExcel(String title, Listbeans, OutputStream out) {

exportExcel(title, beans, out,"yyyy-MM-dd");

}/*** 根据集合Bean动态生成Excel*/@SuppressWarnings("unchecked")public void exportExcel(String title, Listbeans, OutputStream out,

String pattern) {//声明一个工作薄

HSSFWorkbook workbook = newHSSFWorkbook();//生成一个表格

HSSFSheet sheet =workbook.createSheet(title);//设置表格默认列宽度为20个字节

sheet.setDefaultColumnWidth(20);//标题样式

HSSFCellStyle style =workbook.createCellStyle();//边框设置

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setBorderTop(HSSFCellStyle.BORDER_THIN);//字体居中

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//生成一个字体

HSSFFont font =workbook.createFont();

font.setFontHeightInPoints((short) 16);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//把字体应用到当前的样式

style.setFont(font);//样式 1

HSSFCellStyle style2 =workbook.createCellStyle();

style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style2.setBorderRight(HSSFCellStyle.BORDER_THIN);

style2.setBorderTop(HSSFCellStyle.BORDER_THIN);

style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//生成另一个字体

HSSFFont font2 =workbook.createFont();

font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//把字体应用到当前的样式

style2.setFont(font2);//指定当单元格内容显示不下时自动换行

style2.setWrapText(true);//样式2

HSSFCellStyle style3 =workbook.createCellStyle();

style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style3.setBorderRight(HSSFCellStyle.BORDER_THIN);

style3.setBorderTop(HSSFCellStyle.BORDER_THIN);

style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//生成另一个字体

HSSFFont font3 =workbook.createFont();

font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//把字体应用到当前的样式

style3.setFont(font3);//指定当单元格内容显示不下时自动换行

style3.setWrapText(true);//标题行

sheet.addMergedRegion(new Region(0, (short) (0), 0, (short) 6));

HSSFRow row= sheet.createRow(0);

HSSFCell cell= row.createCell(0);

row.setHeight((short) 800);

cell.setCellStyle(style);

HSSFRichTextString text= newHSSFRichTextString(title);

cell.setCellValue(text);//数据行

int index = 1;int r = 1;int rf = 3;int j = 1;for(Bean b : beans) {

sheet.addMergedRegion(new Region(r, (short) (0), rf, (short) 0));

sheet.addMergedRegion(new Region(r, (short) (2), r, (short) 6));

sheet.addMergedRegion(new Region(rf, (short) (2), rf, (short) 6));

r+= 3;

rf+= 3;

row= sheet.createRow(index++);

row.setHeight((short) 500);

sheet.setColumnWidth(0, 30 * 35);for (short i = 0; i < 7; i++) {

cell=row.createCell(i);if (i == 1) {

cell.setCellStyle(style3);

}else{

cell.setCellStyle(style2);

}if (i == 1) {

cell.setCellValue("工作任务");

}else if (i == 2) {

cell.setCellValue(b.getStr("WORK_TASK"));

}else if (i == 0) {

cell.setCellValue(j++);

}

}

row= sheet.createRow(index++);

row.setHeight((short) 500);for (short i = 0; i < 7; i++) {

cell=row.createCell(i);if (i == 1 || i == 3 || i == 5) {

cell.setCellStyle(style3);

}else{

cell.setCellStyle(style2);

}if (i == 1) {

cell.setCellValue("主办部门");

}else if (i == 2) {

cell.setCellValue(b.getStr("RESPON_DEPT"));

}else if (i == 3) {

cell.setCellValue("布置时间");

}else if (i == 4) {

cell.setCellValue(b.getStr("FACT_START"));

}else if (i == 5) {

cell.setCellValue("落实时间");

}else if (i == 6) {

cell.setCellValue(b.getStr("FACT_FINISH"));

}

}

row= sheet.createRow(index++);

row.setHeight((short) 1000);for (short i = 0; i < 7; i++) {

cell=row.createCell(i);if (i == 1) {

cell.setCellStyle(style3);

}else{

cell.setCellStyle(style2);

}if (i == 1) {

cell.setCellValue("落实情况");

}else if (i == 2) {

cell.setCellValue(b.getStr("CARRY_SITUATION"));

}

}

}try{

workbook.write(out);

}catch(IOException e) {

e.printStackTrace();

}

}/*** 导出 Excel

*@paramparam

*@throwsIOException*/

public voidexport(ParamBean param) {

HttpServletResponse response=Context.getResponse();

HttpServletRequest request=Context.getRequest();

String paramStr= param.getStr("param");

Bean paramBean=JsonUtils.toBean(paramStr);

String dataId= paramBean.getStr("pkCodes");

String dataIdArrayList= "('" + dataId.replaceAll(",", "','") + "')";

String sql= "select t.type, t.WORK_TASK,t.RESPON_DEPT,t.FACT_START,t.FACT_FINISH,t.CARRY_SITUATION ,m.issue from PDC_WORK_TASK t, PDC_WEEK_MEET m WHERE t.WEEK_MEET_ID = m.ID and t.ID in "

+dataIdArrayList;//任务集合

List beans =Context.getExecutor().query(sql);

String fileName= "";//文件名称

String title = "";//标题

int issue = beans.get(0).getInt("issue");//期号

if (beans.get(0).getStr("type").equals("1")) {

fileName= "第" + issue + "期任务落实情况";

title= "上周例会落实情况";

}else{

fileName= title = "其他工作落实情况";

}

response.setContentType("application/x-zip-compressed;charset=utf-8");

RequestUtils.setDownFileName(request, response, fileName+ ".xls");//设置文件名称

javax.servlet.ServletOutputStream outPutStream= null;try{//工作表对象

ExportExcel ex = new ExportExcel();

outPutStream=response.getOutputStream();

ex.exportExcel(title, beans, outPutStream);

}catch(IOException e) {

e.printStackTrace();

}finally{try{

outPutStream.close();

}catch(IOException e) {

e.printStackTrace();

}

}

}

}