poi导出Excel表格的几种实现方式

 poi生成Excel

 

1、有模板，文件头标题都写好，只要循环添加数据的方法

package com.test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class PoiTestExcel {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			InputStream in = new FileInputStream("D:\\report\\1111.xls");
			Workbook work = new HSSFWorkbook(in);
			// 得到excel的第0张表
			Sheet sheet = work.getSheetAt(0);
			// 得到第1行的第一个单元格的样式
			Row rowCellStyle = sheet.getRow(1);
			CellStyle columnOne = rowCellStyle.getCell(0).getCellStyle();
			// 这里面的行和列的数法与计算机里的一样，从0开始是第一
			// 填充title数据
			Row row = sheet.getRow(0);
			Cell cell = row.getCell(0);
			cell.setCellValue("2010年花名测");
			int i = 2;//计数器
			int number = 0;
			// 得到行，并填充数据和表格样式
			for (;i < 10; i++) {
				row = sheet.createRow(i);// 得到行
				cell = row.createCell(0);// 得到第0个单元格
				cell.setCellValue("琳"+i);// 填充值
				cell.setCellStyle(columnOne);// 填充样式
				cell = row.createCell(1);
				cell.setCellValue("女");
				cell.setCellStyle(columnOne);// 填充样式
				cell = row.createCell(2);
				cell.setCellValue(i+20);
				cell.setCellStyle(columnOne);// 填充样式
				// .....给每个单元格填充数据和样式
				number++;
			}
			//创建每个单元格，添加样式，最后合并
			row = sheet.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue("总计：" + number + "个学生");// 填充值
			cell.setCellStyle(columnOne);
			cell = row.createCell(1);
			cell.setCellStyle(columnOne);
			cell = row.createCell(2);
			cell.setCellStyle(columnOne);
			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(i,i,0,2));
			FileOutputStream os = new FileOutputStream("D:\\report\\workbook.xls");
			work.write(os);
			os.close();
		} catch (FileNotFoundException e) {
			System.out.println("文件路径错误");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("文件输入流错误");
			e.printStackTrace();
		}

	}

}

 

2、无模板生成Excel的方法

package com.test;

import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
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.ss.util.CellRangeAddress;

public class PoiCreateExcel {

public static void main(String[] args) throws Exception {
    // 创建Excel的工作书册 Workbook,对应到一个excel文档
    HSSFWorkbook wb = new HSSFWorkbook();

    // 创建Excel的工作sheet,对应到一个excel文档的tab
    HSSFSheet sheet = wb.createSheet("sheet1");

    // 设置excel每列宽度
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // 创建字体样式
    HSSFFont font = wb.createFont();
    font.setFontName("Verdana");
    font.setBoldweight((short) 100);
    font.setFontHeight((short) 300);
    font.setColor(HSSFColor.BLUE.index);

    // 创建单元格样式
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    // 设置边框
    style.setBottomBorderColor(HSSFColor.RED.index);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

    style.setFont(font);// 设置字体

    // 创建Excel的sheet的一行
    HSSFRow row = sheet.createRow(0);
    row.setHeight((short) 500);// 设定行的高度
    // 创建一个Excel的单元格
    HSSFCell cell = row.createCell(0);

    // 合并单元格(startRow，endRow，startColumn，endColumn)
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    // 给Excel的单元格设置样式和赋值
    cell.setCellStyle(style);
    cell.setCellValue("hello world");

    // 设置单元格内容格式
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

    style1.setWrapText(true);// 自动换行

    row = sheet.createRow(1);

    // 设置单元格的样式格式

    cell = row.createCell(0);
    cell.setCellStyle(style1);
    cell.setCellValue(new Date());

    // 创建超链接
    HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
    link.setAddress("http://www.baidu.com");
    cell = row.createCell(1);
    cell.setCellValue("百度");
    cell.setHyperlink(link);// 设定单元格的链接

    FileOutputStream os = new FileOutputStream("D:\\report\\workbook.xls");
    wb.write(os);
    os.close();
}

}

    注：HSSFWorkbook，XSSFWorkbook的区别：前者是解析出来excel 2007 以前版本的，后缀名为xls的，后者是解析excel 2007 版的，后缀名为xlsx。

 

3、无模板且不需要储存直接通过浏览器下载的方式

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 直接通过浏览器下载Excel表格
 * 
 * @author admin
 */
@SuppressWarnings("serial")
public class ExcelPoiTest extends HttpServlet {
	public ExcelPoiTest() {
		super();
	}

	public void destroy() {
		super.destroy();
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
			/********* 创建excel阶段 *********/
			String exportfileName = "poiExcel导出测试";
			//设置响应方式
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			String name = new String((exportfileName).getBytes("gbk"),"iso8859-1");
			response.setHeader("Content-Disposition", "attachment;filename="+ name);
			
			// 创建execl第一行数句(标题)
			// 标题字体
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(
			jxl.write.WritableFont.COURIER, 13,
			jxl.write.WritableFont.NO_BOLD, true);
			jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
			wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
			wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			wcfFC.setBackground(Colour.GREY_50_PERCENT);

			// 创建一个excel文档
			WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream());
			// 创建sheet工作表+ ".xls"
			WritableSheet sheet = book.createSheet(exportfileName + "数据报表.xls",0);
			// 创建字体对象
			WritableFont font = new WritableFont(WritableFont.createFont("黑体"),13, WritableFont.NO_BOLD);
			WritableFont font2 = new WritableFont(WritableFont.createFont("黑体"), 12, WritableFont.NO_BOLD);

			// 创建格式化对象
			WritableCellFormat format = new WritableCellFormat();
			format.setFont(font);// 设置字体
			format.setAlignment(Alignment.CENTRE);// 设置居中
			format.setVerticalAlignment(VerticalAlignment.CENTRE);
			format.setBackground(Colour.GRAY_25);// 设置背景色

			WritableCellFormat format2 = new WritableCellFormat();
			format2.setFont(font2);// 设置字体
			format2.setAlignment(Alignment.CENTRE);// 设置居中
			format2.setVerticalAlignment(VerticalAlignment.CENTRE);
			
			int cols = 3;	//列数
			String headArray[] = {"姓名","性别","年龄"};
			
			/*********** 开始写第二行已下的数据 ************/
			for (int i = 0; i < headArray.length; i++) {
				sheet.addCell(new Label(i,cols, headArray[i], format));
				//10:表示每列的宽
				sheet.setColumnView(i,10);
			}
			
			for (int i = 0; i < 3; i++) {
				sheet.addCell(new Label(0, i + (cols + 1), "熊敏",format2));
				sheet.addCell(new Label(1, i + (cols + 1), "男",format2));
				sheet.addCell(new Label(2, i + (cols + 1), "28",format2));
			}
			
			//加入标题
			sheet.mergeCells(0, 0, cols - 1, cols - 1);
			sheet.addCell(new jxl.write.Label(0, 0,"poi导出文件测试", wcfFC));
			
			//写execel文档
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("存储失败");
		}
	}

	public void init() throws ServletException {

	}
}

 说明：完整例子及jar见附件