学习：POI导出EXCEL

于 2012-03-07 17:15:00 发布
阅读量62 收藏
点赞数
文章标签： java 开发工具

目前用的POI版本为3.8.0beta5

Excel的相关类基本以HSSF开头，下面列举几个常用类：

HSSFWorkbook：工作簿，对应Excel文件

HSSFSheet：表单，对应Excel的表单

HSSFRow：行，对应Excel的每一行

HSSFCell：单元格，对应Excel的单元格

HSSFCellStyle：单元格样式，用于定义每个单元格的颜色、边框、字体、字号等等

 

创建的次序应该为HSSFWorkbook -> HSSFSheet -> HSSFRow-> HSSFCell

行高定义由Row决定，列宽由Sheet的columnWidth决定。columnWidth的内容是按照像素来的。

写入文件只需调用HSSFWordbook#write(OutputStream);即可

备忘一段代码：

package com.anrainie.ide.flow.utilities.tool;

import com.anrainie.ide.core.translators.StyleTranslator;
import com.anrainie.ide.flow.utilities.document.MarsApplication;
import com.anrainie.ide.flow.utilities.document.MarsCptContainer;
import com.anrainie.ide.flow.utilities.document.MarsCptLevel1;
import com.anrainie.ide.flow.utilities.document.MarsCptLevel2;
import com.anrainie.ide.flow.utilities.document.MarsProject;
import com.anrainie.ide.flow.utilities.document.MarsTechCpt;
import com.anrainie.ide.flow.utilities.document.MarsTrade;
import com.anrainie.ide.flow.utilities.nls.Messages;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

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;
import org.eclipse.core.resources.IFile;
import org.eclipse.core.resources.IFolder;

/**
 * Excel文档导出工具类
 * 
 * @author caiyu
 * 
 */
public class ExcelTool {
	private static String[] HEADS_TWF = new String[] {
			Messages.ExcelTool_TWF_APPNAME, Messages.ExcelTool_TWF_APPDESC,
			Messages.ExcelTool_TWF_TRADECATEGORY,
			Messages.ExcelTool_TWF_TRADENAME, Messages.ExcelTool_TWF_TRADEDESC,
			Messages.ExcelTool_TWF_TRADEMODELNAME,
			Messages.ExcelTool_TWF_AUTHOR,
			Messages.ExcelTool_TWF_COMPILERESULT, Messages.ExcelTool_Tooltip };

	private static String[] HEADS_TCD = new String[] {
			Messages.ExcelTool_TCD_LEVEL1, Messages.ExcelTool_TCD_LEVEL2,
			Messages.ExcelTool_TCD_NAME_CN, Messages.ExcelTool_TCD_NAME_EN,
			Messages.ExcelTool_TCD_STYLE, Messages.ExcelTool_TCD_ANNOTATION };

	/**
	 * 按Excel格式导出TCD文档
	 * 
	 * @param fileList
	 * @param path
	 */
	public static void exportTcdDocument(List<IFile> fileList, String path) {
		// TODO
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle headStyle = createHeadStyle(workbook);
		HSSFCellStyle contentStyle = createContentStyle(workbook);
		List<MarsCptContainer> containerList = DocumentInfoUtil
				.getTcdDocumentInfo(fileList);
		for (MarsCptContainer container : containerList) {
			HSSFSheet sheet = workbook.createSheet(container.getName());
			sheet.setDefaultColumnWidth(20);
			// 产生表格标题行
			HSSFRow row = sheet.createRow(0);

			for (int i = 0; i < HEADS_TCD.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(headStyle);
				HSSFRichTextString text = new HSSFRichTextString(HEADS_TCD[i]);
				cell.setCellValue(text);
			}
			int rowCount = 1;
			for (int i = 0; i < container.getPretreatList().size(); i++) {
				MarsCptLevel1 level1 = container.getPretreatList().get(i);
				int j = 0;
				int oldCount = rowCount;
				for (; j < level1.getSubList().size(); j++) {
					MarsCptLevel2 level2 = level1.getSubList().get(j);
					int n = 0;
					for (; n < level2.getCptList().size(); n++) {
						MarsTechCpt cpt = level2.getCptList().get(n);

						row = sheet.createRow(rowCount++);
						createCell(level1.getName(), row, contentStyle, 0);
						createCell(level2.getName(), row, contentStyle, 1);
						createCell(cpt.getChineseName(), row, contentStyle, 2);
						createCell(cpt.getEnglishName(), row, contentStyle, 3);
						createCell(StyleTranslator.translateValueToDesc(cpt
								.getStyle()), row, contentStyle, 4);
						createCell(cpt.getAnnocation(), row, contentStyle, 5);
					}
				}
				if (rowCount - 2 <= 0)
					continue;
				sheet.groupRow(oldCount, rowCount - 2);
			}
			sheet.setColumnWidth(3, 100 * 80);
			sheet.setColumnWidth(4, 30 * 80);
			sheet.setColumnWidth(5, 120 * 80);
		}
		FileOutputStream out = null;
		try {
			File file = new File(path);
			if (!file.exists())
				file.createNewFile();
			out = new FileOutputStream(file);
			workbook.write(out);
			DocumentInfoUtil.successRemind();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null)
					out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * 导出Excel格式的TWF文档
	 * 
	 * @param fileList
	 *            TWF文件列表
	 * @param path
	 *            导出文件的位置
	 */
	public static void exportTwfDocument(List<IFolder> fileList, String path) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle headStyle = createHeadStyle(workbook);
		HSSFCellStyle contentStyle = createContentStyle(workbook);

		List<MarsProject> projectList = DocumentInfoUtil
				.getTwfDocumentInfo(fileList);
		for (MarsProject project : projectList) {
			HSSFSheet sheet = workbook.createSheet(project.getName());
			sheet.setDefaultColumnWidth(15);
			// 产生表格标题行
			HSSFRow row = sheet.createRow(0);

			for (int i = 0; i < HEADS_TWF.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(headStyle);
				HSSFRichTextString text = new HSSFRichTextString(HEADS_TWF[i]);
				cell.setCellValue(text);
			}
			int rowCount = 1;
			for (int i = 0; i < project.getAppList().size(); i++) {
				MarsApplication app = project.getAppList().get(i);
				int j = 0;
				int oldCount = rowCount;
				for (; j < app.getTradeList().size(); j++) {
					MarsTrade trade = app.getTradeList().get(j);
					row = sheet.createRow(rowCount++);
					createCell(app.getName(), row, contentStyle, 0);
					createCell(app.getDesc(), row, contentStyle, 1);
					createCell(trade.getCategory(), row, contentStyle, 2);
					createCell(trade.getName(), row, contentStyle, 3);
					createCell(trade.getDesc(), row, contentStyle, 4);
					createCell(trade.getTradeModel(), row, contentStyle, 5);
					createCell(trade.getAuthor(), row, contentStyle, 6);
					createCell(trade.getCompileResult(), row, contentStyle, 7);
					createCell(trade.getTooltip(), row, contentStyle, 8);
				}
				if (rowCount - 2 <= 0)
					continue;
				sheet.groupRow(oldCount, rowCount - 2);
			}
			sheet.setColumnWidth(1, 95 * 80);
			sheet.setColumnWidth(4, 95 * 80);
			sheet.setColumnWidth(7, 95 * 80);
			sheet.setColumnWidth(8, 95 * 80);
		}
		FileOutputStream out = null;
		try {
			File file = new File(path);
			if (!file.exists())
				file.createNewFile();
			out = new FileOutputStream(file);
			workbook.write(out);
			DocumentInfoUtil.successRemind();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null)
					out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}
	}

	/**
	 * 创建cell
	 * 
	 * @param value
	 *            值
	 * @param row
	 *            行对象
	 * @param contentStyle
	 *            cell风格
	 * @param count
	 *            cell位置
	 */
	private static void createCell(String value, HSSFRow row,
			HSSFCellStyle contentStyle, int count) {
		HSSFCell cell = row.createCell(count);
		cell.setCellStyle(contentStyle);
		HSSFRichTextString text = new HSSFRichTextString(value);
		cell.setCellValue(text);
	}

	private static HSSFCellStyle createContentStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();

		style.setFillForegroundColor(HSSFColor.WHITE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		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.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		style.setFont(font);
		return style;
	}

	private static HSSFCellStyle createHeadStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();

		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		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);

		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		style.setFont(font);
		return style;
	}

}

　　应该说POI操作Excel还是比较方便的，除了设置行列的高度、宽度这一部分。

      但是一旦操作Word，就遇到了一堆的问题。所以操作word本人还是选择了itext，目前正在解决自动生成目录的问题。

weixin_33709364
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值