Java代码生成和解析xls文件

使用poi来生成xls文件：引用的包poi-3.2.final.jar;

要了解的类HSSFWorkbook,HSSFSheet,HSSFRow,HSSFCell

HSSFWorkbook workbook=new HSSFWorkbook();得到一个Excel文件的引用

HSSFSheet sheet=workbook.createSheet();得到一个sheet,还有HSSFSheet sheet=workbook.createSheet("sheet1");方法可以设置Sheet的名称。

然后我们可以得到一行HSSFRow row=sheet.createRow(rowNum);

最后我们可以得到的是HSSFCell cell=row.create(cellNum);在Cell中还有一个类型的问题，一定要注意



下面是一个从数据库里面读取数据生成文件的方法

public void testQuery() {
Transaction tx = null;
tx = session.beginTransaction();
Query query = session.createQuery("from Tid");
List<Tid> list = query.list();
tx.commit();
int k = 0;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");

HSSFRow row = sheet.createRow(k);
k++;

//得到一个HSSFCell对象
HSSFCell cell1 = row.createCell(0);

//首先要设置HSSFCell的类型，一共有五种CELL_TYPE_STRING,CELL_TYPE_NUMBERIC,CELL_TYPE_FORMULA,

//CELL_TYPE_BLANK,CELL_TYPE_BOOLEAN,CELL_TYPE_ERROR,共6种

//具体可以查看文档http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getCellType()
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setCellValue((new HSSFRichTextString("MER_NUM")));



HSSFCell cell2 = row.createCell(1);
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setCellValue(new HSSFRichTextString("TML_NUM"));



HSSFCell cell3 = row.createCell(2);
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell3.setCellValue(new HSSFRichTextString("COUNTER_NUM"));


for (Iterator<Tid> it = list.iterator(); it.hasNext();) {
Tid tid = (Tid) it.next();
HSSFRow row_ = sheet.createRow((short) k);


HSSFCell c1 = row_.createCell(0);
c1.setCellType(HSSFCell.CELL_TYPE_STRING);
c1.setCellValue((new HSSFRichTextString(tid.getMer_num())));



HSSFCell c2 = row_.createCell(1);
c2.setCellType(HSSFCell.CELL_TYPE_STRING);
c2.setCellValue(new HSSFRichTextString(tid.getTml_num()));



HSSFCell c3 = row_.createCell(2);
c3.setCellType(HSSFCell.CELL_TYPE_STRING);
c3.setCellValue(new HSSFRichTextString(tid.getCounter_num()));
k++;
}

//新建输出流对象FileOutputStream
FileOutputStream fos = null;
try {
fos = new FileOutputStream("D://T99_TID_RELA.xls");

//然后写入HSSFWorkbook中
workbook.write(fos);
fos.flush();
} catch (Exception e) { // TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (null != fos) {
try {
fos.close();
System.out.println("文件生成！");
} catch (IOException e) {
e.printStackTrace();
}
}
}
}



HSSFCell类型判断与取值的方法

public String getCellValue(HSSFCell cell)throws Exception{

int cellType=cell.getCellType();

String cellValue=null;

switch(cellType){

case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());

/*

*DecimalFormat df=new DecimalFormat("#");
*cellValue = df.format(cell.getNumericCellValue());

*DecimalFormat表示的数据格式化，有很多中用法

*/
break;
case HSSFCell.CELL_TYPE_FORMULA:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;

default:
throw new IllegalArgumentException("数据格式不对");

}

return cellValue;

}







解析一个Excel文件

public void testRead() {
HSSFWorkbook workbook = null;
DecimalFormat df = new DecimalFormat("#");
try {
workbook = new HSSFWorkbook(new FileInputStream(new File(
"D://Book1.xls")));

//POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
// 获得Sheet数
System.out
.println("===SheetsNum===" + workbook.getNumberOfSheets());
// 开始读取
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
if (null != workbook.getSheetAt(i)) {
// 获得一个Sheet
HSSFSheet sheet = workbook.getSheetAt(i);
System.out.println("共有" + sheet.getLastRowNum() + "行");
for (int rowNumOfSheet = 10; rowNumOfSheet <= sheet
.getLastRowNum(); rowNumOfSheet++) {
if (null != sheet.getRow(rowNumOfSheet)) {
// 获得一个row
HSSFRow row = sheet.getRow(rowNumOfSheet);
System.out.print("第" + rowNumOfSheet + "行 ");
// System.out.print("一行有"+row.getLastCellNum()+"单元格");
for (short cellNumOfRow = 0; cellNumOfRow < row
.getLastCellNum(); cellNumOfRow++) {

HSSFCell cell = row.getCell(cellNumOfRow);
int cellType = cell.getCellType();
switch (cellType) {
case 0:// Numberic
String strCell = df.format(cell.getNumericCellValue());
System.out.print(strCell + " ");
break;
case 1:
strCell = cell.getRichStringCellValue().getString();
System.out.print(strCell + " ");
break;
default:
System.out.println("数据格式不对！");
}
}
System.out.print("/n");
}
}
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}





工具集HSSFCellUtil http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/contrib/HSSFCellUtil.html



设置Sheet名称和单元格内容为中文

workbook.setSheetName(n,"库存",HSSFCell.ENCODING_UTF_16);

cell.setEncoding((short)1);cell.setCellValue("单位");



设置列宽和行高

sheet.setColumnWidth((short)column,(short)width);

row.setHeight((short)height);



添加区域，合并单元格

Region region=new Region((short)rowForm,(short)columnForm,(short)rowTo,(short)columnTo);

sheet.addMergedRegion(region);

// 得到该区域

sheet.getNumMergedRegion();



设置字体和内容位置

HSSFFont font=workbook.createFont();

font.setFontHeightInPoints((short) 11);//字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中

style.setRotation((short)rotation);//单元格内容的旋转的角度

HSSFDataFormat df = wb.createDataFormat();

style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式

cell.setCellFormula(string);//给单元格设公式

style.setRotation((short)rotation);//单元格内容的旋转的角度

cell.setCellStyle(style)



常用的单元格边框格式

public static HSSFCellStyle getCellStyle(short type) {

HSSFWorkbook workbook =new HSSFWorkbook();

HSSFCellStyle style = wb.createCellStyle();

style.setBorderBottom(type);//下边框

style.setBorderLeft(type);//左边框

style.setBorderRight(type);//右边框

style.setBorderTop(type);//上边框

return style;

}

虚线HSSFCellStyle.BORDER_DOTTED

实线HSSFCellStyle.BORDER_THIN



最后是插入图片

/先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray

ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));

ImageIO.write(bufferImg,"jpg",byteArrayOut);

//读进一个excel模版

FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");

fs = new POIFSFileSystem(fos);

//创建一个工作薄

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);

patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));