POI模版导出excel

package com;

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");
									// 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.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.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.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.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.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.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.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.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.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.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.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.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.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.setCellValue("出票保证信息");
											}
										}
										break;

									}

									HSSFCell aCell = aRow.getCell(cellNumOfRow);
									int cellType = aCell.getCellType();
									if (cellType == 0) {// number型
										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);
			}
		}
	}
}