根据excel模板里设置指定的num0,num1,num2,num3.然后替换成想到的内容,生成新的excel文件
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
/**
* Writer: fuanyu
* Date : 14-12-26
* Time : 上午9:43
*/
@SuppressWarnings("unchecked")
public class TestExcel {
/**
* 替换Excel模板文件内容
* @param item 文档数据
* @param sourceFilePath Excel模板文件路径
* @param targetFilePath Excel生成文件路径
*/
public static boolean replaceModel(Map item, String sourceFilePath, String targetFilePath) {
boolean bool = true;
try {
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(sourceFilePath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while(rows.hasNext()){
HSSFRow row = (HSSFRow) rows.next();
if(row!=null)
{
int num = row.getLastCellNum();
for(int i=0;i<num;i++)
{
HSSFCell cell= row.getCell(i);
if(cell!=null)
{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(cell==null || cell.getStringCellValue()==null)
{
continue;
}
String value= cell.getStringCellValue();
if(!"".equals(value))
{
Set<String> keySet = item.keySet();
Iterator<String> it = keySet.iterator();
while (it.hasNext()) {
String text = it.next();
if(value.equalsIgnoreCase(text))
{
cell.setCellValue((String)item.get(text));
break;
}
}
}else{
cell.setCellValue("");
}
}
}
}
// 输出文件
FileOutputStream fileOut = new FileOutputStream(targetFilePath);
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
bool = false;
e.printStackTrace();
}
return bool;
}
public static void main(String[] args) {
Map item = new HashMap();
item.put("num0","000");
item.put("num1","1");
item.put("num2","2");
item.put("num3","3");
item.put("num4","4");
item.put("num5","5");
item.put("num6","6");
//d:\\template.xls为Excel模板文件,d:\\test.xls为程序根据Excel模板文件生成的新文件
replaceModel(item, "d:\\template.xls", "d:\\test.xls");
}
}