java操作execl文件（2003与2007不兼容问题）

package com.huawei.bss.execlComm;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ProcessXSL {
HSSFWorkbook wb = new HSSFWorkbook();
String workbookFileName = "d:/资源池人员跟踪_20120911.xlsx";
protected final Log log = LogFactory.getLog(getClass());

public ProcessXSL() {
}

/**
* 创建 Execl 文件
*
* @param wb
*            HSSFWorkbook
* @param exportInfo
*            BaseDataExportInfo
*/
public void createWorkBookSheet(BaseDataExportInfo exportInfo) {

Vector cellList = null;
if (exportInfo.getWorkbookFileName() != null)
workbookFileName = exportInfo.getWorkbookFileName() + ".xlsx";
try {

HSSFSheet sheet = wb.createSheet(exportInfo.getSheetIndex() + "");
wb.setSheetName(exportInfo.getSheetIndex(), exportInfo
.getSheetName());

/** 设置列宽 */

for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
sheet.setColumnWidth(i,  7000);
} else {
sheet.setColumnWidth(i,  4000);
}
}

/** 合并单元格 */
//Region(int rowFrom, short colFrom, int rowTo, short colTo)与CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
sheet.addMergedRegion(new CellRangeAddress(0,  0, 0, (exportInfo.getTableHead().size() - 1)));

/** 表 标题 */
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short) 500); // 设置行高

HSSFFont titleFont = wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight((short) 20);
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中

row = this.createCell(row, (short) 0, titleStyle, exportInfo
.getSheetTitle());

/** 表头 */
HSSFFont headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBoldweight((short) 20);

HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

HSSFRow row2 = sheet.createRow((short) 1);
row2.setHeight((short) 400); // 设置行高

for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
row2 = this.createCell(row2, (short) i, headStyle, exportInfo
.getTableHead().get(i));
}
/** 表体 */
HSSFFont font = wb.createFont();
/** 设置字体样式 */
font.setFontName("宋体");
HSSFCellStyle cellStyle = wb.createCellStyle();
if (exportInfo.getCellDataFomat() != null
&& !"".equals(exportInfo.getCellDataFomat())) {
short stringFormat = HSSFDataFormat.getBuiltinFormat(exportInfo
.getCellDataFomat());
if (stringFormat != -1) {
cellStyle.setDataFormat(stringFormat);
}
}
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左

for (int i = 0; i < exportInfo.getRowList().size(); i++) {
cellList = (Vector) exportInfo.getRowList().get(i);
HSSFRow row3 = sheet.createRow((short) i + 2);
row3.setHeight((short) 300); // 设置行高
for (int j = 0; j < cellList.size(); j++) {
row3 = this.createCell(row3, (short) j, cellStyle, cellList
.get(j));
}
}

} catch (Exception ex) {
log.info("error while create work book sheet ", ex);
}

}

/**
* 创建 包含多个bookSheet 的 Execl 文件
*
* @param wb
*            HSSFWorkbook
* @param exportInfo
*            BaseDataExportInfo
*/
public void createMoreWorkBookSheet(BaseDataExportInfo exportInfo) {

if (exportInfo.getWorkbookFileName() != null)
workbookFileName = exportInfo.getWorkbookFileName() + ".xls";
try {

for (int n = 0; n < exportInfo.getRowList().size(); n++) {

// JOptionPane.showMessageDialog(null,"第" +(n+1)+ "分页！");

HSSFSheet sheet = wb.createSheet(n + "");
wb.setSheetName(n, exportInfo.getSheetName()+ " " + (n + 1));

/** 设置列宽 */
// 设置第二列和倒数第二类的宽度为7000，其它列宽度为4000
for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
sheet.setColumnWidth( i,  7000);
} else {
sheet.setColumnWidth( i,  4000);
}
}

/** 合并单元格 */
// 生成标题行, Region的四个参数分别对应 (x1,y1,x2,y2)
sheet.addMergedRegion(new Region(0, (short) 0, 0,
(short) (exportInfo.getTableHead().size() - 1)));

/** 表标题 */
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short) 500); // 设置行高

// 格式化表标题
HSSFFont titleFont = wb.createFont();
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight((short) 20);
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中

row = this.createCell(row, (short) 0, titleStyle, exportInfo
.getSheetTitle());

/** 表头 */
// 格式化表头
HSSFFont headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 12);
headFont.setBoldweight((short) 20);

HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

HSSFRow row2 = sheet.createRow((short) 1);
row2.setHeight((short) 400); // 设置行高

for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
row2 = this.createCell(row2, (short) i, headStyle,
exportInfo.getTableHead().get(i));
}

/** 表体 */
// 格式化表体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
HSSFCellStyle cellStyle = wb.createCellStyle();
if (exportInfo.getCellDataFomat() != null
&& !"".equals(exportInfo.getCellDataFomat())) {
short stringFormat = HSSFDataFormat
.getBuiltinFormat(exportInfo.getCellDataFomat());
if (stringFormat != -1) {
cellStyle.setDataFormat(stringFormat);
}
}
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左

for (int i = 0; i < 98; i++) {
if (i < exportInfo.getRowList().size()) {
if ((i + n * 100) >= exportInfo.getRowList().size())
break;

Vector cellList = null;
cellList = (Vector) exportInfo.getRowList().get(
i + n * 100);
HSSFRow row3 = sheet.createRow((short) i + 2);
row3.setHeight((short) 300); // 设置行高
for (int j = 0; j < cellList.size(); j++) {
row3 = this.createCell(row3, (short) j, cellStyle,
cellList.get(j));
}

}
}

if ((100 * (n + 1) - 1) >= exportInfo.getRowList().size())
break;
}
} catch (Exception ex) {
log.info("error while create work book sheet ", ex);
}

}

/**
* 创建单元格
*
*
* @param row
*            HSSFRow
* @param cellIndex
*            short
* @param cellStyle
*            HSSFCellStyle
* @param cellValue
*            Object
* @return HSSFRow
*/
public HSSFRow createCell(HSSFRow row, int cellIndex,
HSSFCellStyle cellStyle, Object cellValue) {
HSSFCell cell = row.createCell( cellIndex);
try {
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}

if (cellValue == null) {
cell.setCellValue("");
} else if (cellValue instanceof Boolean) {
cell.setCellValue(((Boolean) cellValue).booleanValue());
} else if (cellValue instanceof String) {
cell.setCellValue((String.valueOf(cellValue)));
} else if (cellValue instanceof Date) {
cell.setCellValue((Date) cellValue);
} else {
cell.setCellValue("");
}

// log.info("this cell value is " + cellValue);
} catch (Exception ex) {
log.error("error while execut create cell ", ex);
}
return row;
}

/**
* 读取Excel 表
*
*
* @param aSheet
*            HSSFSheet
* @throws Exception
* @return List
*/
public List<Object> readWeekBookSheet(Sheet aSheet) throws Exception {
List<Object> rowList = new ArrayList<Object>();
Vector<String> rowVector = null;
int rowNum = 1;
int cellNum = 1;
int maxCellNum = aSheet.getRow(1).getLastCellNum();

// HSSFCellStyle cellStyle = wb.createCellStyle();
// short stringFormat = HSSFDataFormat.getBuiltinFormat("@"); //请参考HSSFDataFormat内置的数据类型，例如"@"代表文本
// cellStyle.setDataFormat(stringFormat);

for (int rowNumOfSheet = 2; rowNumOfSheet <= aSheet.getPhysicalNumberOfRows(); rowNumOfSheet++) {
rowNum = rowNumOfSheet;
if (null != aSheet.getRow(rowNumOfSheet))
{
Row aRow = aSheet.getRow(rowNumOfSheet);
rowVector = new Vector<String>();

// System.out.println("==============================="+aRow.getCell(2).getCellType());


for (short cellNumOfRow = 0; cellNumOfRow <= maxCellNum; cellNumOfRow++)
{
cellNum = cellNumOfRow;
if (null != aRow.getCell(cellNumOfRow))
{
Cell aCell = aRow.getCell(cellNumOfRow);
int cellType = aCell.getCellType();

switch (cellType) {
case HSSFCell.CELL_TYPE_NUMERIC: // 整形
if (HSSFDateUtil.isCellDateFormatted(aCell))
{
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
rowVector.add(cellNumOfRow, format.format(aCell.getDateCellValue()));
}
else
{
rowVector.add(cellNumOfRow, String
.valueOf(aCell.getNumericCellValue()));
}

break;
case HSSFCell.CELL_TYPE_STRING: // 字符串型
rowVector.add(cellNumOfRow, aCell
.getStringCellValue().trim());
break;
case HSSFCell.CELL_TYPE_FORMULA: // double 型

rowVector.add(cellNumOfRow, String.valueOf(aCell.getStringCellValue()));
break;
case HSSFCell.CELL_TYPE_BLANK: // 空字符

rowVector.add(cellNumOfRow, "");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔型

rowVector.add(cellNumOfRow, String
.valueOf(aCell.getBooleanCellValue()));
break;
default:
System.out.println("gggggggggggggggggggggggg"+cellType);
rowVector.add(cellNumOfRow, "");
}
}
else
{
rowVector.add(cellNumOfRow, "");
}
}


rowList.add(rowVector);
}
}
return rowList;
}

/**
* 写Execl 文件
*
* @param wb
*            HSSFWorkbook
* @param outPutStream
*            OutputStream
*/
public void writeWorkBook(HttpServletResponse response) {
try {

OutputStream outputStream = new BufferedOutputStream(response
.getOutputStream());
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=\""
+ new String(workbookFileName.getBytes(), response
.getCharacterEncoding()) + "\"");
wb.write(outputStream);
outputStream.close();
} catch (Exception ex) {
log.error("error while create work book ", ex);
}

}
/**
* 修改properties文件
* @param properties
* @param pathFile
*/
public static void modifyProperties(Properties properties,String pathFile)
{
    InputStream inputStream = null;
    OutputStream  fos = null;
        Properties tempProper = new Properties();
        File file = new File(pathFile);
        try
        {
            inputStream = new FileInputStream(file);
            tempProper.load(inputStream);
            fos = new FileOutputStream(file);
            tempProper.setProperty("userName", properties.getProperty("userName"));
            tempProper.setProperty("passwd", properties.getProperty("passwd"));
            tempProper.store(fos, null);
        }
        catch (IOException e)
            {
                System.err.println(e.getStackTrace());
            }
        finally
        {
            try
            {
                if(null != fos)
                {
                    fos.close();
                }
                if(null != inputStream)
                {
                    inputStream.close();
                }
            }
            catch (IOException e)
            {
                System.err.println(e.getStackTrace());
            }
        }
   
}

/**
* 读取Peoperties
* @param args
*/

/**
     * 得到属性文件实例
     */
    public static Properties getPropertiesByFile(String filePath)
    {
        InputStream inputStream = null;
        Properties properties = new Properties();
        try
        {
            File file = new File(filePath);
            inputStream = new FileInputStream(file);
            properties.load(inputStream);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        finally
        {
            try
            {
                inputStream.close();
            }
            catch (IOException e)
            {
            System.out.println(e.getMessage());
            }
        }
        return properties;
    }

public static void main(String[] args)
{
//需要导入poi-bin-3.8-20120326.zip包
ProcessXSL xsl =new ProcessXSL();
Workbook workBook = null;
try {
        try {
workBook = new XSSFWorkbook(new FileInputStream("d:/资源池人员跟踪_20120911.xlsx")); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(
"d:/资源池人员跟踪_20120911.xlsx")); // 支持2003及以前
}
Sheet aSheet = workBook.getSheetAt(0);
List<Object> list = xsl.readWeekBookSheet(aSheet);
System.out.println("====================================="
+ list.size());
for (Object object : list)
{
System.out.println(object);
}
}
catch (Exception e1)
{
e1.printStackTrace();
} 
}
}