关于java写office Excel 2007的方法

说明:
① writeRecord(String[] strings) 当传一个数组参数过来的时候,数组的每一项写到行的每个列的格里面.示例参照注释掉的main方法.
② writeRecord(String arg) 这一个字符串是行的一个列格,如新起一行需要调用newLine()方法,示例参照main方法.
很少写文章有什么语意不通的地方还望谅解 ,如有问题再行沟通.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * ExcelWriter Description
 * 
 */
public class ExcelWriter {

	private OutputStream outputStream = null;
	private String sheetName="sheet1";
	private XSSFWorkbook workbook;

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	/**
	 * @param fileOutputStream
	 * @param forName
	 * @throws IOException
	 */
	public ExcelWriter(String filePath) throws IOException {
		workbook = new XSSFWorkbook();
		this.outputStream = new FileOutputStream(filePath);
	}

	public void close() {
		try {
			workbook.write(outputStream);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.flush();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void writeRecord(String[] strings) throws IOException {
		int sheetNum = workbook.getNumberOfSheets();
		XSSFSheet sheet = null;
		if (sheetNum == 0) {
			sheet = workbook.createSheet();
			workbook.setSheetName(0, sheetName);
		} else {
			sheet = workbook.getSheetAt(0);
		}
		XSSFCell cell = null;
		XSSFCellStyle cs = null;
		XSSFRichTextString xssfValue = null;
		int rowNum = sheet.getLastRowNum();
		rowNum++;
		XSSFRow row = sheet.createRow(rowNum);
		for (int n = 0; n < strings.length; n++) {// 写出列
			cell = row.createCell(n);
			cs = cell.getCellStyle();
			cs.setFillPattern(XSSFCellStyle.ALIGN_GENERAL);
			cs.setWrapText(true);
			cs.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
			cell.setCellStyle(cs);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			xssfValue = new XSSFRichTextString(strings[n]);
			cell.setCellValue(xssfValue);
		}
	}

	/*
	 * public static void main(String[] args) throws IOException { ExcelWriter
	 * writer = null; try { writer = new
	 * ExcelWriter("D:\\test\\test.xlsx"); } catch
	 * (FileNotFoundException e) { System.out.println("112"); }
	 * writer.setSheetName("sheet1"); List<String[]> list = new
	 * ArrayList<String[]>(); list.add(new String[] { "name", "张三",
	 * "11" }); list.add(new String[] { "age", "34", "22" });
	 * list.add(new String[] { "sex", "man", "33" }); 
          * for (int i = 0; i < list.size(); i++)
	 * { String[] arg = list.get(i); writer.writeRecord(arg); } writer.close();
	 * }
	 */

	public static void main(String[] args) throws IOException {
		ExcelWriter writer = null;
		try {
			writer = new ExcelWriter("D:\\test\\test.xlsx");
		} catch (FileNotFoundException e) {
		}
		writer.setSheetName("sheet1");
		List<String> list = new ArrayList<String>();
		list.add("aaa");
		list.add("bbb");
		list.add("ccc");
		list.add("\n");
		list.add("ddd");
		list.add("");
		list.add("eee");
		for (int i = 0; i < list.size(); i++) {
			String arg = list.get(i);
			writer.writeRecord(arg);
		}
		writer.close();
	}

	public void newLine() {
		writeRecord("\n");
	}

	/**
	 * @param arg
	 */
	public void writeRecord(String arg) {
		int sheetNum = workbook.getNumberOfSheets();
		XSSFSheet sheet = null;
		if (sheetNum == 0) {
			sheet = workbook.createSheet();
			workbook.setSheetName(0, sheetName);
		} else {
			sheet = workbook.getSheetAt(0);
		}
		XSSFCell cell = null;
		XSSFCellStyle cs = null;
		XSSFRichTextString xssfValue = null;
		XSSFRow row = null;
		int rowNum = sheet.getLastRowNum();
		if (rowNum == -1) {
			rowNum++;
			row = sheet.createRow(rowNum);
		} else if (arg.equals("\n")) {
			rowNum++;
			row = sheet.createRow(rowNum);
			return;
		} else {
			row = sheet.getRow(rowNum);
		}
		int colNum = row.getLastCellNum();
		if (colNum == -1) {
			colNum++;
		}
		cell = row.createCell(colNum);
		colNum++;
		cs = cell.getCellStyle();
		cs.setFillPattern(XSSFCellStyle.ALIGN_GENERAL);
		cs.setWrapText(true);
		cs.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
		cell.setCellStyle(cs);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		xssfValue = new XSSFRichTextString(arg);
		cell.setCellValue(xssfValue);
	}
}

评论

4 楼 hantsy 2009-03-27  

2007 就没有必要用poi来写的了，直接用xml就行了。。。

3 楼 zyihang 2009-03-27  

支持OOXML -- Apache POI 3.5 beta 5 版本发布
准备试一下最新的POI，如果通过指定XML来设定excel的样式和属性，java部分只负责处理数据部分，相信会解放很大的工作量。

2 楼 zyihang 2009-03-26  

poi的使用中有一个问题，就是文件超过5M的时候会变得很慢。还有就是生成文件的样式问题，以前做的时候采用模板的方式。
感觉只是通过改扩展文件名xlsx来实现的，希望与楼主多沟通。

1 楼 xiaojianhx 2009-03-25  

方法很不错,学习下

我用过一种方法,比较简单的,
画个Excel-->另存为a.html-->查看源代码-->保存到一个jsp文件-->加上需要的jsp头指令,当页面到达此jsp的时候,会出现一个下载对话框