加载模板生成excel报表
----------------------------------------------------------------------------------------------------------------------------------------
- package com.stake.mis.baobiao;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
- import javax.servlet.ServletOutputStream;
- 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.HSSFFooter;
- 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.HSSFColor;
- import org.apache.poi.hssf.util.Region;
- import com.stake.mis.bean.User ;
- public class ExeclManager {
- /**
- * list 存放查询返回得结果列表
- * sheetname excel模板中sheet得名字
- * out 输出流对象
- /
- public void getMainCardExcel(List list,String filepath,String sheetname,ServletOutputStream out) throws FileNotFoundException, IOException
- {
- // 读取工作簿
- System.out.println(filepath);
- HSSFWorkbook workbook =new HSSFWorkbook(new FileInputStream(filepath));
- try
- {
- //读取工作表
- HSSFSheet sheet = workbook.getSheet(sheetname);
- HSSFRow row;
- HSSFCell cell = null;
- int a = 8;
- HSSFCellStyle style = this.getStyle(workbook);
- for(int i =0;i<list.size();i++)
- {
- //从查询结果列表中获取对象
- User user = (User )list.get(i);
- //该行以前得部分从模板中取得;
- row = sheet.createRow(a);
- cell = row.createCell((short) 0,HSSFCellStyle.ALIGN_CENTER);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(i+1);
- cell.setCellStyle(style);
- cell = row.createCell((short) 1,HSSFCellStyle.ALIGN_CENTER);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(mc.getUserName());
- cell.setCellStyle(style);
- cell = row.createCell((short) 2,HSSFCellStyle.ALIGN_CENTER);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(mc.getUserAge());
- cell.setCellStyle(style);
- cell = row.createCell((short) 3,HSSFCellStyle.ALIGN_CENTER);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(mc.get某属性());
- cell.setCellStyle(style);
- cell = row.createCell((short) 4,HSSFCellStyle.ALIGN_CENTER);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(mc.getEq某属性());
- cell.setCellStyle(style);
- a++;
- }
- workbook.write(out);
- out.flush();
- out.close();
- }
- catch(Exception e)
- {
- e.printStackTrace();
- }
- }
- public HSSFCellStyle getStyle(HSSFWorkbook workbook)
- {
- // 设置字体;
- HSSFFont font = workbook.createFont();
- //设置字体大小;
- font.setFontHeightInPoints((short)9);
- //设置字体名字;
- font.setFontName("Courier New");
- //font.setItalic(true);
- //font.setStrikeout(true);
- // 设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- //设置底边框;
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- //设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- //设置左边框;
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- //设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- //设置右边框;
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- //设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- //设置顶边框;
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- //设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- //在样式用应用设置的字体;
- style.setFont(font);
- //设置自动换行;
- style.setWrapText(false);
- //设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- return style;
- }
- }
strut的action某方法中在获取查询结果后的部分代码
-------------------------------------------------------------------------------------------------------------------------------------------
- List list = null;
- String filename = "";
- //action中调用service对象的方法,查询User信息;
- list = service.queryUser(查询约束);
- ExeclManager ma = new ExeclManager();
- //设置响应的编码方式;
- response.setCharacterEncoding("gb2312");
- filename = filename + "设备台账.xls";
- response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(filename, "utf-8"));
- response.setContentType("application/vnd.ms-excel;charset=gb2312");
- //excel模板所在的位置;
- String filepath = this.getServlet().getServletContext().getRealPath("/bbmanage");
- ServletOutputStream out = response.getOutputStream();
- //模板名称为example.xls;Sheet1为模板中sheet的名称
- //调用方法生成excel;
- ma.getMainCardExcel(list, filepath+"/example.xls", "Sheet1",out);