工程目录结构以及所需的jar包如下:
工具:poi-2.0-RC2-20040102.jar(注意:这里并没有使用本机自带的Excel2007制作模板)
现在很多工具都要版本对应才能使用,eg:在使用poi-2.0-RC2时 必须使用老版本的Excel新建xls文件,Excel2007
新建的Excel是".xlsx"结尾的,不能使用poi-2.0-RC2。
又比如:在使用itext1.3时,使用现在普遍存在的adobe acrobat7.0以上的版本制作的模板都不行,但使用itext2.0就可以解决中文无法显示(或乱码的问题)
如果你电脑上安装的是Excel2007的话,可能就比较麻烦啦!当然也不是必须使用Excel来建立模板
我们可以这样做:使用poi自身来建立一个.xls文件,这个文件肯定是符合poi版本的
1.fos = new FileOutputStream(file); 直接建立一个xls文档
2.用如下方式
package com.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.FileOutputStream;
/**
* 利用POI API创建Excel文档
* @author fshitd88
*
*/
public class CreateXL {
/** Excel 文件要存放的位置,假定在D盘下。
* 现在D盘下还没有excel文件,通过下面成程序可以创建Excel文件
* */
public static String outputFile = "D:\\test.xls";
public static void main(String argv[]) {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值
// 如要新建一名为"效益指标"的工作表,其语句为:
// HSSFSheet sheet = workbook.createSheet("效益指标");
HSSFSheet sheet = workbook.createSheet();
// 在索引0的位置创建行(最顶端的行)
HSSFRow row = sheet.createRow((short) 0);
// 在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short) 0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("高红成");//写入中文有问题
//解决中文件乱码
HSSFCell cell1 = row.createCell((short) 1);
cell1.setEncoding((short) 1);//解决中文乱码问题
// 在单元格中输入一些内容
cell1.setCellValue("高伟刚");//写入中文有问题
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
System.out.println("已运行 xlCreate() : " + e);
}
}
}
修改Excel文件第一行,就形成了一个简单的模板了
package com.excel;
import java.io.File;
import java.sql.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
//import com.ageon_cnooc.common.util.POIFactoryImpl;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.vo.Agentinfo;
import java.io.FileInputStream;
import java.io.FileOutputStream;
//v1.01 20070423 jasonzhu 修改职级为CA的为LP职级
public class CardExcel {
private static final int COL_POS = 0;
private static final int ROW_POS = 1;
public static void read(List<Agentinfo> agentinfos) {
// 导出的Excel 名称
String toFileName = "D:\\report" + "\\cardreport\\"
+ "agCard_gaoweigang" + ".xls";
//Excel模板(即第一行添加了相应的字段的Excel)
String templateName = "D:/report" + "/cardreport/" +
"agCardTemplate.xls";
POIFSFileSystem pfs = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow hr = null;
HSSFCell cell = null;
FileOutputStream fos = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// HSSFCellStyle cs=null;
int rowPos = ROW_POS;
int colPos = 0;
try {
// xls定义
File file = new File(templateName);
// fos = new FileOutputStream(file); //创建一个xls文件 ,注意不是xlsx文件,新建的是xlsx文件
FileInputStream fis = new FileInputStream(file);
pfs = new POIFSFileSystem(fis);
workbook = new HSSFWorkbook(pfs);
// TM报表
sheet = workbook.getSheetAt(0);
rowPos = ROW_POS;
List cardStats = agentinfos;
for (int i = 0; i < cardStats.size(); i++) {
colPos = COL_POS;
hr = sheet.createRow(rowPos);
cell = hr.createCell((short) (colPos));
// cell.setCellStyle(cs);
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgbegdate());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgentId());
// cell.setHidden(true);
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgentname());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i))
.getAgidno());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgentaddress());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgentpost());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i))
.getAgentphone());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i))
.getAgentsubphone());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i))
.getAgentmobile());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgentmail());
colPos = colPos + 1;
cell = hr.createCell((short) (colPos));
cell.setEncoding((short) 1);
cell.setCellValue(((Agentinfo) cardStats.get(i)).getAgarea());
rowPos = rowPos + 1;
cell = null;
hr = null;
}
// test
// HSSFCellStyle cs=null;
// rowPos = rowPos + 1;
// cell.setCellStyle(cs);
// cs.setHidden(true);
// test
fos = new FileOutputStream(new File(toFileName));
workbook.write(fos);
fos.flush();
} catch (Exception t) {
t.printStackTrace();
} finally {
try {
if (fos != null) {
fos.close();
}
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
workbook = null;
pfs = null;
}
}
/* String agbegdate;
this.agentId = agentId;
this.agentname = agentname;
this.agidno = agidno;
this.agentaddress = agentaddress;
this.agentpost = agentpost;
this.agentphone = agentphone;
this.agentsubphone = agentsubphone;
this.agentmobile = agentmobile;
this.agentmail = agentmail;
this.agarea = agarea;
}
*/
public static void main(String[] args) {
List<Agentinfo> agentinfos = new ArrayList<Agentinfo>();
Agentinfo agentinfo1 = new Agentinfo("20140108", "0000000066", "高伟刚",
"gaoweigang","上海某某区", "20000", "154817191469", "95814154",
"13817191469", "1245508765@qq.com", "shanghai");
agentinfos.add(agentinfo1);
Agentinfo agentinfo2 = new Agentinfo("20140109", "0000000067", "牛海龙",
"liuhailong","上海某某区", "20000", "15817191239", "9555222585",
"13817191469", "138171921459@163.com", "shanghai");
agentinfos.add(agentinfo2);
read(agentinfos);
}
}