poi生成EXCEL封装（Datagrid数据填充方式）代码,Java代码库,德仔网

代码语言
代码分类
【Java】 poi生成EXCEL封装（Datagrid数据填充方式）
作者:axhack / 发布于2014/12/30/ 316

	//设置表头
	ExportExcel.Columns[] headers = new ExportExcel.Columns[]{
	 new ExportExcel().new Columns("clazzName", "班级名称").setWidth(50),
	 new ExportExcel().new Columns("clazzNo", "编号"),
	 new ExportExcel().new Columns("studycount", "入住人数"),
	 new ExportExcel().new Columns("quantity", "额外备品数").setFormatter(new ExportExcel.Formatter() {
	 //Formatter回调方法可以对每个单元格的值进行处理
	 public Object formatter(Object value, int index) {
	 long v = (Long)value;
	 if(v==0){
	 return "无";
	 }
	 return value;
	 }
	 }),
	 new ExportExcel().new Columns("subtotal", "小计")
	};
	//尾行
	List<Map> footers = new ArrayList<Map>();
	Map footerMaps = new HashMap();
	footerMaps.put("quantity", "合计：");
	footerMaps.put("subtotal", sumtotal);
	footers.add(footerMaps);
	//生成EXCEL,并返回路径
	String path = new ExportExcel("备品统计").exportExcel(dataList, headers, footers);

	

	package plat.honsan.util;
	
	import java.io.File;
	import java.io.FileOutputStream;
	import java.io.IOException;
	import java.io.OutputStream;
	import java.text.SimpleDateFormat;
	import java.util.Date;
	import java.util.HashMap;
	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.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.HSSFColor;
	
	/**
	* 生成EXCEL
	* @author axhack
	*
	*/
	public class ExportExcel {
	
	 private String name;
	
	 public ExportExcel(){}
	 /**
	 * EXCEL名称
	 * @param name
	 */
	 public ExportExcel(String name){
	 this.name = name;
	 }
	
	 /**
	 * 生成EXCEL
	 * @param data 数据
	 * @param headers 表头
	 * @return 返回相对路径，失败返回null
	 */
	 public String exportExcel(List<Map> data, Columns[] headers){
	 return exportExcel(data, headers, null);
	 }
	
	 /**
	 * 生成EXCEL
	 * @param data 数据
	 * @param headers 表头
	 * @param footers 尾行
	 * @return 返回相对路径，失败返回null
	 */
	 public String exportExcel(List<Map> data, Columns[] headers, List<Map> footers){
	 if(data.isEmpty()){
	 return null;
	 }
	 HSSFWorkbook workbook = new HSSFWorkbook();
	 HSSFSheet sheet = workbook.createSheet(name);
	 //生成表头单元样式
	 HSSFCellStyle headerStyle = workbook.createCellStyle();
	 headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	 headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	 headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	 headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	 headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	 headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 //生成一个字体
	 HSSFFont headerFont = workbook.createFont();
	 headerFont.setColor(HSSFColor.WHITE.index);
	 headerFont.setFontHeightInPoints((short) 12);
	 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	 headerStyle.setFont(headerFont);
	
	 //生成数据单元样式
	 HSSFCellStyle dataStyle = workbook.createCellStyle();
	 dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	 dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	 dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	 dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	 dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	 dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	 //生成另一个字体
	 HSSFFont dataFont = workbook.createFont();
	 dataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	 //把字体应用到当前的样式
	 dataStyle.setFont(dataFont);
	
	 //尾行样式
	 HSSFCellStyle footerStyle = workbook.createCellStyle();
	 footerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	 footerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	 footerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	 footerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	 footerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	 footerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 footerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	 //生成一个字体
	 HSSFFont footerFont = workbook.createFont();
	 footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	 footerStyle.setFont(footerFont);
	
	 //生成表头
	 HSSFRow row = sheet.createRow(0);
	 Map<String, Integer> columnIndex = new HashMap<String, Integer>();
	 for (int i=0;i<headers.length;i++) {
	 //设置列宽
	 sheet.setColumnWidth((short)i, (short)(headers[i].getWidth()*256));
	 //填充数据
	 HSSFCell cell = row.createCell((short)i);
	 cell.setCellStyle(headerStyle);
	 HSSFRichTextString text = new HSSFRichTextString(headers[i].getTitle());
	 cell.setCellValue(text);
	
	 columnIndex.put(headers[i].getField(), i);
	 }
	 //生成数据
	 for(int i=0;i<data.size();i++){
	 int excelIndex = i+1; //实际EXCEL行号
	 HSSFRow dataRow = sheet.createRow((short)excelIndex);
	 Map map = data.get(i);
	 for (int h=0;h<headers.length;h++) {
	 HSSFCell cell = dataRow.createCell((short)h);
	 cell.setCellStyle(dataStyle);
	 Object cellValue = headers[h].getFormatter().formatter(map.get(headers[h].getField()), excelIndex);
	 dataProcessing(cell, cellValue);
	 }
	 }
	 //设置尾行
	 if(footers!=null){
	 int footersIndex = data.size()+1;
	 for(int i=0;i<footers.size();i++){
	 footersIndex+=i;
	 HSSFRow footerRow = sheet.createRow((short)footersIndex);
	 Map<String, Object> map = footers.get(i);
	 for(Map.Entry<String, Object> m:map.entrySet()){
	 HSSFCell cell = footerRow.createCell(columnIndex.get(m.getKey()).shortValue());
	 cell.setCellStyle(footerStyle);
	 dataProcessing(cell, m.getValue());
	 }
	 }
	 }
	 //生成EXCEL
	 OutputStream out = null;
	 try {
	 File file = new File(PathUtil.getWebRootPath()+"/excel");
	 if(!file.exists()){
	 file.mkdir();
	 }
	 String outName = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(new Date())+".xls";
	 out = new FileOutputStream(file.getPath()+File.separator+outName);
	 workbook.write(out);
	 return "/excel/"+outName;
	 }catch (IOException e){
	 e.printStackTrace();
	 return null;
	 }finally{
	 if(out!=null){
	 try{out.close();}catch(IOException e){e.printStackTrace();return null;}
	 }
	 }
	 }
	
	 /**
	 * 对各种类型处理
	 * @param cellValue
	 * @param cell
	 */
	 private void dataProcessing(HSSFCell cell, Object value){
	 if(value instanceof Boolean){
	 cell.setCellValue((Boolean)value);
	 }else if(value instanceof Date){
	 cell.setCellValue((Boolean)value);
	 }else if(value instanceof Double){
	 cell.setCellValue((Double)value);
	 }else if(value instanceof Float
	 ||value instanceof Long
	 ||value instanceof Integer){
	 cell.setCellValue(Double.valueOf(value.toString()));
	 }else{
	 HSSFRichTextString str = new HSSFRichTextString((String)value);
	 cell.setCellValue(str);
	 }
	 }
	
	 /**
	 * 数据处理回调
	 */
	 public interface Formatter{
	
	 /**
	 * 数据单元的回调方法
	 * @param value
	 * @param index
	 * @return 返回处理后的数据
	 */
	 public Object formatter(Object value, int index);
	 }
	
	 /**
	 * 表头数据类型
	 */
	 public class Columns{
	
	 private String field;
	 private String title;
	 private int width = 15;
	 private Formatter formatter = new Formatter(){
	 public Object formatter(Object value, int index) {
	 return value;
	 }
	 };
	
	 /**
	 * 设置数据
	 * @param field 字段
	 * @param title 标题
	 */
	 public Columns(String field, String title){
	 this.field = field;
	 this.title = title;
	 }
	
	 public String getField() {
	 return field;
	 }
	
	 public String getTitle() {
	 return title;
	 }
	
	 public Formatter getFormatter() {
	 return formatter;
	 }
	
	 /**
	 * 数据处理回调方法
	 * @param formatter
	 * @return
	 */
	 public Columns setFormatter(Formatter formatter) {
	 this.formatter = formatter;
	 return this;
	 }
	
	 public int getWidth() {
	 return width;
	 }
	
	 /**
	 * 设置表格宽度（字符为单位）
	 * @param width
	 * @return
	 */
	 public Columns setWidth(int width) {
	 this.width = width;
	 return this;
	 }
	
	 }
	}
试试其它关键字
　poi　　　　EXCEL　　Datagrid　
同语言下
可能有用的
axhack贡献的其它代码(1)
.poi生成EXCEL封装（Datagrid数据填充方式）