功能需求是根据客户提供的excel模板，程序动态填充其中的一些数据。该模板包含大量的宏，刚拿到的时候头都要晕了，本人虽天天和电脑打交道，但是excel咱不是高手啊，什么宏，之前光听过，听着都觉得是高级的东西，就晕，但作为富有责任心的程序员，咱得攻克，也真的攻克了，现在鄙人去搞财务也没问题啊。
之前看各种评论说poi普遍对excel的支持更多一些，比较好用，具体jxl不好用在哪里，没有具体的说法，我就以为无伤大雅，而且项目之前的一些功能都是用jxl的，我就继续沿用jxl。首先来说下流程：
1.根据excel模板创建新工作簿，本质就是copy一份新的，在新的上面进行后续操作。
2.读取其中两个sheet页，对其中内容进行修改。
3.设置密码保护。
4.给第一个sheet页设置选中状态。
5.写出excel文件，关闭流之类的。
下面直接贴代码，这都是沥心之作啊，一笔一画都是心血，程序猿亏心亏脑啊，天气炎热，得好好休养。
首先是变量，这玩意是我自己项目需要，贴出来只为大家看代码看的明白，没啥意思的哈。
int colRefNo = 8; //RefNo所在列,列数是从0开始
int colVari = 9; //Variables所在列
int colChannel = 10; //Channel所在列
int colAFYP= 11; //AFYP所在列
int colNBSP= 12; //NBSP所在列
int colVONB= 14; //VONB所在列
int channelNum = 8; //excel模板channel的个数
int channelPart = 35; //每个channel的部分占多少行
int startRow = 18; //excel模板上数据的起始行，行数从0开始的
先贴jxl，断断续续搞了近一个月，中间端午回家，要不是因为它，我完全可以毫无牵挂地休上半个月到一个月的，说起都是泪啊。
public String modifyExcel(){
String infilepath = "c:/AA.xls";//模板文件位置
String filePath = "c:/test/";
String fileName = "jxlFile.xls";
String targetfile = filePath + fileName;//生成文件位置
File file = new File(targetfile);
//判断文件夹是否存在，不存在就创建
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
//创建新工作簿
Workbook rw = null;
WritableWorkbook wwb = null;
try {
rw = Workbook.getWorkbook(new File(infilepath)); //读入excel模板
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
try {
wwb = Workbook.createWorkbook(file, rw); //根据现有只读文件创建一个可写入的Excel工作薄对象
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
//读取第一张隐藏的工作表，取消隐藏属性
WritableSheet ws0 = wwb.getSheet(0);
ws0.setHidden(true);
System.out.println("===ws0.isHidden()="+ws0.isHidden()+"==ws0.isProtected()==="+ws0.isProtected());
WritableSheet wsPP = wwb.getSheet(2); //读取第三张工作表,PP
WritableSheet wsNP = wwb.getSheet(3); //读取第四张工作表,NP
wsPP = modifySheet(wsPP,"PP"); //对PP修改内容
wsNP = modifySheet(wsNP,"NP"); //对NP修改内容
SheetSettings ssPP = wsPP.getSettings();//设置PP的密码保护
ssPP.setPassword("ABCDEFG");
ssPP.setProtected(true);
SheetSettings ssNP = wsNP.getSettings();//设置NP的密码保护
ssNP.setPassword("ABCDEFG");
ssNP.setProtected(true);
ssNP.setSelected(false);
//读取第二张的工作表，设置选中状态
WritableSheet ws1 = wwb.getSheet(1);
ws1.getSettings().setSelected(true);
//写入Excel对象
try {
wwb.write();
} catch (Exception e) {
return e.getMessage();
}
//关闭可写入的Excel对象
try {
wwb.close();
} catch (Exception e) {
e.printStackTrace();
return e.getMessage();
}
//关闭只读的Excel对象
rw.close();
return "文件"+fileName+"已生成在"+filePath+"下！";
}
private WritableSheet modifySheet(WritableSheet ws,String pnp) {
int start = startRow;
DailySalesBean dsb = new DailySalesBean();
dsb.setPnp(pnp);
for (int j= 0 ; j< channelNum; j++){
int end = start+channelPart;
for(int i= start;i< end;i++){
Cell cellRefNo = ws.getCell(colRefNo, i);
Cell cellVari = ws.getCell(colVari, i);
Cell cellChannel = ws.getCell(colChannel, i);
//获取excel中某些列的值作为查询条件
dsb.setRefno(ObjectUtil.isEmpty(cellRefNo.getContents())?"":cellRefNo.getContents());
dsb.setVari(ObjectUtil.isEmpty(cellVari.getContents())?"":cellVari.getContents());
dsb.setChannel(ObjectUtil.isEmpty(cellChannel.getContents())?"":cellChannel.getContents());
//根据条件得出数据结果
DailySalesBean dsb2 = getDailyData(dsb);
WritableCell afypCell = ws.getWritableCell(colAFYP,i);
if(afypCell.getType() == CellType.NUMBER ){//单元格的数据格式，保证是number类型，涉及到自动计算
Number afyp = (Number)afypCell;
afyp.setValue(Double.parseDouble(dsb2.getAfyp()));//修改单元格的数值
}
WritableCell nbspCell = ws.getWritableCell(colNBSP,i);
if(nbspCell.getType() == CellType.NUMBER ){
Number nbsp = (Number)nbspCell;
nbsp.setValue(Double.parseDouble(dsb2.getNbsp()));
}
WritableCell vonbCell = ws.getWritableCell(colVONB,i);
if(vonbCell.getType() == CellType.NUMBER ){
Number vonb = (Number)vonbCell;
vonb.setValue(Double.parseDouble(dsb2.getVonb()));
}
//上面是获取单元格，直接修改值。下面注释掉的是另外一种方法，是new一个新的单元格，设置完值之后，add到sheet页里面去，个人觉得有种多余，应该浪费资源会多一
//点。但是在将数据单元格改为字符串单元格，涉及到数据类型更改的时候，后一种方法就派上用场了。
/* CellFormat cf1 = null;
CellFormat cf2 = null;
CellFormat cf3 = null;
if(j==0 && k==1 ){
Cell afypCell_old = ws.getCell(colAFYP,i);
Cell nbspCell_old = ws.getCell(colNBSP,i);
Cell vonbCell_old = ws.getCell(colVONB,i);
cf1 = afypCell_old.getCellFormat();
cf2 = nbspCell_old.getCellFormat();
cf3 = vonbCell_old.getCellFormat();//为保持原有单元格的样式，譬如边框，颜色，字体之类的，需要获取原有样式赋到新单元格上
}
Number afypCell = new Number(colAFYP, i, Double.parseDouble(dsb2.getAfyp()));
afypCell.setCellFormat(cf1);
try {
ws.addCell(afypCell);
} catch (Exception e) {
e.printStackTrace();
}
Number nbspCell = new Number(colNBSP, i, Double.parseDouble(dsb2.getNbsp()));
nbspCell.setCellFormat(cf2);
try {
ws.addCell(nbspCell);
} catch (Exception e) {
e.printStackTrace();
}
Number vonbCell = new Number(colVONB, i, Double.parseDouble(dsb2.getVonb()));
vonbCell.setCellFormat(cf3);
try {
ws.addCell(vonbCell);
} catch (Exception e) {
e.printStackTrace();
}
*/
}
start += channelPart;
}
return ws;
}
再贴个简洁的POI代码：
public String modifyExcel(){
String infilepath = "c:/AA.xls";
String filePath = "c:/Sales_Daily_java/";
String fileNane = "InputTemplate-Sales_Daily_Draft.xls";
String targetfile = filePath + fileNane;
File file = new File(targetfile);
//判断文件夹是否存在，不存在就创建
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
//创建新工作簿
HSSFWorkbook wb = null;//创建97-03版excel，如果是07-10版，则XSSFWorkbook wb = null;
NPOIFSFileSystem fs = null;
try {
fs = new NPOIFSFileSystem(new File(infilepath));
} catch (Exception e1) {
e1.printStackTrace();
return "读取excel模板文件失败！";
}
try {
wb = new HSSFWorkbook(fs.getRoot(),true);
} catch (Exception e1) {
e1.printStackTrace();
return "创建excel文件失败！";
}
//读取第三张工作表,PP_DailyInput
Sheet spp = wb.getSheetAt(2);
//读取第四张工作表,NP_DailyInput
Sheet snp = wb.getSheetAt(3);
spp = modifySheet(spp,"PP"); //对PP_DailyInput修改内容
snp = modifySheet(snp,"NP"); //对NP_DailyInput修改内容
spp.protectSheet("XDFZALDEXCAVPGS");//设置PP_DailyInput Sheet的密码保护
snp.protectSheet("XDFZALDEXCAVPGS");//设置NP_DailyInput Sheet的密码保护
snp.setSelected(false);
//读取第二张的工作表，设置选中状态
Sheet s1 = wb.getSheetAt(1);
s1.setSelected(true);
//输出流文件，关闭流
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(targetfile);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
return "excel文件生成失败！";
}
try {
fs.close(); //一定要比上面的后关闭，不然write的时候会抛异常，文件写出不了
} catch (IOException e1) {
e1.printStackTrace();
}
return "文件"+fileNane+"已生成在"+filePath+"下！";
}
/**
* 获取单元格的值
* @param cell
* @return
*/
private String getCellValue(Cell cell){
String result = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
result = cell.getDateCellValue().toString();
} else {
Double cellValue = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("#.#");
result = df.format(cellValue);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
result = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
default:
result ="";
}
if(StringUtils.isEmpty(result)){
result = "";
}
return result;
}
private Sheet modifySheet(Sheet ws,String pnp) {
int start = startRow;
DailySalesBean dsb = new DailySalesBean();
dsb.setPnp(pnp);
for (int j= 0 ; j< channelNum; j++){
int end = start+channelPart;
for(int i= start;i< end;i++){
Row row = ws.getRow(i);
Cell cellRefNo = row.getCell(colRefNo);
Cell cellVari = row.getCell(colVari);
Cell cellChannel = row.getCell(colChannel);
dsb.setRefno(getCellValue(cellRefNo));
dsb.setVari(getCellValue(cellVari));
dsb.setChannel(getCellValue(cellChannel));
DailySalesBean dsb2 = getDailyData(dsb);
Cell afypCell = row.getCell(colAFYP);
afypCell.setCellValue(Double.parseDouble(dsb2.getAfyp()));
Cell nbspCell = row.getCell(colNBSP);
nbspCell.setCellValue(Double.parseDouble(dsb2.getNbsp()));
Cell vonbCell = row.getCell(colVONB);
vonbCell.setCellValue(Double.parseDouble(dsb2.getVonb()));
}
start += channelPart;
}
ws.setForceFormulaRecalculation(true);//excel中有写计算公式，数据修改之后强制重新调用里面的公式自动计算
return ws;
}
现在来总结下吧，之所以最后UAT发布关头改变方法，是因为jxl生成出来的excel在用office2010或07打开的时候总弹出来俩数据丢失和数据格式丢失的alert框，这玩意给客户感觉得是多不好啊，而且客户给的excel模板比较恶心，貌似因为宏的原因隐藏了个sheet页，一打开的时候下面的sheet页切换不灵光，而且原有的sheet设置的tab颜色也丢了。如果用office2003打开，那就是直接都打不开了，一打开就直接excel停止工作。琢磨了三四天，各种资料查下来都说是excel各种版本不兼容之类的问题，大家混乱的解决方案下来我也没解决问题。痛心之下抱着死马当活马医的心态，在发布UAT的前一天晚上查询poi的相关资料，对比了下jxl和poi的不同写法，第二天一早动笔修改程序，换血用poi重写代码，那边在如火如荼地发布，解决发布过程中的各种问题，我这边淡定地写着代码，终于赶在他们解决完问题之前写完了代码，中午写完，测试的时候，心情那个忐忑啊，要是还不行，我估计就得撞墙去了。颤抖着手，咽着快餐，启动服务器，测试，生成，生成的excel完美，和模板一模一样，激动的我呀，小心肝都快飞出来了。没有alert框，sheet切换正常，tab颜色也在，perfect!!!经此一事，忠实拥戴poi，看了下poi的api，齐全的，各种office操作的东西，poi都有相应的方法可供调用。
参考文献：http://poi.apache.org/spreadsheet/quick-guide.html#Iterator，著名的Busy Developers' Guide to HSSF and XSSF Features，新手必看，比看API块
扫一扫
408
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
