POI写文件测试

我想测试一下POI怎么写文件，并封装成一个类，用来方便从数据库数据转成excel文件;

这个类中方法是：在构造方法中要输入xls文件的名字;

调用nextSheet()方法，就会在开始在下一个sheet中写数据;

调用writeline()方法就换行;

调用write(Ojbect)方法，就根据数据类型来写入数据;

 

第一次写的代码如下：

 

package com.optionracing.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.RichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelWriter {

	static Logger logger = LoggerFactory.getLogger(ExcelWriter.class);
	private String filename;
	HSSFWorkbook wb;
	private int sheetNum = 0;
	private int rowNum = 0;
	private int cellNum = 0;
	private HSSFSheet sheet;
	private String dateFormat = "yyyy-MM-dd HH:mm:ss"; 
	
	public ExcelWriter(String filename) {
		this.filename = filename;
		wb = new HSSFWorkbook();
		nextSheet();
	}
	
	public void setDateFormat(String dateformat) {
		this.dateFormat = dateformat;
	}

	public void close() {
		FileOutputStream fileOut;
		try {
			fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
			fileOut.close();
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}

	}

	

	public void nextSheet() {
		sheetNum ++;
		sheet = wb.createSheet("sheet" + sheetNum );
		rowNum = 0;
		cellNum = 0;
	}

	public void writeline() {
		rowNum ++;
		cellNum = 0;
	}

	public void write(Object obj) {
		HSSFRow row = sheet.createRow(rowNum);
		HSSFCell cell = row.createCell(cellNum);
		if(obj == null) {
			
		} else if(obj instanceof Date) {
			writeDate((Date)obj, cell);
		} else if(obj instanceof Calendar) {
			writeDate(((Calendar)obj).getTime(), cell);
		} else if(obj instanceof Number) {
			writeNumber((Number)obj, cell);
		} else if(obj instanceof String) {
			writeString((String)obj, cell);
		} else {
			writeString(obj.toString(), cell);
		}
		cellNum ++;
	}
	

	private void writeString(String obj, HSSFCell cell) {
		sheet.getRow(rowNum).getCell(cellNum).setCellValue(obj);
	}

	private void writeNumber(Number obj, HSSFCell cell) {
		if((obj.doubleValue() - obj.longValue()) == 0 ) {
			cell.setCellValue(obj.intValue());
		} else {
			cell.setCellValue(obj.doubleValue());
		}
		
	}

	private void writeDate(Date obj, HSSFCell cell) {
		HSSFCellStyle style = wb.createCellStyle();
		short dateFormat = wb.createDataFormat().getFormat(this.dateFormat);
		style.setDataFormat(dateFormat);
		cell.setCellStyle(style);
		cell.setCellValue(obj);
	}

}

测试代码：

写道

ExcelWriter writer = new ExcelWriter(new Date().getTime() + ".xls");
writer.write("Hello");
writer.write("world");
writer.write("lhc");
writer.writeline();
writer.write("Hello\nWorld");
writer.write("1234");
writer.write(12.34);
writer.nextSheet();
writer.write("1234");
writer.write("1234");
writer.close();

 

测试结果发现：

只有在最后一列的数据才能写入，本人没有查找到是什么原因

 

 

于是转换方法，修改write(Object)方法，不再是：当每一次调用创建一个HSSFCell，

而是在close()方法中统一创建输出

 

代码如下：

写道

package com.optionracing.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 我选用读写xls文件的HSSF，是因为excel 2007兼容97，而反之不行
*
*
* @author Administrator
*
*/

public class ExcelWriter {

static Logger logger = LoggerFactory.getLogger(ExcelWriter.class);
private String filename;
HSSFWorkbook wb;
private int sheetNum = 0;
private String dateFormat = "yyyy-MM-dd HH:mm:ss";
List<List<List<?>>> sheetdata = new ArrayList<List<List<?>>>();
List<List<?>> rowCellData;
List<Object> cellData;

public ExcelWriter(String filename) {
this.filename = filename;
wb = new HSSFWorkbook();
nextSheet();
}

public void setDateFormat(String dateformat) {
this.dateFormat = dateformat;
}

public void close() {
FileOutputStream fileOut;
try {
HSSFSheet sheet;
HSSFRow row;
HSSFCell cell;
for (int sheetNum = 0; sheetNum < sheetdata.size(); sheetNum++) {
sheet = wb.createSheet("sheet" + (sheetNum + 1));
List<List<?>> tmpData = sheetdata.get(sheetNum);
for (int rowNum = 0; rowNum < tmpData.size(); rowNum++) {
row = sheet.createRow(rowNum);
List<?> line = tmpData.get(rowNum);
for (int cellNum = 0; cellNum < line.size(); cellNum++) {
cell = row.createCell(cellNum);
Object obj = line.get(cellNum);
writeObj(obj, cell);
}
}
}

fileOut = new FileOutputStream(filename);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}

}

public void nextSheet() {
rowCellData = new ArrayList<List<?>>();
sheetdata.add(rowCellData);
cellData = new ArrayList<Object>();
rowCellData.add(cellData);
sheetNum++;
}

public void writeline() {
cellData = null;
}

public void write(Object obj) {
if(cellData == null) {
cellData = new ArrayList<Object>();
rowCellData.add(cellData);
}
cellData.add(obj);
}

public void writeObj(Object obj, HSSFCell cell) {
if (obj == null) {

} else if (obj instanceof Date) {
writeDate((Date) obj, cell);
} else if (obj instanceof Calendar) {
writeDate(((Calendar) obj).getTime(), cell);
} else if (obj instanceof Number) {
writeNumber((Number) obj, cell);
} else if (obj instanceof String) {
writeString((String) obj, cell);
} else {
writeString(obj.toString(), cell);
}
}

private void writeString(String obj, HSSFCell cell) {
cell.setCellValue(obj);
}

private void writeNumber(Number obj, HSSFCell cell) {
if ((obj.doubleValue() - obj.longValue()) == 0) {
cell.setCellValue(obj.intValue());
} else {
cell.setCellValue(obj.doubleValue());
}

}

private void writeDate(Date obj, HSSFCell cell) {
HSSFCellStyle style = wb.createCellStyle();
short dateFormat = wb.createDataFormat().getFormat(this.dateFormat);
style.setDataFormat(dateFormat);
cell.setCellStyle(style);
cell.setCellValue(obj);
}


}

 红色部份是主要的修正部份，但是这种方法会浪费内存，用来存放数据的List