使用poi生成Excel文件

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipOutputStream;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

/**
 * 
 * @修改时间：2011-9-24
 * @修改内容：添加单元格加边框、自动调整某列宽度、设置合并单元格
 * 
 */
public class XlsExport {

	// 设置cell编码解决中文高位字节截断
	// private static short XLS_ENCODING = HSSFWorkbook.ENCODING_UTF_16;

	private static enum XlsFormatEm {
		DATE("m/d/yy"), NUMBER("0.00"), CURRENCY("#,##0.00"), PERCENT("0.00%");
		private final String pattern;

		XlsFormatEm(String pattern) {
			this.pattern = pattern;
		}

		public String getPattern() {
			return this.pattern;
		}
	}

	private HSSFWorkbook workbook; //定义Excel文档对象
	private HSSFSheet sheet; //定义sheet页对象
	private HSSFRow row; //定义行对象
	private HSSFFont f; //定义字体对象
	private HSSFCellStyle style; //定义单元格样式对象

	/**
	 * 构造方法，设置字体、对齐方式、单元格加边框
	 * 
	 * @param sheetname
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public XlsExport(String sheetname) {
		this.workbook = new HSSFWorkbook();

		// 设置字体
		this.f = workbook.createFont();
		f.setFontName("宋体");
		f.setFontHeightInPoints((short) 12);

		this.style = workbook.createCellStyle();
		style.setFont(f);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置横向居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置纵向居中

		// 给excel单元格加边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

		this.sheet = workbook.createSheet(sheetname);
		// this.sheet.setDefaultColumnWidth((short) 18);
	}

	public XlsExport(HSSFWorkbook workbook, HSSFSheet sheet) {
		this.workbook = workbook;
		this.sheet = sheet;
		// this.sheet.setDefaultColumnWidth((short) 18);
	}

	/**
	 * 自动调整某列宽度 setAutoColumnWidth
	 * 
	 * @param colNum
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void setAutoColumnWidth(short colNum) {
		sheet.autoSizeColumn(colNum); // 自动调整某列宽度
	}

	/**
	 * 设置合并单元格的区域的风格 setRegionStyle
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	private void setRegionStyle(HSSFSheet sheet, Region region, HSSFCellStyle cs) {
		for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) { //循环每一行
			HSSFRow row = sheet.getRow(i);
			for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
				HSSFCell cell = row.getCell((short) j);
				cell.setCellStyle(cs);
			}
		}
	}

	/**
	 * 定义要合并的单元格
	 * addMerge
	 * @param rowFrom
	 * @param columnFrom
	 * @param rowTo
	 * @param columnTo
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void addMerge(int rowFrom, short columnFrom, int rowTo, short columnTo) {
		Region region = new Region(rowFrom, columnFrom, rowTo, columnTo);// 设置合并的行列
		this.setRegionStyle(sheet, region, style);// 设置合并单元格的风格（加边框）
		this.sheet.addMergedRegion(region);// 将单元格合并
	}

	/**
	 * 导出Excel文件
	 * exportXls
	 * @param xlsFileName文件名路径
	 * @throws RuntimeException
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void exportXls(String xlsFileName) throws RuntimeException {
		FileOutputStream fOut = null;
		try {
			fOut = new FileOutputStream(xlsFileName);
			workbook.write(fOut);
			fOut.flush();
		} catch (FileNotFoundException e) {
			RuntimeException fnf = new RuntimeException("生成导出Excel文件出错!", e);
		} catch (IOException e) {
			RuntimeException io = new RuntimeException("写入Excel文件出错!", e);
		} finally {
			try {
				if (fOut != null) {
					fOut.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 导出Excel文件
	 * exportXls
	 * @param response
	 * @throws RuntimeException
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void exportXls(HttpServletResponse response) throws RuntimeException {
		ServletOutputStream os = null;
		try {
			os = response.getOutputStream();
			workbook.write(os);
			os.flush();
		} catch (FileNotFoundException e) {
			throw new RuntimeException("生成导出Excel文件出错!", e);
		} catch (IOException e) {
			throw new RuntimeException("写入Excel文件出错!", e);
		} finally {
			try {
				if (os != null)
					os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 增加一行
	 * 
	 * @param index 行号
	 */
	public void createRow(HSSFSheet sheet, int index) {
		this.row = sheet.createRow(index);
		this.row.setHeight((short) 285);// 单位换算20等于excel表中的1
	}

	/**
	 * 增加一行
	 * 
	 * @param index 行号
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
		this.row.setHeight((short) 285);
	}

	/**
	 * 根据行号获取一行
	 * getRow
	 * @param rowNum行号
	 * @return
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public HSSFRow getRow(int rowNum) {
		return this.sheet.getRow(rowNum);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index  列号
	 * @param value  单元格填充值
	 */
	public void setCell(int index, String value) {
		HSSFCell cell = this.row.createCell((short) index);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index  列号
	 * @param value  单元格填充值
	 */
	public void setCell(int index, int value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(style);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index  列号
	 * @param value  单元格填充值
	 */
	public void setCell(int index, double value) {
		HSSFCell cell = this.row.createCell((short) index);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue(value);
	}

	/**
	 * 删除文件
	 * 
	 * @param filePathAndName 删除文件的路径+文件名
	 */
	public static void delFile(String filePathAndName) {
		try {
			String filePath = filePathAndName;
			filePath = filePath.toString();
			java.io.File myDelFile = new java.io.File(filePath);
			myDelFile.delete();

		} catch (Exception e) {
			System.out.println("删除文件操作出错");
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @param inputFileName 输入一个文件夹
	 * @param zipFileName 输出一个压缩文件夹，打包后文件名字
	 * @throws Exception
	 */
	public void zip(String inputFileName, String zipFileName) throws Exception {
		zip(zipFileName, new File(inputFileName));
	}

	private void zip(String zipFileName, File inputFile) throws Exception {
		ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
				zipFileName));
		zip(out, inputFile, "");
		out.close();
	}

	private void zip(ZipOutputStream out, File f, String base) throws Exception {
		if (f.isDirectory()) { // 判断是否为目录
			File[] fl = f.listFiles();
			out.putNextEntry(new org.apache.tools.zip.ZipEntry(base + "/"));
			base = base.length() == 0 ? "" : base + "/";
			for (int i = 0; i < fl.length; i++) {
				zip(out, fl[i], base + fl[i].getName());
			}
		} else { // 压缩目录中的所有文件
			out.putNextEntry(new org.apache.tools.zip.ZipEntry(base));
			FileInputStream in = new FileInputStream(f);
			int b;
			System.out.println(base);
			while ((b = in.read()) != -1) {
				out.write(b);
			}
			in.close();
		}
	}

	/**
	 * 新建目录
	 * newFolder
	 * @param folderPath
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void newFolder(String folderPath) {
		String filePath = folderPath;
		filePath = filePath.toString();
		java.io.File myFilePath = new java.io.File(filePath);
		try {
			if (!myFilePath.isDirectory()) { //目录尚不存在
				myFilePath.mkdir();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

示例：

public class Test {
	/**
	 * 生成一个3行3列(不算标题行与列表头)的Excel文件 drawExcel
	 * 
	 * @author tf.li
	 * @data Sep 24, 2011
	 */
	public void drawExcel() {
		int row = 3;
		int col = 3;
		String filePath = "c:/报表/";
		String title = "统计表";
		XlsExport xlse = new XlsExport(title);
		
		 // 第一行，标题行
		xlse.createRow(0);
		for (int i = 0; i < 3; i++) { // 把这一行里的每个单元都填上，防止出现null
			xlse.setCell(i, "");
		}

		xlse.addMerge(0, (short) 0, 0, (short) 3); // 第一行合并单元格 ltf

		 // 第二行，放表头
		xlse.createRow(1);
		for (int i = 0; i < 3; i++) {
			xlse.setCell(i, "第"+(i+1)+"列");		}
		
		// 填入数据，开始画表
		for (row=0; row<3; row++) {
			xlse.createRow(row+2); //从第二行开始
			for(col=0; col<3; col++) {
				xlse.setCell(col, ((row+1)+","+(col+1)));
			}
		}

		for(int i=0; i<3; i++) {
			xlse.setAutoColumnWidth((short)i); //自动调整列宽度 ltf
		}

		xlse.getRow(0).getCell((short)0).setCellValue(filename); //标题要最后填上，防止把表格第一列宽度撑开ltf

		xlse.newFolder(filePath);

		xlse.exportXls(filePath + title + ".xls");