poi包 EXCEL模板读取填数据并导出以及在模板某行插入新的行

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
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.hssf.util.Region;

/**
*   附件中是模板
* poi-2.5.1-final-20040804.jar
*
* java读取excel文件
*
* 一个Excel文件的层次：Excel文件-> 工作表-> 行-> 单元格 对应到POI中，为：workbook-> sheet-> row-> cell
*
*/
public class testInsertCell {
public static String outputFile = "d:\\shengcheng.xls "; // 生成的文件
public static String fileToBeRead = "d:\\aa.xls ";// 模板

/**
*
* 读取excel，遍历各个小格获取其中信息，
*
*
* 注意： 1.sheet， 以0开始，以workbook.getNumberOfSheets()-1结束 2.row，
* 以0开始(getFirstRowNum)，以getLastRowNum结束 3.cell，
* 以0开始(getFirstCellNum)，以getLastCellNum结束, 结束的数目不知什么原因与显示的长度不同，可能会偏长
*
*
*/
public void readExcel() {

try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
fileToBeRead));
// workbook.getNumberOfSheets());//获取sheet数
for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
if (null != workbook.getSheetAt(numSheets)) {
HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
System.err.println(aSheet.getFirstRowNum());
for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
.getLastRowNum(); rowNumOfSheet++) {
if (null != aSheet.getRow(rowNumOfSheet)) {
HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
for (short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
if (null != aRow.getCell(cellNumOfRow)) {


List list =new ArrayList();
list.add("1");
list.add("2");
// list.add("3");
// list.add("4");
// list.add("5");
// list.add("6");
// list.add("7");
// list.add("8");
// list.add("9");
// list.add("10");
// rowNumOfSheet 表示动态生成那一行的上一行的num，例如11行是模板中的出票保证信息
while (rowNumOfSheet == 10&&cellNumOfRow==0) {
for (int i = list.size()-1; i >0; i--) {
int newRow=11;
int rows = 1;//设定插入几行
aSheet.shiftRows(newRow, aSheet.getLastRowNum(), rows);
HSSFRow sourceRow =aSheet.getRow(newRow);

aSheet.addMergedRegion(new Region(newRow,(short) 2, newRow,(short) 2)); //
HSSFCell cew2 =sourceRow.createCell((short) 2);
addSyteml(workbook,aSheet,newRow,newRow,2,2);
cew2.setEncoding(HSSFCell.ENCODING_UTF_16);
cew2.setCellValue("保证人名称");

aSheet.addMergedRegion(new Region(newRow,(short) 3, newRow,(short) 4)); //
HSSFCell cew3 =sourceRow.createCell((short) 3);
addSyteml(workbook,aSheet,newRow,newRow,3,4);
cew3.setEncoding(HSSFCell.ENCODING_UTF_16);
cew3.setCellValue(list.get(i).toString());

aSheet.addMergedRegion(new Region(newRow,(short) 5, newRow,(short) 6)); //
HSSFCell cew5 =sourceRow.createCell((short) 5);
addSyteml(workbook,aSheet,newRow,newRow,5,6);
cew5.setEncoding(HSSFCell.ENCODING_UTF_16);
cew5.setCellValue("保证人地址");

aSheet.addMergedRegion(new Region(newRow,(short) 7, newRow,(short) 12)); //
HSSFCell cew11 =sourceRow.createCell((short) 7);
addSyteml(workbook,aSheet,newRow,newRow,7,12);
cew11.setEncoding(HSSFCell.ENCODING_UTF_16);
cew11.setCellValue(list.get(i).toString());

aSheet.addMergedRegion(new Region(newRow,(short) 13, newRow,(short) 15)); //
HSSFCell cew12 =sourceRow.createCell((short) 13);
addSyteml(workbook,aSheet,newRow,newRow,13,15);
cew12.setEncoding(HSSFCell.ENCODING_UTF_16);
cew12.setCellValue("保证日期");

aSheet.addMergedRegion(new Region(newRow,(short) 16, newRow,(short) 18)); //
HSSFCell cew13 =sourceRow.createCell((short) 16);
addSyteml(workbook,aSheet,newRow,newRow,16,18);
cew13.setEncoding(HSSFCell.ENCODING_UTF_16);
cew13.setCellValue(list.get(i).toString());

if (i==1){
aSheet.addMergedRegion(new Region(10,(short) 0, 10+list.size()-1,(short) 1)); //
HSSFCell cew =sourceRow.createCell((short) 0);
addSyteml(workbook,aSheet,10,10+list.size()-1,0,1);
cew.setEncoding(HSSFCell.ENCODING_UTF_16);
cew.setCellValue("出票保证信息");
}
}
break;

}
List lis1t =new ArrayList();
lis1t.add("111");
lis1t.add("222");
lis1t.add("333");
lis1t.add("444");
lis1t.add("555");

//当出票保证信息为0条的时候下面的标题合并出问题,加此判断
int size=list.size();
if(size==0){
size=1;
}

while (rowNumOfSheet == 17+size&&cellNumOfRow==0) {
for (int i = lis1t.size()-1; i >0; i--) {
int newRow=17+size;
int rows = 1;//设定插入几行
aSheet.shiftRows(newRow, aSheet.getLastRowNum(), rows);
HSSFRow sourceRow =aSheet.getRow(newRow);

aSheet.addMergedRegion(new Region(newRow,(short) 2, newRow,(short) 2)); //
HSSFCell cew2 =sourceRow.createCell((short) 2);
addSyteml(workbook,aSheet,newRow,newRow,2,2);
cew2.setEncoding(HSSFCell.ENCODING_UTF_16);
cew2.setCellValue("保证人名称");

aSheet.addMergedRegion(new Region(newRow,(short) 3, newRow,(short) 4)); //
HSSFCell cew3 =sourceRow.createCell((short) 3);
addSyteml(workbook,aSheet,newRow,newRow,3,4);
cew3.setEncoding(HSSFCell.ENCODING_UTF_16);
cew3.setCellValue(lis1t.get(i).toString());

aSheet.addMergedRegion(new Region(newRow,(short) 5, newRow,(short) 6)); //
HSSFCell cew5 =sourceRow.createCell((short) 5);
addSyteml(workbook,aSheet,newRow,newRow,5,6);
cew5.setEncoding(HSSFCell.ENCODING_UTF_16);
cew5.setCellValue("保证人地址");

aSheet.addMergedRegion(new Region(newRow,(short) 7, newRow,(short) 12)); //
HSSFCell cew11 =sourceRow.createCell((short) 7);
addSyteml(workbook,aSheet,newRow,newRow,7,12);
cew11.setEncoding(HSSFCell.ENCODING_UTF_16);
cew11.setCellValue(lis1t.get(i).toString());

aSheet.addMergedRegion(new Region(newRow,(short) 13, newRow,(short) 15)); //
HSSFCell cew12 =sourceRow.createCell((short) 13);
addSyteml(workbook,aSheet,newRow,newRow,13,15);
cew12.setEncoding(HSSFCell.ENCODING_UTF_16);
cew12.setCellValue("保证日期");

aSheet.addMergedRegion(new Region(newRow,(short) 16, newRow,(short) 18)); //
HSSFCell cew13 =sourceRow.createCell((short) 16);
addSyteml(workbook,aSheet,newRow,newRow,16,18);
cew13.setEncoding(HSSFCell.ENCODING_UTF_16);
cew13.setCellValue(lis1t.get(i).toString());

if (i==1){//当是循环中的最后一条数据的时候将总标题嵌入到最前面
aSheet.addMergedRegion(new Region(newRow-1,(short) 0, newRow+lis1t.size()-2,(short) 1)); //
HSSFCell cew =sourceRow.createCell((short) 0);
addSyteml(workbook,aSheet,newRow-1,newRow+lis1t.size()-2,0,1);
cew.setEncoding(HSSFCell.ENCODING_UTF_16);
cew.setCellValue("出票保证信息");
}
}
break;

}

HSSFCell aCell = aRow.getCell(cellNumOfRow);
int cellType = aCell.getCellType();
if (cellType == 0) {//number型
aCell.setEncoding(HSSFCell.ENCODING_UTF_16);// 中文处理 
int NumericCellValue = (int) aCell.getNumericCellValue();//得到模板中值所在cell中的数字
switch (NumericCellValue) {
case 1:  //出票日期
aCell.setCellValue("111");
break;
case 2:  //票据号码
aCell.setCellValue("111");
break;
case 3://出 票 人全 称
aCell.setCellValue("111");
break;
case 4://出票人账号
aCell.setCellValue("111");
break;
}

}
FileOutputStream fOut = new FileOutputStream(
outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
}
}

}

}

}
}
} catch (Exception e) {
System.out.println("ReadExcelError " + e);
}

}

public static void main(String[] args) {
System.out.println("开始 ");
testInsertCell poi = new testInsertCell();
poi.readExcel();
System.out.println("结束 ");

}

/**
* 设置动态数据的样式
*
*/
private static void addSyteml(HSSFWorkbook workbook,HSSFSheet aSheet,int rowFrom,int rowTo,int columnFrom,int columnTo){
HSSFCellStyle cloneStyle = workbook.createCellStyle();
cloneStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cloneStyle.setBorderLeft((short)0);
cloneStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cloneStyle.setBorderBottom((short)0);
cloneStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        for(int i=rowFrom;i<=rowTo;i++){
            HSSFRow row_temp = aSheet.getRow(i);
            for(int j=columnFrom;j<=columnTo;j++){
                HSSFCell cell_temp = row_temp.getCell((short)j);
                if(cell_temp ==null ){
                    cell_temp = row_temp.createCell((short)j);
                }
                cell_temp.setCellStyle(cloneStyle);
            }
        }
}
}

评论

2 楼 tl418 2012-06-27  

   

1 楼 tl418 2012-06-27  

rowNumOfSheet